Using EF6 / VS 2013, I created the ADO.NET Entity Data Model. This model uses three views stored in SQL Server to prepare 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 user objects and roles are mapped, the application compiles and runs without problems. However, when I add the Association between the user and the Role and map it to the vw_UserRoles view, I get an EntityCommandCompilationException with an internal exception, KeyNotFoundException {"The specified key is not 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.
For me, this just seems like a mistake, but I'm new to many EFs, and this could be due to connecting to the views. Out of interest, I implemented this trick to treat the view as a table , but that didn't matter.
UPDATE # 1: I created a new web application with a new SQL Server database. I created base tables with relationships and views for each table. When I create an entity model based on tables, I have no problem - even with the association. When I created the model from the views, I get a KeyNotFoundException. Note. I adjusted the keys and added and matched the association in the second, view-based model. You can download the project here - http://sdrv.ms/1eZ7ytp
UPDATE # 2: I registered this as a problem with MS @Codeplex - https://entityframework.codeplex.com/workitem/1777
I am still at a standstill about this and really want to complete this using a series of views. Any thoughts? You are welcome....?
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: