C# LINQ Group By multiple fields with Custom Properties

c# list group by multiple columns
linq group by select
c# linq group by count
linq group by sum
c# linq group by multiple levels
linq group by select all columns
linq group by method syntax
list group by c#

I'm trying to agregate a list of multiple propertys with Linq. My Second field is a List of Strings + an other List of strings inside.

Here's a sample of my code :

using System;
using System.Collections.Generic;
using System.Linq;

public class RefValueData
{
    public int ReferenceId { get; set; }
    public int SiteId { get; set; }
    public string SiteName { get; set; }
    public string Code { get; set; }
    public decimal UnitPoints { get; set; }
    public List<TranslationData> Texts { get; set; }
}

public class TranslationData
{
    public string Text { get; set; }
    public List<TranslationValue> Translations { get; set; }
}

public class TranslationValue
{
    public string Culture { get; set; }
    public string TranslationText { get; set; }
}



public class Program
{
    public static void Main()
    {       
        var values = new List<RefValueData>
            {
                new RefValueData(){
                    ReferenceId = 4,
                    Code = "Code",
                    SiteId = 2,
                    SiteName = "Paris",
                    UnitPoints = 50,
                    Texts = new List<TranslationData>
                    {
                        new TranslationData(){
                            Text = "A",
                            Translations = new List<TranslationValue>
                            {
                                new TranslationValue() { Culture = "FR-fr", TranslationText = "Bonjour" },
                                new TranslationValue() { Culture = "ES-es", TranslationText = "Hola" },
                            }
                        }
                    }
                },
                new RefValueData()
                {
                    ReferenceId = 5,
                    Code = "Code",
                    SiteId = 4,
                    SiteName = "Lyon",
                    UnitPoints = 50,
                    Texts = new List<TranslationData>
                    {
                        new TranslationData(){
                            Text = "A",
                            Translations = new List<TranslationValue>
                            {
                                new TranslationValue() { Culture = "FR-fr", TranslationText = "Bonjour" },
                                new TranslationValue() { Culture = "ES-es", TranslationText = "Hola" },
                            }
                        }
                    }
                },
                new RefValueData()
                {
                    ReferenceId = 6,
                    Code = "Code",
                    SiteId = 3,
                    SiteName = "Paris",
                    UnitPoints = 52,
                    Texts = new List<TranslationData>
                    {
                        new TranslationData(){
                            Text = "B",
                            Translations = new List<TranslationValue>
                            {
                                new TranslationValue() { Culture = "FR-fr", TranslationText = "Salut" },
                                new TranslationValue() { Culture = "ES-es", TranslationText = "Ciao" },
                            }
                        }
                    }
                }
            };


        var values2 = values
            .Distinct()
            .GroupBy(x => new
                     {
                         x.UnitPoints,
                         x.Texts
                     })
            .Select(x => new
                    {
                        x.Key.UnitPoints,
                        Texts = x.Key.Texts,
                        Site = x.Select(y=>y.SiteName)
                    })
            .ToList();
        Console.WriteLine(values2.Count);
    }
}

I want to have only two lines in my values2 list, but everytime it returns me the whole list.

When I only group by Unit Point, it's work great !

I tried to group the first two lines of my list with some custom Linq query but it doesn't work at all...

Any help / advice is much appreciated :) !

EDIT : I also tried with an override of the Equals methods like this, but I can't make it work :

public class TranslationValue
{
    public string Culture { get; set; }
    public string TranslationText { get; set; }

    public override bool Equals(object obj)
    {
        var other = obj as TranslationValue;

        if (other == null)
        {
            return false;
        }

        return Culture == other.Culture && TranslationText == other.TranslationText;
    }

    public override int GetHashCode()
    {
        var hashCode = -2095322044;
        hashCode = hashCode * -1521134295 + EqualityComparer<string>.Default.GetHashCode(Culture);
        hashCode = hashCode * -1521134295 + EqualityComparer<string>.Default.GetHashCode(TranslationText);
        return hashCode;
    }
}

public class TranslationData
{
    public string Text { get; set; }
    public List<TranslationValue> Translations { get; set; }

    public override bool Equals(object obj)
    {
        var other = obj as TranslationData;

        if (other == null)
        {
            return false;
        }
        return Text == other.Text && Translations.SequenceEqual(other.Translations);
    }

    public override int GetHashCode()
    {
        var hashCode = -1551681861;
        hashCode = hashCode * -1521134295 + EqualityComparer<string>.Default.GetHashCode(Text);
        hashCode = hashCode * -1521134295 + EqualityComparer<List<TranslationValue>>.Default.GetHashCode(Translations);
        return hashCode;
    }

}

EDIT2 : Here's my 'real' code :

var values = referenceValues.Select(value => new
{
    ReferenceId = value.ReferenceId,
    SiteId = value.Reference.SiteId ?? -1,
    SiteName = value.Reference.Site.Name ?? allSitesName,
    Code = value.Code,
    UnitPoints = value.UnitPoints,
    Texts =     // Type: List<TranslationData> , but it will not use the TranslationDataList class that normally work thanks to your help
        value.ReferenceValueTexts.Select(text =>
            new TranslationData
            {
                Text = text.Text, // string
                Translations = text.TranslationDataValues.Select(translation => // List<TranslationValue>
                new TranslationValue {
                    Culture = translation.Language.StrCulture,
                    TranslationText = translation.Value
                }).ToList()
            }).ToList()
}

Julien.

Here's one solution. It works for the sample code that you wrote. But it needs a little work to be robust:

// and also change the declarations in the main method to: new TranslationDataList 
public class TranslationDataList : List<TranslationData>
{
    public override int GetHashCode()
    {
        int hash = 13;
        // string.GetHashCode() is not reliable. This should be an algorithm that returns the same value for two different lists that contain the same data
        foreach (var data in this)
            hash = (hash * 7) + data.Text.GetHashCode(); 
        return hash;

    }

    public override bool Equals(object obj)
    {
        var other = obj as TranslationDataList;
        if (other == null) return false;
        if (other.Count != Count) return false;
        // write the equality logic here. I don't know if it's ok!
        for (int i = 0; i < other.Count; i++)
            if (other[i].Text != this[i].Text)
                return false;
        return true;
    }
}

Group query results (LINQ in C#), Learn how to group results using LINQ in C#. shows how to use an anonymous type to encapsulate a key that contains multiple values. LINQ Group By Multiple Columns: Conclusion To be perfectly honest, whenever I have to use Group By in a query, I’m tempted to return back to raw SQL. I find the SQL syntax terser, and more readable than the LINQ syntax with having to explicitly define the groupings.

First of all you should add a constructor to the TranslationDataList:

public class TranslationDataList : List<TranslationData>
{
    public TranslationDataList(IEnumerable<TranslationData> translationData)
        : base(translationData)
    { }
    // other members ...
}

Now you can use the TranslationDataList in your query:

var values = referenceValues.Select(value => new
{
    ReferenceId = value.ReferenceId,
    SiteId = value.Reference.SiteId ?? -1,
    SiteName = value.Reference.Site.Name ?? allSitesName,
    Code = value.Code,
    UnitPoints = value.UnitPoints,
    Texts = new TranslationDataList( value.ReferenceValueTexts.Select(text =>
        new TranslationData
        {
            Text = text.Text, // string
            Translations = text.TranslationDataValues.Select(translation => // List<TranslationValue>
            new TranslationValue {
                Culture = translation.Language.StrCulture,
                TranslationText = translation.Value
            }).ToList()
        })); // don't ToList() here anymore
}

How to populate object collections from multiple sources (LINQ) (C#), The first collection of strings represents the student names and IDs, and the second collection represents the student ID (in the first column) and  You are too late at the end of the query to add new Where. You have already grouped the data, and projected it, removing nearly all the fields. Try: var baseQuery = from o in _db.Orders join l in _db.OrderLines.Where(x => x.ParaBirimi == model.ParaBirimi) on o.orderId equals l.OrderId where o.OrderDate.Value.Year

And here is another solution: The GroupBy method takes an IEqualityComparer that can do the responsibility of comparing items for the grouping. But the problem is you used an anonymous type for the key in your grouping "GroupBy(x=>new{x.UnitPoints, x.Texts})". First we have to create a class to play the key role:

public class Key
{
    public Key(decimal unitPoints, List<TranslationData> texts)
    {
        UnitPoints = unitPoints;
        Texts = texts;
    }
    public decimal UnitPoints { get; set; }
    public List<TranslationData> Texts { get; set; }
}

then we can implement the comparer:

public class Comparer : IEqualityComparer<Key>
{
    public bool Equals(Key x, Key y)
    {
        if (x.UnitPoints != y.UnitPoints) return false;
        if (!ListsAreEqual(x.Texts, y.Texts)) return false;
        return true;
    }

    private bool ListsAreEqual(List<TranslationData> x, List<TranslationData> y)
    {
        if (x.Count != y.Count) return false;
        for (int i = 0; i < x.Count; i++)
            if (x[i].Text != y[i].Text)
                return false;
        return true;
    }

    public int GetHashCode(Key key)
    {
        int hash = 13;
        hash = (hash * 7) + key.UnitPoints.GetHashCode();
        foreach (var data in key.Texts)
            hash = (hash * 7) + data.Text.GetHashCode();
        return hash;
    }
}

and finally this is what your query will look like:

var values2 = values
    .Distinct()
    .GroupBy(x => new Key(x.UnitPoints, x.Texts), new Comparer())
    .Select(x => new
    {
        x.Key.UnitPoints,
        Texts = x.Key.Texts,
        Site = x.Select(y => y.SiteName)
    }).ToList();

I think the first solution (creating the customized list class) is better, because you can also refactor your code and extract some logic to it.

Using custom grouping operators in LINQ, In LINQ, we can group data using the \ For example, you could create groups of values for which the value of the key is ascending or  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more C# LINQ Group By multiple fields with Custom Properties

Grouping data: the GroupBy() Method, With LINQ, this is very easy, even though the use of the GroupBy() method can be It's just as simple to create your own, custom groups, based on whatever you like own keys which contain several values - these are called composite keys. Data Transformations with LINQ (C#) 07/20/2015; 5 minutes to read +7; In this article. Language-Integrated Query (LINQ) is not only about retrieving data. It is also a powerful tool for transforming data. By using a LINQ query, you can use a source sequence as input and modify it in many ways to create a new output sequence.

Grouping on Multiple Properties with LINQ, In that article I briefly explained that you can create groups that are based upon multiple fields, properties or expressions using an anonymous  In that case all you need (assuming your C# entities maps 1:1 with DB columns): var result = db.GroupBy(x => x.Type) .Select(x => new { Type = x.Key, Count = x.Sum(y => y.Count) });

Group collections by their elements property, If this behavior is ok I would recommend using Dictionary to avoid multiple list Key, new CustomerInfo(group.Key, null, null, group)); return result.Values; }. Here's a simple example to show you how to GroupBy Multiple Values using LINQ. In this example, I am grouping by Age and Sex to find the count of people who have the same age and sex. public partial classLINQ: System.Web.UI.Page. protected void Page_Load (objectsender, EventArgs e) List<Employee> empList = newList<Employee> ();

Comments
  • Thank you !! it's working great for my exemple ! But in my case, the list of TranslationData came from a Linq query. I'm not able to change my select query with 'new TranslationDataList' because then my object is automatically a List<TranslationDataList>, the overriding methods doesn't work :( Would you like to see my code?
  • @julien of course! edit your post so I can see the code! we will work something out.
  • i updated with Edit2 :) Thanks again for your help !
  • Thanks a lot !!! this one is perfect ! I customize a bit the 'ListsAreEqual' from the Comparer class to check the Translations attribute rather than Text and I use my custom GetHashCode :D Again, thanks a lot for your help and your time, it was the first time I ask this kind of help on internet ;)