How can I use LINQ to SELECT WHERE NOT EXIST?

c# entity-framework linq sql

Question

I have to list all "shift" data to be assigned to an "employee" but shift data must not be included if it is already existing in employee's data. Let's see the image sample.

No filtering yet

This query solves the problem. I found this here:
Scott's Blog

select * from shift where not exists 
(select 1 from employeeshift where shift.shiftid = employeeshift.shiftid
and employeeshift.empid = 57);  

Let's see the result:

Filtered

Now my question is, how could I make this in linQ ? I'm using entity framework.
Hope someone could help. Thanks a lot!!!

1
54
7/19/2013 11:52:23 PM

Accepted Answer

from s in context.shift
where !context.employeeshift.Any(es=>(es.shiftid==s.shiftid)&&(es.empid==57))
select s;

Hope this helps

94
1/27/2012 9:24:26 AM

Popular Answer

The outcome sql will be different but the result should be the same:

var shifts = Shifts.Where(s => !EmployeeShifts.Where(es => es.ShiftID == s.ShiftID).Any());


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow