Map entity to lookup table with composite foreign key that is partially hard-coded

entity-framework-core

Question

Using EF Core 3.1.2 database-first, we have a model like below. This is an inherited database, and changing the schema is not preferred. We also have a team decision to prefer data annotations rather than fluent.

We have many different tables that have related rows in ReferenceDictionary. The relationship between these tables and ReferenceDictionary is defined by the composite key (ReferenceDictionary.Category, ReferenceDictionary.Id). The Category value is always the same for any given row in the same related table.

For example, all instances of FooEntity from FooTable use a value of 'FooCategory' for ReferenceDictionary.Category. Likewise, all instances of BarEntity from BarTable use a value of 'BarCategory' for ReferenceDictionary.Category.

The Category for ReferenceDictionary is not actually stored anywhere in FooTable or BarTable, however (there are no database columns FooEntity.ReferenceDictionaryCategory or BarEntity.ReferenceDictionaryCategory).

I tried the solution below using the [NotMapped] attribute to configure this relationship, but I get an error that FooEntity.ReferenceDictionaryCategory is not a column in the database (which it isn't).

Question:

How can I configure the EF relationships so that the navigational property FooEntity.ReferenceDictionary successfully maps to an instance of ReferenceDictionary using the following composite key mapping?

  • ReferenceDictionary.Category = 'FooCategory'
  • ReferenceDictionary.Id = FooEntity.ReferenceDictionaryId

Current Code:

public class ReferenceDictionary
{
    [PrimaryKey(Order = 1)]
    public string Category { get; set; }

    [PrimaryKey(Order = 2)]
    public int Id { get; set; }

    ...
}

public class FooEntity
{
    [Key]        
    public int Id { get; set; }

    [NotMapped]
    public string ReferenceDictionaryCategory { get; } = "FooCategory";

    public int ReferenceDictionaryId { get; set; }

    [ForeignKey(nameof(ReferenceDictionaryCategory) + ", " + nameof(ReferenceDictionaryId))]
    public virtual ReferenceDictionary ReferenceDictionary { get; set; }

    ...
}

public class BarEntity
{
    [Key]        
    public int Id { get; set; }

    [NotMapped]
    public string ReferenceDictionaryCategory { get; } = "BarCategory";

    public int ReferenceDictionaryId { get; set; }

    [ForeignKey(nameof(ReferenceDictionaryCategory) + ", " + nameof(ReferenceDictionaryId))]
    public virtual ReferenceDictionary ReferenceDictionary { get; set; }

    ...
}
1
1
4/17/2020 3:18:16 PM

Popular Answer

When you set foreign key like this

[ForeignKey("ReferenceDictionaryCategory,ReferenceDictionaryId")]
public virtual ReferenceDictionary ReferenceDictionary { get; set; }

and after you try to load data like below,

var test = await _dbcontext.FooEntities.Include(x => x.ReferenceDictionary).ToListAsync();

It will translate to sql like below,

SELECT  [x].[Id], 
        [x].[ReferenceDictionaryCategory], 
        [x].[ReferenceDictionaryId], 
        [x.ReferenceDictionary].[Category], 
        [x.ReferenceDictionary].[Id]
FROM [FooEntity] AS [x]
LEFT JOIN [ReferenceDictionary] AS [x.ReferenceDictionary] 
ON ([x].[ReferenceDictionaryCategory] = [x.ReferenceDictionary].[Category]) 
    AND ([x].[ReferenceDictionaryId] = [x.ReferenceDictionary].[Id])

That is why you are getting [ReferenceDictionaryCategory] is not a column. It does not do any effect by adding [NotMapped] cause it is not the place for calling column.

There is a workaround, you can write query like below. (If you don't want to add ReferenceDictionaryCategory column to FooEntity)

var result = from fe in _dbcontext.FooEntities
             join rd in _dbcontext.ReferenceDictionaries
             on new { Category = "FooCategory", fe.Id } equals new { rd.Category, rd.Id } into data
             from final in data.DefaultIfEmpty()
             select new FooEntity
             {
                 Id = fe.Id,
                 ReferenceDictionaryId = fe.ReferenceDictionaryId,
                 ReferenceDictionary = final
             };

This will generate sql like this.

SELECT [rd].[Category], [rd].[Id], [fe].[Id] AS [Id0], [fe].[ReferenceDictionaryId]
FROM [FooEntity] AS [fe]
LEFT JOIN [ReferenceDictionary] AS [rd] ON (N'FooCategory' = [rd].[Category]) AND ([fe].[Id] = [rd].[
1
4/15/2020 5:16:33 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow