CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
user517406
user517406

Reputation: 13773

LINQ syntax question

I have this original SQL that I need to rewrite in LINQ :

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

I have done most of it in LINQ, but I am not sure how to add the NoOfRights part to my LINQ. This is what I have done so far :

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

Can anybody tell me the right syntax to include the NoOfRights part in my LINQ?

Upvotes: 3

Views: 163

Answers (4)

Genius
Genius

Reputation: 1782

I would recommend you to change SQL first to something like this:

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

So this can easily be transformed to 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() }

(not tested, so do it yourself)

Upvotes: 1

abatishchev
abatishchev

Reputation: 100248

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

Upvotes: 3

Yakimych
Yakimych

Reputation: 17752

If you are using LINQ-to-SQL or EF, and you have an FK set up, you should have a navigational property ProfileRights. Tn that case, you can query this way:

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

Upvotes: 2

alex
alex

Reputation: 3720

I think this would help you out:

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

Upvotes: 1

Related Questions