Contains matches for item pairs

Question

I have a mapping table as follows:

Id  ReferenceId  ReferenceType LinkId

To get a set of combinations, I could execute each request separately:

var pairs = new List<Pair>
{
    Pair.Create(1000, "Car"),
    Pair.Create(2000, "Truck"),
};
var maps = new List<Mapping>();
foreach (var pair in pairs)
{
    maps.AddRange(context.Mappings.Where(x => x.ReferenceId = pair.Id && x.ReferenceType == pair.Type).ToList());
}    

However, I want to combine them into one operator to reduce my db hits. Is there any form of the Contains operator that can work with pairs of objects? Or can I add an OR clause in an IQueryable in a loop? Any other solutions?

Answer 1

Not sure if it works for your LINQ provider, but you can try to join an anonymous type:

var mapQuery = from p in pairs 
               join m in context.Mappings
               on new { p.Id, p.Type } equals new { m.ReferenceId, m.ReferenceType}
               select m;
List<Mapping> maps = mapQuery.ToList();

Answer 2

You can combine your queries together.

Something like that:

var pairs = new List<Pair>
{
    Pair.Create(1000, "Car"),
    Pair.Create(2000, "Truck"),
};

List<Mapping> result =
    pairs
    .Select(pair =>
        context.Mappings.Where(
            x => x.ReferenceId == pair.Id
            && x.ReferenceType == pair.Type))
    .Aggregate(Queryable.Union)
    .ToList();