Monday, February 13, 2012

WCF Data Services: Select query ($select) failed for certain Column combinations with error message “Value cannot be null”

You created a WCF based data (OData) service and trying to select certain columns by using $select operator.

Error Stack Trace
at System.Data.Services.WebUtil.CheckArgumentNull[T](T value, String parameterName)
at System.Data.Services.Internal.ProjectedWrapper.set_PropertyNameList(String value)
at lambda_method(Closure , Shaper )
at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Data.Services.Internal.ProjectedWrapper.EnumeratorWrapper.MoveNext()
at System.Data.Services.DataService`1.SerializeResponseBody(RequestDescription description, IDataService dataService)
at System.Data.Services.DataService`1.HandleNonBatchRequest(RequestDescription description)
at System.Data.Services.DataService`1.HandleRequest()

Root Cause
  1. If your table do not have primary key defined then by default entity designer creates a primary key in entity conceptual model by selecting all the null columns as primary key. In this situtation you must need to select  all the columns $select query that are part of primary key combination.
  2. This problem mostly happens when view is used as data source because views do not have primary key.
  1. If your table do not have primary key defined
    1. Easiest is to create a primary key column with auto number (identity)
    2. Alternatively instead of directly using table use DefiningQuery tag in edmx and explicitly use  ROWNUMBER to generate primary column.
    3. If above to does not works for you then look for any column that is not null and have high possibly of uniqueness and mark it as primary key in entity conceptual model.
    4. If none of above is suitable then use minimal group of keys that can be used as primary key at entity level.
  2. If view is being used as data source
    1. You can use any of solution 2-4 explained above.
Best Practices
  • There must be a primary key in a table.
  • In case edmx create from view, you must manually fine tune edmx to reduce number of columns being used as primary key in entity model.
	  <EntityType Name="GradeReport">
    			<PropertyRef Name="CourseID" />
    			<PropertyRef Name="FirstName" />
    			<PropertyRef Name="LastName" />
  •  If possible there should be only one column should be selected as entity key.

No comments:

Post a Comment