通过交叉联接将SQL转换为Linq [英] SQL to Linq conversion with cross join

问题描述

有人可以帮助将以下sql转换为c#中的linq吗?

Can someone please help in converting the following sql to linq in c#?

select s.SYSTEM_NAME,
       r.RESET_CODE,
       COUNT(v.reset_code) 
  from (select distinct system_name 
          from tbl) s 
cross join (select distinct reset_code 
              from tbl) r 
left join tbl v on v.SYSTEM_NAME = s.SYSTEM_NAME 
               and v.RESET_CODE=r.RESET_CODE 
 group by s.SYSTEM_NAME,r.RESET_CODE 

推荐答案

交叉连接通常表示为查询表达式中的多个from子句，或使用扩展方法语法对SelectMany的调用.

Cross joins are generally represented as multiple from clauses in a query expression, or a call to SelectMany in extension method syntax.

所以查询的第一部分可能是:

So the first part of your query might be:

var query = from systemName in db.Table.Select(x => x.SystemName).Distinct()
            from resetCode in db.Table.Select(x => x.ResetCode).Distinct()
            ...

左外部联接通常用"join ... into ..."查询表示，可能是这样的:

Left outer joins are usually represented with a "join ... into ..." query, possibly like this:

var query = from systemName in db.Table.Select(x => x.SystemName).Distinct()
            from resetCode in db.Table.Select(x => x.ResetCode).Distinct()
            join tmp in db.Table on 
                new { ResetCode = resetCode, SystemName = systemName } 
                equals new { tmp.ResetCode, tmp.SystemName }
                into tmpGroup
            select new { ResetCode = resetCode,
                         SystemName = systemName,
                         Count = tmpGroup.Count() };

我不确定Count部分，说实话...我不确定100％COUNT(v.ResetCode)在原始SQL中的作用.

I'm not sure about the Count part, to be honest... I'm not 100% sure what the COUNT(v.ResetCode) does in your original SQL.

这篇关于通过交叉联接将SQL转换为Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！