LINQ语法问题

Question

我有这个原始的SQL，我需要在LINQ中重写:

SELECT 
    luProfiles.luProfileID,
    luProfiles.ProfileName,
    NoOfRights = (SELECT Count(pkProfileRightsID) FROM tblProfileRights WHERE fkProfileID = luProfileID)
FROM  luProfiles 
WHERE luProfiles.ProfileName LIKE ...

我已经在LINQ中完成了大部分工作，但我不确定如何将NoOfRights部分添加到我的LINQ中。以下是我目前所做的:

return from p in _database.LuProfiles
       where p.ProfileName.ToLower().StartsWith(strProfile.ToLower())             
       select p; 

谁能告诉我在LINQ中包含NoOfRights部分的正确语法?

Answer 1

from p in _database.LuProfiles
let NoOfRights = (from r in database.tblProfileRights 
                  where r.fkProfileID == p.luProfileID
                  select r).Count()
where p.ProfileName.ToLower().StartsWith(strProfile.ToLower())             
select new
{
    p.luProfileID,
    p.ProfileName,
    NoOfRights 
};

Answer 2

如果您正在使用LINQ-to-SQL或EF，并且您已经设置了FK，那么您应该有一个导航属性ProfileRights。在这种情况下，您可以这样查询:

from p in _database.LuProfiles
where p.ProfileName.ToLower().StartsWith(strProfile.ToLower())
select new 
{
    p.ProfileId,
    p.ProfileName,
    NoOfRights = p.ProfileRights.Count()
};

Answer 3

我想这会对你有所帮助:

from l in luProfiles
where l.ProfileName.Contains(something)
select new
{
    l.luProfileID,
    l.ProfileName,
    noOfRights = tblProfileRights.Count(t => t.fkProfileID == l.luProfileID)
}

Answer 4

我建议您先将SQL更改为如下内容:

SELECT 
  luProfiles.luProfileID,
  luProfiles.ProfileName,
  NoOfRights = COUNT(pkProfileRightsID)
FROM luProfiles
LEFT JOIN tblProfileRights ON fkProfileID = luProfileID
WHERE luProfiles.ProfileName like ...
GROUP BY luProfiles.luProfileID, luProfiles.ProfileName

所以这可以很容易地转换为LINQ:

return from p in _database.LuProfiles
join o in p.Profiles on p.luProfileID equals o.fkProfileID
group p by new { p.luProfileID, p.ProfileName } into g
select new { g.Key.luProfileID, g.Key.ProfileName , g.Count() }

(未测试，所以自己做)