Raw SQL command not executing using Entity Framework

Question

I have trouble seeding data using the following raw SQL command and entity framework

databaseContext.Database.ExecuteSqlCommand
      (@"INSERT INTO    NatureOfBusinessRiskClass
            ( ProductSectionId ,
            NatureOfBusinessId ,
            RiskClassId ,
            ConstructionType ,
            AreaType ,
            SumInsuredLimit ,
            MaximumRate ,
            MinimumRate ,
            IsAggregated ,
            CreatedBy ,
            CreateDate)
      SELECT P.Id,T.NatureOdBusinessId,T.RiskClassId,ConstructionType,AreaType,0.00,
      0,0,0,1,GETDATE() FROM TempNatureBusiness T
            CROSS JOIN TempAreaType A
            CROSS JOIN TempConstructionType C
            CROSS JOIN ProductSection P");

When I execute this SQL statement within Microsoft SQL Server Management Studion everything works as expected, but when I use entity framework the tables are not populated and no exception is generated.

Use SQL Profiler to check if the same query is used and if it executes with other sql users, make sure permission is given — Arindam Nayak, Oct 27 '14 at 06:21
@user65439 Try this: `Var rowsaffected=databaseContext.Database.ExecuteSqlCommand(....);` — Thirisangu Ramanathan, Oct 27 '14 at 06:29

score 0 · Answer 1 · answered Oct 27 '14 at 06:45

The problem was that I was also using RAW sql commands to populate the the tables this query rely on to generate it's content, for example

databaseContext.Database.ExecuteSqlCommand("INSERT INTO TempAreType...");
databaseContext.Database.ExecuteSqlCommand("INSERT INTO TempConstructionType...");

Then I executed the command I'm having trouble with,

databaseContext.Database.ExecuteSqlCommand
  (@"INSERT INTO    NatureOfBusinessRiskClass
        ( ProductSectionId ,
        NatureOfBusinessId ,
        RiskClassId ,
        ConstructionType ,
        AreaType ,
        SumInsuredLimit ,
        MaximumRate ,
        MinimumRate ,
        IsAggregated ,
        CreatedBy ,
        CreateDate)
  SELECT P.Id,T.NatureOdBusinessId,T.RiskClassId,ConstructionType,AreaType,0.00,
  0,0,0,1,GETDATE() FROM TempNatureBusiness T
        CROSS JOIN TempAreaType A
        CROSS JOIN TempConstructionType C
        CROSS JOIN ProductSection P");

At the end I executed

databaseContext.SaveChanges();

Obviously this statemnent will commit my database changes, so the TempAreaType and TempConstructionType tables did not have any data when executing the third command. When I executed the commadn in Microsoft SQL Server Management studio, my changes were already commited to the database and the command worked.

The solution was to call the

databaseContext.SaveChanges();

after the first to commands and then again at the end, for example

databaseContext.Database.ExecuteSqlCommand("INSERT INTO TempAreType...");
databaseContext.Database.ExecuteSqlCommand("INSERT INTO TempConstructionType...");

databaseContext.SaveChanges();

databaseContext.Database.ExecuteSqlCommand
  (@"INSERT INTO    NatureOfBusinessRiskClass
        ( ProductSectionId ,
        NatureOfBusinessId ,
        RiskClassId ,
        ConstructionType ,
        AreaType ,
        SumInsuredLimit ,
        MaximumRate ,
        MinimumRate ,
        IsAggregated ,
        CreatedBy ,
        CreateDate)
  SELECT P.Id,T.NatureOdBusinessId,T.RiskClassId,ConstructionType,AreaType,0.00,
  0,0,0,1,GETDATE() FROM TempNatureBusiness T
        CROSS JOIN TempAreaType A
        CROSS JOIN TempConstructionType C
        CROSS JOIN ProductSection P");

databaseContext.SaveChanges();

Raw SQL command not executing using Entity Framework

1 Answers1