Foreign keys against AspNetUsers table causes dramas with migrations

I have a basic MVC5 site, using the out of the box asp.net membership provider/tables.

I have the created a one to many (UserPointsAwarded) table, FK-ed to the the AspNetUsers table. Sweet! I can now query my users points using my current.userid.

However when I try and use migrations to set up the db schema on another team members dev enviroment, we get errors:

Column AspNetUsers.Id is not the same length or scale as reference column 'UserPointsTable.AspNetUsers' in foreign key Fk_...

The trouble seems to be that AspNetUsers.Id is a nvarchar(128) However lightspeed creates the FK on my UserPointsAwarded table as a ModelDataType.String aka nvarchar(max)

[If you use a nvarchar(max for the id column sql server complains that you cant use that type on an id column]

I have tried editing the migration up/down code to create my UserPointsTable.AspNetUser ForeignKeyField() as a nvarchar(128) but there does not seem to be an overload which would let you do that [like you can on a regular field]

Any thoughts on how I could work around this?

• Is it possible to create a field with a length 128 and then convert that to a FK in the migration?
• Can I create computed column in the migration (and create a guid from a hash of the AspNetUsers.Id column) and FK against that?

OK I found a workaround...

From my Up migration method:

this.AddTable("UserPointsTable", null, new Field("Id", ModelDataType.Int32, false), new Field[] {
        new Field("TotalPoints", ModelDataType.Int32, false),
        //new ForeignKeyField("AspNetUsers", ModelDataType.String, false, "AspNetUsers", null, "Id")   //Yeah this no play nice
        new Field("AspNetUsersId", "NVARCHAR(128)", false)});

this.ExecuteNativeCommand("ALTER TABLE UserPointsTable ADD constraint UserPointsTable_AspNetUsersId_FK FOREIGN KEY (AspNetUsersId) REFERENCES [AspNetUsers](Id);");

• Add the FK column as a normal column but with dbtype as NVARCHAR(128)
• Then ExecuteNativeCommand with an ALTER TABLE <tablename> ADD constraint <tableName>_<columnName>_FK FOREIGN KEY (AspNetUsersId) REFERENCES [AspNetUsers](Id)

It seems to work. But seems a bit of a painful to do each FK change.

If there was an overload on the ForeignKeyField() constructor to take a size; that would make things easier. That and LS to automatically working it out (Just saying :P )

Normally I would tell some one who was trying to use a NVARCHAR as an ID column, to: 'go away and think about that for a little while'. But in their wisdom M$ thought it would be best; so we are kind of stuck with it now.

Using ExecuteNativeCommand to call out to a custom script is required for these types of changes. Also make sure you also remove the associated native migration code that was generated if your script would collide with or super cede that change.

Jeremy