在LINQ中转换OrderBy Case语句

我正在尝试将以下SQL从Oracle转换为Linq-to-Entity查询。

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

我希望我可以使用一个函数来传递一些参数，并让它返回正确的日期。我尝试创建一个名为SortDate的新属性，然后在页面上创建了一个函数，该函数将接收参数并返回正确的日期，但没有成功。我得到一个异常，说"LINQ to Entities不识别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;
}

这是我完整的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();
}

有人对我如何转换此OrderBy有什么建议吗？

更新的问题

目前，我通过使用如@Markus所示的辅助SELECT使查询正常工作。第一个查询只提取数据，然后提取方法的所有格式和调用，以获得正确的SortByDate。

然而，我的经理更喜欢在DB中进行排序，而不是在内存中进行排序。他放弃了这个，因为很少有人打电话给这个资历表，而且每个月只有一次。

出于学习目的，我想看看我是否可以让DB完成下面@IvanStoev显示的所有排序。所以，回到那个路线，我无法让OrderBy完全像它应该的那样工作。

如果你查看原始SQL，我试图转换它，首先查看此人以前是否有"ABC"公司，如果有，然后查看SeniorityDate（如果为NULL，则设置默认日期），并将其与收购日期进行比较。如果不满足这个条件，那么只需使用他们的SeniorityDate（如果为NULL，则设置为默认值）。狡猾……我知道。

使用LinqPad中建议的OrderBy，然后查看返回的SQL，我可以看到OrderBy的第一部分查看前一家公司，然后查看SeniorityDate并设置一个值。然后查看收购日期。我需要以某种方式对一些条件进行分组，首先看看我不知道这是可能的。

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]

我需要想出一些类似的东西

ORDER BY (CASE WHEN ((t0.PREV_CO = :p10) AND (COALESCE(t0.CO_SENIORITY, :p11) < :p12) THEN :p13 ELSE COALESCE(t0.CO_SENIORITY, :p11) END)

这是我在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();
}