linq2sql join select data that is not in another table

Question

Please do not respond using lamba. I have found similar threads but still need help.

I am trying to display the names from the employees table that are not employees of the currently selected manager.

My two sql tables are structured like this but this is fake data

Employees:   
           pk      name     
           1      bob
           2      sam
           3      greg
           4      kip
           5      jill
           6      kelly
           7      chris


ExpenseTeamMembers: 

           pk      expMgrPk     empPk     
           1         7          2
           2         7          5
           3         7          1
           4         3          6
           5         3          4

So if the the current selected (mgr variable) is 3 I want to get the names of all empPks in the employees table except for 6, 4. (kelly, kip) Right now unselectedEmps = sam, jill, bob instead of all 5 of other names from the employees table.

var unselectedEmps = (from u in db.employees
                      join o in db.expenseTeamMembers on u.pk equals o.empPk
                      where o.expMgrPk != mgr 
                      select u.name).ToList();

                lstAvailable.DataSource = unselectedEmps;

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

The problem is that you are doing an inner join when you actually need a left outer join

See this SO question

edited May 23 '17 at 12:03

Community

1
1

answered Jul 25 '12 at 02:29

cordialgerm

8,403
5
31
47

Still getting same results with var unselectedEmps = (from u in db.employees join o in db.expenseTeamMembers on u.pk equals o.empPk into sr from x in sr.DefaultIfEmpty() where x.expMgrPk != mgr select u.name).ToList(); – Sealer_05 Jul 25 '12 at 02:45
I think you want to swap the order, you want everything from employees, so that should be in the 2nd spot, expenseTeamMembers should go first – Ted Elliott Jul 25 '12 at 02:52
No same result. var unselectedEmps = (from o in db.expenseTeamMembers join u in db.employees on o.empPk equals u.pk into sr from x in sr.DefaultIfEmpty() where o.expMgrPk != mgr select x.name).ToList(); – Sealer_05 Jul 25 '12 at 02:57
Even if I delete my where clause now it still only searches the expenseTeamMembers table and not the employees table – Sealer_05 Jul 25 '12 at 03:19

Bert · Accepted Answer · 2012-07-29T06:20:15.530

1

After our extended discussion, I think what you want is this.

from u in db.Employees
where !(from e in db.ExpenseTeamMembers
        where e.expMgrPk == selectedMgr.pk
        select e.empPk).Contains(u.pk)
select u.Name

edited Jul 29 '12 at 06:20

answered Jul 25 '12 at 03:33

Bert

80,741
17
199
164

The top one is not working. It is still retrieving the records I dont want. The outer join works but it is outputting {name= in front of every record. Is there a way I can get rid of that without parsing? Thanks!! – Sealer_05 Jul 25 '12 at 04:54
1

@osiris355 Try it now, with the modified final select. – Bert Jul 25 '12 at 05:13
I found a bug after I add a record to the expenseTeamMembers table. I will update tonight or tomorrow after i have some more time to debug. – Sealer_05 Jul 27 '12 at 00:01
Its pretty odd. The first time I add a record to the expenseTeamMembers table I fire this query and it works flawlessly. As soon as I try to add the same employee to a different manager it no longer refreshes correctly. I can add a different employee to a different manager fine. its just when I try to use the same emp with two different expMgrPks. I've been trying to figure it out but am struggling to come up with a solution. – Sealer_05 Jul 27 '12 at 23:13
To be more specific the second time i add an employee it still shows the employee I have already added. So it shows the full list. Let me know if I need to give a better description – Sealer_05 Jul 27 '12 at 23:20
1

@osiris355 I've been playing around with the query in LinqPad, adding employees, changing their managers, etc, and it seems to work every time for me. That means the issue probably lies elsewhere, or I don't completely understand. Perhaps you are missing a DataBind after you've changed the datasource? To explore it more, I'd really need to see some more code. – Bert Jul 27 '12 at 23:50
No the data should be rebinding because it is firing your query after every record i add or change the manager in the combo box. Is there a cleaner way i can submit my code to you than through comments? Email or something? I really appreciate your help! – Sealer_05 Jul 27 '12 at 23:56
1

@osiris355 You can zip it and email to either of the addresses in my profile. I may not be able to look at it until tomorrow. – Bert Jul 28 '12 at 00:03
1

@osiris355 Take a look at the current answer, or your email. – Bert Jul 29 '12 at 06:22

score 1 · Answer 3 · answered Jul 25 '12 at 04:34

I have tried the following and it is giving the correct output. Please try it:

List<Employees> emps = new List<Employees>();
        emps.Add(new Employees { PK = 1, Name = "bob" });
        emps.Add(new Employees { PK = 2, Name = "sam" });
        emps.Add(new Employees { PK = 3, Name = "greg" });
        emps.Add(new Employees { PK = 4, Name = "kip" });
        emps.Add(new Employees { PK = 5, Name = "jill" });
        emps.Add(new Employees { PK = 6, Name = "kelly" });
        emps.Add(new Employees { PK = 7, Name = "chris" });

        List<ExpenseTeamMembers> etm = new List<ExpenseTeamMembers>();
        etm.Add(new ExpenseTeamMembers { empPK = 2, ExpMgrPK = 7, PK = 1 });
        etm.Add(new ExpenseTeamMembers { empPK = 5, ExpMgrPK = 7, PK = 2 });
        etm.Add(new ExpenseTeamMembers { empPK = 1, ExpMgrPK = 7, PK = 3 });
        etm.Add(new ExpenseTeamMembers { empPK = 6, ExpMgrPK = 3, PK = 4 });
        etm.Add(new ExpenseTeamMembers { empPK = 4, ExpMgrPK = 3, PK = 5 });

        var query = from t in
                        (
                            from emp in emps
                            join o in etm on emp.PK equals o.empPK into j
                            from k in j.DefaultIfEmpty()
                            select new { Name = k == null ? string.Empty : emp.Name })
                    where t.Name != string.Empty
                    select t.Name;

I am not working with a collection. I am working with sql data — Sealer_05, Jul 25 '12 at 04:36
I have taken collection only to display how the query can be written. You can replace the emps with db.Employee and so on. — Umesh, Jul 25 '12 at 05:50
Im trying to get your code to work but I am having some problems. Since I am searching for pk's dont I need to weange the string.empty to some how check for an integer? Also I dont see how this will work consider there is no checking for the expense Manager — Sealer_05, Jul 26 '12 at 23:57

linq2sql join select data that is not in another table

3 Answers3