CODEWITHSUNDEEP
.netlinqc#-4.0entity-framework-4
user20358
user20358

Reputation: 14736

Linq query with left join having multiple tables

This is my linq query

    from row in (
    from c in db.TabC
    from cpd in db.TabPD
    from slm in db.TabSLM
    where cpd.SLid == slm.SLid
    where c.Id == cpd.CID
    where c.O_Id == 1
    select new { c, cpd, slm })
group row in row.slm.SLType into g
select new
{
    SLType = g.Key,
    EStat = g.Count(r => r.c.EstatID != null),
    Training = g.Count(r => r.cpd.TrId != null),
    TrainingComplete =
        g.Count(r => r.cpd.TrStat == 44),
    AssessmentComplete =
        g.Count(r => r.cpd.CndAssess == 44)
};

I need to make a left join to table db.TabSLM and get as many records as there are in that table. TabC is a master table and so is TabSLM. TabPD has detail records that references both TabC and TabSLM. With this query, when there are no matches in TabPD for either TabC rowid or TabSLM rowid, I get no records returned. What I want is to return the number of records present in TabSLM incase there are no matches. Any ideas on how to modify this query?

Here is below is the sample data for each of the three tables with minimal fields listed

TabC
ID  O_ID   Name
1       1     ABC
2       1     XYZ
3       1     RST

TabPD
RowID    CID    SLid 
1        2      1
2        1      1
3        3      2
4        ...

TabSLM
SLid   SLType
1      single level 
2      Multi level

With the query above, I am able to get records where TabC.O_ID == 1, but not where TabC.O_ID == 3 because TabC.O_ID doesn't have any records with ID == 3. I still want to display SLType with the other columns showing as 0

Thanks for your time...

Upvotes: 0

Views: 1466

Answers (2)

Saket Kumar
Saket Kumar

Reputation: 4835

Just try this once:

var query =
    from row in (
      from c in db.TabC
      join cpd in db.TabPD on c.Id equals cpd.CID into temp
                          from cpd in temp.DefaultIfEmpty()
      from slm in db.TabSLM.Where(x => cpd.SLid == x.SLid)
                           .DefaultIfEmpty()
      where c.O_Id == 1
      select new { c, cpd, slm })
group row by row.slm.SLType into g
select new
{
    SLType = g.Key,
    EStat = g.Count(r => r.c.EstatID != null),
    Training = g.Count(r => r.cpd.TrId != null),
    TrainingComplete =
        g.Count(r => r.cpd.TrStat == 44),
    AssessmentComplete =
        g.Count(r => r.cpd.CndAssess == 44)
};

Upvotes: 0

Aducci
Aducci

Reputation: 26644

You need to use DefaultIfEmpty

var query =
    from row in (
      from c in db.TabC
      join cpd in db.TabPD on c.Id equals cpd.CID
      from slm in db.TabSLM.Where(x => cpd.SLid == x.SLid)
                           .DefaultIfEmpty()
      where c.O_Id == 1
      select new { c, cpd, slm })
group row by row.slm.SLType into g
select new
{
    SLType = g.Key,
    EStat = g.Count(r => r.c.EstatID != null),
    Training = g.Count(r => r.cpd.TrId != null),
    TrainingComplete =
        g.Count(r => r.cpd.TrStat == 44),
    AssessmentComplete =
        g.Count(r => r.cpd.CndAssess == 44)
};

Upvotes: 2

Related Questions