Linq (or C# code) to Self join a table to fill null/empty values

Question

let's assume I have a list which has elements with missing values but matching id(which may or not may be missing so I would like to take the values which arent null or empty and put them into a single element which in the matching field name converts to a IGrouping/list if there are several values which arent null or a single null value if there none,

this is an example

     public class MyClass 
        { 
            public int vali;
            public string vala;
            public string valb;
            public long? vall;
         }


      var list = new List<MyClass>()
                {
                  new MyClass(){vali= 2,vala=null,valb="how are you",vall=7},
                  new MyClass {vali=3,vala="hi",valb="how are you doing",vall=null},
                  new MyClass{vali=2,vala="hello",valb="how are you",vall=null},
                  new MyClass{vali=3,vala=null,valb=null,vall=8},
                  new MyClass(){vali= 2,vala=null,valb=null,vall=7},
                };

I would like to get the following output

 {2,"hello",string[] {"how are you","how are you" },int[] {7,7} }
 {3 , "hi" , "how are you doing" ,8 },

using linq


    list.GroupBy(x=>x.vali).Select(x=>x.FirstOrDefault()).ToList();

returns

 {2,"how are you",null,7},
 {3, "how are you doing",null}

so what query could I use or how could I implement to return a list(or a IGrouping as linq does but writing my own algorithm)

Thank you

The GroupBy uses the IEquatable method for the class object. See : https://learn.microsoft.com/en-us/dotnet/api/system.iequatable-1.equals?view=net-6.0 — jdweng, Jan 21 '22 at 16:16
@jdweng eans if I can implement my own I could make it make the grouping as desired? if so how i could determine where it should make a group? — PontiacGTX, Jan 21 '22 at 16:18
Why do you want a mix of arrays and single values depending on whether there's multiple items? Why not have an array in any case? Why would the last element be an array of `int` when `vall` is declared as `long?`? I don't believe you've given due consideration to what you want. Your output looks arbitrary, not based on a set of rules. If only you had a set of rules for that output, it would be clear as to why you should get that result, and maybe you could even figure out how to implement it yourself. — madreflection, Jan 21 '22 at 17:48
One of the problems with only providing an example of the output is that rules have to be inferred. There's no indication of what to do with `vala`. Do you want a single value when there's only one *distinct* value, do you want the *first non-null* value, or do you want to group by `vali` *and* `vala`, and you didn't include that in your code? There's not enough in the example. If we infer one possible rule and it works for this, then you try it with another input and it's not what you want, it'll have been a waste of time and you're going to be right back here asking another question. — madreflection, Jan 21 '22 at 17:52
@madreflection using a nullable type should be ok as when i would return it it should be casted before hand but i will only assign it checking if there is a nonnullable type, I mean this is how I would do it group non null values and only include a single null value in the field, but i thought about a solution I dont know if you think there is a better approach(check my comment on the on jdweng's answer — PontiacGTX, Jan 21 '22 at 20:00
Being a nullable type is orthogonal. Even if it wasn't nullable, you're changing the type from `long` to `int`. You haven't answered the questions I asked, just talked around them. You haven't explained why you have to have a `string` if there's one non-null value for `valb` and a `string[]` if there's more than one, and how you expect to use that. You haven't answered if `vala` has the same rules as `valb` or if it's somehow different, such as collapsing distinct values -- this can't be inferred from the example. — madreflection, Jan 21 '22 at 20:02
Forget about the example. There's not enough information there. Explain the rules for arriving at each part of output. Essentially, you need to write plain-language pseudocode (translate to English to post it here if do it in another language). Hopefully, you'll find that it helps you figure it out for yourself and you don't need to ask for help. Programming is about breaking a problem down to its smallest sub-problem and solving that and then using that to solve a larger problem. — madreflection, Jan 21 '22 at 20:12
@madreflection what I am trying to achieve is to group similar items(by field name) and if they are similar group them in a single collection what I think makes it reasonable if I was using Linq i would like to use IGrouping if I were to use my own code probably i would use a dictionary of String,List, what I dont really know if you could tell linq to group by if there is more than one field with value and if not store a single field, I suppose you cant? — PontiacGTX, Jan 22 '22 at 03:49
The whole "single value or collection" idea is just going to get you in trouble. A collection of one is perfectly fine. I think you're conflating data processing with data presentation. And if you really need that, handle it in the UI (or a follow-up step). You're already grouping by `vali`, and `x` in the projection (`Select`) *is* an `IGrouping`. Use `x.Key` for the value of `vali` in the group. Now you just need to figure out how to aggregate each of the fields. That's where the rules I've been harping on come into play. `.Select(x => new { vali = x.Key, /*...aggregate the rest here*/}` — madreflection, Jan 22 '22 at 04:13
But I can't reasonably give you an answer because it's unclear, based on the example, how the remaining properties should be aggregated within each group. There are multiple ways to get some of those properties' results, which may yield the wrong results with a different data set because it wasn't what you intended. — madreflection, Jan 22 '22 at 04:22
@madreflection I mean I was wondering if a single value or group could be done in linq that was my only question, if it couldnt I could try my approach — PontiacGTX, Jan 22 '22 at 04:33
Yes, it's possible with LINQ. The issue is that the data type of the resulting property is going to be `object`. — madreflection, Jan 22 '22 at 04:52
@madreflection so how could you make it a group or make it a single value in linq based on the values in the group? — PontiacGTX, Jan 22 '22 at 04:54

Enigmativity · Answer 1 · 2022-01-22T07:32:26.420

You can't get a mixed output with some values having an array and other having a single value. It's all one way or the other.

It seems to me that this does what you want:

var result =
    list
        .GroupBy(x => x.vali)
        .Select(gxs => new 
        {
            vali = gxs.Key,
            vala = gxs.Select(gx => gx.vala).Where(x => x != null).ToList(),
            valb = gxs.Select(gx => gx.valb).Where(x => x != null).ToList(),
            vall = gxs.Select(gx => gx.vall).Where(x => x != null).ToList(),
        })
        .ToList();

I get:

Otherwise, to get single values, try this:

List<MyClass> result =
    list
        .GroupBy(x => x.vali)
        .Select(gxs => new MyClass()
        {
            vali = gxs.Key,
            vala = gxs.Select(gx => gx.vala).Aggregate((x, y) => x ?? y),
            valb = gxs.Select(gx => gx.valb).Aggregate((x, y) => x ?? y),
            vall = gxs.Select(gx => gx.vall).Aggregate((x, y) => x ?? y),
        })
        .ToList();

Using your data, I get this result:

score 1 · Answer 2 · answered Jan 21 '22 at 16:30

Try following :

    class Program
    {

        static void Main(string[] args)
        {
            var list = new List<MyClass>()
                {
                  new MyClass(){vali= 2,vala=null,valb="how are you",vall=7},
                  new MyClass {vali=3,vala="hi",valb="how are you doing",vall=null},
                  new MyClass{vali=2,vala="hello",valb="how are you",vall=null},
                  new MyClass{vali=3,vala=null,valb=null,vall=8},
                  new MyClass(){vali= 2,vala=null,valb=null,vall=7},
                  new MyClass(){vali= 2,vala=null,valb="how are you",vall=7},
                  new MyClass {vali=3,vala="hi",valb="how are you doing",vall=null},
                  new MyClass{vali=2,vala="hello",valb="how are you",vall=null},
                  new MyClass{vali=3,vala=null,valb=null,vall=8},
                  new MyClass(){vali= 2,vala=null,valb=null,vall=7},
                };

            var results = list.GroupBy(x => x).ToList();
          
        }
    }
    public class MyClass : IEquatable<MyClass>
    {
        public int vali;
        public string vala;
        public string valb;
        public long? vall;

        public bool Equals(MyClass other)
        {
            return
                (this.vali == other.vali) &&
                (this.vala == other.vala) &&
                (this.valb == other.valb) &&
                (this.vall == other.vall);
        }
        public override bool Equals(object obj)
        {
            return Equals(obj as MyClass);
        }
        public override int GetHashCode()
        {
            return (this.vali.ToString() + "^" + this.vala + "^" + this.valb + "^" + this.vall.ToString()).GetHashCode();
        }

    }

I checked and somehow groups the same way as it does in Linq, what i wa s looking for if you could make sub dictionaries where values arent null or empty and cna be grouped within its own object I wonder if one could make a similar algorithm to how linq created a dictionary where values they are equal, now that I am thinking one could use an object that hold the <"FieldName",List>> where T is the type of the field and calling this dictionary ["FieldName"] maybe make an extension method for the object and if there is no value(Count() /Length is 0) return the default for this value (T) — PontiacGTX, Jan 21 '22 at 16:59
Microsoft must of fixed a bug because group by didn't work properly when mixed types (strings and int) were in a class. To do what you want you would need to modify the Equals method in my code. I think you want a hierarchy rather than in my code all the values are compared at same level. So you want to use https://stackoverflow.com/questions/9316918/what-is-the-difference-between-iequalitycomparert-and-iequatablet and use if(vali ==vali) else (vala == vala). You get three values for each compare 1) -1 if less than 2) 0 if equal 3) +1 if greater than — jdweng, Jan 21 '22 at 17:13
The mixed types are in the output, not the input. It doesn't have to group on mixed types, only project them in the result. — madreflection, Jan 21 '22 at 17:29

PontiacGTX · Answer 3 · 2022-01-23T16:10:09.233

I think I have found what I wanted

with the following query


     var 

    list2=list.ToDictionary(x=>x).GroupBy(x=>x.Key.vali).Select(x=>new  {  
                      vali =  x.First().Key.vali,
                      vala =   x.Count(v =>  v.Value.vala !=null) > 0  ?x.Where(x=>x.Value.vala!=null).Select(x=>x.Value.vala).ToList() : new List<string> { x.FirstOrDefault(x=>x.Value.vala!=null).Value.vala },
                      valb =  x.Count(v =>  v.Value.valb !=null) > 0  ?x.Where(x=>x.Value.valb!=null).Select(x=>x.Value.valb).ToList() : new List<string> { x.FirstOrDefault(x=>x.Value.valb!=null).Value.valb },
                      vall = x.Count(v =>  v.Value.vall !=null) > 0  ?x.Where(x=>x.Value.vall!=0).Select(x=>x.Value.vall).ToList() : new List<long?> { x.FirstOrDefault(x=>x.vall!=null).Value.vall },
    
                     }).ToList();

Linq (or C# code) to Self join a table to fill null/empty values

3 Answers3