将具有多个联接的SQL转换为LINQ [英] Convert SQL with multiple join into LINQ

问题描述

我想知道如何将以下sql语句更改为C#中的Linq或Lambda Extension

I would like to know how can i change the following sql statement into Linq or Lambda Extension in C#

SELECT m.mdes as AgeGroup,COUNT(DISTINCT(mbcd))as "No.of Member" FROM mageg m
LEFT JOIN  (select distinct(mbcd) ,mage
FROMtevtl
JOIN mvipm 
ON tevtl.mbcd = mvipm.mvip
WHERE datm >= '2014-04-01'
AND datm <= '2014-04-30'
)vip
ON m.tage >= vip.mage AND m.fage <= vip.mage
GROUP BY m.mdes

我设法完成LINQ语句的前半部分.不确定是否正确 这是上半年.我不知道如何与左联接连接.

I manage to do the first half of the LINQ statement. Not sure If it is correct here is the first half. I do not know how to connect with the left join.

(from mem in mvipms
from log in tevtls
from grp in magegs
where mem.mage >=grp.fage && mem.mage <=grp.tage && mem.mvip.Equals(log.mbcd)
&& log.datm >= DateTime.Parse("2014-04-01") && log.datm <= DateTime.Parse("2014-04-30")
select new {mem.mvip,grp.mdes}).Distinct()

请咨询.我正在使用MSSQL 2008和VS2010.

Pls advice. I am using the MSSQL 2008 and VS2010.

感谢一百万.

推荐答案

我不是LINQ方面的专家，但是由于没有其他人回答，因此可以了！

I am no expert on LINQ, but since no-one else has answered, here goes!

首先，您不能(AFAIK)对等号以外的任何东西进行LINQ连接，因此必须更改LEFT JOIN的结构.部分出于调试目的，也出于可读性考虑，我更喜欢将LINQ布局为一口大小的块，因此在您的情况下，我将执行以下操作(假设我正确理解了您的数据结构):

Firstly you cannot (AFAIK) do a LINQ join on anything other than equals so the structure of your LEFT JOIN has to change. Partly for debugging purposes, but also for readability, I prefer to layout my LINQ in bite-size chunks, so what I would do in your case is something like this (assuming I have understood your data structure correctly):

    var vip = (from t in tevtl
               join v in mvipl
               on t.mbcd equals v.mvip
               where t.datm >= new DateTime(2014, 4, 1) && t.datm <= new DateTime(2014, 4, 30)
               select new { t.mbcd, v.mage }).Distinct();
    var mv = from m in magegl
             from v in vip
             where m.tage >= v.mage && m.fage <= v.mage
             select new
             {
                 m.mdes,
                 v.mbcd
             };
    var gmv = from m in mv
              group m by new { m.mdes } into grp
              select new
              {
                  mdes = grp.Key.mdes,
                  CountMBCD = grp.Count()
              };
    var lj = from m in magegl
             join v in gmv
             on m.mdes equals v.mdes into lhs
             from x in lhs.DefaultIfEmpty()
             select new
             {
                 AgeGroup = m.mdes,
                 CountMBCD = x != null ? x.CountMBCD : 0
             };

通过解释的方式. mv是与您的左联接等效的结构，因为它具有相关的where子句，但显然不包含任何null-等效于INNER JOIN. gmv是mv上的一个群组，因此仍然有效地是INNER JOIN.然后选择丢失的mdes(如果有的话)，我使用添加的语法DefaultIfEmpty()在magel上重新加入-这会将连接转换为等效于LEFT OUTER JOIN.

By way of explanation. mv is the equivalent structure for your left join in that it has the relevant where clause, but obviously it does not contain any nulls - it is equivalent to an INNER JOIN. gmv is a group on mv, so is still effectively an INNER JOIN. Then to pick up the missing mdes (if any) I re-join on magel with the added syntax DefaultIfEmpty() - this converts the join into the equivalent of a LEFT OUTER JOIN.

没有任何示例数据，我无法对其进行测试，但我希望它能为您提供足够的指导方向！

Without any sample data, I haven't been able to test this, but I hope it gives you enough to point you in the right direction!

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