Linq query with string array in where clause

Question

I have the following LINQ query where clause which isn't returning the expected result. dtTemp row.Item(0) has values like "GE", "LE", "ST", "PL" while Stkyard is a string array with one or more of these values.

I am expecting the below query to filter values based on entry in Stkyard but the returned result set has sum of all values in dtTemp

dtQuery = (From row In dtTemp
           Group row By grp = 
           New With {Key .mth = row.Item(1), Key .mthdesc = row.Item(2)} Into Group, 
           val1 = Sum(Convert.ToDecimal(row.Item(3))), val2 = Sum(Convert.ToDecimal(row.Item(4)))
           Where Group.Any(Function(p) Stkyard.Contains(p.Item(0)))
           Select New With {grp.mth, grp.mthdesc, val1, val2}
           Distinct).ToList

Any help?

DonNetFiddle Example

Can you share a .NETFiddle example?

aloisdg
– aloisdg

2016-05-12 08:59:38 +00:00
Commented May 12, 2016 at 8:59 — aloisdg
– aloisdg, Commented May 12, 2016 at 8:59
@aloisdg I have added an example in the post.

Nilanjan
– Nilanjan

2016-05-12 09:30:57 +00:00
Commented May 12, 2016 at 9:30 — Nilanjan
– Nilanjan, Commented May 12, 2016 at 9:30

Richard · Accepted Answer · 2016-05-12 11:52:15Z

3

You've grouped by Month before you filter, and then you filter for any groups where any matches are found. For example, your Jan 16 Group contains 3 rows, one from each Type, and your filter matches the whole group as it contains one of the items in Stkyard.

To fix this, filter the rows before you group:

dtQuery = (From row In dtTemp
       Where Stkyard.Contains(row.Item(0))
       Group row By grp = 
       New With {Key .mth = row.Item(1), Key .mthdesc = row.Item(2)} Into Group, 
       val1 = Sum(Convert.ToDecimal(row.Item(3))), val2 = Sum(Convert.ToDecimal(row.Item(4)))
       Select New With {grp.mth, grp.mthdesc, val1, val2}
       Distinct).ToList

Updated Fiddle

answered May 12, 2016 at 11:52

Richard

30.7k12 gold badges79 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nilanjan Over a year ago

Thank! I was under the impression that Where clause always comes after Group By clause.

Richard Over a year ago

You can use as many wheres as you like. If you use it after the Group, you're filtering the groups (more like a HAVING in SQL).

Collectives™ on Stack Overflow

Linq query with string array in where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related