Linq To SQL and Having

Question

I am fairly new to Linq To SQL but trying to run what should be a fairly simple SQL query and can't figure out how to make it play nice in LINQ.

SELECT     Users.Id, Users.Id AS Expr1, Users.FirstName, Users.LastName, 
User_x_Territory.UserID
FROM         Users LEFT OUTER JOIN
             User_x_Territory ON User_x_Territory.UserID = Users.Id
GROUP BY Users.Id, Users.Id, Users.FirstName, Users.LastName, User_x_Territory.UserID
HAVING      (COUNT(User_x_Territory.UserID) = 0)

Just trying to get all users that do not have a territory assigned, the only way to tell if they have a territory is to check the user_x_territory gerrund.

I am able to get all of the users out of my DB with this:

var users = from u in db.Users
            join uXt in db.User_x_Territories on u equals uXt.User into gerr
            from users in gerr.DefaultIfEmpty()
            select users;

But from there I can't figure out how to add a group by/having to refine the search results to only show users with no territories.

Thanks for any help.

Answer 1

I don't know how efficient this is (I'm guessing not very), but you could try something like this:

var users = (from u in db.Users
             join uXt in db.User_x_Territories on u equals uXt.User into gerr
             from users in gerr.DefaultIfEmpty()
             select u).Where(u => u.User_x_Territories.Count == 0);

Answer 2

edit: heh, guess someone beat me to it :(

from t in db.Users
join t0 in db.User_x_Territory on new { UserID = t.Id } equals new { UserID = t0.UserID } into t0_join
from t0 in t0_join.DefaultIfEmpty()
group new {t, t0} by new {
  t.Id,
  Column1 = t.Id,
  t.FirstName,
  t.LastName,
  t0.UserID
} into g
where   g.Count() == 0
select new {
  Id = g.Key.Id,
  Expr1 = g.Key.Id,
  g.Key.FirstName,
  g.Key.LastName,
  UserID = g.Key.UserID
}

Answer 3

I suggest the following solution.

db.Users.Where(u => u.User_x_Territories.Count == 0)