EF, Do i really have to create an entity in the db before being able to relate it?

Question

You can tell from the following code that things aren't going quite as planned:

DateTime now = DateTime.Now;

            fieldBooking.CreatedAt = now;
            fieldBooking.CreatedBy = user;
            fieldBooking.UpdatedAt = now;
            fieldBooking.Field = field;

            fieldBooking.FieldBookingDateRange.CreatedAt = now;
            fieldBooking.FieldBookingDateRange.CreatedBy = user;
            fieldBooking.FieldBookingDateRange.UpdatedAt = now;
            fieldBooking.FieldBookingDateRange.FieldBooking = fieldBooking;

            fieldBooking.FieldBookingDateRange.DateRange.CreatedAt = now;
            fieldBooking.FieldBookingDateRange.DateRange.CreatedBy = user;
            fieldBooking.FieldBookingDateRange.DateRange.UpdatedAt = now;

            FieldBookingMessageThread fieldBookingMessageThread = new FieldBookingMessageThread()
            {
                CreatedAt = now,
                CreatedBy = user,
                UpdatedAt = now,
                SentTo = field.CreatedBy,
                FieldBooking = fieldBooking,
                Subject = user.Profile.FirstName + " has made a booking for your field: " + field.Name,
                FieldBookingMessages = new List<FieldBookingMessage>()
            };

            fieldBookingMessageThread.FieldBookingMessages.Add(
                new FieldBookingMessage(){
                    FieldBookingMessageThread = fieldBookingMessageThread,
                    Body = Request.Params["FieldBookingComment"],
                    CreatedAt = now,
                    CreatedBy = user,
                    UpdatedAt = now,
                    SentTo = field.CreatedBy
                }
            );

            fieldBooking.FieldBookingMessageThread = new List<FieldBookingMessageThread>()
            {
                fieldBookingMessageThread
            };

            this.unitOfWork.FieldBookingRepository.Insert(fieldBooking);

It's a mess.

I'm having to set CreatedAt and CreatedBy etc on all of my entities regardless of all of them having that...
I'm having to relate each object to each other
The application doesn't even like it:

Cannot insert the value NULL into column 'FieldBookingMessageThreadID', table '**.dbo.FieldBookingMessageThreads'; column does not allow nulls. INSERT fails. The statement has been terminated.

The problem seems to be that the entities must first be created, in order to link them all up... But this can't be right... there must be a way of making entity framework do the bulk of this work.

What am I missing out, where am I going wrong?

Extra info:

Field booking

public class FieldBooking : ISoftDeletable, ITimeStamps, ICreatedBy
{
    [Key]
    public int FieldBookingID { get; set; }

    [Display( Name = "Created at" )]
    public DateTime CreatedAt { get; set; }

    [Display( Name = "Updated at" )]
    public DateTime UpdatedAt { get; set; }

    ...

    public virtual List<FieldBookingMessageThread> FieldBookingMessageThread { get; set; }
}

Field booking message thread

public class FieldBookingMessageThread : MessageThread, ISoftDeletable, ITimeStamps, ICreatedBy
{
    [Key]
    public int FieldBookingMessageThreadID { get; set; }

    [Display( Name = "Field" )]
    [Required]
    public virtual FieldBooking FieldBooking { get; set; }

    ...
}

MessageThread inherited by FieldMessageThread:

public class MessageThread: ISoftDeletable, ITimeStamps, ICreatedBy
{

    [Display(Name = "Created at")]
    public DateTime CreatedAt { get; set; }

    [Display(Name = "Updated at")]
    public DateTime UpdatedAt { get; set; }

    [Display(Name = "Deleted at")]
    public DateTime? DeletedAt { get; set; }

    [Display(Name = "Created by")]
    public virtual ApplicationUser CreatedBy { get; set; }

    [Display(Name = "Sent to")]
    public virtual ApplicationUser SentTo { get; set; }

    [Required]
    [MaxLength(350)]
    [Display(Name = "Subject")]
    public string Subject { get; set; }
}

Field Message class

public class FieldMessage : Message, ISoftDeletable, ITimeStamps, ICreatedBy
{
    [Key]
    public int FieldMessageID { get; set; }

    [Required]
    public virtual FieldMessageThread FieldMessageThread { get; set; }
}

Error output of insert:

INSERT [dbo].[FieldBookingMessageThreads]([CreatedAt], [UpdatedAt], [DeletedAt], [Subject], [CreatedBy_Id], [FieldBooking_FieldBookingID], [SentTo_Id])
VALUES (@0, @1, NULL, @2, @3, @4, @5)
SELECT [FieldBookingMessageThreadID]
FROM [dbo].[FieldBookingMessageThreads]
WHERE @@ROWCOUNT > 0 AND [FieldBookingMessageThreadID] = scope_identity()


-- @0: '28/04/2015 15:20:46' (Type = DateTime2)

-- @1: '28/04/2015 15:20:46' (Type = DateTime2)

-- @2: 'Super has made a booking for your field: Test' (Type = String, Size = 350)

-- @3: 'fb2f22ca-8de9-4206-99ce-d43af3d776da' (Type = String, Size = 128)

-- @4: '6' (Type = Int32)

-- @5: 'fb2f22ca-8de9-4206-99ce-d43af3d776da' (Type = String, Size = 128)

-- Executing at 28/04/2015 15:20:56 +01:00

-- Failed in 76 ms with error: Cannot insert the value NULL into column 'FieldBookingMessageThreadID', table 'FieldLover.dbo.FieldBookingMessageThreads'; column does not allow nulls. INSERT fails.
The statement has been terminated.



Closed connection at 28/04/2015 15:20:56 +01:00

A first chance exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/2063751123/ROOT-5-130747043423504979): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\12.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll'.

As to the cannot insert null, it is hard to tell what your object structure is as you show properties in your example, but not the classes. If you could post those as well, it would make assisting you easier. — Martin Noreke, Apr 27 '15 at 18:14
You should probably strip this down to a [Minimum, Complete, Verifiable Example](http://stackoverflow.com/help/mcve). There's a lot of noise in your code, so it's difficult for us (and you!) to see what's going wrong. It is certainly possible to insert entire object graphs at once with EF. I'll make my own MCVE and see if I can reproduce the problem. — Iain Galloway, Apr 28 '15 at 09:16

score 2 · Accepted Answer · edited May 23 '17 at 11:58

I created the following MCVE of a 1:* relationship as close as I could to yours given the information specified in your question using a blank console application and the EF 6.1.3 nuget package:-

internal class Program
{
    private static void Main(string[] args)
    {
        using (var context = new MyContext())
        {
            var fieldBooking = new FieldBooking();

            var fieldBookingMessageThread = new FieldBookingMessageThread();

            fieldBooking.FieldBookingMessageThread = new List<FieldBookingMessageThread>()
            {
                fieldBookingMessageThread
            };

            context.FieldBookings.Add(fieldBooking);
            context.SaveChanges();
        }
        using (var context = new MyContext())
        {
            foreach (var item in context.FieldBookingMessageThreads.ToList())
            Console.WriteLine(
                "Id: {0}, Created by: {1}, Created at: {2}",
                item.FieldBookingMessageThreadID,
                item.CreatedBy,
                item.CreatedAt);
        }

        Console.ReadKey();
    }
}

public class MyContext : DbContext
{
    private readonly string _user;

    public IDbSet<FieldBooking> FieldBookings { get; set; }
    public IDbSet<FieldBookingMessageThread> FieldBookingMessageThreads { get; set; }

    public MyContext()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
        _user = "Iain Galloway";
    }

    public override int SaveChanges()
    {
        var trackables = ChangeTracker.Entries<ITimestamps>().ToList();

        foreach (var item in trackables.Where(t => t.State == EntityState.Added))
        {
            item.Entity.CreatedAt = DateTime.Now;
            item.Entity.CreatedBy = _user;
            item.Entity.UpdatedAt = DateTime.Now;
            item.Entity.UpdatedBy = _user;
        }
        foreach (var item in trackables.Where(t => t.State == EntityState.Modified))
        {
            item.Entity.UpdatedAt = DateTime.Now;
            item.Entity.UpdatedBy = _user;
        }

        return base.SaveChanges();
    }
}

public interface ITimestamps
{
    DateTime CreatedAt { get; set; }

    string CreatedBy { get; set; }

    DateTime UpdatedAt { get; set; }

    string UpdatedBy { get; set; }
}

public class FieldBooking : ITimestamps
{
    [Key]
    public int FieldBookingID { get; set; }

    [Display(Name = "Created at")]
    public DateTime CreatedAt { get; set; }

    [Display(Name = "Created by")]
    public string CreatedBy { get; set; }

    [Display(Name = "Updated at")]
    public DateTime UpdatedAt { get; set; }

    [Display(Name = "Updated by")]
    public string UpdatedBy { get; set; }

    public virtual List<FieldBookingMessageThread> FieldBookingMessageThread { get; set; }
}

public class FieldBookingMessageThread : ITimestamps
{
    [Key]
    public int FieldBookingMessageThreadID { get; set; }

    [Display(Name = "Created at")]
    public DateTime CreatedAt { get; set; }

    [Display(Name = "Created by")]
    public string CreatedBy { get; set; }

    [Display(Name = "Updated at")]
    public DateTime UpdatedAt { get; set; }

    [Display(Name = "Updated by")]
    public string UpdatedBy { get; set; }

    [Display(Name = "Field")]
    [Required]
    public virtual FieldBooking FieldBooking { get; set; }
}

This program outputs the following, which matches the expected behaviour:-

Id: 1, Created by: Iain Galloway, Created at: 28/04/2015 11:14:50

This means something is going wrong elsewhere in your code, or possibly in your previously created database. There is nothing wrong with your code as you have described it, and it is not the case that "Entities must first be created, in order to link them all up".

You need to strip your own code back to an MCVE. In my experience, doing so generally either makes the bug obvious enough to spot, or otherwise gives answerers enough information to help resolve your problem. In particular, my intuition is that the problem may lie somewhere in the inheritance between FieldBookingMessageThread and MessageThread. Would you care to post the code to your MessageThread class?

As to your second question, there are several ways to automate audit columns on your tables. I use more-or-less the solution listed here:- DateCreated or Modified Column - Entity Framework or using triggers on SQL Server (as per my MCVE), but it's probably worth having a hunt around all the various different solutions to find one you like.

I've added the MessageThread... I'm checking through the code right now though, thanks for helping — Jimmyt1988, Apr 28 '15 at 14:03
From the failing SQL query it looks like your dev database isn't auto-generating the key. Are you doing code-first? Is the application still in development? If yes to both, can you drop and recreate? — Iain Galloway, Apr 28 '15 at 15:20
This is code first. I emptied everything out of my database and rerun my EF migrations... All created fine, still happening. I have checked my table and it says the `FieldBookingMessageThreadID` column is Primary Key and does not all nulls... But it does say it's not its identity! — Jimmyt1988, Apr 28 '15 at 15:24
Adding [DatabaseGenerated( DatabaseGeneratedOption.Identity )] does not invoke any migration changes... weird? I will try and force it myself — Jimmyt1988, Apr 28 '15 at 15:30
Did you *drop* the database and recreate it? You can get a lot of weird behaviour if your code model and db model are out of sync. — Iain Galloway, Apr 28 '15 at 15:31
I certainly did, it's because it was implicitly set inside my migration without identity there... i've added it to an existing migration... fingers crossed. — Jimmyt1988, Apr 28 '15 at 15:32

score 1 · Answer 2 · answered Apr 27 '15 at 18:10

In order to automatically set CreatedAt/SavedAt, I solved this one by overriding the SaveChanges in the DbContext class as shown here:

public partial class MyDbContext : DbContext
{
    public override int SaveChanges()
    {
        IEnumerable<ObjectStateEntry> objectStateEntries;
        ObjectContext context = ((IObjectContextAdapter)this).ObjectContext;

        //Find all Entities that are Added/Modified that inherit from BaseDataItem
        objectStateEntries =
                from item in context.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Modified)
                where (item.IsRelationship == false) &&
                            (item.Entity != null) &&
                            (typeof(BaseDataItem).IsAssignableFrom(item.Entity.GetType()))
                select item;

        DateTime currentTime = DateTime.Now;
        foreach (ObjectStateEntry entry in objectStateEntries)
        {
            BaseDataItem entityBase = entry.Entity as BaseDataItem;

            if (entry.State == EntityState.Added)
                entityBase.CreatedDate = currentTime;

            entityBase.ModifiedDate = currentTime;
        }

        return base.SaveChanges();
    }
}

BaseDataItem is a base class for all of my EntityFramework data types that has my audit fields on it.

This could be extended to do CreatedBy/ModifiedBy by having a property on the DataContext set for who is currently accessing data (User property) that is used to set a field in the same manner.

Actually it answers part of the question relating to "I'm having to set CreatedAt and CreatedBy etc on all of my entities regardless of all of them having that..." — Martin Noreke, Apr 27 '15 at 18:39
Thanks I've implemented this... Very cool! Any ideas why my entity relationship stuff isn't working (the error) — Jimmyt1988, Apr 28 '15 at 09:18

EF, Do i really have to create an entity in the db before being able to relate it?

2 Answers2