C# – Entity Framework – Insert with foreign key

centity-frameworkforeign-keysinsert-update

Apologies if there is clear answer for this somewhere. But I can't insert into a simple table due to it containing a foreign key.

Task Table

  • TaskId (PK)
  • Description
  • StatusId (FK)

    Into which I try to insert like so:

            Task t = new Task();
            t.Id = 1234;
            t.Title = "foo";
            t.Status = db.Status.ToList().First();
    

    But get an updateException error:

    A relationship is being added or deleted from an AssociationSet 'FK_Task_Status'. With cardinality constraints, a corresponding 'Task' must also be added or deleted.

    How can I insert into this table?

    Cheers

    ….

    Found my issue….

    My schema was wrong. When I created my foreign key I pointed it to the wrong field. Had a look in SQL profiler, saw this:

    SELECT
    1 AS [C1],
    [Extent1].[Id] AS [Id],
    [Extent1].[Descr] AS [Descr],
    [Extent2].[Id] AS [Id1]
    FROM [dbo].[Status] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Task] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

    Which should be this (joining on statusId not id):

    SELECT
    1 AS [C1],
    [Extent1].[Id] AS [Id],
    [Extent1].[Descr] AS [Descr],
    [Extent2].[Id] AS [Id1]
    FROM [dbo].[Status] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Task] AS [Extent2] ON [Extent1].[Id] = [Extent2].[StatusId]

    Silly me 😉

  • Best Answer

    in .net framework 4.0 u can use this simple way:

     Task t = new Task();
        t.Id = 1234;
        t.Title = "foo";
        t.StatusId = 5678;
    

    reference: http://blogs.msdn.com/b/adonet/archive/2009/11/06/foreign-key-relationships-in-the-entity-framework.aspx