how to use T-SQL Max inside of linq query in entity framework 6

Question

I have t sql query as below,

T-SQL Query

SELECT  
     6,
    O.SUGGESTION, 
    0 
    ,CASE 
        WHEN MAX(PCO.PARENT_OPTION_ID) IS NOT NULL THEN MAX(PCO.SUGGESTION_TYPE)
        ELSE MAX(O.SUGGESTION_TYPE)
    END AS SUGGESTION_TYPE
    ,O.EXAMPLE_IMAGE_ID,x.VISIT_DATE+O.COMPLETE_TIME as COMPLETE_TIME
FROM 

       (SELECT * FROM SURVEY WHERE SURVEY_ID = 6 OR PARENT_SURVEY_ID = 6) S

    join SURVEY_X x on x.SURVEY_ID = s.SURVEY_ID    
    JOIN SURVEY_ANSWER_SELECTION SA (NOLOCK) ON S.SURVEY_ID = SA.SURVEY_ID
    JOIN REP_OPTION O (NOLOCK) ON SA.OPTION_ID = O.OPTION_ID

    LEFT JOIN REP_PARENT_CHILD_OPTIONS PCO (NOLOCK) ON SA.OPTION_ID = PCO.CHILD_OPTION_ID
    LEFT JOIN SURVEY_ANSWER_SELECTION SAP (NOLOCK) ON SAP.OPTION_ID = PCO.PARENT_OPTION_ID AND SAP.SURVEY_ID = S.SURVEY_ID

WHERE   1 = 1
    AND (S.SURVEY_ID = 6 OR S.PARENT_SURVEY_ID = 6)
    AND PCO.SUGGESTION_TYPE != 0 
    AND O.SUGGESTION IS NOT NULL 
    AND SAP.OPTION_ID IS NOT NULL

GROUP BY O.SUGGESTION,O.EXAMPLE_IMAGE_ID,COMPLETE_TIME,VISIT_DATE

I am trying to convert above t sql to linq.

I tried to convert as below

Linq query

 var result =

(from s in context.SURVEY
where s.SURVEY_ID == 6 || s.PARENT_SURVEY_ID == 6

   join x in context.SURVEY_X on s.SURVEY_ID equals x.SURVEY_ID
   join sa in context.SURVEY_ANSWER_SELECTION on s.SURVEY_ID equals sa.SURVEY_ID
   join o in context.REP_OPTION on sa.OPTION_ID equals o.OPTION_ID

   from pco in context.REP_PARENT_CHILD_OPTIONS.Where(w => w.CHILD_OPTION_ID == sa.OPTION_ID).DefaultIfEmpty()
   from sap in context.SURVEY_ANSWER_SELECTION.Where(w => w.OPTION_ID == pco.PARENT_OPTION_ID && w.SURVEY_ID == s.SURVEY_ID).DefaultIfEmpty()

   where
   1 == 1 &&
   (s.SURVEY_ID == 6 || s.PARENT_SURVEY_ID == 6) &&
   pco.SUGGESTION_TYPE != 0 &&
   o.SUGGESTION != null &&
   sap.OPTION_ID != null

      select new
      {
        SURVEY_ID = 6,
        SUGGESTION = o.SUGGESTION,
        DISPLAY_ORDER = 0,
        //SUGGESTION_TYPE = 1,
        o.EXAMPLE_IMAGE_ID,
        COMPLETE_TIME = (x.VISIT_DATE.HasValue && o.COMPLETE_TIME.HasValue ? DbFunctions.AddDays(x.VISIT_DATE, o.COMPLETE_TIME.Value) : (DateTime?)null)

       }).GroupBy(y => new { y.SURVEY_ID, y.SUGGESTION, y.DISPLAY_ORDER, y.EXAMPLE_IMAGE_ID, y.COMPLETE_TIME }).ToList();

Question:

How can i convert below t sql query inside of my linq query while selecting columns?

CASE 
WHEN MAX(PCO.PARENT_OPTION_ID) IS NOT NULL THEN MAX(PCO.SUGGESTION_TYPE)
ELSE MAX(O.SUGGESTION_TYPE)
END AS SUGGESTION_TYPE

Any help will be appreciated.

Thanks.

Possible duplicate of [What is the equivalent of “CASE WHEN THEN” (T-SQL) with Entity Framework?](http://stackoverflow.com/questions/20496919/what-is-the-equivalent-of-case-when-then-t-sql-with-entity-framework) — stuartd, Mar 10 '16 at 10:21

score 2 · Accepted Answer · answered Mar 10 '16 at 12:33

The CASE WHEN is not a problem as soon as you have access to the respective columns. And you don't have access because you reversed select and group by - remember, in LINQ query select clause is the last.

So the equivalent LINQ query would be something like this

var result = (

    from s in context.SURVEY
    where s.SURVEY_ID == 6 || s.PARENT_SURVEY_ID == 6

    join x in context.SURVEY_X on s.SURVEY_ID equals x.SURVEY_ID
    join sa in context.SURVEY_ANSWER_SELECTION on s.SURVEY_ID equals sa.SURVEY_ID
    join o in context.REP_OPTION on sa.OPTION_ID equals o.OPTION_ID

    from pco in context.REP_PARENT_CHILD_OPTIONS.Where(w => w.CHILD_OPTION_ID == sa.OPTION_ID).DefaultIfEmpty()
    from sap in context.SURVEY_ANSWER_SELECTION.Where(w => w.OPTION_ID == pco.PARENT_OPTION_ID && w.SURVEY_ID == s.SURVEY_ID).DefaultIfEmpty()

    where
        1 == 1 &&
        (s.SURVEY_ID == 6 || s.PARENT_SURVEY_ID == 6) &&
        pco.SUGGESTION_TYPE != 0 &&
        o.SUGGESTION != null &&
        sap.OPTION_ID != null

    group new { pco, o } // make "pco" and "o" available in select 
    by new { o.SUGGESTION, o.EXAMPLE_IMAGE_ID, o.COMPLETE_TIME, x.VISIT_DATE }
    into g

    select new
    {
        SURVEY_ID = 6,
        SUGGESTION = g.Key.SUGGESTION,
        DISPLAY_ORDER = 0,
        SUGGESTION_TYPE = g.Max(e => e.pco.PARENT_OPTION_ID) != null ? 
            g.Max(e => e.pco.SUGGESTION_TYPE) :
            g.Max(e => e.o.SUGGESTION_TYPE),
        EXAMPLE_IMAGE_ID = g.Key.EXAMPLE_IMAGE_ID,
        COMPLETE_TIME = g.Key.VISIT_DATE.HasValue && g.Key.COMPLETE_TIME.HasValue ?
            DbFunctions.AddDays(g.Key.VISIT_DATE, g.Key.COMPLETE_TIME.Value) :
            (DateTime?)null
    }).ToList();

score 1 · Answer 2 · answered Mar 10 '16 at 10:43

1

Try this:

select new
{
    SURVEY_ID = 6,
    SUGGESTION = o.SUGGESTION,
    DISPLAY_ORDER = 0,
    SUGGESTION_TYPE = (pco.PARENT_OPTION_ID.Max() != null ? pco.SUGGESTION_TYPE.Max() : o.SUGGESTION_TYPE.Max()),
    o.EXAMPLE_IMAGE_ID,
    COMPLETE_TIME = (x.VISIT_DATE.HasValue && o.COMPLETE_TIME.HasValue ? DbFunctions.AddDays(x.VISIT_DATE, o.COMPLETE_TIME.Value) : (DateTime?)null)
}

answered Mar 10 '16 at 10:43

diiN__________

7,393
6
42
69

i get error as int does not contain defination for Max the best extension method overload 'Enumerable.Max(IEnumarable)' requires of type 'Ienumarable' , i mean i can not reach pco inside of select query where i miss thank you – Soner Mar 10 '16 at 10:48

how to use T-SQL Max inside of linq query in entity framework 6

2 Answers2