Convert SQL with multiple join into LINQ

Question

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

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()

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

Thanks a million.

I suggest with such a question to change original table & attribute names to something more self explanatory. Also the question could be improved by adding description of the query type like grouped left join with interval condition or similar. Of course sample data would help too. — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Jul 4, 2017 at 10:59

Jonathan Willcock · Accepted Answer · 2017-07-04 09:22:50Z

1

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

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
             };

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!

edited Jul 4, 2017 at 9:22

answered Jul 4, 2017 at 9:17

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vojtěch Dohnal Over a year ago

Your mv query is inner join, he needs the left join.

Jonathan Willcock Over a year ago

@VojtěchDohnal Yes I know. I point this out in my answer, plus how to add back any potential missing answers. The resultset in lj is the desired LEFT JOIN

Collectives™ on Stack Overflow

Convert SQL with multiple join into LINQ

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related