how to make dbContext ExecuteSqlCommand work with new uncommitted entities

Question

Is there a way to make ExecuteSqlCommand work with new uncommitted entities.

        using (var context = new EarthContext())
        {
            var country = new Country(){
                Id = "ZZZ",
                IsoCodeAlpha2 = "ZZ",
                IsoCodeNumberic = 999
            };

            context.Countries.Add(country);

            context.Database.ExecuteSqlCommand(
            @"
              INSERT INTO dbo.Location([Line1],[CountryId])
              VALUES ('random line','ZZZ')
            ");

            context.SaveChanges();
        }

It gives a "The INSERT statement conflicted with the FOREIGN KEY constraint" exception because ExecuteSqlCommand executes before the new entities are committed.

*Code must run in one transaction i.e. I cannot commit changes before the ExecuteSqlCommand

Answer 1

As I know all sql queries called using context.Database.ExecuteSqlCommand are executed on different transaction than "common" context operation. Another point is that ExecuteSqlCommand is called immediately and all operations like context.Countries.Add(country); (all inserts, updates or deletes) are executed with context.SaveChanges();.

You should try with:

using (var context = new EarthContext())
{
    var country = new Country(){
        Id = "ZZZ",
        IsoCodeAlpha2 = "ZZ",
        IsoCodeNumberic = 999
    };

    context.Countries.Add(country);
    context.SaveChanges(); // to commit country insertion

    context.Database.ExecuteSqlCommand(
    @"
      INSERT INTO dbo.Location([Line1],[CountryId])
      VALUES ('random line','ZZZ')
    ");
}

But if you have to meet with those requirements

Code must run in one transaction i.e. I cannot commit changes before the ExecuteSqlCommand

you should avoid mixing SQL statements and EF-like code.

In that situation (I assume that you have all FKs defined correctly) you should be able to do sth like that:

using (var context = new EarthContext())
{
    var country = new Country(){
        Id = "ZZZ",
        IsoCodeAlpha2 = "ZZ",
        IsoCodeNumberic = 999
    };

    context.Countries.Add(country);
    country.Locations.Add(new Location() { Line1 = "random line" } );

    context.SaveChanges();
}