Entity Framework loads all rows before calling INSERT

Question

I have some Entity Framework code that I don't think is very efficient. Basically, I have an ApplicationUser class which looks like this:

public class ApplicationUser : IdentityUser
{
   public string CompanyName { get; set; }
   public virtual ICollection<SolverRun> Runs { get; set; }

   // .. other stuff generated by the Visual Studio template
}

What I want to do is create a new row in SolverRuns for the current user:

// Log run in database
var manager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext()));
var currentUser = manager.FindById(OwnerId);
currentUser.Runs.Add(new SolverRun
{
   Base = job.Base,
   Duration = context.JobRunTime,
   Result = (context.Result ?? "").ToString(),
   RunName = job.RunName,
   Start = context.FireTimeUtc.GetValueOrDefault().LocalDateTime
});

manager.Update(currentUser);

This works great. However, what I'm seeing is the following query:

exec sp_executesql N'SELECT 
    [Extent1].[SolverRunID] AS [SolverRunID], 
    [Extent1].[RunName] AS [RunName], 
    [Extent1].[Base] AS [Base], 
    [Extent1].[Start] AS [Start], 
    [Extent1].[Duration] AS [Duration], 
    [Extent1].[Result] AS [Result], 
    [Extent1].[ApplicationUser_Id] AS [ApplicationUser_Id]
    FROM [dbo].[SolverRuns] AS [Extent1]
    WHERE ([Extent1].[ApplicationUser_Id] IS NOT NULL) AND ([Extent1].[ApplicationUser_Id] = @EntityKeyValue1)',N'@EntityKeyValue1 nvarchar(128)',@EntityKeyValue1=N'029a20a9-b487-4579-87d0-50608ed7f058'

Which will select every row in the SolverRuns table for that user. I suspect that's happening on the getter for currentUser.Runs.

I'm concerned that as a user adds more and more runs (there could eventually be thousands), this will get slower and slower. Is there a way to just add the new row, and not select everything first? Thanks!

Update:

Based on py3r3str's answer below, here's my new working code (after changing the model and migrating the database and what not):

// Log run in database
using (var dbContext = new ApplicationDbContext())
{
   var manager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(dbContext));
   var currentUser = manager.FindById(job.OwnerId);

   dbContext.Set<SolverRun>().Add(new SolverRun
   {
      Base = job.Base,
      Duration = context.JobRunTime,
      Result = (context.Result ?? "").ToString(),
      RunName = job.RunName,
      Start = context.FireTimeUtc.GetValueOrDefault().LocalDateTime,
      ApplicationUser = currentUser
   });

   dbContext.SaveChanges();
}

It now will run this INSERT statement (which is odd, but I gave up trying to understand EF SQL queries so I'll assume someone smarter than me thinks it's efficient):

exec sp_executesql N'INSERT [dbo].[SolverRuns]([ApplicationUserId], [RunName], [Base], [Start], [Duration], [Result])
VALUES (@0, @1, @2, @3, @4, @5)
SELECT [SolverRunID]
FROM [dbo].[SolverRuns]
WHERE @@ROWCOUNT > 0 AND [SolverRunID] = scope_identity()',N'@0 nvarchar(128),@1 nvarchar(max) ,@2 nvarchar(max) ,@3 datetime2(7),@4 time(7),@5 nvarchar(max) ',@0=N'029a20a9-b487-4579-87d0-50608ed7f058',@1=N'PilotRun',@2=N'YUL',@3='2015-09-03 11:07:32.0353181',@4='00:00:04.1521401',@5=N'Success'

Note that calling currentUser.Runs will lazily load all runs, as expected.

For updating only subset of the properties you can see [this post](http://stackoverflow.com/questions/12871892/entity-framework-validation-with-partial-updates) — Yosi Dahari, Sep 03 '15 at 17:24

score 2 · Accepted Answer · edited Sep 03 '15 at 17:46

2

One of simplest way is to add foreign key to your SolverRun entity:

class SolverRun
{
    ...
    public string ApplicationUserId { get; set; }

    [ForeignKey("ApplicationUserId")]
    public virtual ApplicationUser ApplicationUser { get; set; }
}

And save only SolverRun entity using ApplicationContext:

var solverRun = new SolverRun
{
    Base = job.Base,
    Duration = context.JobRunTime,
    Result = (context.Result ?? "").ToString(),
    RunName = job.RunName,
    Start = context.FireTimeUtc.GetValueOrDefault().LocalDateTime,
    ApplicationUserId = OwnerId
};
dbContext.Set<SolverRun>().Add(solverRun);
dbContext.SaveChanges();

In that case EF take care of adding object also in ApplicationUser.Runs.

edited Sep 03 '15 at 17:46

Mike Christensen

88,082
50
208
326

answered Sep 03 '15 at 17:42

py3r3str

1,879
18
23

Doesn't appear to work. I get the exception: `{"An entity object cannot be referenced by multiple instances of IEntityChangeTracker."} System.Exception {System.InvalidOperationException}` – Mike Christensen Sep 03 '15 at 17:58
Note, I changed your code `ApplicationUserId = OwnerId` to `ApplicationUser = currentUser` since obviously you can't set a string to an `ApplicationUser` – Mike Christensen Sep 03 '15 at 17:59
Nevermind, I'm an idiot. I was new'ing up an `ApplicationDbContext` twice. Your answer is perfect. – Mike Christensen Sep 03 '15 at 18:03
I'm glad I could help ;) – py3r3str Sep 03 '15 at 18:05
I've updated my question with the final working code. – Mike Christensen Sep 03 '15 at 18:12

Entity Framework loads all rows before calling INSERT

1 Answers1