Converting Sql to Linq (to Entities)

Question

I have a query like that and it works normally. If a student gets more than one course, it list all of them:

SELECT ks.KullaniciKodu as username, ks.Sifre as password, k.adi as firstname, k.soyadi as lastname, k.Email as email, 
    MAX(CASE WHEN c.DERSKODU = 'ENF100' THEN 'ENF100' ELSE '' END)As course1,
    MAX(CASE WHEN c.DERSKODU = 'ATA101' THEN 'ATA101' ELSE '' END) As course2,
    MAX(CASE WHEN c.DERSKODU = 'TDB101' THEN 'TDB101' ELSE '' END) As course3,
    MAX(CASE WHEN c.DERSKODU = 'İNG101' THEN 'İNG101' ELSE '' END) As course4
FROM Kayit k
JOIN DersNotu dn ON dn.KayitNo = k.KayitNo
JOIN Ders c ON c.DersKayitNo = dn.DersKayitNo AND c.DERSKODU IN ('ENF100','ATA101','TDB101', 'İNG101') AND c.DONEM = '201512'
JOIN KullaniciSifre ks ON ks.KullaniciKodu = k.KullaniciKodu
GROUP BY ks.KullaniciKodu, ks.Sifre, k.adi, k.soyadi, k.Email

But when i convert it to Linq (to Entity), it only gets one course or all courses are null. Where is the difference and problem in this code?

from k in db.Kayit
join dn in db.DersNotu on k.KayitNo equals dn.KayitNo
join c in db.Ders on dn.DersKayitNo equals c.DersKayitNo
join ks in db.KullaniciSifre on k.KullaniciKodu equals ks.KullaniciKodu
where
     (new string[] { "ENF100", "ATA101", "TDB101", "İNG101" }).Contains(c.DersKodu) &&
     c.Donem == 201512 
group new { ks, k, c } by new
{
     ks.KullaniciKodu,
     ks.Sifre,
     k.Adi,
     k.Soyadi,
     k.Email
     } into g
 select new 
 {
     KullaniciKodu = g.Key.KullaniciKodu,
     Sifre = g.Key.Sifre,
     Adi = g.Key.Adi,
     Soyadi = g.Key.Soyadi,
     Email = g.Max(p => (
         p.k.Email == string.Empty ? "NULL" : p.k.Email)),
     Course1 = g.Max(p => (
         p.c.DersKodu == "ENF100" ? "ENF100,1" : "NULL,1")),
     Course2 = g.Max(p => (
         p.c.DersKodu == "ATA101" ? "ATA101,1" : "NULL,1")),
     Course3 = g.Max(p => (
         p.c.DersKodu == "TDB101" ? "TDB101,1" : "NULL,1")),
     Course4 = g.Max(p => (
         p.c.DersKodu == "İNG101" ? "İNG101,1" : "NULL,1"))
 }

Answer 1

Using MAX in SQL is just a trick to work-around the problem. You should not convert that explicitly to LINQToEntity. The problem here is you use Max against strings. In your case the NULL,1 seems be always selected as max except for the TDB101,1.

The actual logic here is such as for ENF100, if any one found, project for "ENF100", otherwise project for "NULL,1". The same logic for others. So it should be like this:

 Course1 = g.Any(p => p.c.DersKodu == "ENF100") ? "ENF100,1" : "NULL,1",
 Course2 = g.Any(p => p.c.DersKodu == "ATA101") ? "ATA101,1" : "NULL,1",
 Course3 = g.Any(p => p.c.DersKodu == "TDB101") ? "TDB101,1" : "NULL,1",
 Course4 = g.Any(p => p.c.DersKodu == "İNG101") ? "İNG101,1" : "NULL,1"

Also the Email is projected wrong, it is already included in the group key, so you can just project it via the group key:

 Email = g.Key.Email