CODEWITHSUNDEEP
c#sqllinq
Sasan
Sasan

Reputation: 614

how can I implement CASE WHEN with LINQ?

I wrote a T-SQL code which has used case when in select scope. We couldn't use t-sql or store procedure in application, because of that I need to convert follong code to LINQ.
Is there any way to change this code to linq quickly?

SELECT 
    T.TaskID,
    SUM(CASE WHEN T.LogDate<@fromDate AND T.TaskStatusID=2 THEN ISNULL(DA_CHILD.Score,0)*(T.DoneScore/100) ELSE 0 END) PreAmount,
    SUM(CASE WHEN T.LogDate>=@fromDate AND T.LogDate<=@toDate AND T.TaskStatusID=2 THEN ISNULL(DA_CHILD.Score,0)*(T.DoneScore/100) ELSE 0 END) CurAmount

FROM 
    NetTasks$ T
    INNER JOIN NetDeviceActions DA ON DA.DeviceActionID=T.DeviceActionID
    LEFT JOIN NetFinancialInfoDetail FID ON FID.TaskID=T.TaskID
    INNER JOIN NetActionParents AP ON AP.ParentID=DA.ActionID
    INNER JOIN NetDeviceActions DA_CHILD ON DA_CHILD.ActionID=AP.ChildID AND 
                                            DA_CHILD.DeviceID=DA.DeviceID AND 
                                            DA_CHILD.ContractInfoID=DA.ContractInfoID

WHERE 
      T.ParentTaskID = 0 AND 
      T.FinishDate<=@toDate AND 
      DA.ContractInfoID=9

GROUP BY 
      T.TaskID, T.DoneScore,T.FinishDate

Upvotes: 1

Views: 125

Answers (3)

bbsimonbb
bbsimonbb

Reputation: 29002

With such good looking SQL, would you not be happier using QueryFirst and forgetting about Linq? You run your SQL directly in your C# app.

disclaimer : I wrote QueryFirst

Upvotes: 0

sidecus
sidecus

Reputation: 752

In LINQ you can use C# statements so CASE WHEN is actually not hard. Assuming you have finished all the joining into a query object called values, you can use something like below for the grouping and select:

        var q = from a in values
        group a by new {a.TaskID, a.DoneScore, a.FinishDate} into g 
        select new {
            g.Key.TaskID,
            PreAmount = g.Where(x => x.LogDate < fromDate && x.TaskStatusID == 2 && x.DA_CHILD.HasValue).Select(x => x.DoneScore).Sum(),
            CurAmount = g.Where(x => x.LogDate >= fromDate && x.LogDate < toDate && x.TaskStatusID == 2 && x.DA_CHILD.HasValue).Select(x => x.DoneScore).Sum()
            };

And of course, a friendly reminder, left joining in LINQ is very tedious.

Upvotes: 1

Matt W
Matt W

Reputation: 12423

Are you just looking for a simple where clause in your statement? (Though I admit this LINQ query is not going to be particularly simple.)

https://msdn.microsoft.com/en-us/library/bb397927.aspx

I advise building it up slowly.

Upvotes: 0

Related Questions