我想打印计数使用Groupby和左连接在linq

本文关键字：连接 linq Groupby 打印 想打 | 更新日期: 2023-09-27 18:04:11

我有两个列表或表如下:查询:

 var q = db.tbl_User_to_CustomerMast
     .Where(i => i.fk_Membership_ID == m.MembershipID)
     .Join(
         db.tbl_CustomerMast, 
         u => u.fk_Customer_ID, 
         c => c.CustomerID, 
         (u, c) => new { UserCustomer = u, Customer = c })
     .Where(i => i.UserCustomer.fk_Store_ID == shopid).ToList();
输出:

 List A:
    User_Customer_ID        Name
    ===================================
    1                       XYZ
    2                       ABC
查询:

var rewards = q.Join(
        db.tbl_RewardAwardMast, 
        i => i.UserCustomer.User_Customer_ID, 
        j => j.fk_Customer_UserID, 
       (i, j) => new { Customer = i, Reward = j })
   .Where(i => i.Reward.RewardDate >= i.Customer.UserCustomer.Membership_Start)
   .GroupBy(i => i.Reward.fk_Customer_UserID)
   .Select(i => new { CustomerID = i.Key, RewardCount = i.Count()})
   .ToList();
输出:

List B:
User_Customer_ID     RewardCount
===================================
1                       5

这是最终的输出表

User_Customer_ID        Name       RewardCount
===============================================
1                       XYZ            5
2                       ABC            0

如果我想检查哪个user_customer_ID的奖励计数少于5，我将如何检查:

查询:

var final = q.GroupJoin(
        rewards, 
        i => i.UserCustomer.User_Customer_ID, 
        j => j.CustomerID, 
        (i, j) => new { Customer = i, Reward = j.DefaultIfEmpty() })
    .Select(i => new { Count = i.Reward, id = i.Customer.UserCustomer.User_Customer_ID })
    .ToList();
var final1 = final.Where(i => i.Count < m.MembershipMinVisit.Value).ToList();

错误:操作符"<"不能应用于"System.Collections.Generic"类型的操作数。

我想打印计数使用Groupby和左连接在linq

这里不需要组连接，因为对于每个客户，您需要单个结果(奖励)。也因为你只需要有奖励的客户<5、使用该条件的内连接会给你想要的:

var final = q.Join(                               // Join instead of GroupJoin
        rewards.Where(r => r.RewardCount < 5),    // filter out rewards >= 5
        i => i.UserCustomer.User_Customer_ID, 
        j => j.CustomerID, 
       (i, j) => new { Customer = i, Reward = j })
    .Select(i => new { 
        Reward = i.Reward,                            // 'Count' is a bad name
                                                      // it is still the reward object
        id = i.Customer.UserCustomer.User_Customer_ID
    })
    .ToList();

在您的原始查询中，Count(坏名字)是奖励的集合(IEnumerable)，这就是为什么您得到该错误。要修复它，您必须检查单个返回的奖励不为空(过滤掉没有奖励的用户，因为您使用左连接)，并且它的RewardCount小于5:

var final1 = final.Where(i => i.Count.Single() != null &&
                              i.Count.Single().RewardCount < 5)
             .ToList();
相关文章：