[Solved] Convert OrderBy Case statement in LINQ

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 40): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `CO` is indexed, the index won’t be used as it’s wrapped with the function `SUBSTR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  2. Avoid Calling Functions With Indexed Columns (query line: 87): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `CO` is indexed, the index won’t be used as it’s wrapped with the function `SUBSTR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  3. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  5. Use UNION ALL instead of UNION (query line: 70): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
CREATE INDEX sap_employee_idx_pers_ar_event_worksta_co ON SAP_EMPLOYEE (PERS_SUB_AREA,EVENT_TYP,WORKSTAT,CO);
CREATE INDEX sap_employee_idx_pers_area_workstat_co ON SAP_EMPLOYEE (PERS_SUB_AREA,WORKSTAT,CO);
The optimized query:
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
        ((SELECT
            t0.ABV_POSITION_TITLE AS t0_abv_position_title,
            t0.BIRTH_DT AS t0_birth_dt,
            t0.CITY_CODE AS t0_city_code,
            t0.CO AS t0_co,
            t0.CO_SENIORITY AS t0_co_seniority,
            t0.EMP AS t0_emp,
            t0.FIRST_NAME AS t0_first_name,
            t0.LAST_NAME AS t0_last_name,
            t0.PART_TIME_IND AS t0_part_time_ind,
            t0.PREV_CO AS t0_prev_co,
            t0.WORKSTAT AS 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
                        ) 
                        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) 
UNION
DISTINCT (SELECT
t0.ABV_POSITION_TITLE AS t0_abv_position_title,
t0.BIRTH_DT AS t0_birth_dt,
t0.CITY_CODE AS t0_city_code,
t0.CO AS t0_co,
t0.CO_SENIORITY AS t0_co_seniority,
t0.EMP AS t0_emp,
t0.FIRST_NAME AS t0_first_name,
t0.LAST_NAME AS t0_last_name,
t0.PART_TIME_IND AS t0_part_time_ind,
t0.PREV_CO AS t0_prev_co,
t0.WORKSTAT AS 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))) 
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)
) AS union1 
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),
union1.t0_birth_dt

Related Articles



* original question posted on StackOverflow here.