MigrationBuilder: InsertData and get back the ID

Question

I have created a new migration where I want to add some data to the database. In my Up function I usually call migrationBuilder.InsertData. With simple inserts this works fine. But I want to add values that are dependent on each other. I cannot use fixed Ids because there is already data in the tables.

Simple example: I have the tables "group" and "user" and want to insert a new group with one user. I can easily insert the group with InsertData. But now I wanna insert the User with the foreignKey groupId - how do I get the ID of the newly created group?

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.InsertData(
        table: "Group",
        columns: new[] { "Name" },
        values: new object[] { "The Groupname" }
    );

    // this is the problem: how do I get the groupId ? 
    migrationBuilder.InsertData(
        table: "User",
        columns: new[] { "Username", "GroupId" },
        values: new object[] { "specialuser", "???" }
    );  
}

user_1105564 · Answer

Probably not the most elegant solution, but one that would work: just use plain SQL.

You can pass it to migrationBuilder.Sql("your SQL here") function. Than it's just a problem of getting ID with normal SQL means, i.e. with SCOPE_IDENTITY() SQL function.

So it would be something like

var commandText = @"
    DECLARE @groupId int
    INSERT INTO Group (""Name"") VALUES (""MyName"")
    SELECT @groupId = SCOPE_IDENTITY()
    INSERT INTO [User] (""Username"", ""GroupId"") VALUES (""MyUser"", @groupId)";
migrationBuilder.Sql(commandText);

MigrationBuilder: InsertData and get back the ID

1 Answers