How to SELECT WHERE NOT EXIST using LINQ

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       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         Now my question is  how could I make this in linQ   I m using entity framework  Hope someone could help  Thanks a lot

User · Accepted Answer

from s in context shift where  context employeeshift Any es  gt  es shiftid  s shiftid  amp  amp  es empid  57   select s    Hope this helps

User · Answer

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  lt  gt   E  AndAlso s accion  lt  gt  Nothing                              Where cs accion  lt  gt   E  AndAlso cs accion  lt  gt  Nothing                              Select c descripcion  Single   Is Nothing                       Where s accion  lt  gt   E  AndAlso s accion  lt  gt  Nothing                       Select s id  s Descripcion

User · Answer

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 lt int gt  empShiftIds   dc employeeshift Where p   gt  p EmpID   57  Select s   gt  s ShiftID  ToList       Here we get the list of our shifts List lt shift gt  shifts   dc shift Where p   gt   empShiftIds Contains p ShiftId   ToList

User · Answer

The outcome sql will be different but the result should be the same   var shifts   Shifts Where s   gt   EmployeeShifts Where es   gt  es ShiftID    s ShiftID  Any

User · Answer

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

[c#] How to SELECT WHERE NOT EXIST using LINQ?

Examples related to c#

Examples related to sql

Examples related to linq

Examples related to entity-framework