子查询的动态linq语法 [英] Dynamic linq syntax for subquery

问题描述

我想在Dynamic LINQ中进行以下查询.我尝试了一些解决方案，但尚未成功.

 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]

我当前的代码看起来像这样，但是我需要重写它以提供上面的SQL.

    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和SecondaryDimension.Name包含要分组的列的名称.

解决方案

考虑在子查询中查询数据库，然后将结果存储在数据表中，然后可以从中进行计算.

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

MSDN文章-计算

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.

解决方案

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

这篇关于子查询的动态linq语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！