使用subselect上的连接将sql查询转换为linq语句

I am trying to convert the following sql query to LINQ statement

我试图将以下sql查询转换为LINQ语句

SELECT t.*
FROM    (
        SELECT Unique_Id, MAX(Version) mversion
        FROM    test
        GROUP BY Unique_Id
    ) m INNER JOIN
    test t  ON m.Unique_Id = t.Unique_Id AND m.mversion = t.Version

LINQ statement

var testalt = (from altt in CS.test
group altt by altt.Unique_Id into g
join bp in CS.alerts on g.FirstOrDefault().Unique_Id equals bp.Unique_Id
select new ABCBE
{
ABCName= bp.Name,
number = bp.Number,
Unique_Id =  g.Key,
Version = g.Max(x=>x.Version)
});

I am getting an error of where clause. Please help

我收到了where子句的错误。请帮忙

SQL FIDDLE

1 个解决方案

#1

This is not an easy straight forward conversion but you can accomplish the same thing using linq method syntax. The first query is executed to an expression tree, then you are joining that expression tree from the grouping against CS.alerts. This combines the expression tree from CS.test query into the expression tree of CS.alerts to join the two expression trees.

这不是一个简单的直接转换,但您可以使用linq方法语法完成相同的操作。第一个查询将执行到表达式树,然后您将从CS.alerts的分组中加入该表达式树。这将来自CS.test查询的表达式树组合到CS.alerts的表达式树中,以连接两个表达式树。

The expression tree is evaluated to build the query and execute said query upon enumeration. Enumeration in this case is the ToList() call but anything that gets a result from the enumeration will execute the query.

评估表达式树以构建查询并在枚举时执行所述查询。在这种情况下,枚举是ToList()调用,但从枚举中获取结果的任何内容都将执行查询。

var query1 = CS.test.GroupBy(x => x.Unique_Id);
var joinResult = CS.alerts.Join(query1, 
   alert => new { ID = alert.Unique_Id, Version = alert.Version },
   test => new { ID = test.Key, Version = test.Max(y => y.Version }, 
   (alert, test) => new ABCBE { 
      ABCName = alert.Name, 
      number = alert.Number, 
      Unique_Id = test.Key, 
      Version = test.Max(y => y.Version)
    }).ToList();

Because query1 is still an IQueryable and you are using CS.alerts (which I'm guessing CS is your data context) it should join and build the query to execute upon the ToList() enumeration.

因为query1仍然是IQueryable并且您正在使用CS.alerts(我猜CS是您的数据上下文),它应该加入并构建查询以在ToList()枚举上执行。