LINQ left outer join with exclusion

Question

I'm trying to exclude the results of a first query from being included in the results of my second query.

The SQL equivalent is here, where A is my current query, and B is the old query.

I've been trying to use this guide to do the left join, but I can't seem to figure out how it should work in my case. I'm just not understanding how this should work (I can't get the syntax highlighting to be happy).

var emp = from employee in empl
    join jc in jce on callout.job_class_code_fk equals jc.job_class_code_fk
    join av in ab on employee.employee_id_pk equals av.employee_id_fk
    join sh in sho on employee.employee_id_pk equals sh.employee_id_fk into lj
    from rnd2 in lj.DefaultIfEmpty()
    orderby employee.seniority descending
    select new
    {
      eid = employee.employee_id_pk,
      sen = employee.seniority,
      nam = employee.employee_name,
      pho = employee.phone_number,
      lje = sho == null ? sho.employee_id_fk : null //left outer join with exclusion??
    };

EDIT:: Based on the suggestions in the comments I've tried both of the following. While I don't have syntax issues any more, neither of the following return ANY results, so there's still something wrong here.

    var emp = from employee in empl
              join jc in jce on callout.job_class_code_fk equals jc.job_class_code_fk
              join av in ab on employee.employee_id_pk equals av.employee_id_fk
              join sh in sho on employee.employee_id_pk equals sh.employee_id_fk into lj
              from rnd2 in lj.DefaultIfEmpty() where sho == null
              orderby employee.seniority descending
              select new
              {
                  eid = employee.employee_id_pk,
                  sen = employee.seniority,
                  nam = employee.employee_name,
                  pho = employee.phone_number,
              };

    var emp = from employee in empl
              join jc in jce on callout.job_class_code_fk equals jc.job_class_code_fk
              join av in ab on employee.employee_id_pk equals av.employee_id_fk
              join sh in sho on employee.employee_id_pk equals sh.employee_id_fk into lj
              from rnd2 in lj.DefaultIfEmpty() where rnd2 == null
              orderby employee.seniority descending
              select new
              {
                  eid = employee.employee_id_pk,
                  sen = employee.seniority,
                  nam = employee.employee_name,
                  pho = employee.phone_number,
              };

Possible duplicate of [How would you do a "not in" query with LINQ?](http://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq) — JuanR, Jan 17 '17 at 19:51
I don't really see how mine is a duplicate, when I've actually asked the question in language that a competent searcher would find it, and taken the time to provide diagrams, code samples, and references. The other question may have fundamentally been asking the same thing, but these two are a far cry from the same question. — Scuba Steve, Jan 17 '17 at 19:54
`WHERE B.Key IS NULL` from the picture translates to `where rnd2 == null` in your query. — Ivan Stoev, Jan 17 '17 at 19:54
http://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Luis Lavieri, Jan 17 '17 at 19:54
Luis, I'm not asking how to do a left outer join, I'm asking how to do a left outer join with exclusion. — Scuba Steve, Jan 17 '17 at 19:56
Ivan Stoev, but I have to do the left join before the exclusion, no? — Scuba Steve, Jan 17 '17 at 19:57
Sure you have to. What are you asking is called [antijoin](https://en.wikipedia.org/wiki/Relational_algebra) and the typical implementation in queries is `left outer join` with right side `null` check (exactly as shown in the picture, all you need is to use LINQ left join equivalent and combine it with null check as I mentioned in the first comment). — Ivan Stoev, Jan 17 '17 at 20:24
That's the problem I'm having. I don't understand how to complete the left-join and do the check for null on the right side. Please look at the guide I posted, my code is a reproduction of that left-join. — Scuba Steve, Jan 17 '17 at 20:37
Yes indeed. So `from rnd2 in lj.DefaultIfEmpty()` completes your left join. What do you want to do next? Btw, if you replace `rnd2` with `sh`, does it make more sense to you? — Ivan Stoev, Jan 17 '17 at 20:48
yeah it'd make more sense to replace rnd2 with sh. So after completing the left join, I want to select rows where the right side is null, right? — Scuba Steve, Jan 17 '17 at 21:38

score 1 · Accepted Answer · answered Jan 18 '17 at 00:48

Okay so (to me anyway) the answer that is the easiest to read and understand ended up being this.

Create two lists, the one I want to exclude, and the master list.

They we run master.Except(exclude) and voila. We've accomplished the effect of a left outer join with exclusion.

Here's the working code. The solutions above could very well have worked, as there was another problem with how the first list was being put together.

            var ex = from employee in empl
                 join sh in sho on employee.employee_id_pk equals sh.employee_id_fk
                 select new
                 {
                     eid = employee.employee_id_pk,
                     sen = employee.seniority,
                     nam = employee.employee_name,
                     pho = employee.phone_number,
                 };
        ex.Distinct();

        //get a list of employees who have the enabled orientations and job classifications
        var emp = from employee in empl
                  join jc in jce on employee.employee_id_pk equals jc.employee_id_fk
                  join av in ab on employee.employee_id_pk equals av.employee_id_fk
                  orderby employee.seniority descending
                  select new
                  {
                      eid = employee.employee_id_pk,
                      sen = employee.seniority,
                      nam = employee.employee_name,
                      pho = employee.phone_number,
                  };
        emp = emp.Distinct();
        emp = emp.Except(ex);

LINQ left outer join with exclusion

1 Answers1