I have a Order->OrderLine table setup where I need to check if any “duplicate” orders exists. In order to check this I need to be able to select select various columns *not* contained in the group (key) – playing around in LinqPad I quickly came up with this query:
from line in AxSaleLines
where line.QuantityRemaining > 0
group line by new {line.AxSale.AccountNumber, line.ItemNumber, ProdName = line.Product.Name, ProdFormat = line.Product.ProductFormat.Name}
into myGroup
where myGroup.Count() > 1
from s in AxSaleLines
where s.ItemNumber == myGroup.Key.ItemNumber &&
s.AxSale.AccountNumber == myGroup.Key.AccountNumber
select new { s.AxSale.SalesId, s.AxSale.AccountNumber, CustomerName = s.AxSale.Customer.Name, s.ItemNumber,
ProductName = s.Product.Name, s.Product.ProductFormatName,
s.Quantity, s.AxSale.SalesOrigin}
When bound to a grid, I can then show this to the user:
