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!!!
This question is related to
c#
sql
linq
entity-framework
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());
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
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
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();
Source: Stackoverflow.com