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):

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]

#11336 and #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;
        }

[SetTrend]

@AndriySvyryd: I tried everything, but it doesn't seem to work:

Test method DatabaseBackEndTests.CreateDatabaseTests.SuccessfullyCreateDatabaseAndDelete threw
exception: System.InvalidOperationException: The seed entity for entity type 'DataText' cannot be
added because it has the navigation 'Language' set. To seed relationships,  add the entity seed to
'Language' and specify the foreign key values {'Id'}. Consider using 'DbContextOptionsBuilder.
EnableSensitiveDataLogging' to see the involved property values.

I uploaded the updated code to branch Andriy.

[AndriySvyryd]

For that you'd need #10000

As a workaround, set the navigation to null when adding seed data:

dataTexts.Add(new DataText(textId, "de", itemLabels[i][0])
{
    Language = null!
});
dataTexts.Add(new DataText(textId, "en", itemLabels[i][1])
{
    Language = null!
});

Also, I made a mistake in the previous comment, the relationship should be one-to-many:

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

[SetTrend]

It's working like a charm!

Thanks so much, @AndriySvyryd, for your continued and prompt support.

Allow me to give you (all) my grateful kudos for your excellent work an support in this repo. You are so excellently engaged in your project!

Your are a solitary, shining lighthouse in the dark, forsaken world of Microsoft support. All the other repos don't give a damn:

• It took MS three years to answer a WPF question
• MS Windows Docker containers don't work for months. No reply yet …
• It took a year and 60 reminder messages for MS to tell me they shut-down German Azure cloud two years before my question while still actively advertising it in the documentation.

It's so hard to work with Microsoft as their support (in the other repos) is so poor. As a professional I'd be willing to pay a subscription just to get immediate support and things to work.

Thank you so much again. You have been lifting me from a heavy burdon this time.