EF Core Multiple Entities have a property of a common type

c# entity-framework-core

Question

I have the following schema (abbreviated to relevant code)

class Person
{
  public int Id {get;set;}
  public string Name {get;set;}
  public ICollection<MetaData> MetaData {get;set;}
}
class Car
{
  public int Id {get;set;}
  public string Make {get;set;}
  public ICollection<MetaData> MetaData {get;set;}
}
class Building
{
  public int Id {get;set;}
  public string Address {get;set;}
  public ICollection<MetaData> MetaData {get;set;}
}
class MetaData
{
  public int Id {get;set;}
  public string CLRType {get;set;}
  public string Value {get;set;}

  // These two fields together form a connection to the other entities
  public int Key {get;set;} // the PK of the connected entity
  public string Type {get;set;} // nameof(Entity)
}

How can I represent this in EF Core?

Using OwnsMany create a table per type+metadata type connection If I try using TPH, then the metadata table ends up with additional fields per connected entity

Neither of those solutions are ideal for me. I do not need restraints in the database, I just want a way to express this relation in such a way that EF Core will be able to hydrate my models Even if I have to provide the sql manually (even for both reads and writes) that would be workable

If this is not possible with the existing APIs provided, is there a way I can write extensions on my own that will do what I need?

1
1
5/29/2019 11:32:01 AM

Popular Answer

You are mixing two forms of relations. You can either have type safe relations and replicate the foreign keys on your Metadata class (don't forget to add unique constraints)

class MetaData
{
  public int Id {get;set;}
  public string CLRType {get;set;}
  public string Value {get;set;}
  public int PersonId {get;set;}
  public Person Person {get;set;}
  public int CarId {get;set;}
  public Car Car {get;set;}
  public int BuildingId {get;set;}
  public Person Building {get;set;}
}

Or keep it loosely coupled and do the joins by hand

class Car
{
  public int Id {get;set;}
  public string Make {get;set;}
}

// not mapped in the database, just used for display purposes
class CarViewModel 
{
  public int Id {get;set;}
  public string Make {get;set;}
}




var carIds = new [] { 1, 2, 3 };

// we use 2 contexts here so we can query both tables at the same time.
// otherwise Task.WhenAll(...) would throw an exception.
// you should also disable change tracking to improve speed.
var carsTask = context1.Cars
    .Where(c => carIds.Contains(c.Id))
    .ToListAsync();
var metadataTask = context2.Metadata
    .Where(m => carIds.Contains(m.Key) && m.Type == "Car")
    .GroupBy(m => m.Key)
    .ToDictionaryAsync(g => g.Key, g => g.ToList());

await Task.WhenAll(carsTask, metadataTask).ConfigureAwait(false);

var cars = carsTask.Result
    .Select(c => new CarViewModel
    {
        Id = c.Id,
        Make = c.Make,
        Metadata = metadataTask.Result.TryGetValue(c.Id, out var m) ? m : Array.Empty<Metadata>(),
    })
    .ToList();

Or have separate tables for the metadata

abstract class MetaData
{
  public int Id {get;set;}
  public string CLRType {get;set;}
  public string Value {get;set;}
}

class CarMetaData : MetaData
{
  public int CarId {get;set;}
  public Car Car {get;set;}
}

class Car
{
  public int Id {get;set;}
  public string Make {get;set;}
  public ICollection<CarMetaData> MetaData {get;set;}
}

Which version suits you best is up to your and your specific business needs.

1
5/29/2019 12:45:02 PM


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