Query on a many to one and then returning the parents-CodePudding

I have entity Person which have ONE entity Car. The cars entities have multiple properties and one of this is color. I want to get all the persons that have a car with all the colors that is selected in a colorlist. If person had many cars i should be able to write it like

PersonQuery.Include(c => c.Cars).Where(q=>q.Cars.Any(colors=>ListOfColors.Contains(colors.Color)));

and I want to replace Any with Where, but thats is not correct(!?). What do I miss?

CodePudding user response：

If your expectation is to filter the Cars returned with each applicable Person to only the cars that match the filtered colours, then EF6 does not support this. Your query will return Persons and their cars where that person has a matching colour, but it will return all cars for that person.

One side note: when using Linq expressions, the choice of identifier should either just be a placeholder value (I.e. "x") or reflect the items being queried, not what you are querying for. For instance when writing PersonQuery.Where(c => ... you are querying against "Persons" not "Cars", so "p" would be a less confusing identifier than "c". Expanded out we would have .Where(person => person.Cars.Any(car => ...))

In EF Core 5 there is support for filtered includes:

var persons = context.Persons
   .Include(p => p.Cars
       .Where(c => ListOfColors.Contains(c.Color))
   .Where(p => p.Cars.Any(c=>ListOfColors.Contains(c.Color)))
   .ToList();

In EF 6, you would be better off selecting the Cars, then you can build the applicable Person list:

var cars = context.Cars
    .Include(c => c.Person)
    .Where(c => ListOfColors.Contains(c.Color))
    .ToList();
var persons = cars.Select(c => c.Person).ToList();

However, a caveat to this approach is that it should only be done with a virgin DbContext, meaning a DbContext that has not loaded any other data. If for any reason the DbContext is already tracking another car of another colour for one of the applicable person records, that car would automatically be associated with the returned Person so you could easily find yourself with the odd car that doesn't match.

If you are running a DbContext scoped to the request and want to be 100% safe, you should consider projecting the results rather than dealing with the entities directly:

var results = context.Persons
    .Where(p => p.Cars.Any(c => ListOfColors.Contains(c.Color)))
    .Select(p => new PersonViewModel
    {
        PersonId = p.Id,
        Name = p.Name,
        MatchingCars = p.Cars
            .Where(c => ListOfColors.Contains(c.Color))
            .Select(c => new CarViewModel
            {
                CarId = c.Id,
                Brand = c.Brand,
                Model = c.Model, 
                // ...
            }).ToList()
    }.ToList();

This ensures that the data returned will always only be the matching coloured cars.