Linq Lambda SQL query - join the same table with a group condition

Question

Anyone can help convert to SQL query in C # LinQ Lambda Expression? Thanks

tbl_CLASS

ClassID Student

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

Result

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

When students are dynamic, this means that the result class needs a class of results:

class ResultRow
{
    public ResultRow()
    {
        Students = new Dictionary<string, string>();
    }

    public string Class { get; set; }

    public IDictionary<string, string> Students { get; set; }
}

Because you need dynamic columns for students.
Now I can generate a result similar to your expected result with this code:

var res = tblClass
        .GroupBy(g=> g.ClassId)
        .Select(c =>
        {
            var rr = new ResultRow {Class = c.Key};
            foreach (var r in tblClass.GroupBy(gg=> gg.Student))
            {
                rr.Students[r.Key] = "N";
            }
            foreach (var r in c.Where(w=> w.ClassId == c.Key))
            {
                rr.Students[r.Student] = "Y";
            }
            return rr;
        })
        .toList();  //optional

---

You can also add these two methods to the class ResultRow:

public string GetHeader()
{
    return Students.Aggregate("Class", (current, s) => current + "|" + s.Key);
}

public string GetSolidRow()
{
    return Students.Aggregate(Class, (current, s) => current + "|" + s.Value);
}

[ Demo Here]
NTN

Answer 2

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 gets your data

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

Answer 3

System.Collections.Generic.List < > System.Collections.Generic.IEnumerable < >

public SystemAccessList GetAccessList(SystemAccessList systemAccessList)
{

        var qresult = db.tbl_SystemAccessList
                .GroupBy(g => g.ClassID)
                .AsEnumerable().Select(c =>
                {
                    var rr = new ResultRow { Class = c.Key };
                    foreach (var r in db.tbl_SystemAccessList.GroupBy(gg => gg.StudentID))
                    {
                        rr.Student[r.Key] = "N";
                    }
                    foreach (var r in c.Where(w => w.ClassID == c.Key))
                    {
                        rr.Student[r.StudentID] = "Y";
                    }
                    return rr;
                }).ToList();

        systemAccessList.SystemAccessList.AddRange(qresult);
        return systemAccessList;
    }