How do I group by in LINQ using multiple criteria?

Question

Is there a way to get all the combinations possibles in an ObservableCollection?

I have a model like this:

    public string MyProperty { get; set; }
    public string MyProperty2 { get; set; }
    public string MyProperty3 { get; set; }
    public string MyProperty4 { get; set; }
    public string MyProperty5 { get; set; }
    public string MyProperty6 { get; set; }
    public string MyProperty7 { get; set; }
    public string MyProperty8 { get; set; }

And I fill this model with data from a spreadsheet, but some values have zero or empty values (which I need to exclude). Is there a way to get all the possible combinations with the same pattern?

For example, all the combinations with values different from 0 in all the properties, and all the combinations when there has only one property with value and the others zero, etc.

So far I have something like this:

var group1 = _sourceStructure.Where(c => c.MyProperty != "0" && c.MyProperty2 != "0" && c.MyProperty3 != "0" && c.MyProperty4 != "0"
        && c.MyProperty5 != "0" && c.MyProperty6 != "0" && c.MyProperty7 != "0" && c.MyProperty8 != "0");

But with this I need to use more than 30 cases to evaluate, is there a way to get all the possible combinations using LINQ or another solution?

I want to build an SQL query with the values from the collection, but if the value has 0 or is empty, I'll not add that value to the query. I want to get all the combinations with the same pattern in order to be able to put all of the items with same pattern within an IN in SQL.

The output data will be something like this:

string query = @"Select field1, field2, field3, fieldn FROM table WHERE "

query = query + "field1 = " + _sourceStructure.MyProperty1;

query = query + "fieldN =  " + _sourceStructure.MyPropertyN;

Basically, I don't care which the value is. I only need to group the collection with all the possible combinations with the same pattern.

Sample data from the original file in Excel:

       MyProperty1 MyPropert2 MyPropertN
Row1      0          1           3
Row2      2          0           6
Row3      0          5           9
Row4      9          9           4
Row5      4          3           6
Row6      0          0           0

Here, for example, I'm expecting that Row1 and Row3 will be in the same group (the values are not the same but the "structure" it's the same), and then Row4 and Row5 will be another group, Row6 another one, and Row2 another one.

If those are the correct property names, have you considered using a `List` instead of discrete fields? — 15ee8f99-57ff-4f92-890c-b56153, May 07 '19 at 20:29
Are you sure that grouping is what you're looking for, or do you just want to query the object to find or exclude certain ones? Grouping is something different. — Scott Hannen, May 07 '19 at 20:32
@ScottHannen Yes I want to get only the fields with a value different from "0", but if the item has one property with "0" I want to get the others properties,make sense? and get all the possible combinations from all the collection, there will be more than 90k records — Shepniel Sh, May 07 '19 at 20:33
I agree with @EdPlunkett. If you had a list then the code would be `Where(c => c.MyList.All(x => x != "0"))` — juharr, May 07 '19 at 20:34
Can you PIVOT your table? That make those kind of query a lot more easy — Juan Carlos Oropeza, May 07 '19 at 20:34
If you exclude one value because it equals "0" or is empty, how will you know which one has been excluded? Do you just want to extract a list of strings from each object, and it doesn't matter if there's one string or eight? Perhaps it would be clearer if you showed what the data would look like after you extract it. — Scott Hannen, May 07 '19 at 20:36
@ScottHannen I edited my question to try to be more specifi. — Shepniel Sh, May 07 '19 at 20:56
Can you add some sample data and expected output. After the edit not sure if my answer works. — Juan Carlos Oropeza, May 07 '19 at 20:58
First you mention some filter, now you mention need group based only on the structure? Still I want to see what is the output you want I dont want have to guess as looks like I already guess wrong. My answer aim to find you rows with the structure you need. But this seem totally different — Juan Carlos Oropeza, May 07 '19 at 21:16
@JuanCarlosOropeza Sorry English it's not my natural language I tend to write kinda confusing sometimes, basically, I have a collection with a structure like the last edit, I want to be able to group my collection like I mentioned above, the Row1...RowN it's only to show, there are not fields in the collection such as Row1, etc. In order to get one "group" of items and put it together within a single query in SQL, then another "group" and run it in SQL, I don't know if that make sense? — Shepniel Sh, May 07 '19 at 21:24
Any chance you main language is spanish? I know row# is just a handler. But you should have some ID to be Primary Key. What I understand by Groups Is you want add a new field and start numbering from 1 to the number of distinct group? And a group is based on how many properties with 0 has? — Juan Carlos Oropeza, May 07 '19 at 21:31
If I am not mistaken you need to generate [permutation](https://en.wikipedia.org/wiki/Permutation), where count row is *n!*. Look at this answer: https://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server — vladimir, May 07 '19 at 21:32
@JuanCarlosOropeza Yes it is spanish, any chances to try to contact you by message? — Shepniel Sh, May 07 '19 at 21:33

score 1 · Answer 1 · edited May 09 '19 at 11:32

UNPIVOT your table to something like this:

 Observable     Property      Value
     A             1            0
     A             2            1
     A             3            2
     B             1            0
     B             2            0
     B             3            1
     C             1            1
     C             2            2
     C             3            3

All the combinations with values different from 0 in all the properties:

SELECT Observable
FROM Table
GROUP BY Observable
HAVING SUM(CASE WHEN VALUE = 0 THEN 1 ELSE 0 END) = 0

All the combinations when there has only one property with value and the others zero:

SELECT Observable
FROM Table
GROUP BY Observable
HAVING COUNT(CASE WHEN VALUE > 0 THEN 1 END) = 1

SQL DEMO

score 1 · Accepted Answer · edited May 09 '19 at 11:33

You can use reflection to get all the properties named MyPropertyn, where n is 1 to whatever, and then you can use the List of PropertyInfos to compute a bitmask for the populated properties, and group by that value.

First some extensions methods I use:

public static class StringExt {
    public static string Past(this string s, string starter) {
        var starterPos = s.IndexOf(starter);
        return starterPos == -1 ? String.Empty : s.Substring(starterPos + starter.Length);
    }
}

public static class NumericExt {
    public static int ToInt<T>(this T obj) => Convert.ToInt32(obj);

    public static int IntPow(this int x, int pow) {
        int ans = 1;

        while (pow != 0) {
            if ((pow & 0x1) == 1)
                ans *= x;
            x *= x;
            pow >>= 1;
        }
        return ans;
    }
}

Now you can gather the properties of interest, sorted, and then compute the bitmask for each row:

var myPropInfos = typeof(COC).GetProperties()
                             .Where(pi => pi.Name.StartsWith("MyProperty"))
                             .OrderBy(pi => pi.Name.Past("MyProperty").ToInt()) // just in case properties aren't ordered
                             .ToList();
var GroupedFilters = src.Select(r => new { r, ValuedMask = myPropInfos.Select((pi, p) => pi.GetValue(r).ToString() != "0" ? 2.IntPow(p) : 0).Sum() })
                        .GroupBy(rm => rm.ValuedMask, rm => rm.r);

If the first property doesn't end in an integer you will need a test to handle that in the ordering, or alternatively, (especially if the properties don't all end in a number) you can leave off the OrderBy and use whatever order GetProperties returns - the order doesn't really matter. The answer returned is an IGrouping for each combination of valued properties, with the Key the bitmask showing which properties were valued.

If you have more than 31 properties, you should switch to using long and create the obvious LongPow extension method (2L.LongPow(p) : 0L).

How do I group by in LINQ using multiple criteria?

2 Answers2