How to use BETWEEN in ASP.NET CORE EF?
-
I have this SQL Statement "SELECT vYear, EmpCode, VacType, DocNum, DocDate, DepCode, SecCode, vName, vStart, vEnd, vDays, LType, CUsrName, CDate, MUsrName, MDate, Result, Reson, EmpCode_Act, MngID FROM NetVac WHERE ('12/07/2022' BETWEEN vStart AND vEnd) AND (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) OR (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) AND (CONVERT(DATETIME, '2022-07-13 00:00:00', 102) BETWEEN vStart AND vEnd)" and it work fine, i would like to do the same with EF but not using ADO or >,< Can anybody help me
-
I have this SQL Statement "SELECT vYear, EmpCode, VacType, DocNum, DocDate, DepCode, SecCode, vName, vStart, vEnd, vDays, LType, CUsrName, CDate, MUsrName, MDate, Result, Reson, EmpCode_Act, MngID FROM NetVac WHERE ('12/07/2022' BETWEEN vStart AND vEnd) AND (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) OR (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) AND (CONVERT(DATETIME, '2022-07-13 00:00:00', 102) BETWEEN vStart AND vEnd)" and it work fine, i would like to do the same with EF but not using ADO or >,< Can anybody help me
If you are using Entity Framework and you want to use BETWEEN, the standard way is to use => and<=.
-
I have this SQL Statement "SELECT vYear, EmpCode, VacType, DocNum, DocDate, DepCode, SecCode, vName, vStart, vEnd, vDays, LType, CUsrName, CDate, MUsrName, MDate, Result, Reson, EmpCode_Act, MngID FROM NetVac WHERE ('12/07/2022' BETWEEN vStart AND vEnd) AND (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) OR (vYear = 2022) AND (EmpCode = 5229) AND (VacType <> 10) AND (VacType <> 15) AND (VacType <> 16) AND (VacType <> 17) AND (VacType <> 18) AND (VacType <> 19) AND (VacType <> 20) AND (VacType <> 21) AND (VacType <> 23) AND (VacType <> 24) AND (Result <> 1) AND (CONVERT(DATETIME, '2022-07-13 00:00:00', 102) BETWEEN vStart AND vEnd)" and it work fine, i would like to do the same with EF but not using ADO or >,< Can anybody help me
I'd start by looking at what your SQL is trying to do and simplify it. From what I can see, your WHERE clause can be reduced to
vYear = 2022 AND EmpCode = 5229 AND NOT VacType IN (10, 15, 16, 17, 18, 19, 20, 21, 23, 24) AND Result <> 1 AND ('12/07/2022' BETWEEN vStart AND vEnd OR CONVERT(DATETIME, '2022-07-13 00:00:00', 102) BETWEEN vStart AND vEnd )I do not understand what the date ranges are for and why there are two different ways of representing dates unless your data has two sets of texts neither as real dates, in which case the comparisons are meaningless.