Why is Entity Framework trying to insert NULL?

Question

I have two tables, Quote and Agent. Quote has a column called AgentID that is a foreign key in Agent.

When adding the tables to my model in the VS the Quote class has a reference to Agent.

When attempting to add a new quote I create a new quote entity and set the Agent like this:

entity.Agent = (from x in entities.AgentEntities 
    where x.AgentID == quote.AgentID select x).FirstOrDefault();

Right before SaveChanges is called I examine the object and see that all of the values are set. The Agent object has all of its values set. I even checked the EntityKey property and it is set.

Despite the values being there I am getting this error:

Cannot insert the value NULL into column 'AgentID', table 'Database.dbo.Quote'; 
column does not allow nulls. INSERT fails.

I am not sure what else to check, perhaps there is a way to view the SQL?

EDIT: I am using the repository pattern in my application. I use PONO's in my application then create new entity objects. When I save a new quote I call this method:

public override void CreateQuote(Quote quoteToCreate)
{
  var entity = ConvertQuoteToQuoteEntity(quoteToCreate);
  entities.AddToQuoteEntities(entity);
  entities.SaveChanges();  //Error is thrown here
}

private QuoteEntity ConvertQuoteToQuoteEntity(Quote quote)
        {
            var entity = new QuoteEntity();

            if (quote != null)
            {
                entity.QuoteID = quote.QuoteID;
                entity.DiscoveryMethod = quote.DiscoveryMethod;
                entity.CompletedDateTimeStamp = quote.CompletedDateTimeStamp;
                entity.CommisionAmount = quote.CommisionAmount;
                entity.QuoteKey = quote.QuoteKey;
                entity.SelectedOption = quote.SelectedOption;
                entity.SentDateTimeStamp = quote.SentDateTimeStamp;
                entity.CustomerName = quote.CustomerName;
                entity.CustomerEmail = quote.CustomerEmail;
                entity.CustomerPrimaryPhone = quote.CustomerPrimaryPhone;
                entity.CustomerAlternatePhone = quote.CustomerAlternatePhone;
                entity.Agent = (from x in entities.AgentEntities where x.AgentID == quote.AgentID select x).First<AgentEntity>();
            }
            return entity;  //Everything looks good here (Agent is fully populated)
        }

Here is something odd. I was able to see the SQL generated and it looks strange to me:

insert [dbo].[Quote]([QuoteKey], [CommisionAmount], [QuoteRequestID], [DiscoveryMethod], [SelectedOption], [CreatedDateTimeStamp], [SentDateTimeStamp], [CompletedDateTimeStamp], [CustomerName], [CustomerEmail], [CustomerPrimaryPhone], [CustomerAlternatePhone]) values (@0, null, null, @1, null, @2, null, null, @3, @4, @5, @6) select [QuoteID], [AgentID] from [dbo].[Quote] where @@ROWCOUNT > 0 and [QuoteID] = scope_identity()

Answer 1

Are you checking the value of entity.Agent? I suspect that the null comes from FirstOrDefault() when it encounters a query that returns no records.

Even if the agent object has all of its values set, that won't matter if entity doesn't have a reference to the agent object.

Answer 2

I'm having the exact same problem - I've spent an entire day trying to crack this, I have no idea what's going on. It worked for a while, then suddenly it didn't work anymore, no clue why.

My case is more or less exactly the same as yours - I have an entity with a bunch of text and numeric attributes, and one related entity. I have set all of the attributes, including the related entity, to valid values, and it throws the insert-error when I try to call SaveChanges().

I am SO lost...

Answer 3

I solved my problem.

Let me preface by saying that I'm using ASP.NET MVC - if you're writing a single-user desktop-application, your problem may be completely different from mine.

In my case, I had apparently come up with a bad solution to a problem I had earlier.

I was creating multiple instances of my object-context - and when you create entities with one instance, and try to associate them with an entity created by another instance, you get weird errors.

To work around that, I thought, well, I'll just make sure I have only one object-context. So I created a class with a public getter, which would create the instance if not already created, and return it. Kind of a singleton pattern, ensuring that I had one object-context for the entire application.

In my app, I occasionally create "throw-away" objects - e.g. a temporary entity, sometimes pre-populated with a few default values, just so I can render a form. When the form is submitted, a new object is created, populated, validated, and then saved.

The save would fail though - giving the error described on this page, about some attribute being empty, even though I had populated all the fields, and the entity passed validation.

The problem was, it wasn't trying to save the object I just created - it was hanging on to the "throw-away" object from the previous request, and trying to save that one first.

Coming from PHP, what threw me off here was the realization that ASP.NET MVC apps have a very different lifecycle from PHP apps. In PHP, scripts start, process a request, end then they end - whereas in ASP.NET, they start, run for a while, serving many requests, and then, eventually, they end and restart.

By creating my object-context in a static method, I was creating not one instance per request - but one instance per application. Because the object-context persists between requests, my "throw-away" entities will pile up - and eventually, when I try to SaveChanges(), it will of course fail.

This confusion stems in part from the fact that the Entity Framework was written with desktop applications in mind - it was not designed for the life-cycle of a web application.

You can work around this, and here's the solution:

public class App
{
    public static MyEntities DB
    {
        get {
            // Create (as needed) and return an object context for the current Request:

            string ocKey = "MyEntities_" + HttpContext.Current.GetHashCode().ToString("x");

            if (!HttpContext.Current.Items.Contains(ocKey))
                HttpContext.Current.Items.Add(ocKey, new MyEntities());

            return HttpContext.Current.Items[ocKey] as MyEntities;
        }
    }
}

You can now the your object-context from anywhere:

MyEntities DB = MyNamespace.App.DB;

I found the solution in this lengthy article, which explores several (right and wrong) ways to manage the lifecycle of an object-context:

http://dotnetslackers.com/articles/ado_net/Managing-Entity-Framework-ObjectContext-lifespan-and-scope-in-n-layered-ASP-NET-applications.aspx

Hope this a helpful to others :-)