As EFCore requires you to explicitly create join entities I am trying to find ways to make this code more manageable. I am following this tutorial (4 part series, this part and next part are the relevant ones): https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-2-hiding-as-ienumerable/
It hides the join class away as a private ICollection and then populates another ICollection with the joined entitis so it works in similar fashion to EF6. Here is my implementation for tracking what cars people own:
public class Person
{
public Person() =>
Cars = new JoinCollectionFacade<Car, PersonCar>
(PersonCars, pc => pc.Car, c => new PersonCar { Person = this, Car = c });
public int Id { get; set; }
public string Name { get; set; }
private ICollection<PersonCar> PersonCars { get; } = new List<PersonCar>();
[NotMapped]
public ICollection<Car> Cars { get; }
}
public class Car
{
public Car() => Persons = new JoinCollectionFacade<Person, PersonCar>
(PersonCars, pc => pc.Person, p => new PersonCar { Person = p, Car = this });
public int Id { get; set; }
public string Manufacturer { get; set; }
private ICollection<PersonCar> PersonCars { get; } = new List<PersonCar>();
[NotMapped]
public ICollection<Person> Persons { get; }
}
public class PersonCar
{
public Person Person { get; set; }
public int PersonId { get; set; }
public Car Car { get; set; }
public int CarId { get; set; }
}
My mappings:
modelBuilder.Entity<PersonCar>(e =>
{
e.HasKey(t => new { t.PersonId, t.CarId });
e.HasOne(pc => pc.Person).WithMany("PersonCars");
e.HasOne(pc => pc.Car).WithMany("PersonCars");
});
And some seed data:
using (var db = new ManyDbContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Persons.AddRange(
new Person() { Name = "John" },
new Person() { Name = "Peter" },
new Person() { Name = "Paul" }
);
db.Cars.AddRange(
new Car() { Manufacturer = "Audi" },
new Car() { Manufacturer = "Honda" },
new Car() { Manufacturer = "Mercedes" },
new Car() { Manufacturer = "Ferrai" },
new Car() { Manufacturer = "Porche" }
);
db.SaveChanges();
db.PersonCars.AddRange(
new PersonCar() { PersonId = 1, CarId = 2},
new PersonCar() { PersonId = 1, CarId = 3 },
new PersonCar() { PersonId = 2, CarId = 2 },
new PersonCar() { PersonId = 3, CarId = 1 }
);
db.SaveChanges();
}
If I pull back a list of people including cars like below it works and outputs the data:
var drivers = db.Persons.Include("PersonCars.Car").ToList();
foreach(var person in drivers)
{
foreach(var car in person.Cars)
{
Console.WriteLine($"{person.Name} has a {car.Manufacturer}");
}
}
However, if I try to view the Cars collection results inside debugger then VS2017 crashes which isn't great but at code level it seems to work.
But, lets say I want to filter the list to just be Audi drivers, the below results in 0 results:
var audiDrivers = db.Persons.Include("PersonCars.Car").Where(x => x.Cars.Any(c => c.Manufacturer == "Audi"));
The series of articles is mainly focused on improving adding/removing and doesn't mention filtering. I'd like that functionality but I want to be able to filter the Persons by their Cars.
If I make PersonCars collection public then I can do:
var audiDrivers = db.Persons
.Include(i => i.PersonCars).ThenInclude(i => i.Car)
.Where(x => x.PersonCars.Select(pc => pc.Car).Any(c => c.Manufacturer == "Audi"))
.ToList();
So to summarize:
If navigation property is private like article suggests, can lookup be filtered?
Why does VS2017 crash when looking at Car collection?
Is there a better way to do this that is simple like in EF6?
