c# - 如何在 Entity Framework 6 的 linq 查询中使用 T-SQL Max

Question

我有如下的 sql 查询，

T-SQL 查询

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

我正在尝试将上面的 t sql 转换为 linq。

我尝试如下转换

Linq 查询

 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();

问题:

如何在选择列时在我的 linq 查询中转换以下 t sql 查询？

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

我们将不胜感激。

谢谢。

Answer 1

只要您有权访问相应的列，CASE WHEN 就不是问题。而且您没有访问权限，因为您颠倒了 select 和 group by - 请记住，在 LINQ 查询中，select 子句是最后一个。

所以等效的 LINQ 查询应该是这样的

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();