Using EF6 / VS 2013, I have created an ADO.NET Entity Data Model. This model uses three views, stored in SQL Server, to prepare the data in a suitable form from an existing system. One for Users (vw_Users), one for Roles (vw_Roles) and a cross-reference/association view (vw_UserRoles).
When the User and Role entities are mapped, the application compiles and executes without issue. However, when I add the Association between User and Role and map it to the vw_UserRoles view, I get an EntityCommandCompilationException with an inner exception, KeyNotFoundException {"The given key was not present in the dictionary."}.
An exception of type 'System.Data.Entity.Core.EntityCommandCompilationException' occurred in mscorlib.dll but was not handled in user code
Additional information: An error occurred while preparing the command definition. See the inner exception for details.
To me it just feels like a bug but I am new to a lot of the EF and it could be to do with connecting to views. Out of interest I implemented this trick to treat the view as a table but it made no difference.
UPDATE #1: I created a new web application with a new SQL Server database. I created the underlying tables with relationships and a view for each table. When I create a entity model based on the tables, I have no problems -- even with the association. When I created a model from the views, I get the KeyNotFoundException. Note: I have corrected the keys and added and mapped the association on the second, view-based model. You can download the project from here - http://sdrv.ms/1eZ7ytp
UPDATE #2: I have logged this as an issue with MS @ Codeplex - https://entityframework.codeplex.com/workitem/1777
I'm still stumped on this one and am really keen to complete this using a series of views. Any thoughts? Please....?
Full Exception:
System.Data.Entity.Core.EntityCommandCompilationException was unhandled by user code
HResult=-2146232005
Message=An error occurred while preparing the command definition. See the inner exception for details.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver)
at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__a()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__9()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Lazy`1.get_Value()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__2[TResult](IEnumerable`1 sequence)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)
at System.Data.Entity.Internal.Linq.InternalSet`1.FindInStore(WrappedEntityKey key, String keyValuesParamName)
at System.Data.Entity.Internal.Linq.InternalSet`1.Find(Object[] keyValues)
at System.Data.Entity.DbSet`1.Find(Object[] keyValues)
at Mind.Mindlink.Portal.Controllers.HomeController.Index() in c:\Users\James\Documents\Visual Studio 2013\Projects\Mind.Mindlink.Portal\Mind.Mindlink.Portal\Controllers\HomeController.cs:line 13
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__36(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
at System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3c()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass45.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3e()
InnerException: System.Collections.Generic.KeyNotFoundException
HResult=-2146232969
Message=The given key was not present in the dictionary.
Source=mscorlib
StackTrace:
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at System.Data.Entity.Core.Mapping.ViewGeneration.Structures.MemberDomainMap.GetDomainInternal(MemberPath path)
at System.Data.Entity.Core.Mapping.ViewGeneration.Structures.MemberDomainMap.GetDomain(MemberPath path)
at System.Data.Entity.Core.Mapping.ViewGeneration.QueryRewriting.FragmentQueryKB.CreateIsOfTypeCondition(MemberPath currentPath, IEnumerable`1 derivedTypes, MemberDomainMap domainMap)
at System.Data.Entity.Core.Mapping.ViewGeneration.QueryRewriting.FragmentQueryKB.CreateAssociationConstraints(EntitySetBase extent, MemberDomainMap domainMap, EdmItemCollection edmItemCollection)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewgenContext..ctor(ViewTarget viewTarget, EntitySetBase extent, IList`1 extentCells, CqlIdentifiers identifiers, ConfigViewGenerator config, MemberDomainMap queryDomainMap, MemberDomainMap updateDomainMap, StorageEntityContainerMapping entityContainerMapping)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewGenerator.CreateViewgenContext(EntitySetBase extent, ViewTarget viewTarget, CqlIdentifiers identifiers)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewGenerator.GenerateDirectionalViewsForExtent(ViewTarget viewTarget, EntitySetBase extent, CqlIdentifiers identifiers, KeyToListMap`2 views)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewGenerator.GenerateDirectionalViews(ViewTarget viewTarget, CqlIdentifiers identifiers, KeyToListMap`2 views)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewGenerator.GenerateAllBidirectionalViews(KeyToListMap`2 views, CqlIdentifiers identifiers)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewgenGatekeeper.GenerateViewsFromCells(List`1 cells, ConfigViewGenerator config, CqlIdentifiers identifiers, StorageEntityContainerMapping containerMapping)
at System.Data.Entity.Core.Mapping.ViewGeneration.ViewgenGatekeeper.GenerateViewsFromMapping(StorageEntityContainerMapping containerMapping, ConfigViewGenerator config)
at System.Data.Entity.Core.Mapping.StorageMappingItemCollection.ViewDictionary.SerializedGenerateViews(StorageEntityContainerMapping entityContainerMap, Dictionary`2 resultDictionary)
at System.Data.Entity.Core.Mapping.StorageMappingItemCollection.ViewDictionary.SerializedGetGeneratedViews(EntityContainer container)
at System.Data.Entity.Core.Common.Utils.Memoizer`2.<>c__DisplayClass2.<Evaluate>b__0()
at System.Data.Entity.Core.Common.Utils.Memoizer`2.Result.GetValue()
at System.Data.Entity.Core.Common.Utils.Memoizer`2.Evaluate(TArg arg)
at System.Data.Entity.Core.Mapping.StorageMappingItemCollection.ViewDictionary.GetGeneratedView(EntitySetBase extent, MetadataWorkspace workspace, StorageMappingItemCollection storageMappingItemCollection)
at System.Data.Entity.Core.Mapping.StorageMappingItemCollection.GetGeneratedView(EntitySetBase extent, MetadataWorkspace workspace)
at System.Data.Entity.Core.Metadata.Edm.MetadataWorkspace.GetGeneratedView(EntitySetBase extent)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.ExpandView(ScanTableOp scanTableOp, IsOfOp& typeFilter)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.ProcessScanTable(Node scanTableNode, ScanTableOp scanTableOp, IsOfOp& typeFilter)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.Visit(ScanTableOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.ScanTableOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.SubqueryTrackingVisitor.VisitRelOpDefault(RelOp op, Node n)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.Visit(FilterOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.FilterOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.SubqueryTrackingVisitor.VisitRelOpDefault(RelOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitSortOp(SortBaseOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.Visit(ConstrainedSortOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.ConstrainedSortOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfNode.VisitDefault(Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfNode.VisitPhysicalOpDefault(PhysicalOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.Visit(PhysicalProjectOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.PhysicalProjectOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.Process(Dictionary`2& tvfResultKeys)
at System.Data.Entity.Core.Query.PlanCompiler.PreProcessor.Process(PlanCompiler planCompilerState, StructuredTypeInfo& typeInfo, Dictionary`2& tvfResultKeys)
at System.Data.Entity.Core.Query.PlanCompiler.PlanCompiler.Compile(List`1& providerCommands, ColumnMap& resultColumnMap, Int32& columnCount, Set`1& entitySets)
at System.Data.Entity.Core.Query.PlanCompiler.PlanCompiler.Compile(DbCommandTree ctree, List`1& providerCommands, ColumnMap& resultColumnMap, Int32& columnCount, Set`1& entitySets)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
InnerException:
UPDATE: Identified as a regression as part of the initial EF6 release.
Thanks for reviewing the project for me, @Pawel. If you can put a comment on the CodePlex issue, that would likely aid in it getting some priority. http://entityframework.codeplex.com/workitem/1777
On one hand I am happy the issue is repeatable and a likely bug. On the other hand, I just want to get on with doing this in EF 6. I have resorted to completing this in VS 2012 using EF 5 for the meantime. I will update if I uncover anything else.
I have the same issue. When the model was originally created the EF version used was 5. Now using VS 2012 and EF6. Don't want to roll back to EF5.