CODEWITHSUNDEEP
c#mysqlasp.netservicestackormlite-servicestack
Prajval Gahine
Prajval Gahine

Reputation: 101

How can we use MySql Select Case construct in ORMLite ServiceStack

Select
    sum(
        CASE
            WHEN e01f04 < '2024-02-01' THEN DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), '2024-02-01') + 1
            WHEN ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) > '2024-02-29' THEN DATEDIFF(e01f04, '2024-02-29') + 1
            ELSE DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), e01f04) + 1
        END
    ) AS Leave_Count
    , E01F06
FROM
    lve01
where
        e01f02 = 1
    AND
        e01f07 = 2
    AND
        (e01f04 >= '2024-02-01' AND
        e01f04 <= '2024-02-29')
    OR
        (ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) >= '2024-02-01' AND
        ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) <= '2024-02-29');

e01f02 - employeeId

e01f04 - leave start date

ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) - leave last date

e01f05 - no. of leaves

e01f07 - leave status (where 2 = approved)

Code explaination - Above query is to get total approved leave count for feb-2024 for employeeId = 1

How can I perform the above query using ORMLite functionalities or query?

UPDATE

SqlExpression<LVE01> sqlExp = db.From<LVE01>()
    .Where(l => l.e01f02 == EmployeeId && l.e01f07 == LeaveStatus.Approved)
    .Where($"(e01f04 >= '{MonthFirstDate}' AND e01f04 <= '{MonthLastDate}')")
    .Or($"(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) >= '{MonthFirstDate}' AND ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) <= '{MonthLastDate}')");

sqlExp.SelectExpression = $"SELECT sum( CASE WHEN e01f04 < '{MonthFirstDate}' THEN DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), '{MonthFirstDate}') + 1 WHEN ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) > '{MonthLastDate}' THEN DATEDIFF(e01f04, '{MonthLastDate}') + 1 ELSE DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), e01f04) + 1 END) AS Leave_Count";

I tried and was able to create query using ORMLite, but certainly I needed to use mysql query literals at some place (like select expression).

Can I make the query more function based?

Upvotes: 1

Views: 53

Answers (1)

mythz
mythz

Reputation: 143284

You would need to use Custom SQL as you're doing, but you should be able to use a typed expression to compare DateTime's, I'd recommend using DB parameters instead of string interpolation and you can use UnsafeSelect instead of populating SelectExpression, e.g:

var q = db..From<LVE01>()
    .Where(l => l.e01f02 == EmployeeId && l.e01f07 == LeaveStatus.Approved)
    .And(x => x.e01f04 >= MonthFirstDate && x.e01f04 <= MonthLastDate)
    .Or("(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) >= {0} AND ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) <= {1})", 
        MonthFirstDate, MonthLastDate)
     .UnsafeSelect("sum(CASE WHEN e01f04...");

Upvotes: 1

Related Questions