Cascading insert with Entity Framework

Question

public class User
{
    public int ID { get; set; }

    public string EmailAddress { get; set; }

    public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
    public int ID { get; set; }

    public string City { get; set; }

    public string Street { get; set; }

    public string Postcode { get; set; }
}

class TestDbContext : DbContext
{
    public TestDbContext()
        : base("DefaultConnectionString")
    {
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Address> Addresses { get; set; }
}

Above are the model definitions and DbContext difinitions. I want to add a new address for the user, so i wrote my code as bellow:

var context = new TestDbContext();

var user = context.Users.FirstOrDefault(item => item.ID == 1);

user.Addresses.Add(new Address()
{
    City = "City",
    Street = "Street",
    Postcode = "Postcode",
});

context.SaveChanges();

My doubt is why there are 3 SQL queries are executed in this code?

1. It's generated in FirstOrDefault

   SELECT TOP (1) 
     [Extent1].[ID] AS [ID], 
     [Extent1].[EmailAddress] AS [EmailAddress]
     FROM [dbo].[Users] AS [Extent1]
     WHERE 1 = [Extent1].[ID]
   

2. It's generated in user.Addresses.Add

   exec sp_executesql N'SELECT 
       [Extent1].[ID] AS [ID], 
       [Extent1].[City] AS [City], 
       [Extent1].[Street] AS [Street], 
       [Extent1].[Postcode] AS [Postcode], 
       [Extent1].[User_ID] AS [User_ID]
       FROM [dbo].[Addresses] AS [Extent1]
       WHERE ([Extent1].[User_ID] IS NOT NULL) 
         AND ([Extent1].[User_ID] = @EntityKeyValue1)',N'@EntityKeyValue1 int',@EntityKeyValue1=1
   

3. It's generated in SaveChanges

   exec sp_executesql N'INSERT [dbo].[Addresses]([City], [Street], [Postcode], [User_ID])
   VALUES (@0, @1, @2, @3)
   SELECT [ID]
   FROM [dbo].[Addresses]
   WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()',N'@0 nvarchar(max) ,@1 nvarchar(max) ,@2 nvarchar(max) ,@3 int',@0=N'City',@1=N'Street',@2=N'Postcode',@3=1
   

   How can I avoid the second SQL?

Answer 1

The Addresses nav property is lazy loading when you access the property (i.e. user.Addresses), which is why you're getting the second SQL command.

Try disabling lazy loading and see if that works (don't forget to initialize the Addresses property in a constructor for User e.g.:

public User()
{
    Addresses = new HashSet<Address>();
}

Answer 2

You can even prevent the first two queries!

You already know the user's ID value, so all you have to do is set the foreign key value in Address. Of course, Address should have this property:

public class Address
{
    public int ID { get; set; }
    public string City { get; set; }
    public string Street { get; set; }
    public string Postcode { get; set; }

    public int UserID { get; set; } // Set this property
    public User User { get; set; }
}

The pair User and UserID is called a foreign key association, which is the preferred way to deal with associations in EF (precisely because it can reduce the number of queries).

Answer 3

Have you tried changing the class definition slightly:

public class Address
{
    public int ID { get; set; }

    public string City { get; set; }

    public string Street { get; set; }

    public string Postcode { get; set; }

    public virtual User User { get; set;}
}

so that now you can write:

var context = new TestDbContext();
var user = context.Users.FirstOrDefault(item => item.ID == 1);

context.Addresses.Add(new Address()
{
    City = "City",
    Street = "Street",
    Postcode = "Postcode",
    User = user
});

context.SaveChanges();

Answer 4

As already pointed out the problem here is your Addresses property is a navigation property so when you access it EF is generating a SELECT statement to load the collection in. To avoid this from happening you have 2 options:

1. Eager load the addresses when you load the User so you take the hit when you first load the user e.g. Users.Include(x => x.Addresses)
2. Disable lazy loading on that particular property by making the Addresses property non-virtual

Answer 5

I would add a UserId foreign key to the Address class, then I'd do this:

var context = new TestDbContext();

context.Addresses.Add(new Address()
{
    UserId = 1,
    City = "City",
    Street = "Street",
    Postcode = "Postcode",
});

context.SaveChanges();

No need to retrieve the user or the user's existing addresses

Foreign keys make Entity Framework is easier to use:

Why does Entity Framework Reinsert Existing Objects into My Database?

Making Do with Absent Foreign Keys

And relationship fix-up will synchronise the navigation property:

http://msdn.microsoft.com/en-gb/data/jj713564.aspx