Linq group by except column

linq group by select multiple columns
c# linq group by multiple levels
linq group by select all columns
linq join group by multiple columns
linq group by sum multiple columns
linq group by select one column
linq group by select columns
c# lambda group by multiple columns

I have a class with large amount of properties that I need to group by almost all columns.

class Sample {
    public string S1 { get; set; }
    public string S2 { get; set; }
    public string S3 { get; set; }
    public string S4 { get; set; }
    // ... all the way to this:
    public string S99 { get; set; }

    public decimal? N1 { get; set; }
    public decimal? N2 { get; set; }
    public decimal? N3 { get; set; }
    public decimal? N4 { get; set; }
    // ... all the way to this:
    public decimal? N99 { get; set; }
}

From time to time I need to group by all columns except one or two decimal columns and return some result based on this (namely object with all the fields, but with some decimal value as a sum or max).

Is there are any extension method that would allow me to do something like this:

sampleCollection.GroupByExcept(x => x.N2, x => x.N5).Select(....);

instead of specifying all columns in object?

Borrowing from this answer here:

Create a class EqualityComparer

public class EqualityComparer<T> : IEqualityComparer<T>
{
    public bool Equals(T x, T y)
    {
        IDictionary<string, object> xP = x as IDictionary<string, object>;
        IDictionary<string, object> yP = y as IDictionary<string, object>;

        if (xP.Count != yP.Count)
            return false;
        if (xP.Keys.Except(yP.Keys).Any())
            return false;
        if (yP.Keys.Except(xP.Keys).Any())
            return false;
        foreach (var pair in xP)
            if (pair.Value.Equals( yP[pair.Key])==false)
                return false;
        return true;

    }

    public int GetHashCode(T obj)
    {
        return obj.ToString().GetHashCode();
    }
}

Then create your GroupContent method:

private void GroupContent<T>(List<T> dataList, string[] columns, string[] columnsToExclude)
    {
string[] columnsToGroup = columns.Except(columnsToExclude).ToArray();
        EqualityComparer<IDictionary<string, object>> equalityComparer = new EqualityComparer<IDictionary<string, object>>();
        var groupedList = dataList.GroupBy(x =>
        {
            var groupByColumns = new System.Dynamic.ExpandoObject();
            ((IDictionary<string, object>)groupByColumns).Clear();
            foreach (string column in columnsToGroup)
                ((IDictionary<string, object>)groupByColumns).Add(column, GetPropertyValue(x, column));
            return groupByColumns;
        }, equalityComparer);


        foreach (var item in groupedList)
        {
            Console.WriteLine("Group : " + string.Join(",", item.Key));
            foreach (object obj in item)
                Console.WriteLine("Item : " + obj);
            Console.WriteLine();
        }

    }

    private static object GetPropertyValue(object obj, string propertyName)
    {
        return obj.GetType().GetProperty(propertyName).GetValue(obj, null);
    }

LINQ: Select all from each group except the first item, This will give you one item per group: List<dynamic> data = new List<dynamic> { new {ID = 1, Message = "Hello", GroupId = 1, Date = DateTime� LINQ Group By a Single Column. Before we use Group By with multiple columns, let’s start with something simpler. Let’s say that we just want to group by the names of the Categories, so that we can get a list of them. The SQL would look like this: SELECT Category FROM Recipes GROUP BY Category In LINQ query syntax, the same query would read

I extended the code above borrowing another answer.

public static class IEnumerableExt {
    public static IEnumerable<T> GroupBye<T, C>(this IEnumerable<T> query, Func<IGrouping<IDictionary<string, object>, T>, C> grouping) where T : class
    {
        var cProps = typeof(C).GetProperties().Select(prop => prop.Name).ToArray();
        var columnsToGroup = typeof(T).GetProperties().Select(prop => prop.Name).Except(cProps).ToArray();
        var equalityComparer = new EqualityComparer<IDictionary<string, object>>();
        return query
            .GroupBy(x => ExpandoGroupBy(x, columnsToGroup), equalityComparer)
            .Select(x => MergeIntoNew(x, grouping, cProps));
    }

    private static IDictionary<string, object> ExpandoGroupBy<T>(T x, string[] columnsToGroup) where T : class
    {
        var groupByColumns = new System.Dynamic.ExpandoObject() as IDictionary<string, object>;
        groupByColumns.Clear();
        foreach (string column in columnsToGroup)
            groupByColumns.Add(column, typeof(T).GetProperty(column).GetValue(x, null));
        return groupByColumns;
    }

    private static T MergeIntoNew<T, C>(IGrouping<IDictionary<string, object>, T> x, Func<IGrouping<IDictionary<string, object>, T>, C> grouping, string[] cProps) where T : class
    {
        var tCtor = typeof(T).GetConstructors().Single();
        var tCtorParams = tCtor.GetParameters().Select(param => param.Name).ToArray();
        //Calling grouping lambda function
        var grouped = grouping(x);
        var paramsValues = tCtorParams.Select(p => cProps.Contains(p) ? typeof(C).GetProperty(p).GetValue(grouped, null) : x.Key[p]).ToArray();
        return (T)tCtor.Invoke(paramsValues);
    }

    private class EqualityComparer<T> : IEqualityComparer<T>
    {
        public bool Equals(T x, T y)
        {
            var xDict = x as IDictionary<string, object>;
            var yDict = y as IDictionary<string, object>;

            if (xDict.Count != yDict.Count)
                return false;
            if (xDict.Keys.Except(yDict.Keys).Any())
                return false;
            if (yDict.Keys.Except(xDict.Keys).Any())
                return false;
            foreach (var pair in xDict)
                if (pair.Value == null && yDict[pair.Key] == null)
                    continue;
                else if (pair.Value == null || !pair.Value.Equals(yDict[pair.Key]))
                    return false;
            return true;
        }

        public int GetHashCode(T obj)
        {
            return obj.ToString().GetHashCode();
        }
    }
}

Which can be used in the following way:

var list = enumerable.GroupBye(grp => new
    {
        Value = grp.Sum(val => val.Value)
    });

The result will like grouping all other columns but Value, which will be valued to the sum of grouped elements' value

Select all columns but group by only one in linq, This will give you one item per group: List<dynamic> data = new List<dynamic> { new {ID = 1, Message = "Hello", GroupId = 1, Date = DateTime� Group by in linq with sum In above example if you notice, we have used sum function to get the sum of score, here is the piece of code again var groupScores = from techStream in studentsStream group techStream by techStream.StreamId into studentGroup select new { Stream = studentGroup.Key, GroupScore = studentGroup.Sum(x => x.Score), };

You won't find anything builtin that handles such a case. You'd have to create one yourself. Depending on how robust you need this to be, you could take a number of approaches.

The main hurdle you'll come across is how you'll generate the key type. In an ideal situation, the new keys that are generated would have their own distinct type. But it would have to be dynamically generated.

Alternatively, you could use another type that could hold multiple distinct values and still could be suitably used as the key. Problem here is that it will still have to be dynamically generated, but you will be using existing types.

A different approach you could take that doesn't involve generating new types, would be to use the existing source type, but reset the excluded properties to their default values (or not set them at all). Then they would have no effect on the grouping. This assumes you can create instances of this type and modify its values.

public static class Extensions
{
    public static IQueryable<IGrouping<TSource, TSource>> GroupByExcept<TSource, TXKey>(this IQueryable<TSource> source, Expression<Func<TSource, TXKey>> exceptKeySelector) =>
        GroupByExcept(source, exceptKeySelector, s => s);

    public static IQueryable<IGrouping<TSource, TElement>> GroupByExcept<TSource, TXKey, TElement>(this IQueryable<TSource> source, Expression<Func<TSource, TXKey>> exceptKeySelector, Expression<Func<TSource, TElement>> elementSelector)
    {
        return source.GroupBy(BuildKeySelector(), elementSelector);

        Expression<Func<TSource, TSource>> BuildKeySelector()
        {
            var exclude = typeof(TXKey).GetProperties()
                .Select(p => (p.PropertyType, p.Name))
                .ToHashSet();
            var itemExpr = Expression.Parameter(typeof(TSource));
            var keyExpr = Expression.MemberInit(
                Expression.New(typeof(TSource).GetConstructor(Type.EmptyTypes)),
                from p in typeof(TSource).GetProperties()
                where !exclude.Contains((p.PropertyType, p.Name))
                select Expression.Bind(p, Expression.Property(itemExpr, p))
            );
            return Expression.Lambda<Func<TSource, TSource>>(keyExpr, itemExpr);
        }
    }
}

Then to use it you would do this:

sampleCollection.GroupByExcept(x => new { x.N2, x.N5 })...

But alas, this approach won't work under normal circumstances. You won't be able to create new instances of the type within a query (unless you're using Linq to Objects).


If you're using Roslyn, you could generate that type as needed, then use that object as your key. Though that'll mean you'll need to generate the type asynchronously. So you probably will want to separate this from your query all together and just generate the key selector.

public static async Task<Expression<Func<TSource, object>>> BuildExceptKeySelectorAsync<TSource, TXKey>(Expression<Func<TSource, TXKey>> exceptKeySelector)
{
    var exclude = typeof(TXKey).GetProperties()
        .Select(p => (p.PropertyType, p.Name))
        .ToHashSet();
    var properties =
        (from p in typeof(TSource).GetProperties()
        where !exclude.Contains((p.PropertyType, p.Name))
        select p).ToList();
    var targetType = await CreateTypeWithPropertiesAsync(
        properties.Select(p => (p.PropertyType, p.Name))
    );
    var itemExpr = Expression.Parameter(typeof(TSource));
    var keyExpr = Expression.New(
        targetType.GetConstructors().Single(),
        properties.Select(p => Expression.Property(itemExpr, p)),
        targetType.GetProperties()
    );
    return Expression.Lambda<Func<TSource, object>>(keyExpr, itemExpr);

    async Task<Type> CreateTypeWithPropertiesAsync(IEnumerable<(Type type, string name)> properties) =>
        (await CSharpScript.EvaluateAsync<object>(
            AnonymousObjectCreationExpression(
                SeparatedList(
                    properties.Select(p =>
                        AnonymousObjectMemberDeclarator(
                            NameEquals(p.name),
                            DefaultExpression(ParseTypeName(p.type.FullName))
                        )
                    )
                )
            ).ToFullString()
        )).GetType();
}

To use this:

sampleCollection.GroupBy(
    await BuildExceptKeySelector((CollectionType x) => new { x.N2, x.N5 })
).Select(....);

[SOLVED], Books = g.ToList() //Actually this line of is not working. Probably because Books property is type of List<string> , not List<ActualClass> . Can you� In this article, I am going to discuss the LINQ Except in C# with examples. Please read our previous article where we discussed the LINQ Distinct Method with examples. As part of this article, we are going to discuss the following pointers. What is LINQ Except in C#? Examples of C# LINQ Except Method using both Method and Query Syntax

[SOLVED], gazmendmehmeti - 20 mins ago. You can do it in this way: Hide Copy Code. var ReportData = from a in context.Table1 from b in context.Table2� These names not only sound alike in name but also are like the type of joins present in any relational database management system like SQL Server and they even operate in the same way. The following figure provides a high-level explanation of all these joins except the Group Join that is specific to LINQ.

Select a field that is not in Group By clause in LINQ, The group by statement gives us this same capability in LINQ – by The first step in creating any query is to refine the fields we want to return� Group query results. 12/01/2016; 8 minutes to read +3; In this article. Grouping is one of the most powerful capabilities of LINQ. The following examples show how to group data in various ways: By a single property. By the first letter of a string property. By a computed numeric range. By Boolean predicate or other expression. By a compound key.

Grouping and Aggregating Data with LINQ, What is LINQ Except in C#?; Examples of C# LINQ Except Method using both Method and Query Syntax; How to implement IEqualityComparer? if in table are inserted a date for every month and I want to get all data except those that are not in February i would do: ReportData = ReportData.Where(a => a.DateField.Value.Month != 2); it doesn't care about this condition because i have just the first month