c# - I want to print Count using Groupby and Left join in linq -

i having 2 list or table per below: query:

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

output:

 list a:     user_customer_id        name     ===================================     1                       xyz     2                       abc 

query:

var rewards = q.join(         db.tbl_rewardawardmast,          => 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(); 

output:

list b: user_customer_id     rewardcount =================================== 1                       5 

here final output table

user_customer_id        name       rewardcount =============================================== 1                       xyz            5 2                       abc            0 

if want check user_customer_id has less 5 reward count, how check:

query:

var final = q.groupjoin(         rewards,          => 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(); 

error: operator '<' cannot applied operands of type 'system.collections.generic.ienumerable' , 'int'

you don't need group join here each customer need single result (reward). because need customers rewards < 5, inner join using condition wil give want:

var final = q.join(                               // join instead of groupjoin         rewards.where(r => r.rewardcount < 5),    // filter out rewards >= 5         => i.usercustomer.user_customer_id,          j => j.customerid,         (i, j) => new { customer = i, reward = j })     .select(i => new {          reward = i.reward,                            // 'count' bad name                                                       // still reward object         id = i.customer.usercustomer.user_customer_id     })     .tolist(); 

in original query, count (bad name) collection (ienumerable) of awards, that's why error. fix it, have check single returned reward not null (to filter out users without rewards @ all, because use left join) , has rewardcount less 5:

var final1 = final.where(i => i.count.single() != null &&                               i.count.single().rewardcount < 5)              .tolist();