EF Database Migration: Adding data and getting the ID

I had to add data to existing tables. Using seed data wasn’t possible, therefore I wanted to do it via database migration. If its simple data, migrationBuilder.InsertData works fine. But its not that easy when you have to add a row and then use the ID to insert further dependent entries.

A simple example: I have User and Groups tables and want to add a new group and a user that belongs to this group. My first thought didn’t really work out:

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", "???" }
    );  
}

As always, Stackoverflow was very helpful, the problem can be solved using direct sql and scope_identity, which can be done with migrationBuilder.Sql():

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

And that works!