I have an Entity Core (.Net 6) user profile like this:

public class driver {
  [Key]
  public int Id { get; set;}
  public List<CarRace>? RacesParticipatedIn { get; set; }
  public List<CarRace>? RacesWon { get; set; }
  public List<CarRace>? RacesLost { get; set; }
}

So I have 3 different lists of type CarRace. How do I set up the Entity relationship? Is this One-to-many or many-to-many?

I intend to have one driver's table and a separate table with a list of 50 CarRaces. I just want to copy the race details to the driver's lists if they took part in the race.

public class CarRace {
 [Key]
 public int Id {get; set;}
 public string Title {get; set;}
 public string Distance {get; set; }
 public int PrizeMoney {get; set}
}

CodePudding user response：

Consider the problem from the DB side as Entities reflect data records. A Driver participates in races, and in some they may win, others they do not.

From a schema perspective I could design something like:

[Driver]
Id (PK)
Name
// Other driver related fields.

[Race]
Id (PK)
// Other race related fields.


[DriverRace]
DriverId (PK)
RaceId (PK)
IsWinner

Then from an Entity perspective:

public class Driver
{
    [Key]
    public int Id {get; set; }
 
    public virtual ICollection<DriverRace> DriverRaces { get; set; } = new List<DriverRace>();
}

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

    public virtual ICollection<DriverRace> DriverRaces { get; set; } = new List<DriverRace>();
}

public class DriverRace
{
    [Key, ForeignKey(nameof(Driver)), Column(Order=0)]
    public int DriverId { get; set; }
    [Key, ForeignKey(nameof(Race)), Column(Order=1)]
    public int RaceId { get; set; }
    public bool IsWinner { get; set; }    

    public virtual Driver Driver { get; set; }
    public virtual Race Race { get; set; }
}

Relationships like this with Races, winners, and losers come with caveats in terms of the schema. With the above model there is no way to really enforce that a given race's drivers don't have more than one IsWinner set, or has any IsWinner set. If we instead opt for having a non-nullable WinnerDriverId in a Race table, or such there is no way to enforce that the WinnerDriverId is a DriverId that is in the DriverRaces table for that Race. Enforcing data integrity means either checks as data is updated (which can lead to chicken & egg issues) or running health check maintenance queries periodically to detect and alert you to invalid data scenarios.

Now the topic of identifying winners and losers. There are a couple of options to consider. We can leverage unmapped properties in the entities, or we can leverage projection.

Unmapped Properties:

public class Driver
{
    [Key]
    public int Id {get; set; }
 
    public virtual ICollection<DriverRace> DriverRaces { get; set; } = new List<DriverRace>();

    [NotMapped]
    public virtual ICollection<Race> WonRaces => DriverRaces.Where(dr => dr.IsWinner).Select(dr => dr.Race).ToList();
    [NotMapped]
    public virtual ICollection<Race> LostRaces => DriverRaces.Where(dr => !dr.IsWinner).Select(dr => dr.Race).ToList();
}

There are some pretty significant caveats of this approach.

1. This will trigger a lazy load of DriverRaces and Race when one of these properties is accessed for the first time if these relationships were not eager-loaded when the Driver was loaded. They will otherwise not work if lazy-loading is disabled.
2. You cannot use unmapped properties in EF Linq queries that would end up translating to SQL. Doing so would result in an error that EF cannot resolve WonRaces or LostRaces.

So for example if you wanted to get the drivers that won a race at a particular Track. (Assuming a Race has a TrackId reference) This wouldn't be legal:

var winningDrivers = Context.Drivers
    .Where(d => d.WonRaces.Any(r => r.TrackId == trackId))
    .ToList();

EF will raise an exception around the use of WonRaces, and the typical "hack" to fix it would be:

var winningDrivers = Context.Drivers
    .ToList();
    .Where(d => d.WonRaces.Any(r => r.TrackId == trackId))
    .ToList();

Which will technically work, but result in absolutely terrible performance in a growing system as this would load all drivers, and then subsequently lazy load each of their DriverRaces and Races when WonRaces is touched.

Instead, you need to go through the mapped DriverRaces reference:

var winningDrivers = Context.Drivers
    .Where(d => d.DriverRaces.Any(dr => dr.Race.TrackId == trackId))
    .ToList();

Projection: A better option is to think about entities as more pure data representations and leave translation to more view-based facts up to projection. This can lead to better querying, and fewer performance pitfalls & bugs appearing with the above approach.

If we have a ViewModel for a Driver and their subsequent Race details we might have something like:

[Serializable]
public class DriverViewModel
{
    public int Id { get; set; }
    public ICollection<RaceViewModel> WonRaces { get; set; } = new List<RaceViewModel>();
    public ICollection<RaceViewModel> LostRaces { get; set; } = new List<RaceViewModel>();
}

[Serializable]
public class RaceViewModel
{
    public int Id { get; set; }
    // Other details.
}

Now when we go to select our driver details to pass to the view:

var drivers = _context.Drivers
    // can use .Where() clause...
    .Select(d => new DriverViewModel
    {
        Id = d.Id,
        WonRaces = d.DriverRaces
            .Where(dr => dr.IsWinner)
            .Select(dr => new RaceViewModel
            {
                Id = dr.Race.Id
            }).ToList(),
        LostRaces = d.DriverRaces
            .Where(dr => !dr.IsWinner)
            .Select(dr => new RaceViewModel
            {
                Id = dr.Race.Id
            }).ToList()
    ).ToList();

This can be simplified considerably using Automapper to perform the projection using a MapperConfiguration set up to know how to project a Driver -> DriverViewModel and a Race -> RaceViewModel.

// Mapper configuration can be centralized or created on demand.
var mapperConfig = new MapperConfiguration( cfg => {
    cfg.CreateMap<Driver, DriverViewModel>()
       .ForMember(x => x.WonRaces, opt => opt.MapFrom(src => src.DriverRaces
            .Where(dr => dr.IsWinner)
            .Select(dr => dr.Race)))
       .ForMember(x => x.LostRaces, opt => opt.MapFrom(src => src.DriverRaces
            .Where(dr => !dr.IsWinner)
            .Select(dr => dr.Race)));
    cfg.CreateMap<Race, RaceViewModel>();
});

var drivers = _context.Drivers
    // can use .Where() clause...
    .ProjectTo<DriverViewModel>(mapperConfig)
    .ToList();

Provided your property naming conventions between the entities and view models are consistent, Automapper can work out how to map most of the properties.