I'm using Entity Framework code first, It has been working ok updating the database with db migrations up until recently...
I've added a anew property to the AspNetUser table
public partial class AspNetUser
{
....
public ICollection<Feed> Feeds { get; set; }
}
This is my new table
public class Feed
{
public int Id { get; set; }
public string UserId { get; set; }
public AspNetUser User { get; set; }
public MessageType Type { get; set; }
public string Data { get; set; }
public DateTime DateCreated { get; set; }
}
And this the is DBMigration script generated
public override void Up()
{
CreateTable(
"dbo.Feeds",
c => new
{
Id = c.Int(nullable: false, identity: true),
UserId = c.String(nullable: false, maxLength: 128),
Type = c.Int(nullable: false),
Data = c.String(),
DateCreated = c.DateTime(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
.Index(t => t.UserId);
}
In the Context class:
modelBuilder.Entity<Feed>().HasRequired(x => x.User).WithMany(x => x.Feeds).HasForeignKey(x => x.UserId);
This created the table fine, on localhost, but when i deployed and run the migration on staging, the error i get is:
Column 'dbo.AspNetUsers.Id' is not of same collation as referencing column 'Feeds.UserId' in foreign key 'FK_dbo.Feeds_dbo.AspNetUsers_UserId'. Could not create constraint or index. See previous errors.
What must I do... I've gone with the code first approach, thinking this would be easier, but this is really frustrating.
Note: I'm using sql Azure
This Is how I got round my problem, but is by no means a solution I would have wanted.
The data comes through too, so no loss there.
I've tried a few db migrations and they seem to work too.
The collation of both columns 'dbo.AspNetUsers.Id' and 'Feeds.UserId' should be the same, To make them the same you can modify the collation of one of those columns using below sample code:
context.Database.SqlCommand("ALTER TABLE MyTable ALTER COLUMN MyColumn VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL");
Hope this helps.
Regards,
Alberto Morillo