Sum column with Dynamic Field Name in LINQ
The DynamicQueryable class allows you to build dynamic queries by passing column names to LINQ.
For example, myQuery.OrderBy("StartDate") will order the query by startdate.
So, how would you tell the LINQ query to return the Sum of a column (with a dynamic column name)? Normally, you pass the column name in LINQ: myQuery.Select(r=> r.UnitAmount).Sum() - however that doesn't work if the column name is not known at complile time.
The solution is to use dynamic query to select the column and then LINQ to sum it. But in order to have LINQ do the sum, you'll need to cast the dynamic query back to an appropriate type. For example, here I am summing a numeric column:
myQuery.Select("UnitAmount").Cast < decimal? > ().Sum();
This same technique should work with other functions such as Min, Max, Avg, etc.
Here's the output SQL, which is doing a sum as desired:
exec sp_executesql N'SELECT SUM([t0].[UnitAmount]) AS [value]
FROM [dbo].[SqlView] AS [t0]
WHERE ([t0].[CategoryId] = @p0) AND ([t0].[PlanId] = @p1)',N'@p0 int,@p1 int',@p0=0,@p1=14
Created By: amos 6/20/2013 2:37:52 PM
Updated: 5/8/2017 9:26:17 PM
|
|