Dynamic linq syntax for subquery

问题:

I want to have the following query in Dynamic LINQ.. I have tried some solutions but have not succeeded yet.

 select
    SUM([Value1]) AS [Sum]
        ,[Dim1] AS [Primary], 
        [Dim2] AS [Secondary]
    from
    (
        SELECT 
          value1, dim1, dim2
        FROM [BudgetLine]
        WHERE [BudgetID] = 4
    ) as a
    GROUP BY [Dim1], [Dim2]

My current code looks like this, but I need to rewrite it to give me the SQL above.

    var query = (DatabaseConnection.DataMemoryContext.GetTable<BudgetLineEntity>().AsQueryable()
    .Where(String.Format("BudgetID={0}",filter.BudgetId))
    .GroupBy(String.Format("new({0},{1})",primaryDimension.Name,secondaryDimension.Name), "new(Value1)")
    .Select(String.Format("new (Key.{0} as Primary, Key.{1} as Secondary, Sum(Value1) as Sum)",primaryDimension.Name,secondaryDimension.Name)));

primaryDimension.Name and SecondaryDimension.Name contain the name of the columns to group by.

回答1:

Consider querying the database on your subquery, then stashing the result in a datatable which you can then compute off of.

SELECT 
value1, dim1, dim2
FROM [BudgetLine]
WHERE [BudgetID] = 4

MSDN article - Compute