CODEWITHSUNDEEP
c#entity-framework
Ibrahim D.
Ibrahim D.

Reputation: 326

Having trouble with Group by and Join EF c#

I am having trouble converting this T-SQL code into EF C#

select 
    se.pcname, count(u.usrid) as total 
from 
    tbusers as u
inner join 
    tbhcontainer as hc on u.hcid = hc.hcid
inner join 
    tbusersettings as se on hc.sid = se.sid
where 
    day(u.created) = 18
group by  
    se.pcname
order by 
    total desc

tbusers:

Username, PCName, Usrid, Created, HCID

tbhcontainer:

hcid, sid

tbusersettings:

sid, pcname

EDIT 1:

DateTime yesterday = DateTime.UtcNow.Date.AddDays(-1).AddHours(-3);
DB_121002_psmainEntities ctx = new DB_121002_psmainEntities();

var res = from r in ctx.tbusers
          join hc in ctx.tbhcontainers on r.hcid equals hc.hcid
          join s in ctx.tbUserSettings on hc.sid equals s.sid
          group s by s.pcname
          where r.created >= yesterday || r.created <= DateTime.Today
          select r;
          return res.Count();

It fails on all levels, just don't know how to use group by with joined tables

Upvotes: 1

Views: 66

Answers (1)

Jeff Mercado
Jeff Mercado

Reputation: 134871

A direct translation would look more like this:

from u in ctx.Users
join hc in ctx.HContainers on u.Hcid equals hc.Hcid
join us in ctx.UserSettings on hc.Sid equals us.Sid
where u.Created.Day == 18
group u.Userid by us.Pcname into g
let total = g.Count()
orderby total descending
select new
{
    pcname = g.Key,
    total,
}

If you have additional clauses after the grouping, you need to place the results into another variable (g). Then you can access the group key and perform any aggregating function on that group.

Upvotes: 1

Related Questions