Stored Procedures for CUD: What is the purpose of the two SELECT statements in a scaffolded Insert stored procedure?

Question

Going through Tom Dykstra's Getting Started with Entity Framework 6 Code First using MVC 5 tutorial, part 9 covers how to set up EF6 to use stored procedures for CUD.

When the DepartmentSP migration is added via the package manager console, the following CreateStoredProcedure() call is automatically generated to create the Department_Insert stored procedure:

CreateStoredProcedure(
    "dbo.Department_Insert",
    p => new
        {
            Name = p.String(maxLength: 50),
            Budget = p.Decimal(precision: 19, scale: 4, storeType: "money"),
            StartDate = p.DateTime(),
            InstructorID = p.Int(),
        },
    body:
        @"INSERT [dbo].[Department]([Name], [Budget], [StartDate], [InstructorID])
          VALUES (@Name, @Budget, @StartDate, @InstructorID)

          DECLARE @DepartmentID int
          SELECT @DepartmentID = [DepartmentID]
          FROM [dbo].[Department]
          WHERE @@ROWCOUNT > 0 AND [DepartmentID] = scope_identity()

          SELECT t0.[DepartmentID]
          FROM [dbo].[Department] AS t0
          WHERE @@ROWCOUNT > 0 AND t0.[DepartmentID] = @DepartmentID"
);

Why are there two SELECT statements in the automatically generated stored procedure?

I tested the following simplification:

CreateStoredProcedure(
    "dbo.Department_Insert",
    p => new
        {
            Name = p.String(maxLength: 50),
            Budget = p.Decimal(precision: 19, scale: 4, storeType: "money"),
            StartDate = p.DateTime(),
            InstructorID = p.Int(),
        },
    body:
        @"INSERT [dbo].[Department]([Name], [Budget], [StartDate], [InstructorID])
          VALUES (@Name, @Budget, @StartDate, @InstructorID)

          SELECT t0.[DepartmentID]
          FROM [dbo].[Department] AS t0
          WHERE @@ROWCOUNT > 0 AND t0.[DepartmentID] = scope_identity()"
);

.. and this appears to work fine but I might be missing something.

I have read What's New in Entity Framework 6 (Plus How To Upgrade!) and the Code First Insert/Update/Delete Stored Procedure Mapping spec. Also, I looked through the EF6 git commit history and found commit 1911dc7, which is the first part of enabling stored procedure scaffolding in migrations.

score 2 · Accepted Answer · answered Aug 15 '15 at 21:25

I think I figured it out.

The code that generates the Insert stored procedure body is found in the DmlFunctionSqlGenerator.GenerateInsert() method within src/EntityFramework.SqlServer/SqlGen/DmlFunctionSqlGenerator.cs.

Here is the relevant code:

// Part 1
sql.Append(
    DmlSqlGenerator.GenerateInsertSql(
        firstCommandTree,
        _sqlGenerator,
        out _,
        generateReturningSql: false,
        createParameters: false));

sql.AppendLine();

var firstTable
    = (EntityType)((DbScanExpression)firstCommandTree.Target.Expression).Target.ElementType;

// Part 2
sql.Append(IntroduceRequiredLocalVariables(firstTable, firstCommandTree));

// Part 3
foreach (var commandTree in commandTrees.Skip(1))
{
    sql.Append(
        DmlSqlGenerator.GenerateInsertSql(
            commandTree,
            _sqlGenerator,
            out _,
            generateReturningSql: false,
            createParameters: false));

    sql.AppendLine();
}

var returningCommandTrees
    = commandTrees
        .Where(ct => ct.Returning != null)
        .ToList();

// Part 4
if (returningCommandTrees.Any())
{
    //...

Part 1 generates the INSERT statement. Part 2 generates the DECLARE line and first SELECT statement. Part 4 generates the second SELECT statement.

In the Contoso University sample, the Department entity class is a simple model class. It appears that in such cases, the commandTrees collection passed to DmlFunctionSqlGenerator.GenerateInsert() contains only one DbInsertCommandTree element. Therefore, the foreach loop in Part 3 is effectively skipped.

In other scenarios, there can be more than one DbInsertCommandTree element in the commandTrees collection, such as when an entity class extends another entity class and the Table per Type inheritance mapping strategy is used. For example:

[Table("SpecialOrder")]
public class SpecialOrder
{
    public int SpecialOrderId { get; set; }

    public DateTime Date { get; set; }

    public int Status { get; set; }
}

[Table("ExtraSpecialOrder")]
public class ExtraSpecialOrder : SpecialOrder
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ExtraSpecialOrderId { get; set; }

    public string ExtraNotes { get; set; }
}

The scaffolded Insert stored procedure for the ExtraSpecialOrder entity is:

CreateStoredProcedure(
    "dbo.ExtraSpecialOrder_Insert",
    p => new
        {
            Date = p.DateTime(),
            Status = p.Int(),
            ExtraNotes = p.String(),
        },
    body:
        @"INSERT [dbo].[SpecialOrder]([Date], [Status])
          VALUES (@Date, @Status)

          DECLARE @SpecialOrderId int
          SELECT @SpecialOrderId = [SpecialOrderId]
          FROM [dbo].[SpecialOrder]
          WHERE @@ROWCOUNT > 0 AND [SpecialOrderId] = scope_identity()

          INSERT [dbo].[ExtraSpecialOrder]([SpecialOrderId], [ExtraNotes])
          VALUES (@SpecialOrderId, @ExtraNotes)

          SELECT t0.[SpecialOrderId], t1.[ExtraSpecialOrderId]
          FROM [dbo].[SpecialOrder] AS t0
          JOIN [dbo].[ExtraSpecialOrder] AS t1 ON t1.[SpecialOrderId] = t0.[SpecialOrderId]
          WHERE @@ROWCOUNT > 0 AND t0.[SpecialOrderId] = @SpecialOrderId"
);

Notice that two INSERT statements are required in this case.

So, the scaffolded Insert stored procedure for the Department entity class contains two SELECT statements because this way, the SQL generation is extensible to cases where more than one INSERT statement is generated. Although the output is not adapted for the case where there is only one INSERT statement, it is possible to hand-edit the generated stored procedure body so that there is only one SELECT statement.

score 0 · Answer 2 · edited May 23 '17 at 12:31

Unfortunately, entity framework does often generate code that looks unnecessarily complex. It seems to prefer breaking queries into more, smaller statements rather than handle it all in one, also do bare in mind it's code is not really designed to be 'human readable' whereas handwritten t-sql often would be. This question has some good answers on the subject: Why does Entity Framework generate slow overengineered SQL?

Stored Procedures for CUD: What is the purpose of the two SELECT statements in a scaffolded Insert stored procedure?

2 Answers2