- 1 year ago 117
SQL Question

How to add a parent entity

My code-first data model started with the standard

entity, which included postal address and billing properties. Now I have extended my model by adding a parent

  • One Account has many ApplicationUsers; ApplicationUser now has a non-nullable foreign key to the related Account record

  • Billing and postal address are now associated with the Account entity rather than the ApplicationUser entity, so the associated properties have been moved from the ApplicationUser entity to the Account entity

In order to update the database schema to incorporate the new parent table, I will assign each existing ApplicationUser to a newly created Account. So, I need to do two things for each existing ApplicationUser:

  1. I need to create a new Account row whose billing and postal address field values are taken from the ApplicationUser row.

  2. I need to set the ApplicationUser.AccountId foreign key field to the primary key value of the newly created Account row.

Note that my code is hosted on Azure, and the data is in the Azure SQL Server.

The scaffolded Migration code looks like this (greatly simplified)

// Create the new Accounts table
c => new
Id = c.Int(nullable: false, identity: true),
BillingInfo = c.String(),
PostalAddress = c.String(),
.PrimaryKey(t => t.Id);

// Add the new FK column
AddColumn("dbo.AspNetUsers", "AccountId", c => c.Int(nullable: false));
CreateIndex("dbo.AspNetUsers", "AccountId");

// Before we add the AspNetUsers.AccountId foreign key, we need to populate
// the Accounts table (one Account for each User)
Sql("some SQL command(s)")

// Make the column a foreign key
AddForeignKey("dbo.AspNetUsers", "AccountId", "dbo.Accounts", "Id", cascadeDelete: true);

// Deleted fields
DropColumn("dbo.AspNetUsers", "BillingInfo");
DropColumn("dbo.AspNetUsers", "PostalAddress");

As far as I can tell, my only option for creating or modifying data is to use the
method (or
), as shown in the above code.

If I'm limited to using SQL, what sort of SQL command would accomplish the task? Can I do it in one big ol' command with some sort of JOIN (to records that don't yet exist), or do I need to resort to SQL looping (as shown in this article)?

Answer Source

Core idea is pretty simple:

  1. Your schema and data migration should be in different migration classes

  2. For migration you need to introduce temporary pseudo-FK column on the side that you are inserting, and migrate data using this column. High level idea looks approximately like this:

ALTER TABLE [dbo].[Accounts] ADD [_Temp_AspNetUserId] int;

/*insert data into accounts with reference from AspNetUsers*/
INSERT INTO [dbo].[Accounts] (BillingInfo, PostalAddress, _Temp_AspNetUserId)
SELECT user.BillingInfo, user.PostalAddress, user.Id 
FROM [dbo].[AspNetUsers] user

/*update original foreign key relationship for [AspNetUsers] table*/
UPDATE [dbo].[AspNetUsers]
SET AccountId = [dbo].[AspNetUsers].Id
FROM [dbo].[Accounts]
WHERE [dbo].[Accounts]._Temp_AspNetUserId = [dbo].[AspNetUsers].Id 

/*drop temporary column*/
ALTER TABLE [dbo].[Accounts] DROP COLUMN [_Temp_AspNetUserId];

Sorry for formatting, for some reason it looks a bit screwed up if I don't add comment tag.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download