EverSQL Database Performance Knowledge Base
Convert OrderBy Case statement in LINQ
Database type:
I'm trying to convert the following SQL from Oracle into a Linq to Entity query.
ORDER by
case when(e.prev_co = 'ABC' and(nvl(co_seniority, '1-jan-2099') < to_date('10-apr-2001')))
then '2001-04-01'
else to_char(nvl(co_seniority, '1-jan-2099'), 'YYYY-MM-DD') end,
nvl(co_seniority, '1-jan-2099'),
birth_dt
I was hoping I could use a function to pass in some parameters and have it return the correct date. I tried creating an new property called SortDate and then created a function on my page that would take in the parameters and return the correct date but that didn't work. I get and exception that says "LINQ to Entities does not recognize the method GetSortDate".
Model
SortByDate = GetSortDate(e.PREV_CO, e.CO_SENIORITY),
Function
public static DateTime GetSortDate(string PreviousCo, DateTime? CoSeniorityDate)
{
//set variable to default date
DateTime sortDate = System.DateTime.Parse("2001-04-01");
//set default date if NULL
if (CoSeniorityDate == null)
{
CoSeniorityDate = System.DateTime.Parse("2099-01-01");
}
if (PreviousCo == "ABC" && (CoSeniorityDate < System.DateTime.Parse("2001-04-10")))
{
sortDate = System.DateTime.Parse("2001-04-01");
}
else
{
sortDate = System.DateTime.Parse(CoSeniorityDate.ToString());
}
return sortDate;
}
Here is my complete EF
using (DataContext db = new DataContext())
{
db.Configuration.AutoDetectChangesEnabled = false; //no changes needed so turn off for performance.
var workStatus = new string[] { "1", "3" };
var company = new string[] { "EX", "SM" };
var eventReason = new string[] { "21", "22", "23" };
data = (from e in db.EMPLOYEE
where workStatus.Contains(e.WORKSTAT)
&& company.Contains(e.CO.Substring(0, 2))
&& ((e.EVENT_TYP != "35") || (e.EVENT_TYP == "35" && !eventReason.Contains(e.EVENT_RSN)))
select new Employee
{
Co = e.CO,
CityCode = e.CITY_CODE,
EmployeeNumber = e.EMP,
LastName = e.LAST_NAME,
FirstName = e.FIRST_NAME,
Position = e.ABV_POSITION_TITLE,
EmploymentType = e.PART_TIME_IND == "X" ? "PT" : "FT",
SeniorityDate = e.CO_SENIORITY == null ? DateTime.MaxValue : e.CO_SENIORITY,
BirthDate = e.BIRTH_DT,
SortByDate = GetSortDate(e.PREV_CO, e.CO_SENIORITY),
PreviousCo = e.PREV_CO
}).OrderBy(o => o.SortByDate).ThenBy(o => o.SeniorityDate).ThenBy(o => o.BirthDate).ToList();
}
Anyone have a suggestion on how I can convert this OrderBy?
UPDATED QUESTION
At the moment I have my query working correctly by using a secondary SELECT like @Markus showed. The first query just pulls the data and then all the formatting and calling of a method to get the correct SortByDate.
However, my manager would really prefer to do the sorting in the DB versus in memory. He let this one go because there are very few people calling this seniority list and only once a month.
For learning purposes I would like to see if I could get the DB to do all the sorting as @IvanStoev shows below. So, going back that route I’m not able to get the OrderBy to work exactly like it should.
If you look at the original SQL I’m trying to convert it first looks to see if the person had a previous company of “ABC” and if they do then look at the SeniorityDate (set a default date if that’s NULL) and compare it to an acquisition date. If that condition isn’t met then just use their SeniorityDate (set it’s default if NULL). Tricky….I know.
Using the suggested OrderBy in LinqPad and then looking at the returned SQL I can see that the first part of the OrderBy looks at the previous company and then the SeniorityDate and sets a value. Then it looks at the acquisition date. I need to somehow group some conditions to look at first which I don’t know it that’s possible.
SELECT t0.ABV_POSITION_TITLE, t0.BIRTH_DT, t0.CITY_CODE, t0.CO, t0.CO_SENIORITY, t0.EMP, t0.FIRST_NAME, t0.LAST_NAME, t0.PART_TIME_IND, t0.PREV_CO, t0.WORKSTAT
FROM SAP_EMPLOYEE t0
WHERE ((((t0.WORKSTAT IN (:p0, :p1) AND (t0.PERS_SUB_AREA = :p2)) AND SUBSTR(t0.CO, 0 + 1, 2) IN (:p3, :p4)) AND (t0.CO <> :p5)) AND ((t0.EVENT_TYP <> :p6) OR ((t0.EVENT_TYP = :p6) AND NOT t0.EVENT_RSN IN (:p7, :p8, :p9))))
ORDER BY (CASE WHEN ((t0.PREV_CO = :p10) AND (t0.CO_SENIORITY IS NULL)) THEN :p11 WHEN (t0.CO_SENIORITY < :p12) THEN :p13 ELSE COALESCE(t0.CO_SENIORITY, :p11) END), COALESCE(t0.CO_SENIORITY, :p11), t0.BIRTH_DT
-- p0 = [1]
-- p1 = [3]
-- p2 = [200A]
-- p3 = [EX]
-- p4 = [SM]
-- p5 = [EXGS]
-- p6 = [35]
-- p7 = [21]
-- p8 = [22]
-- p9 = [23]
-- p10 = [ABC]
-- p11 = [1/1/2099 12:00:00 AM]
-- p12 = [4/10/2001 12:00:00 AM]
-- p13 = [4/1/2001 12:00:00 AM]
I need to come up with something like
ORDER BY (CASE WHEN ((t0.PREV_CO = :p10) AND (COALESCE(t0.CO_SENIORITY, :p11) < :p12) THEN :p13 ELSE COALESCE(t0.CO_SENIORITY, :p11) END)
Here is the code I used in LinqPad.
void Main()
{
var workStatus = new string[] { "1", "3" };
var company = new string[] { "EX", "SM" };
var eventReason = new string[] { "21", "22", "23" };
var baseDate = new DateTime(2001, 4, 10); // 10-apr-2001
var minDate = new DateTime(2001, 4, 1); // 1-apr-2001
var abcDate = new DateTime(2001, 4, 10); // 10-apr-2001
var maxDate = new DateTime(2099, 1, 1); // 1-jan-2099
var data = (from e in SAP_EMPLOYEE
where workStatus.Contains(e.WORKSTAT)
&& e.PERS_SUB_AREA == "200A"
&& company.Contains(e.CO.Substring(0, 2))
&& e.CO != "EXGS"
&& ((e.EVENT_TYP != "35") || (e.EVENT_TYP == "35" && !eventReason.Contains(e.EVENT_RSN)))
orderby e.PREV_CO == "ABC" && e.CO_SENIORITY == null ? maxDate : e.CO_SENIORITY < abcDate ? minDate : e.CO_SENIORITY ?? maxDate,
e.CO_SENIORITY ?? maxDate,
e.BIRTH_DT
select new Employee
{
Co = e.CO,
CityCode = e.CITY_CODE,
EmployeeNumber = e.EMP,
LastName = e.LAST_NAME,
FirstName = e.FIRST_NAME,
Position = e.ABV_POSITION_TITLE,
EmploymentType = e.PART_TIME_IND == "X" ? "PT" : "FT",
SeniorityDate = e.CO_SENIORITY == null ? maxDate :
e.PREV_CO == "ABC" && e.CO_SENIORITY < twaDate ? maxDate : e.CO_SENIORITY,
LOA = e.WORKSTAT == "1" ? "LOA" : "",
ABC = e.PREV_CO == "ABC" ? "ABC" : "",
BirthDate = e.BIRTH_DT,
PreviousCo = e.PREV_CO
}).ToList();
data.Dump();
}