c# - 对 Linq Lambda 表达式的 SQL 查询 - 使用组条件连接同一个表

Question

任何人都可以帮助将以下 SQL 查询转换为 C# LinQ Lambda 表达式？谢谢

tbl_CLASS

ClassID Student

Class1  A
Class1  B
Class1  C
Class2  B
Class2  C
Class3  C

结果

Class   A   B   C
Class1  Y   Y   Y
Class2  N   Y   Y
Class3  N   N   Y

SELECT  a.ClassID,
        A=case when c.ClassID is null then 'N' else 'Y' end,
        B=case when B.ClassID is null then'N' else 'Y' end,
        C='Y'
 FROM   tbl_CLASS a
        Left join tbl_CLASS b on a.ClassID=b.ClassID AND b.Student='B'
        Left join tbl_CLASS c on a.ClassID=c.ClassID AND c.Student='A'
WHERE a.Student='C'
GROUP by a.ClassID,case when c.ClassID is null then 'N' else 'Y'  end,case when B.ClassID is null then'N' else 'Y' end

Answer 1

List<Class1> myList = GetClass();

var query = myList
    .GroupBy(c => c.ClassID)
    .Select(g => new {
        ClassID = g.Key,
        A = g.Count(c => c.Student=="A")>0?"Y":"N",
        B = g.Count(c => c.Student=="B")>0?"Y":"N",
        C = g.Count(c => c.Student=="C")>0?"Y":"N"
    });

GetClass 就是获取你的数据

public class Class1
{
     public Int32 ClassID {get;set;}
     public String A{get;set;}
     public String B{get;set;}
     public String C{get;set;}
}