Home > Software design >  LINQ filter query with groubBy
LINQ filter query with groubBy

Time:11-12

I'm currently working on a .NET Core application.

I need to filter a LINQ query by the following requirements:

  • Id: if there is no ContactId, select Id only one time (1)
  • Id and ContactId: if exists filter Id (1) and add Id and Contact Id pair (1, 1)
  • An Id and ContactId pair must be unique, but can vary like: (1, 1), (1, 5)
  • empty objects must be removed
public class SearchResult
{
    public int? Id {get; set;}
    public int? ContactId {get; set;}
}

public class Program
{
    public static void Main()
    {
        var searchResults = new List<SearchResult>
        {
            new SearchResult { Id = 1 },
            new SearchResult { },
            new SearchResult { Id = 2 }, // yes
            new SearchResult { Id = 3 }, // yes
            new SearchResult { Id = 4 }, // yes
            new SearchResult { Id = 5 },
                        
            new SearchResult { Id = 5, ContactId = 3 }, // yes
            new SearchResult { Id = 1, ContactId = 1 }, // yes
            new SearchResult { Id = 1, ContactId = 5 }, // yes
            new SearchResult { Id = 8, ContactId = 4 }, // yes
            
            new SearchResult { Id = 1 }, 
            new SearchResult { Id = 2 }, 
            new SearchResult { Id = 10 }, // yes
            new SearchResult { Id = 11 }, // yes
            new SearchResult { Id = 12 }, // yes
        };
        
        // unfortunately this LINQ query does not work correctly:
        var result = searchResults
                 .OrderBy(x => x.Id)
                 .ThenBy(x => x.ContactId)
                 .GroupBy(p => new { p.Id, p.ContactId })
                 .Select(x => x.First());
        
        foreach(var sr in result){         
            Console.WriteLine(sr.Id   " "   sr.ContactId);
        }       
    }

The expected result should be:

1 1
1 5
2 
3 
4  
5 3
8 4
10 
11 
12 

Unfortunately my LINQ query does not work correctly.

Do you know how to solve this issue and to filter the LINQ query according to the rules?

CodePudding user response:

I suggest something like this. Here we GroupBy and then analyze each group. Finally, we flatten groups back into IEnumerable<SearchResult> (please, fiddle)

  var result = searchResults
    .Where(item => item.ContactId.HasValue || item.Id.HasValue)
    .GroupBy(item => item.Id)
    .Select(group => group.Any(item => item.ContactId.HasValue)
       ? group.Where(item => item.ContactId.HasValue)
       : group.Take(1))
    .SelectMany(group => group); 

Let's have a look:

 Console.WriteLine(string.Join(Environment.NewLine, result
   .Select(item => $"{item.Id} {item.ContactId}")));

Outcome:

1 1
1 5
2 
3 
4 
5 3
8 4
10 
11 
12 

CodePudding user response:

Dmitry Bychenko solution's is good except for a bug:

It doesn't remove duplicate pairs.

Which seems mandatory as per:

An Id and ContactId pair must be unique, but can vary like: (1, 1), (1, 5)

In order to do so, you can use the Distinct operator, but since SearchResult is a class, Distinct will not compare the value of each instance, but its reference. So I simply projected each SearchResult to a value tuple to have a quick way to compare on value and not reference.

var result = searchResults
    .Where(e => e.ContactId.HasValue || e.Id.HasValue)
    .Select(e => (Id: e.Id, ContactId: e.ContactId))
    .GroupBy(e => e.Id)
    .Select(g => g.Any(e => e.ContactId.HasValue)
       ? g.Where(e => e.ContactId.HasValue).Distinct()
       : g.Take(1))
    .SelectMany(group => group);

Also you didn't specify the case where a SearchResult would have a ContactId and no Id. The behavior of the current code it to accept such a pair.

If you want to filter these, simply change this line

.Where(e => e.ContactId.HasValue || e.Id.HasValue)

by

.Where(e => e.Id.HasValue)

Below is a full Linqpad Query for your to try. I added duplicates and SearchResults with a null Id and non null ContactId

public static void Main()
{
    
    var searchResults = new List<SearchResult>
    {
        new SearchResult { Id = 1 },
        new SearchResult { },
        new SearchResult { ContactId = 45}, // IS accepted, but the behavior was not specified.
        new SearchResult { ContactId = 45},
        new SearchResult { ContactId = 42},
        new SearchResult { ContactId = 45},
        new SearchResult { ContactId = 45},
        new SearchResult { Id = 2 }, // yes
        new SearchResult { Id = 3 }, // yes
        new SearchResult { Id = 4 }, // yes
        new SearchResult { Id = 5 },
        new SearchResult { Id = 5 },
        new SearchResult { Id = 5 },
        new SearchResult { Id = 5 },

        new SearchResult { Id = 5, ContactId = 3 }, // yes
        new SearchResult { Id = 1, ContactId = 1 }, // yes
        new SearchResult { Id = 1, ContactId = 5 }, // yes
        new SearchResult { Id = 8, ContactId = 4 }, // yes
        new SearchResult { Id = 8, ContactId = 4 },
        new SearchResult { Id = 8, ContactId = 4 },
        new SearchResult { Id = 8, ContactId = 4 },
        
        new SearchResult { Id = 1 },
        new SearchResult { Id = 2 },
        new SearchResult { Id = 10 }, // yes
        new SearchResult { Id = 11 }, // yes
        new SearchResult { Id = 12 }, // yes
    };

    var result = searchResults
    .Where(e => e.ContactId.HasValue || e.Id.HasValue)
    .Select(e => (Id: e.Id, ContactId: e.ContactId))
    .GroupBy(e => e.Id)
    .Select(g => g.Any(e => e.ContactId.HasValue)
       ? g.Where(e => e.ContactId.HasValue).Distinct()
       : g.Take(1))
    .SelectMany(group => group)
    .Dump();
}

public class SearchResult
{
    public int? Id { get; set; }
    public int? ContactId { get; set; }
}

  • Related