如何使用Linq To Sql获取少于2张照片的用户？(How to use Linq To Sql to get Users who has less than 2 photos?)

如何使用Linq To Sql获取少于2张照片的用户？(How to use Linq To Sql to get Users who has less than 2 photos?)

场景是我想要获得少于2张照片的用户。

有两个表：

[Users] (UserId, UserName) [UserPhotos] (PhotoId, PhotoName, UserId)

UserId是外键，但我不想使用像user.Photos这样的关联。

用户可能在[UserPhotos]表中没有照片。

如何使用Linq To Sql获取少于2张照片的List<User> ？

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?

最满意答案

也许这个:( 修正版）

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();

改变问题的解决方案：

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

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();