How to use Linq To Sql to get Users who has less than 2 photos?

Question

The scenario is I want to get the users who has less than 2 photos.

There are two table:

[Users] (UserId, UserName) 

[UserPhotos] (PhotoId, PhotoName, UserId) 

UserId is a Foreign Key but I do not want to use association like user.Photos.

A user may have none photo in the [UserPhotos] table.

How to use Linq To Sql to get List<User> who has less than 2 photos?

Answer 1

What's wrong with this??

var query = from u in dbContext.Users
            where u.Photos.Count > 2
            select u;

That would be the easiest by far - why do you want to specifically not use this method??

Answer 2

Maybe this: (fixed version)

List<User> users = UserPhotos.GroupBy(i => i.UserId)
                .Where(i => i.Count() > 2).Distinct()
                .Join(Users, o => o.Key, i => i.UserId, (o, i) => i)
                .ToList();

solution for changed question:

    List<User> result = Users.Where(user => !UserPhotos.GroupBy(i => i.UserId)
            .Where(i => i.Count() >= 2).Distinct()
            .Any(i => i.Key == user.UserId)).ToList();