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 suggest the following solution.

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