none
model many to many relationship RRS feed

  • Question

  • I generate following many to many relationship tables from database to model.
    use EFRecipes2
    go

    create table [Order](
     [OrderId] int primary key identity(1,1) not null,
     [OrderDate] date
    );
    go

    create table [Item](
     [SKU] int primary key identity(1,1) not null,
     [Description] varchar(max),
     [Price] money
    );
    go

    create table [OrderItem](
     [OrderId] int not null references [Order](OrderId),
     [SKU] int not null references [Item](SKU),
     [Count] int,
    );
    go

    Code:

            static void Main(string[] args)
            {
                using(var context = new EFRecipes2Entities())
                {
                    var order = new Order { OrderId = 1, OrderDate = new DateTime(2010, 1, 18) };
                    var item = new Item() { SKU = 1729, Description = "Backpack", Price = 29.97M };
                    var oi = new OrderItem() { Order = order, Item = item, Count = 1 };
                    item = new Item() { SKU = 2929, Description = "Water Filter", Price = 13.97M };
                    oi = new OrderItem() { Order = order, Item = item, Count = 3 };
                    item = new Item() { SKU = 1847, Description = "Camp Stove", Price = 43.99M };
                    oi = new OrderItem() { Order = order, Item = item, Count = 1 };
                    context.Orders.AddObject(order);
                    context.SaveChanges();
                }

                Console.Read();
            } 

    I have following exception context.SaveChanges().

    {"Unable to update the EntitySet 'OrderItem' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation."}

     

       at System.Data.SqlClient.SqlGen.DmlSqlGenerator.ExpressionTranslator.Visit(DbScanExpression expression)
       at System.Data.Common.CommandTrees.DbScanExpression.Accept(DbExpressionVisitor visitor)
       at System.Data.SqlClient.SqlGen.DmlSqlGenerator.GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, List`1& parameters)
       at System.Data.SqlClient.SqlGen.SqlGenerator.GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, List`1& parameters, CommandType& commandType, HashSet`1& paramsToForceNonUnicode)
       at System.Data.SqlClient.SqlProviderServices.CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree)
       at System.Data.SqlClient.SqlProviderServices.CreateCommand(DbCommandTree commandTree)
       at System.Data.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
       at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(UpdateTranslator translator, Dictionary`2 identifierValues)
       at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
       at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
       at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       at System.Data.Objects.ObjectContext.SaveChanges()
       at ModelManyToManyRelationshipWithPayload2.Program.Main(String[] args) in D:\CodeSamples\EntityFramework\SimpleEntityFramework\ModelManyToManyRelationshipWithPayload2\Program.cs:line 22
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

     

    What's the problem? How to make it work? 

     


    William
    Friday, October 21, 2011 6:37 PM

Answers

  • William,

    What might have happend is that EF treated your intersection table as a view, because it didn't have any primary keys on it.  I think the problem is you don't have any primary keys on your OrderItem table and need to add one.  Or, you need to define modification functions to do the inserts updates and deletes.  these will map to stored procs. 

    So first try to add primary keys to your OrderItem table:

    create table [OrderItem](
     [OrderId] int not null references [Order](OrderId),
     [SKU] int not null references [Item](SKU),
     [Count] int
    PRIMARY KEY(OrderId, SKU)
    );
    go
    
    

     


    Tom Overton
    Friday, October 21, 2011 6:51 PM