C# IQueryable LINQ Group By with null values

Question

i want to select distinct values from database based on one property with returned all values when that property is null. IDs in my database are Strings

My database looks like this:

Id1   Id2    Value
____________________
1     null   Value1
2     1      Value2
3     1      Value3
4     null   Value4
5     null   Value5
6     2      Value6
7     2      Value7
8     2      Value8

I want to get output from my query like this:

Id1   Id2    Value
____________________
1     null   Value1
2     1      Value2  // i dont care which one from Id2 = 1 i get
4     null   Value4
5     null   Value5
6     2      Value6  // i dont care which one from Id2 - 2 i get

As you can see i want to get a List that have all elements where Id2 is null and return only one element where Id2 is the same (i dont care which element query will return).

I tried to code something:

query
   .Where(x => !string.IsNullOrEmpty(x.Id2))
   .GroupBy(z => z.Id2)
   .Select(grp => grp.FirstOrDefault())
   .ToListAsync();

But i dont get what i want, only one item representation by Id2 and only one null value, something like this:

Id1   Id2    Value
____________________
1     null   Value1
2     1      Value2  // I want to get all elements where Id2 = null
6     2      Value6  // and distinct elements based on Id2

My question is, how to write query to EF to get all null items and all distinct items based on property?

To be honest i dont get what are you asking for :D I prepered example output that i want to get from my query — michasaucer, Apr 23 '20 at 08:04

Harald Coppoolse · Answer 1 · 2020-04-24T09:38:12.863

After your GroupBy, you have a sequence of Groups. The Key of the group is the value of Id2.

If the Key (Id2) is not null, you only want one element of the group, you don't care which one.

If the Key equals null, you want all elements of the group.

There are two methods to do this:

Do your GroupBy for all non-null elements
Concat it with the null elements

Or:

Use parameter ResultSelector of the GroupBy to check whether the Key is null or not, and select either only one element, or all elements of the group

Concatenation method

IQueryable<MyClass> source = dbContext....

// the elements with non-null Id2, keep only one element:
IQueryable<MyClass> filledId2 = source
    .Where(item => item.Id2 != null)
    .GroupBy(item => item.Id2,

        // Parameter ResultSelector, take Id2 (key) and all items with this Id2
        // to make one new:
        (key, itemsWithThisKey) => itemsWithThisKey.FirstOrDefault());

Note: there won't be any empty groups, so no "default" items in the result.

The elements with null Id2:

IQueryable<MyClass> emptyId2 = source.Where(item => item.Id2 == null);

Concatenate:

var result = filledId2.Concat(emptyId2);

Note: no query has been executed yet. If desired you can create one big LINQ statement. This won't improve efficiency. It will however deteriorate readability.

ResultParameter method

   IQueryable<MyClass> filledId2 = source.GroupBy(item => item.Id2,

       // resultSelector: if the key is null, select all elements of the group
       // otherwise select a sequence of one element of the group
       (key, itemsWithThisKey) => (key == null) ? 
             itemsWithThisKey : itemsWithThisKey.Take(1))

       // result: a sequence of sequences of MyClass objects
       // use SelectMany to make it one sequence of MyClass objects:
       .SelectMany(group => group);

Rafalon · Accepted Answer · 2020-04-23T08:22:46.810

1

Untested:

var result = query.Where(x => x.Id2 == null || !query.Any(y => y.Id2 == x.Id2 && string.Compare(y.Id1, x.Id1) < 0))

That should get you all rows where Id2 is null and only the row with minimum Id1 for each group of Id2

edited Apr 23 '20 at 08:22

answered Apr 23 '20 at 08:09

Rafalon

4,450
2
16
30

Id1 and Id2 are strings – michasaucer Apr 23 '20 at 08:11
So you're storing numbers as strings in columns named `Id`? Anyway `<` comparison should work with strings too, right? – Rafalon Apr 23 '20 at 08:13
Yup, i stored all ids as Strings (thats why in my code snippet i write `string.IsNullOrEmpty()`) – michasaucer Apr 23 '20 at 08:14
2

`Anyway < comparison should work with strings too, right?` - [Not really](https://stackoverflow.com/q/28582598/11683). – GSerg Apr 23 '20 at 08:15
@Rafalon it wont even compile. When i change to `!x.Id1.equals(y.Id1)` your code not work – michasaucer Apr 23 '20 at 08:19
1

Why would you even replace `<` with `!Equals`? Eventually replace it with `string.Compare(y.Id1, x.Id1) < 0` as in the accepted answer of the question mentioned by GSerg – Rafalon Apr 23 '20 at 08:22
@Rafalon it seems to work, thanks! I will test it and mark your question – michasaucer Apr 23 '20 at 08:26

C# IQueryable LINQ Group By with null values

2 Answers2

Concatenation method

ResultParameter method