Odtnhj

Runtime: netcoreapp2.0

ASP.Net Core: 2.0.1

EF Core: 2.0.1

Microsoft.EntityFrameworkCore.SqlServer: 2.0.1

So, I'm working on an old database that is being used by lots of services and the original creators defined the PK of one table as short and a FK to that table as an int. Is it at all possible for me to handle this case?

Note: it is not feasible to change the column type at this time.

Consider the two classes:

public class Database 
 public short DatabaseId get; set; 
 public Database get; set; 


public class Client 
 public int ClientId get; set; 
 public int DatabaseId get; set; 
 public Database Database get; set; 

My first attempt:

public int DatabaseId get; set; 
[ForeignKey("DatabaseId")] public Database Database get; set; 

throws

System.InvalidOperationException: The relationship from 'Client.Database' to 'Database.Client' with foreign key properties 'DatabaseId' : int cannot target the primary key 'DatabaseId' : short because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.

Using the fluent API now:

modelBuilder.Entity<Client>(table => 
 table.HasOne(p => p.Database)
 .WithOne(i => i.Client)
 .HasForeignKey<Client>(c => c.DatabaseId)
 .HasPrincipalKey<Database>(d => d.DatabaseID);
);

Attempting to define a shadow property failed

modelBuilder.Entity<Client>(table => 
 table.Property<int>("DatabaseId");

 table.HasOne(p => p.Database)
 .WithOne(i => i.Client)
 .HasForeignKey("DatabaseId")
 .HasPrincipalKey<Database>(d => d.DatabaseId);
);

throws

System.InvalidOperationException: You are configuring a relationship between 'Client' and 'Database' but have specified a foreign key on 'DatabaseId'. The foreign key must be defined on a type that is part of the relationship.

Okay, so no shadow properties this time:

modelBuilder.Entity<Client>(table => 

 table.HasOne(p => p.Database)
 .WithOne(i => i.Client)
 .HasForeignKey<Client>(c => c.DatabaseId)
 .HasPrincipalKey<Database>(d => d.SQLDatabaseID);
);

throws

System.InvalidOperationException: The types of the properties specified for the foreign key 'DatabaseId' on entity type 'Client' do not match the types of the properties in the principal key 'DatabaseID' on entity type 'Database'.

So, then I tried just changing the entity type and maybe EF will be able to map int to int16.

public class Client 
 public int ClientId get; set; 
 public short DatabaseId get; set; // <-- now a short
 public Database Database get; set; 

throws

System.InvalidOperationException: An exception occurred while reading a database value for property 'Client.DatabaseId'. The expected type was 'System.Int16' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Int16'.

I'm starting to get the feeling this is not possible.

In EF Core 2.1 you can use value conversions, i.e.

entityBuilder.Property(c => c.Id).HasConversion<string>(); 

where the generic string is the database type and the destination is the model type. You can read further on https://docs.microsoft.com/es-es/ef/core/modeling/value-conversions

You can bypass error generated by entity framework by using the following code to define your foreign key

[Column(TypeName = "int")]
public short DatabaseId get; set; 

but once you run migrations you will get this error and this is generated by sql server.

Column 'Databases.DatabaseId' is not the same data type as referencing column 'Clients.DatabaseId' in foreign key 'FK_Clients_Databases_DatabaseId'.

And this makes perfect sense. What you are trying to achieve is not physically possible as SQL server requires foreign key to be same type otherwise you cannot build foreign key relationship.