How can I force Entity Framework Code First to accept "out of phase" foreign key types, db table vs .NET entity?

Question

I have an inherited Code First Entity Framework project where the entities in C# have fallen out of phase with the database. When I attempt to create a parent-to-child 1-to-1 relationship with an existing table, EF is unable to create valid SQL for establishing the foreign key (FK) relationship.

In this case, the parent entity is called ApplicantRegistration. I need to relate this, one-to-one, with the User entity to represent a CaseManager that belongs to each applicant. (The User entity is used as a child relation in many more parent entities.)

Simple enough on its face. I added the following to the ApplicantRegistration...

public User CaseManager { get; set; }

But the User has an id that says this...

    public class User
    {
        [Key]
        public int UserId { get; set; }
    // ...

... and, in MySQL, the Users table related to that entity has this...

  `UserId` bigint(15) NOT NULL AUTO_INCREMENT,

>>> Since bigint and int don't jibe, I get an error when EF tries to create a Foreign Key. <<<

The int column (EF names it CaseManager_UserId) gets added to the ApplicantRegistrations table fine, but the FK creation borks.

Here's the autogenerated EF SQL:

ALTER TABLE ApplicantRegistrations
ADD COLUMN CaseManager_UserId int NULL
CREATE INDEX IX_ApplicantRegistrations_CaseManager_UserId ON ApplicantRegistrations (CaseManager_UserId)
ALTER TABLE ApplicantRegistrations
  ADD CONSTRAINT FK_ApplicantRegistrations_Users_CaseManager_UserId FOREIGN KEY (CaseManager_UserId) REFERENCES Users (UserId)
  ON DELETE NO ACTION ON UPDATE NO ACTION

ADD CONSTRAINT FK_ApplicantRegistrations_Users_CaseManager_UserId FOREIGN KEY (CaseManager_UserId) isn't going to work, because int and bigint don't match.

Options Considered

1. Swap `int` to `long` in `User` in the C#

I originally started swapping from int for UserId on the User entity over to long (this is in C#) to see if that'd match up with bigint in EF's generated code, but that quickly became a mess, practically speaking. After I swapped public int UserId { get; set; } over to public long UserId { get; set; } and fixed the fallout from changing type on related entities & code, I received a number of errors like the following when trying to Add-Migration:

System.Data.Entity.ModelConfiguration.ModelValidationException: One or more validation errors were detected during model generation:

ProgramWorksite_WorksiteAdministratorUser_Target_ProgramWorksite_WorksiteAdministratorUser_Source: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'WorksiteAdministratorUserId' on entity 'ProgramWorksite' does not match the type of property 'UserId' on entity 'User' in the referential constraint 'ProgramWorksite_WorksiteAdministratorUser'.

That makes sense, of course -- now I've got to change all the other places User is used as a child relation. (There are 33 of them, and it occurs to me there should be an easier way, so I shelved int to long in User for now in the hopes of finding something better, but I'll probably come back to this after posting.)

2. Swap `bigint` for `int` in `Users` in MySQL

I also tried to swap from bigint to int in MySQL, but that would require dropping & replacing tons of FK relationships with UserId in the database, which is a possible solution, but not much fun. I think fixing it in the C# would be easier -- assuming long will map to bigint, which I don't know for sure. (UPDATE: long does map to bigint(15), at least in this system.)

3. Try annotating the column in `ApplicantRegistration`

I thought I might be able to use a Column override for EF's purposes only like this inside of Applicant...

Column("CaseManagerId", TypeName = "bigint(15)")]
public User CaseManager { get; set; }

Unfortunately, that gave the same error as before -- and it didn't use the CaseManagerId name I'd used, instead defaulting back to CaseManager_UserId. So, at best, I've done the annotation wrong.

The annotation route really is my favorite, if there's a way to make it work -- if it could make EF ignore what's in the User entity's UserId definition long enough to create the FK relationship and complete the migration, I'm golden.

What's the most efficient way to insert this new relationship if I don't want to drop EF Code First?

I would just change MySQL back to int. Unless you are planning to have more values than that number, which I wouldn't expect for a list of users. — Ctznkane525, Jan 31 '20 at 20:10
@Ctznkane525 But unfortunately, as I noted in 2., that would involve dropping and recreating every FK relationship involving the `Users` table that's in the existing database. That's exceptionally non-trivial, afaict. But perhaps I'm missing something? I don't think there's an easy way to change `UserId` in the database to an `int` without dropping & recreating those relationships. (This is a system that's been in production for years, if that helps with context. I can't simply create a new migration and greenfield it. That said, I completely agree that `bigint` is overkill here.) — ruffin, Jan 31 '20 at 20:13
i would think it would support going to a smaller column size assuming the values in there all support it...you would drop the FKs...alter the columns on tables involved...recreate FKs...it would have to be done during a period of system maintainable id assume — Ctznkane525, Jan 31 '20 at 20:15
@Ctznkane525 Yep, that's why I'd like to find a more expedient solution. Is there a way to tell Entity Framework, "_Hey, I know it says `int` in C#, but when you're writing SQL, pretend it's a `bigint(15)`, ok?_" Because if there is, I've got the new migration in and at least the database's isn't worse than it was before. — ruffin, Jan 31 '20 at 20:18
Okay, this was ugly. For now, I'm changing the migration code's `Up` method (by hand, after it's auto-generated by `Add-Migration`) from `AddColumn("dbo.ApplicantRegistrations", "CaseManager_UserId", c => c.Int());` to `AddColumn("dbo.ApplicantRegistrations", "CaseManager_UserId", c => c.Long());` -- which is to say, I'm swapping `c => c.Int()` for `c => c.Long()`. I don't feel good about that, but it runs. — ruffin, Jan 31 '20 at 20:33

ruffin · Accepted Answer · 2020-05-07T15:01:21.493

Okay, I'm hoping someone will come back and tell me there's a better way and I can move the check, but for now, here's what I'm doing...

Since all Add-Migration is really doing is auto-generating some C#, you can take that autogenerated code and hack it to match your use case -- in this case, ignoring that the entity is using int and the database is using bigint/long.

Hacking the migration code before running Update-Database breaks nothing. That is, there's nothing checking to make sure your migrations for your datastore match your entity definitions (in code) before the migration C# is run.

In my case, that means I needed to change this Up method from what the autogenerated code was...

        public override void Up()
        {
            AddColumn("dbo.ApplicantRegistrations", 
                "CaseManager_UserId", c => c.Int());

            CreateIndex("dbo.ApplicantRegistrations", "CaseManager_UserId");
            AddForeignKey("dbo.ApplicantRegistrations", "CaseManager_UserId", "dbo.Users", "UserId");
        }

... to this...

        public override void Up()
        {
            AddColumn("dbo.ApplicantRegistrations",
                "CaseManager_UserId", c => c.Long()); // <<< This from Int to Long

            CreateIndex("dbo.ApplicantRegistrations", "CaseManager_UserId");
            AddForeignKey("dbo.ApplicantRegistrations", "CaseManager_UserId", "dbo.Users", "UserId");
        }

This is smelly.

As @Ctznkane525 remarks, since we're unlikely to get past 2147483647 users in this application, bigint is overkill, and the maintainers of this system (possibly including me) should eventually take option #2 from the OP, and fix the danged schema at some point.

So the quick fix (and the answer to this specific question) is to make EF ignore the out of phase entity and schema by hacking the migration file/C# code.

The Long Term Right Thing To Do, however, is to make the database schema and entity code match. Maybe you bcp the data out, massage the db with int from bigint, and recreate the schema if production can stand some downtime, or maybe you bite the bullet and stick with insanely big bigints in your schema and finish doing #1 from the question to make the EF code match and rerun your initial migration. But for now, a hack it is.

You could also fiddle with the current `DatabaseModelSnapshot` so it matches the types in the database. Then generate a migration... — Jeremy Lakeman, Feb 04 '20 at 06:21
@JeremyLakeman DatabaseModelSnapshot is regenerated on each new migration to my knowledge, I don't think that would work. Correct me if I'm wrong — kewur, Aug 06 '22 at 19:18
Creating a migration compares the `dbContext.Model` with the previous snapshot, then serialises the model to overwrite the snapshot. If you fiddle with the snapshot directly, so it mirrors your actual schema (maybe by scaffolding a new context in a new project?) then creating a migration should bring your schema in line with your model... — Jeremy Lakeman, Aug 07 '22 at 03:58

How can I force Entity Framework Code First to accept "out of phase" foreign key types, db table vs .NET entity?

Options Considered

1. Swap int to long in User in the C#

2. Swap bigint for int in Users in MySQL

3. Try annotating the column in ApplicantRegistration

1 Answers1

1. Swap `int` to `long` in `User` in the C#

2. Swap `bigint` for `int` in `Users` in MySQL

3. Try annotating the column in `ApplicantRegistration`