linq group by and select multiple columns not in group by

Question

I'm trying to select multiple columns not in a group by using linq - c#.

Using linq, I'm trying to group by ISNULL(fieldOne,''),ISNULL(fieldTo,'') and then select field_One, field_Two, field_Three for each group. So for each row that the group by would return, I want to see numerous rows.

So far I have the following, but can't seem to select all the needed columns.

var xy = tableQueryable.Where(
            !string.IsNullOrEmpty(cust.field_One)
            || ! string.IsNullOrEmpty(ust.field_Two)
            ).GroupBy(cust=> new { field_One= cust.field_One ?? string.Empty, field_Tow = cust.field_Two  ?? string.Empty}).Where(g=>g.Count()>1).AsQueryable();

Can somebody help pls?

Answer 1

You are pretty much there - all you are missing is a Select from the group:

var xy = tableQueryable
    .Where(!string.IsNullOrEmpty(cust.first_name) || ! string.IsNullOrEmpty(ust.lastName))
    .GroupBy(cust=> new { first_name = cust.first_name ?? string.Empty, last_name = cust.last_name ?? string.Empty})
    .Where(g=>g.Count()>1)
    .ToList() // Try to work around the cross-apply issue
    .SelectMany(g => g.Select(cust => new {
        Id = cust.Id
    ,   cust.FirstName
    ,   cust.LastName
    ,   cust.RepId
    }));

Select from each group does the projection of the fields that you want, while SelectMany dumps all the results into a flat list.

Answer 2

Would this work for you?

var groupsWithDuplicates = tableQueryable
    .Where(c => !string.IsNullOrWhiteSpace(c.first_name) || !string.IsNullOrWhiteSpace(c.last_name))
    .GroupBy(c => new { FirstName = c.first_name ?? "", LastName = c.last_name ?? "" })
    .Where(group => group.Count() > 1) // Only keep groups with more than one item
    .ToList();

var duplicates = groupsWithDuplicates
    .SelectMany(g => g) // Flatten out groups into a single collection
    .Select(c => new { c.first_name, c.last_name, c.customer_rep_id });