CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sqllinq-to-entities
DooDoo
DooDoo

Reputation: 13447

using let operator for Intervals in linq

please consider this image:

enter image description here

I have a table like this:

 Age              Active            Men/Women
 -------------------------------------------------

I want to write a query with linq to entities that calulate Count of every age intervals for men and women. I canusing let operator to calculate single row but I want to calculat All in one query.

thanks

Upvotes: 0

Views: 771

Answers (2)

Marcel B
Marcel B

Reputation: 3664

I don’t think this is possible in Linq2Sql, but I came up with this Query:

Func<int, string> ageGroup = age => string.Format("Age {0}-{1}", (age / 5) * 5, ((age / 5) * 5) + 4);
var blub = from row in table.AsEnumerable()
           where row.IsActive == 1
           group row by ageGroup(row.age)
           into grouped
           from g in grouped
           let menCount = g.Aggregate(0, (sum, r) => sum + r.men)
           let womenCount = g.Aggregate(0, (sum, r) => sum + r.women)
           let totalCount = menCount + womenCount
           select new { AgeGroup = g.Key, Men = menCount, Women = womenCount, Total = totalCount}

First I define a Function to return the AgeGroup for a given age((age / 5) * 5 gives the starting number for a AgeGroup). From there it’s pretty straightforward. Grouping the ages and add up the numbers.

Edit:
I don’t think it’s possible in one query. Because group by ends a query(like select) and you need to group first and then add up.
You can’t use IQueryable, because the ageGroup() Function is not know in SQL and therefore can’t be used in Linq2Sql.

Upvotes: 1

escargot agile
escargot agile

Reputation: 22379

What about using group ... by:

http://code.msdn.microsoft.com/LINQ-to-DataSets-Grouping-c62703ea#Simple1

http://ddkonline.blogspot.com/2008/04/linq-group-by-syntax-for-grouping-on.html

Upvotes: 1

Related Questions