Linq -子查询计数

Question

问题:我试图在Linq中重写这个:

listOfUsersForReviewer是一个IEnumerable<User>

int countOfGreenUsers = 0;
foreach (var user in listOfUsersForReviewer)
{
    var u = (from reviewitems in context.ReviewItems
                join groupaccountlinks in context.GroupAccountLinks on reviewitems.GroupAccountID equals groupaccountlinks.GroupAccountID
                join reviews in context.Reviews on reviewitems.ReviewID equals reviews.ReviewID
                join applications in context.Applications on reviews.ApplicationID equals applications.ApplicationID
                join reviewers in context.Reviewers on applications.ResponsibleReviewerID equals reviewers.ReviewerID
                join accounts in context.Accounts on groupaccountlinks.AccountID equals accounts.AccountID
                join users in context.RBSUsers on accounts.UserID equals users.UserID
                where
                    users.UserID == user.UserID &&
                    reviewers.FullyQualifiedLogin == fullyQualifiedLogin &&
                    reviews.ReviewStatusID == (byte)Enums.ReviewStatus.InProgress &&
                    reviewitems.ReviewItemStatusID == (byte)Enums.ReviewItemStatus.Unapproved
                select reviewitems);
    byte colour = (byte)Enums.UserStatusColour.Red;
    if (u.Count() == 0)
    {
        colour = (byte)Enums.UserStatusColour.Green;
        countOfGreenUsers++;
    }
}

尝试创建一个匿名类型，但是无法编译。

// select number of green users
var x = from user in listOfUsersForReviewer
        from reviewitems in context.ReviewItems
        join groupaccountlinks in context.GroupAccountLinks on reviewitems.GroupAccountID equals
            groupaccountlinks.GroupAccountID
        join reviews in context.Reviews on reviewitems.ReviewID equals reviews.ReviewID
        join applications in context.Applications on reviews.ApplicationID equals applications.ApplicationID
        join reviewers in context.Reviewers on applications.ResponsibleReviewerID equals
            reviewers.ReviewerID
        join accounts in context.Accounts on groupaccountlinks.AccountID equals accounts.AccountID
        join users in context.RBSUsers on accounts.UserID equals users.UserID
        where
            users.UserID == user.UserID &&
            reviewers.FullyQualifiedLogin == fullyQualifiedLogin &&
            reviews.ReviewStatusID == (byte)Enums.ReviewStatus.InProgress &&
            reviewitems.ReviewItemStatusID == (byte)Enums.ReviewItemStatus.Unapproved
        select new
            {
                UserID = user.UserID,
                CountOfGreen = reviewitems.Count()
            };

Answer 1

添加分组子句

var x = from user in listOfUsersForReviewer
        from reviewitems in context.ReviewItems
        join groupaccountlinks in context.GroupAccountLinks on reviewitems.GroupAccountID equals
            groupaccountlinks.GroupAccountID
        join reviews in context.Reviews on reviewitems.ReviewID equals reviews.ReviewID
        join applications in context.Applications on reviews.ApplicationID equals applications.ApplicationID
        join reviewers in context.Reviewers on applications.ResponsibleReviewerID equals
            reviewers.ReviewerID
        join accounts in context.Accounts on groupaccountlinks.AccountID equals accounts.AccountID
        join users in context.RBSUsers on accounts.UserID equals users.UserID
        where
            users.UserID == user.UserID &&
            reviewers.FullyQualifiedLogin == fullyQualifiedLogin &&
            reviews.ReviewStatusID == (byte)Enums.ReviewStatus.InProgress &&
            reviewitems.ReviewItemStatusID == (byte)Enums.ReviewItemStatus.Unapproved
        group user by user.UserID into grouping
        select new
            {
                UserID = grouping.Key,
                CountOfGreen = grouping.Count()
            };

Answer 2

我最终重新思考了我的逻辑，并将这个查询分成了2个更简单的查询。

我使用的工具是:SQL Server Management Studio(图形表示)，Linqer和潜在的Linqpad

用英语写查询很有帮助。