Define SQL table primary key as case sensitive usi

问题:

Is it possible to define a SQL case sensitive primary key in entity framework code first? I know that by default SQL is case insensitive when it comes to strings, just to be clear I don't want to change the entire DB definition to case sensitive, just one table column (primary key).

I'm getting data from an external API which sends the data with case sensitive primary keys ('a' and 'A' are different records), I know I can modify them and save them differently in my DB, but this will also require me to be consistent about it everywhere in my code. That's defiantly possible, but I would rather just avoid it.

Ideally I was hoping to find a way to define my primary key as case sensitive via the entity framework and not using SQL queries.

I would appreciate any suggestions, or better yet a simple way to do it.

Update

OK, so I've pretty much lost hope for this being possible, and now when I try to use the other approach which is:

public ovveride Up()
{
  // drops the existing primary key named PK_dbo.Urls
  Sql("ALTER TABLE dbo.Urls DROP CONSTRAINT [PK_dbo.Urls]");

  // change the collation
  Sql("ALTER TABLE dbo.Urls ALTER COLUMN Url VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL");

  // re-add the primary key to the Url column
  Sql("ALTER TABLE dbo.Urls ADD CONSTRAINT [PK_dbo.ShortUrls] PRIMARY KEY (Url)");
}

I can't really go through with it since I have 4 different constraints on this field (primary key + 3 others).

I don't want to rewrite the constraint code myself. So can I use fluent API to do the case-sensitive change?

This is the auto-generated fluent API code:

CreateTable(
                "dbo.Shapes",
                c => new
                    {
                        TabID = c.Int(nullable: false),
                        Lbl = c.String(nullable: false, maxLength: 128),
                        wasRemoved = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => new { t.TabID, t.Lbl })
                .ForeignKey("dbo.Tabs", t => t.TabID, cascadeDelete: true)
                .Index(t => t.TabID);

I would really appreciate a solution, since I've been stuck on this for a while now, and I'm looking for the best of worsts right now.

Update 2

I also found a 2nd way of doing this, it's fully automatic, well after you define a lot of other things, anyway it uses a custom data annotation. see my answer below for more info.

回答1:

So far the best solution I got (and by no means I consider it a good one), is to generate the sql script using EF, and then modifying it there.

The reason I'm doing it is because the value I'm changing is not only a primary key it is also involved in 3 other constraints.

In case anyone else is experiencing the same and just need a workaround, you can do the following:

Save the sql script to a new query:

Update-Database -Script -SourceMigration:0

Then instead of dropping and adding many constraints just locate the row that defines the primary key which you like to make as case sensitive, in my case:

[Lbl] [nvarchar](128) NOT NULL

change to:

[Lbl] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

Before you go and run your script, make sure to add the COLLATE SQL_Latin1_General_CP1_CS_AS when using this as a foreign key in other tables.

I don't like this solution, but I'm only using it since it's all I got. If you have a better solution please post it!

Solution 2

I can confirm this works as well, it is a bit of an over kill. you define your own data annotation, here we define a custom [CaseSensitive]. Works really nice, but again it really feels like to much... if I didn't had many db migration I would stick to my first suggestion.

回答2:

Entity Framework doesn't have anything built in to do this - it will always create your database and columns using the default collation. However, you can manually run some SQL in a migration Up() method to modify the collation of a specific column:

Sql("ALTER TABLE yourTable ALTER COLUMN YourColumn VARCHAR(50) COLLATE Latin1_General_CS_AS");