CODEWITHSUNDEEP
c#linq
smatter
smatter

Reputation: 29178

LINQ query that searches for tuples

I have a list of tuples (List<Tuple<string,string>>) of Fname and Version as input.

eg.

[('firstname1','1.0.1'), ('firstname2','2.3.3'), ('firstname3','4.4.1')]

Is it possible to write a LINQ query that essentially does what the following SQL query does and returns tuples of Name and AttribX?

SELECT e.Name, a.AttribX
FROM Element e
JOIN Attributes a ON a.AId=e.EAId
where (a.FName='firstname1' and a.Version='1.0.1')
   OR (a.Fname='firstname2' and a.Version='2.3.3')
   OR (a.Fname='firstname3' and a.Version='4.4.1')

There are about a 1000 tuples in the input.

Upvotes: 3

Views: 3141

Answers (2)

Joel Christophel
Joel Christophel

Reputation: 2663

If you're generating SQL, you can avoid string concatenation by building the where clause somewhat explicitly:

Expression<Func<Attributes, bool>> inTuples = a => false;
inTuples = yourListOfTuples.Aggregate(inTuples, (predicate, tuple) => predicate
    .Or(a => a.FName == tuple.Item1 && a.Version == tuple.Item2));

The Or in the above snippet is a simple utility for combining two expressions. See this answer for the code, as well as the And counterpart. These two extension methods are invaluable in working with expressions.

You can now use this inTuples expression to filter a query of Attributes, and the generated where clause will match the desired form:

var filteredQuery = attributesQuery.Where(inTuples).AsEnumerable();

You may have to do a bit of finagling to transform the result to a list of tuples:

var resultingTuples = filteredQuery
    .Select(a => new {a.Name, a.AttribX})
    .AsEnumerable()
    .Select(a => (a.FName, a.AttribX))
    .ToList();

(Note that I returned a.FName instead of e.Name to keep the example simple.)

Upvotes: 0

mjwills
mjwills

Reputation: 23898

Your Where clause could be (if using LINQ to Objects):

var results = yourData.Where(z => yourListOfTuples.Contains(new Tuple<string, string>(z.FirstName, z.Version)))

Another option to try (against Entity Framework):

var tuples = yourListOfTuples.Select(z => z.Item1 + "-" + z.Item2).ToList();
var results = yourData.Where(z => tuples.Contains(z.FirstName + "-" + z.Version))

The second code sample just concatenates the two fields - this will negatively impact database lookups (since it will likely have to do scans rather than seeks). You may also have issues if FirstName or LastName contains - for example. On the upside it will use only 1000 parameters rather than 2000. :)

Upvotes: 3

Related Questions