代码之家 › 专栏 › 技术社区 › Ibrahim D.

遇到分组依据和加入EF C的问题#

entity-framework c#

Ibrahim D. · 技术社区 · 8 年前

我无法将此T-SQL代码转换为EFC#

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

待定用户:

Username, PCName, Usrid, Created, HCID

tbh容器:

hcid, sid

tbusersettings(用户设置):

sid, pcname

编辑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();

它在所有级别上都失败,只是不知道如何将group by与联接的表一起使用。

1 回复 | 直到 8 年前

Jeff Mercado 8 年前

直接翻译看起来更像这样:

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,
}

如果分组后还有其他子句,则需要将结果放入另一个变量中。( g )。然后,您可以访问组密钥并对该组执行任何聚合功能。