Entity Framework with 'Get' Stored Procedure that returns Entities

Question

I am attempting to execute a stored procedure that returns data with exactly the same columns as that of a table Entity I have in my project. I set the 'Returns a Collection Of' property in the 'Add Function Import' dialog to my entity type.

I get a NullReferenceException error when executing the stored procedure and on further digging it seems that it is because the 'EntityKey' property is missing. Is there anything I can do to tell it to ignore those special properties of the Entity?

I already have a partial class for that entity with '[ScaffoldColumn(false)]' but that doesn't seem to matter.

Cheers, Nick

EDIT:

Here is the stack trace:

[NullReferenceException: Object reference not set to an instance of an object.] System.Data.EntityKey.AddHashValue(Int32 hashCode, Object keyValue) +36 System.Data.EntityKey.GetHashCode() +7696654 System.Collections.Generic.GenericEqualityComparer1.GetHashCode(T obj) +23 System.Collections.Generic.Dictionary2.FindEntry(TKey key) +70 System.Collections.Generic.Dictionary2.TryGetValue(TKey key, TValue& value) +17 System.Data.Objects.ObjectStateManager.TryGetEntityEntry(EntityKey key, EntityEntry& entry) +111 System.Data.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly(Func2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet) +102 lambda_method(Closure , Shaper ) +460 System.Data.Common.Internal.Materialization.Coordinator1.ReadNextElement(Shaper shaper) +170 System.Data.Common.Internal.Materialization.SimpleEnumerator.MoveNext() +84 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +406 System.Linq.Enumerable.ToList(IEnumerable1 source) +58 TWAgencySpend.Controllers.ReportsController.Advertising(Nullable1 agencyId, Nullable1 businessUnitId) in C:\dasdsd\Controllers\ReportsController.cs:72 lambda_method(Closure , ControllerBase , Object[] ) +190 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +51 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +409 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +52 System.Web.Mvc.<>c__DisplayClassd.b__a() +127 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +436 System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +61 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) +305 System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +830 System.Web.Mvc.Controller.ExecuteCore() +136 System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +111 System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +39 System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__4() +65 System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +44 System.Web.Mvc.Async.<>c__DisplayClass81.b__7(IAsyncResult _) +42 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +141 System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult, Object tag) +54 System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult, Object tag) +40 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +52 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +38 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8836913 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

Updated: Here is the item in the SSDL for the function:

    <Function Name="GetAgencyReport" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
      <Parameter Name="AgencyId" Type="int" Mode="In" />
      <Parameter Name="Year" Type="int" Mode="In" />
    </Function>

and here is the call:

reportsViewModel.AgencyReportData = twAgencySpendEntities.GetAgencyReport(agencyId.Value, 2010).ToList();

and here is the key for my Entity:

<EntityType Name="AdvertisingData">
  <Key>
    <PropertyRef Name="AgencyId" />
    <PropertyRef Name="BusinessUnitId" />
    <PropertyRef Name="Month" />
    <PropertyRef Name="Year" />
  </Key>
  <Property Name="AgencyId" Type="int" Nullable="false" />
  <Property Name="BusinessUnitId" Type="int" Nullable="false" />
  <Property Name="Month" Type="int" Nullable="false" />
  <Property Name="Year" Type="int" Nullable="false" />
  ...

Do you have a piece of the code? Because most of the time this will lead to a not instantiated object. — pipelinecache
How is the key mapped in EDMX? I.e., what is the <Key> value in SSDL for this proc? — Craig Stuntz
FunctionImport is CSDL, not SSDL. The SSDL element is Function, and the Key element is on the corresponding entity in CSDL -- in your case, AdvertisingData. The function must return the unique key. — Craig Stuntz
Updated to show the Function and Key elements in the EDMX. This is more to do with the EntityKey property though isn't it? — Nick Reeve

Craig Stuntz Craig Stuntz · Accepted Answer · 2010-05-06T12:34:08

First, the reason why the framework is trying to dereference the EntityKey is that the MergeOption property for your query is set to AppendOnly. No great surprise there, as AppendOnlyis the default. With AppendOnly, the framework will examine every entity loaded into memory and try to fixup the references such that:

The same entity can never be loaded into memory twice in two different locations, even if you query it from the database twice.
Any entity which refers to that entity will always point at the same reference.

Entities are identified via the EntityKey property, which accounts for the reference to this property in the call stack you post.

So one option would be to change the MergeOption on the query you're running to NoTracking. This is often the best choice for a read-only query. Just be advised that the entities returned cannot be used as referenced entities when you are editing other entities.

The other option would be to figure out why the EntityKey is null. I still don't see the full EDMX for your proc, but the key in your CSDL is sufficiently complex that I presume it just doesn't map to the entity quite right, so the EF cannot figure out how to construct the correct EntityKey while it is materializing entities from your proc.

Entity Framework with 'Get' Stored Procedure that returns Entities

1 Answers