CASE WHEN in Linq

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
meysam.savameri
Posts: 6
Joined: Sat 22 Sep 2012 12:45

CASE WHEN in Linq

Post by meysam.savameri » Mon 15 Jul 2013 04:50

Hi All.
this is my Query:

Code: Select all

SELECT CASE WHEN tmp.SECTION = '99' THEN tmp.sum1 ELSE null END c99,
       CASE WHEN tmp.SECTION = '100' THEN tmp.sum1 ELSE null END c100,
       CASE WHEN tmp.SECTION = '101' THEN tmp.sum1 ELSE null END c101,
       tmp.STATUS,
       tmp.SECTION
  FROM (  SELECT C.VCCODEDESCRIPTION STATUS,
                 (INTLEVEL3) SECTION,
                 --VCSTATUS,
                 COUNT (*) Sum1
            FROM TABMPLEVEL A, TABWORKREQUEST B, TABMPCODES C
           WHERE     VCCODETYPE = 'JST'
                 AND TRIM (VCSTATUS) = C.VCCODEVALUE
                 AND A.INTRECORDID = B.INTRECORDID
                 AND VCWORKTYPE NOT IN ('01', '03')
                 AND VCSTATUS NOT IN ('8', '9', '10', '11')
                 AND (   (INTLEVEL3) = '99'
                      OR (INTLEVEL3) = '100'
                      OR (INTLEVEL3) = '101')
                 AND TO_CHAR (DTDATEOFISSUE,
                              'yyyy/mm/dd',
                              'nls_calendar=persian') BETWEEN '1391/01/01'
                                                          AND '1392/03/20'
        GROUP BY C.VCCODEDESCRIPTION, INTLEVEL3
        ORDER BY 2, 1) tmp
How to convert this query to LinqToEntity(Devart)
thanks in advanced
sorry for my bad english

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: CASE WHEN in Linq

Post by Shalex » Mon 15 Jul 2013 15:27

Here is a simple example of generating "CASE WHEN" construction:

Code: Select all

new Devart.Data.Oracle.OracleMonitor() { IsActive = true };
using (SCOTTEntities context = new SCOTTEntities()){
    context.DEPTs
        .Select(d => new {
            DEPTNO = d.DEPTNO,
            C1 = d.DNAME == "San Francisco" ? 100 : 0
        })
        .ToList();
}
produces the following SQL

Code: Select all

SELECT 
"Extent1".DEPTNO,
CASE WHEN "Extent1".DNAME = 'San Francisco' THEN 100 ELSE 0 END AS C1
FROM SCOTT.DEPT "Extent1"
Converting plain SQL to LinqToEntities exceeds the goals of our support because this question is not provider-specific. If our answer doesn't help, please post your question at the corresponding forum.

Post Reply