EF - Use multiple WHERE clause in LINQ method of IEnumerable of objects' properties

Question

I am trying to fetch a EF model executing the following query:

SELECT *
FROM Vehicles 
WHERE (TypeId = 1 AND PlanId = 1) OR (TypeId = 2 AND PlanId = 2) OR (TypeId = 3 AND PlanId = 3);

In order to do so, I have the following method:

public IEnumerable<Vehicle> GetByIds()
{
    IEnumerable<VehicleId> resultIds = new List<VehicleId>()
    {
        new VehicleId(1, 1),
        new VehicleId(2, 2),
        new VehicleId(3, 3)
    };

    var vehicleList = DbSet
        .Where(resultIds.Contains(new VehicleId(s.TypeId, s.PlanId)))
        .ToList();
        
    return vehicleList;
}

// ...
public class VehicleId
{
    public readonly int TypeId;
    public readonly int PlanId;

    public VehicleId(int type, int plan)
    {
        TypeId = type;
        PlanId = plan;
    }
}

However, this is giving me the error:

System.NotSupportedException: Unable to create a constant value of type 'VehicleRepository+VehicleId'. Only primitive types or enumeration types are supported in this context.

I already checked older questions and:

performing .Select().Where() won't fit since I have a huge amount of unfiltered data
.Where(s => resultIds.Any(r => r.TypeId == s.TypeId && r.PlanId == s.PlanId)) throws same error

This _resultIds.Contains(new VehicleId(s.TypeId, s.PlanId))_ because VehicleId does not implement IEquatable ... So by default it will be compare with object.ReferenceEqual, which will always return false, because the instances in resultIds and new VehicleId are different. — Legacy Code, Jun 22 '20 at 17:46
By the way... What type is _ResultId.Parse()_ is returning? How is it implemented? With yield? — Legacy Code, Jun 22 '20 at 17:54
It's hard to tell exactly since your code is not clear (what are `ResultId.Parse` and `CreateScanResult`?). But maybe something like this would work: `var resultList = DbSet.Vehicles.Where(vehicle => ids.Contains(vehicle.TypeId) && ids.Contains(vehicle.PlanId)).ToList();` — Rufus L, Jun 22 '20 at 18:03
@RufusL @LegacyCode Question edited. `ResultId.Parse` returns `List` (not generator). `CreateScanResult()` will only parse DTO from model. — fiskolin, Jun 22 '20 at 19:41
@RufusL that was a typo, didn't test it, my bad. Question edited — fiskolin, Jun 22 '20 at 20:07
Please take a moment and look at the code you're presenting. We don't know your classes or methods that aren't shown, so it's a little tough to see where it's going wrong. You posted the `Parse` method, but it doesn't do much more than call `ParseToResultObject`, which we don't see. But more importantly, we don't see what the `ResultId` class looks like, nor `VehicleDto`, nor do we see how a `List` can be created from a `List`. Have you tried the code in my first comment? Does it work? — Rufus L, Jun 22 '20 at 20:36
@RufusL why do you need to know the method implementation besides its returning type? Isn't it enough? Question is related to `How to apply IEnumerable in multiple WHERE IN clause`, each one per property value. Anyway, your code didn't work due to `The type arguments for method 'bool System.Linq.Enumerable.Contains(this IEnumerable, TSource)' cannot be inferred from the usage` — fiskolin, Jun 23 '20 at 10:25
Note that your SQL query isn't correct. It doesn't filter the exact *combinations* of `TypeId` and `PlanId`, as the LINQ query would. This is a [notorious problem](https://stackoverflow.com/q/26198860/861716). — Gert Arnold, Jun 23 '20 at 11:15

score 1 · Accepted Answer · answered Jun 22 '20 at 19:20

You should "split" resultIds, for example:

var resultIds = ResultId.Parse(ids).ToList();
var typeIds = resultIds.Select(vId => vId.TypeId).ToList();
var planIds = resultIds.Select(vId => vId.PlanId).ToList();

var resultList = DbSet
        .Where(s => typeIds.Contains(s.TypeId) && planIds.Contains(s.PlanId)))
        .ToList();

EF - Use multiple WHERE clause in LINQ method of IEnumerable of objects' properties

1 Answers1