Outer join query using LINQ to Entities [duplicate]

Question

There are 0 to n departments in my company, 0 to n offices in 1 department, and 0 to n emplyees in 1 office.Now I need a query using linq to list emplyees's average age by department, if nobody in a department then default average is 0. code is below:

    DataContext ctx = new DataContext();

    var q0 = from d in ctx.Departments
             join o in ctx.Offices on d.Id equals o.DepartmentId
             join e in ctx.Employees on o.Id equals e.OfficeId
             group e by d into de
             select new {
                DepartmentId = de.Key.Id,
                AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
             };


    var q1 = from d in ctx.Departments
             join de in q0 on d.Id equals de.DepartmentId into des
             from de in des.DefaultIfEmpty()
             select new
             {
                 DepartmentName = d.Name,
                 AverageAge = de == null ? 0 : de.AverageAge
             };

    var result = q1.ToList();
    foreach (var item in result)
    {
        Console.WriteLine("{0}-{1}", item.DepartmentName, item.AverageAge);
    }
    ctx.Dispose();

But how to combine q0 and q1 to one query?

Here's a good article about making joins with LINQ: codeproject.com/Articles/488643/LinQ-Extended-Joins — kor_
– kor_, Commented Dec 19, 2012 at 8:21

Pang · Accepted Answer · 2021-08-31 02:20:10Z

7

Were you meaning something along the lines of:

var newQ2 = from d in ctx.Departments
                 outer left join o in ctx.Offices on d.Id equals o.DepartmentId
                 outer left join e in ctx.Employees on o.Id equals e.OfficeId
                 group e by d into de
                 select new {
                    DepartmentId = de.Key.Id,
                    AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
                 };

Changed to:

var newQ2 = from d in ctx.Departments
                     join o in ctx.Offices on d.Id equals o.DepartmentId
                     join e in ctx.Employees on o.Id equals e.OfficeId
                     group e by d into de.DefaultIfEmpty()
                     select new {
                        DepartmentId = de.Key.Id,
                        DepartdentName = select d.Name from d where d.id = de.Key.Id,
                        AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
                     };

Addendum: I would use a sub-select to match up the extra name, not knowing your db layout I have improvised from your code, but you could make it more efficient and have a multipart join based on sub-selects as well. Sorry I cant test this code out at work, I can approximate fairly well, but would need some more info on where your department names are located if you need a more detailed answer:) I have changed the outer left joins back to joins, sorry I forgot in C# with linq you can use DefaultIfEmpty() to cause outer left join behaviour in code.

An outer left join will return nulls where there are no corresponding values, but will allow returns on any parts that do have a corresponding value. Join however will not return any null entries which I suspect is why you had the two queries?

The only caveat on the query I have presented is that you will need to infill any values you require before you use them if they are nulls, for example DepartmentId will need some logic to populate it in case DE is null.

edited Aug 31, 2021 at 2:20

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Dec 19, 2012 at 8:18

GMasucci

2,90224 silver badges42 bronze badges

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

3 Comments

pstrjds Over a year ago

I think you could modify this one a little further to pull in the name part and then I believe the OPs second query is unnecessary.

user1729842 Over a year ago

outer left join is a good solution.but it's not valid linq syntax, if the syntax is valid , the 3 words will be blue as c# key word.

Gert Arnold Over a year ago

I don't understand how this answer was upvoted so often. It's invalid code, incomprehensible.

user1729842 · Accepted Answer · 2012-12-20 02:06:52Z

0

Thank you all，I have got the answer:

        var q1 = 
                 from d in ctx.Departments 
                 from o in ctx.Offices.Where(o => o.DepartmentId == d.Id).DefaultIfEmpty()
                 from e in ctx.Employees.Where(e =>  e.OfficeId == o.Id).DefaultIfEmpty()
                 group e by d into de
                 select new {
                    DepartmentName = de.Key.Name,
                    AverageAge = de.Average(e => e == null ? 0 : e.Age),
                 };

answered Dec 20, 2012 at 2:06

user1729842

1651 gold badge2 silver badges5 bronze badges

Collectives™ on Stack Overflow

Outer join query using LINQ to Entities [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related