Linq Query Group By Multiple Columns Having Count > 1

Question

I have a query I know how to do in SQL but struggling to figure out the LINQ query. Here is the SQL.

SELECT ordNo, tranNo, COUNT(distinct custNo)
FROM orders
GROUP BY ordNo, tranNo
HAVING COUNT(distinct custNo) > 1

I don't feel like this is the same question that I see you marked as a duplicate. The linked question only groups on a single property. I've lost track of the Linq queries I've tried but here is one.

var countList = from o in orders
            group o by new {o.orderNo, o.tranNo, o.custNo}
            into grp
            where grp.Key.custNo.Distinct().Count() > 1
            select grp;

I tried the suggestion below but like someone commented you can't access the custNo property.

Please share your linq too. Show what you have tried and researched. We can help from there but not write it for you.. — Gilad Green, Feb 17 '17 at 15:08
Seems like everything is OK with your LINQ query. Do you have some error message? — Sergey Berezovskiy, Feb 17 '17 at 15:09

Dan S. · Answer 1 · 2017-02-17T15:24:32.233

0

Just spitballing since I don't know the table structure.

context.orders
    .GroupBy(o => new { o.ordNo, o.tranNo, o.custNo })
    .Where(o => o.custNo.Distinct().Count() > 1)
    .Select(o => new {
        ordNo = o.ordNo,
        tranNo = o.tranNo
    });

edited Feb 17 '17 at 15:24

answered Feb 17 '17 at 15:17

Dan S.

173
1
14

i doubt you can access custNo property like this after result of grouping – Ľuboš Pilka Feb 17 '17 at 15:21

Linq Query Group By Multiple Columns Having Count > 1

1 Answers1