Entity Framework with Linq, inner Join, Group By, Order By

Question

I have a SQL Query

select Firma.Name as companyName, 
       Taetigkeit.Taetigkeit as skillName, 
       SUM(Zeit) as time
from Zeiterfassung 
inner join Firma On ZEiterfassung.FirmenID = Firma.ID    
inner join Taetigkeit on Zeiterfassung.TaetigkeitID = Taetigkeit.ID    
group by Taetigkeit, Firma.Name    
order by Firma.Name 

And want to "translate" it to linq. Here is what I tried:

var query = db.Zeiterfassung
              .Where(x => x.Firma.ID == x.FirmenID && x.TaetigkeitID == x.Taetigkeit.ID)
              .GroupBy(x => x.Taetigkeit.Taetigkeit1)
              .Select(x => new Evaluation() { skillName = x.Key, time = x.Sum(y => y.Zeit), //skillName = x.Sum(x => x.Zeit), })
              .OrderBy(x => x.skillName);

I dont know who to solve this with joins and the group by because all the time when i do a groupBy i cant access the other members.

Solution

From data you provided, I think query should look like

from z in db.Zeiterfassung
join f in db.Firma on z.FirmenID equals f.ID
join t in db.Taetigkeit on z.TaetigkeitID equals t.ID
select new { f.Name, t.Taetigkeit, z.Zeit) into x
group x by new { x.Taetigkeit, f.Name } into g
select new {
   CompanyName = g.Key.Name,
   SkillName = g.Key.Taetigkeit,
   Time = g.Sum(i => i.Zeit)
}

Or with navigation properties:

db.Zeiterfassung
  .Select(z => new { z.Zeit, z.Taetigkeit.Taetigkeit1, z.Firma.Name })
  .GroupBy(x => new { x.Taetigkeit1, x.Name })
  .Select(g => new Evaluation {
       companyName = g.Key.Name,
       skillName = g.Key.Taetigkeit1, 
       time = g.Sum(y => y.Zeit)
  });

Comments

[D Stanley]

Post what you have tried so far.

[Markus_DE_HH]

var query = db.Zeiterfassung.Where(x => x.Firma.ID == x.FirmenID && x.TaetigkeitID == x.Taetigkeit.ID).GroupBy(x => x.Taetigkeit.Taetigkeit1).Select(x => new Evaluation() { skillName = x.Key, time = x.Sum(y => y.Zeit), //skillName = x.Sum(x => x.Zeit), }).OrderBy(x => x.skillName);

[The Red Pea]

The method syntax (the second code example here) uses "navigation properties" as Sergey says. For example, notice in the Select how he uses z.Zeit (direct property) z.Taetigkeit.Taetigkeit1 (navigation property; OP achieved in query syntax by JOIN t in db.Taetigkeit), and z.Firma.Name (navigation property; OP achieved in query syntax by JOIN f in db.Firma). I think navigation properties are created automatically by EF if there is Foreign Key relationship. But what if there is no Foreign Key relationship; can we still use navigation properties? Must we explicitly create them first?