Dynamic linq syntax for subquery

Question

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.

You will never get that exact code, LINQ will use extents and lot of aliases, etc, so getting that exact code is as far I know impossible — Gusman, May 29 '14 at 22:36

score 0 · Accepted Answer · answered May 30 '14 at 15:33

0

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

answered May 30 '14 at 15:33

Nathan

51
3

Thanks, I actually ended up doing that (saving the result of the query you mentioned in a database table and running Dynamic LINQ on that) . It resulted in a query execution time improvement of about 20% so I'm content. – JohanLarsson May 30 '14 at 16:40

Dynamic linq syntax for subquery

1 Answers1

Linked