How to correctly reference other table using EF Core and CHAR(2) foreign key?

[SetTrend]

Using Entity Framework Core, I created a Language entity with the 2-letter ISO code as key:

public class Language(string id)
{
  [Length(2, 2)]
  public string Id { get; init; } = id;
}

Next, I created a dependant entity referencing that table:

[PrimaryKey(nameof(Id), nameof(Language))]
public class DataText(string id, Language language, string text)
{
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  [Length(2, 200)]
  public string Id { get; init; } = id;

  public virtual Language Language { get; init; } = language;

  [Length(2, 3000)]
  public string Text { get; set; } = text;
}

Now, using the SQLite provider, I'd like to reference the Language entity from the DataText entity using a foreign key. This doesn't seem possible. In OnModelCreating() I'm getting the following error message:

System.InvalidOperationException: The 'Language' property 'DataText.Language' could not be mapped because the database provider does not support this type. Consider converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

---

Same is true for SQL Server provider. So, I guess EF Core is not able to create foreign keys on strings, although a CHAR(2) foreign key is more efficient than an INT foreign key. Adding an additional INT proxy primary key column would require to introduce an unnecessary burdon to the database.

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[SetTrend]

I see … So here's the MRE.

Just run/debug through the sole test in the MSTest project (DatabaseBackEndTests.CreateDatabaseTests.SuccessfullyCreateDatabaseAndDelete()).

Thanks for taking the time to investigate on this issue! 👍

[ajcvickers]

@SetTrend It is your intention to map Language as an entity type mapped to its own table?

[SetTrend]

Does it help if I provide the intended target database diagram?

---

Here is the T-SQL code I manually created for this diagram to appear (click Details to view the code):

```sql SET NOCOUNT ON USE master IF (SELECT COUNT(*) FROM sys.databases WHERE name = 'DbTest') = 1 DROP DATABASE DbTest GO CREATE DATABASE DbTest ON PRIMARY ( NAME = 'DbTest' ) GO USE DbTest CREATE TABLE Languages ( Id CHAR(2) PRIMARY KEY CHECK (LEN(LTRIM(RTRIM(Id))) = 2) ) CREATE TABLE DataTexts ( Id VARCHAR(200) , Language CHAR(2) -- , Text NVARCHAR(3000) NOT NULL CHECK (LEN(LTRIM(RTRIM(Text))) > 1) -- , CONSTRAINT PK_DataTexts PRIMARY KEY (Id, Language) , CONSTRAINT FK_Language_Id FOREIGN KEY (Language) REFERENCES Languages (Id) ) GO INSERT INTO Languages VALUES ('de'), ('en') INSERT INTO DataTexts VALUES ('ProfileItem.Label.1', 'de', N'Titel') , ('ProfileItem.Label.1', 'en', N'Title') , ('ProfileItem.Label.2', 'de', N'Zuletzt aktualisiert am {0:d}') , ('ProfileItem.Label.2', 'en', N'Last modified on {0:d}') , ('ProfileItem.Label.3', 'de', N'Verfügbar ab {0:d}') , ('ProfileItem.Label.3', 'en', N'Available from {0:d}') , ('ProfileItem.Label.4', 'de', N'Davon zu {0:P0} vor Ort verfügbar') , ('ProfileItem.Label.4', 'en', N'With on-site availability up to {0:P0}') ```

---

The Language entity is supposed to serve as filter and dropdown data source later in the future application.

[SetTrend]

Do you see any path to get this working soon?

[SetTrend]

Hello, @AndriySvyryd,

I'm using EF Core for a commercial project, and I need support. I'm an MS Enterprise customer. Do you see ANY way for me to go to get immediate support for this case?

[roji]

@SetTrend you can contact Microsoft Support for EF like for other Microsoft products - see our support docs. Otherwise you'll have to be patient until we get around to investigating this - there are many things going on at the moment.

[AndriySvyryd]

https://github.com/dotnet/efcore/issues/11336 and https://github.com/dotnet/efcore/issues/12078 would be needed to enable your scenario.

For now, something like this would be the closest:

            modelBuilder.Entity<DataText>()
                .HasOne(d => d.Language)
                .WithOne()
                .HasPrincipalKey<DataText>(d => d.LanguageId)
                .HasForeignKey<Language>(l => l.Id);

        [PrimaryKey(nameof(Id), nameof(LanguageId))]
        public class DataText(string id, string languageId, string text)
        {
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            [Length(2, 200)]
            public string Id { get; init; } = id;

            [Length(2, 2)]
            [Column("Language")]
            public string LanguageId { get; init; } = languageId;
            public virtual Language Language { get; set; } = new Language(languageId);

            [Length(2, 3000)]
            public string Text { get; set; } = text;
        }