CODEWITHSUNDEEP
c#.netlinq
Savage
Savage

Reputation: 2349

Contains matches on pairs of items

I have a mapping table in the following form:

Id  ReferenceId  ReferenceType LinkId

To retrieve a set of combinations, I could run each query 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 these into a single statement to reduce my hits on the db. Is there some form of Contains statement that can work with pairs of objects? Or is it possible to append an OR clause onto an IQueryable within a loop? Any other solutions?

Upvotes: 4

Views: 163

Answers (2)

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

You could union your queries together.

Something like this:

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();

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460078

Not sure if it works for your LINQ provider but you could try to join with 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();

Upvotes: 2

Related Questions