LINQ query that searches for tuples

Question

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.

The 101 Linq samples page is a good starting point. Linq supports joining two lists, so there are some samples: https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — Markus, Jul 12 '17 at 09:27
Are you wanting to use the tuples as a filter to an actual SQL Linq query? — DavidG, Jul 12 '17 at 09:28
With 1000 tuples, you are building one hell of a SQL query from this, are you sure there's no other option? — DavidG, Jul 12 '17 at 09:37
You may well run into the 2100 parameter limit - https://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains . — mjwills, Jul 12 '17 at 09:41
It's getting less ad less clear. Are you really looking at 1000 `OR ( ... )` clauses? Are your tuples your Input, Output or both? — H H, Jul 12 '17 at 09:42
Pretty sure the tuples are the input, which means 1000 of them would create 2000 parameters, dangerously close to the limit. — DavidG, Jul 12 '17 at 09:43
Didn't know of the parameter limit. Would it help to break up the input, issue multiple queries and combine results? — softwarematter, Jul 12 '17 at 09:47
@DavidG: And this is why we shouldn't use parameterised SQL and instead just create it through string concatenation. ;-) (And in case anybody is unsure yes, I am joking - do not do this!). — Chris, Jul 12 '17 at 09:50
@Chris You've suggested it, now that means someone will use it in an answer and get upvotes! — DavidG, Jul 12 '17 at 09:51

mjwills · Accepted Answer · 2017-07-12T09:53:00.130

3

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. :)

edited Jul 12 '17 at 09:53

answered Jul 12 '17 at 09:28

mjwills

23,389
6
40
63

1

This won't work if the source is an Entity Framework db set. – DavidG Jul 12 '17 at 09:31
Great point @DavidG ! If the source data (i.e. `yourData`) is Entity Framework etc then this won't work. See https://stackoverflow.com/questions/17595105/use-tuples-with-entity-framework-contains-statement . – mjwills Jul 12 '17 at 09:32
@DavidG OP explicitly said he/she has a list of tuples – Esko Jul 12 '17 at 09:33
@Esko Yes, as input but it's not explicitly stated what the source is. I suspect the tuples are actually a filter for a DbSet query. For example, where does `AttribX` come from here? – DavidG Jul 12 '17 at 09:35
AttribX is a column in the DB. – softwarematter Jul 12 '17 at 09:37
The question is a litlle zigzag but the Tuples are the desired output. You can see that because a Tuple can never have an `AId` property. – H H Jul 12 '17 at 09:39
@HenkHolterman Well, it can (sort of!) if you're using the latest version of C# of course :) – DavidG Jul 12 '17 at 09:41
Is there a case where the first solution would not work, but the second would? – softwarematter Jul 12 '17 at 09:51
"will work" and "should use this method" are often in opposition, and that's where I stand on this - I'd recommend against it. – DavidG Jul 12 '17 at 09:54
Hard to say as we don't know the context of the app, but the 1000 tuples must have come from somewhere, hopefully the database, so I'd be tempted to leave them in there and just do a join. Or, insert them in there and use that as the join. Batching is also an option, but again that depends on many factors. – DavidG Jul 12 '17 at 09:56
Thanks, option 2 works fine although very slow. Takes around 2 min. I do a bunch of joins too in the query @DavidG the tuples come from large xml files, not DB. – softwarematter Jul 12 '17 at 10:01
1

Then consider adding them to the database and avoiding the use of tuples altogether. There's rarely a good use case for them (other than the newer C#7 version.) – DavidG Jul 12 '17 at 10:02

Joel Christophel · Answer 2 · 2020-08-29T17:43:38.717

0

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.)

edited Aug 29 '20 at 17:43

answered Aug 29 '20 at 05:08

Joel Christophel

2,604
4
30
49

1

I got an error `No overload for method 'Or' takes 1 arguments` in `...(inTuples, (predicate, tuple) => predicate.OR...` any ideas? – Sam Aug 29 '20 at 07:05
1

Ah, found the answer from https://stackoverflow.com/questions/1266742/how-to-append-to-an-expression/1267061 I need to build a function extension for OR as described in the link. – Sam Aug 29 '20 at 07:22
Thanks for the response. I've added clarity to my answer. – Joel Christophel Aug 29 '20 at 17:44
It compiles fine, but when I tried running it, I get error `The binary operator Or is not defined for the types 'System.Func'2[MainDB.tblOrder,System.Boolean]' and 'System.Func'2[MainDB.tblOrder,System.Boolean]'."}` in the Or statement. I ended up using PredicateBuilder which I get it from https://stackoverflow.com/questions/6912733/linq-to-entities-where-in-clause-with-multiple-columns . It is very similar with your answer. I like your answer but can't figure out why it doesn't work :( – Sam Aug 29 '20 at 17:57

LINQ query that searches for tuples

2 Answers2

Linked