CODEWITHSUNDEEP
c#sqllinqentity-framework
fiberOptics
fiberOptics

Reputation: 7165

How to SELECT WHERE NOT EXIST using LINQ?

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!!!

Upvotes: 65

Views: 134013

Answers (6)

Gerrie Pretorius
Gerrie Pretorius

Reputation: 4141

From what I can see here, most solutions are based of an IN or NOT IN clause. I would argue that a Left join and where ID is null type of solution is better.

This is what I am using.

var linqQuery = 
from shift in context.Shift.Where(x=> x.IfYouHaveMoreConditions == true)
from employeeShift in context.EmployeeShift.Where(x=> shift.ShiftId == x.ShiftId ) /* this will become your left join condition */
where employeeShift.ShiftId == null /*this will create a compiler warning, because the ID cannot be null, but will still work and compile and is necessary*/
select shift;

Then this will result in the following SQL:

SELECT shift.* FROM shift
LEFT OUTER JOIN emlpoyeeshift ON shift.shiftid = employeeshift.shiftid
WHERE emlployeeshift.shiftid IS NULL

and then bob is your uncle

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

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

Upvotes: 117

julio
julio

Reputation: 1

        Dim result2 = From s In mySession.Query(Of CSucursal)()
                      Where (From c In mySession.Query(Of CCiudad)()
                             From cs In mySession.Query(Of CCiudadSucursal)()
                             Where cs.id_ciudad Is c
                             Where cs.id_sucursal Is s
                             Where c.id = IdCiudad
                             Where s.accion <> "E" AndAlso s.accion <> Nothing
                             Where cs.accion <> "E" AndAlso cs.accion <> Nothing
                             Select c.descripcion).Single() Is Nothing
                      Where s.accion <> "E" AndAlso s.accion <> Nothing
                      Select s.id, s.Descripcion

Upvotes: -2

hyp
hyp

Reputation: 1380

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());

Upvotes: 32

Ceridan
Ceridan

Reputation: 2516

First of all, I suggest to modify a bit your sql query:

 select * from shift 
 where shift.shiftid not in (select employeeshift.shiftid from employeeshift 
                             where employeeshift.empid = 57);

This query provides same functionality. If you want to get the same result with LINQ, you can try this code:

//Variable dc has DataContext type here
//Here we get list of ShiftIDs from employeeshift table
List<int> empShiftIds = dc.employeeshift.Where(p => p.EmpID = 57).Select(s => s.ShiftID).ToList();

//Here we get the list of our shifts
List<shift> shifts = dc.shift.Where(p => !empShiftIds.Contains(p.ShiftId)).ToList();

Upvotes: 3

Matt Roberts
Matt Roberts

Reputation: 26907

How about..

var result = (from s in context.Shift join es in employeeshift on s.shiftid equals es.shiftid where es.empid == 57 select s)

Edit: This will give you shifts where there is an associated employeeshift (because of the join). For the "not exists" I'd do what @ArsenMkrt or @hyp suggest

Upvotes: -1

Related Questions