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; }
...
}
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].[