Question

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

1
1
7/1/2017 9:17:11 AM

Accepted Answer

This Is how I got round my problem, but is by no means a solution I would have wanted.

  1. Export the Backpac of the db from azure (via the azure portal)
  2. Import the backpac into SSMS (right click databases > import.. follow wizard)
  3. Change the collation here, by right clicking the db > properties > options> collation dropdown.
  4. export this backpac using SSMS
  5. import the backpac (from step 4) onto azure server (I used SSMS to do this)
  6. Point the connection strings to the new db.

The data comes through too, so no loss there.

I've tried a few db migrations and they seem to work too.

0
7/3/2017 8:06:05 AM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow