How can I select distinct and ordered data into a generic list from multiple lists?

Question

I have several generic lists with some shared and some unique data in them. They contains data of the same class, but are populated using different params (Unit). So all of the generic lists are of this type: List<PriceVarianceData>

What I want to do with these generic lists is to extract from them a subset of the distinct data they contain, and then order that amalgamated list.

To be more specific, the lists to be queried have this structure:

public class PriceVarianceData
{
    public String Unit { get; set; }
    public String ShortName { get; set; }
    public String ItemCode { get; set; }
    public String Description { get; set; }
    public String PriceWeek { get; set; }
    public String Week { get; set; }
    public String Price { get; set; }
    public String Variance { get; set; }
    public String VarianceAverage { get; set; }
    public int RegionOrder { get; set; }
    public int ContractPrice { get; set; }
}

...and the generic list into which I want to extract the data has this structure:

public class PriceVarianceSupersetDisplayData
{
    public String ShortName { get; set; }
    public String ItemCode { get; set; }
    public String Description { get; set; }
}

The Units will have some of the same ShortName + ItemCode + Description values, and I only want unique combinations of those values - there should be no duplicates of ShortName + ItemCode + Description, however if they differ in any value, they are to be considered unique/distinct. So data extracted should, after ordering, look like:

SHORTNAME   ITEMCODE    DESCRIPTION
---------   --------    -----------
Fakeroo     001         Stratoblaster
Fender      001         Stratocaster
Gibson      001         335
Gibson      001         SG
Fender      002         Telecaster
Gibson      002         Les Paul
Carvin      003         Knife
Carvin      003         L6S

I [think I] know that what I need here is LINQ query; in pseudocode, something like:

List<PriceVarianceSupersetDisplayData> displayDataAmalgamated = select distinct ShortName, ItemCode, Description from craftworksPVDList, chophousePVDList, gordonbierschPVDList, oldchicagoPVDList, oldchifranchisePVDList, rockbottomPVDList order by ItemCode then by ShortName, then by Description

...but don't know precisely how to turn that from pseudocode into real LINQ.

UPDATE

A combination of user3185569's and Zoran Horvat's answers seems to work, except that I am apparently not getting distinct values. My first clue was that the number of entries in the final generic list seemed to be too high.

Then I looked at the first two entries in the list, and they are (or at least seem to be) identical:

This is the code I'm using; as mentioned, it is a combination of the first two answers provided:

private List<PriceVarianceSupersetDisplayData> GetSharedDisplayDataForAll()
{
    Func<PriceVarianceData, PriceVarianceSupersetDisplayData> selector =
        (p => new PriceVarianceSupersetDisplayData()
        {
            ShortName = p.ShortName,
            ItemCode = p.ItemCode,
            Description = p.Description
        });

    List<PriceVarianceSupersetDisplayData> displayDataAmalgamated =
            craftworksPVDList.Concat(chophousePVDList)
                            .Concat(chophousePVDList)
                            .Concat(gordonbierschPVDList)
                            .Concat(oldchicagoPVDList)
                            .Concat(oldchifranchisePVDList)
                            .Concat(rockbottomPVDList).Select(selector)
                            .Distinct()
                            .OrderBy(x => x.ItemCode)
                            .ThenBy(x => x.ShortName)
                            .ThenBy(x => x.Description).ToList();

    return displayDataAmalgamated;
}

Why does Distinct() return duplicated values?

It can be done easily. But first, `craftworksPVDList`, `chophousePVDList`, `gordonbierschPVDList`, etc... are all of the same type ? Or you mean the result should be of type `PriceVarianceSupersetDisplayData` — Zein Makki, Jun 29 '16 at 18:04
@user3185569: Yes, all those you list are lists of PriceVarianceData, but a "minimized version" of that ("PriceVarianceSupersetDisplayData", possibly misnamed) is where the amalgamated results are to be stored. — B. Clay Shannon-B. Crow Raven, Jun 29 '16 at 18:08

Zein Makki · Accepted Answer · 2016-06-29T18:43:47.107

Method 1 : Modifying the model

Implement Equals and GetHashCode First. Then, You can add all the lists to one list, select the three keys and use Distinct to remove duplicates and OrderBy - ThenBy for ordering:

public class PriceVarianceSupersetDisplayData
{
    public String ShortName { get; set; }
    public String ItemCode { get; set; }
    public String Description { get; set; }

    public override bool Equals(object obj)
    {
        var pv = obj as PriceVarianceSupersetDisplayData;

        if (pv == null)
            return false;

        return this.ShortName == pv.ShortName
            && this.ItemCode == pv.ItemCode
            && this.Description == pv.Description;
    }

    public override int GetHashCode()
    {
        return 0;
    }
}

Func<PriceVarianceData, PriceVarianceSupersetDisplayData> selector =
                (p => new PriceVarianceSupersetDisplayData()
                {
                    ShortName = p.ShortName,
                    ItemCode = p.ItemCode,
                    Description = p.Description
                });

List<PriceVarianceSupersetDisplayData> results =
            craftworksPVDList.Concat(chophousePVDList)
                            .Concat(gordonbierschPVDList)
                            .Concat(oldchicagoPVDList)
                            .Concat(oldchifranchisePVDList)
                            .Concat(rockbottomPVDList).Select(selector).Distinct()
                            .OrderBy(x=> x.ItemCode).ThenBy(x=> x.ShortName)
                            .ThenBy(x=> x.Description).ToList();

Method 2 : Without modifying the model

(No need to implement Equals or GetHashCode by making using of Tuple's Equality)

List<PriceVarianceSupersetDisplayData> results = 
        craftworksPVDList.Concat(chophousePVDList)
                        .Concat(gordonbierschPVDList)
                        .Concat(oldchicagoPVDList)
                        .Concat(oldchifranchisePVDList)
                        .Concat(rockbottomPVDList).Select(x=> Tuple.Create(x.ShortName, x.ItemCode, x.Description))
                        .Distinct().OrderBy(x=> x.Item2).ThenBy(x=> x.Item1).ThenBy(x=> x.Item3)
                        .Select(t=> new PriceVarianceSupersetDisplayData()
                        {
                            ShortName = t.Item1,
                            ItemCode = t.Item2,
                            Description = t.Item3
                        }).ToList();

Will not work w/o `IEqualityComparer` or `Equals`/`GetHashCode` overrides in the class — Ivan Stoev, Jun 29 '16 at 18:22
@IvanStoev I used `Tuple` first and then changed it forgetting this point. Now Fixed providing both methods. — Zein Makki, Jun 29 '16 at 18:41
Method 2 works just fine - no more dups, apparently, and reduced from 37,020 records to 13,419, so I'm pretty confident there are none. Thanks! — B. Clay Shannon-B. Crow Raven, Jun 29 '16 at 18:58
can you help me with this linq query please:http://stackoverflow.com/questions/38120664/how-to-group-by-on-2-child-entities-and-get-total-of-both-this-child-entities — I Love Stackoverflow, Jun 30 '16 at 11:22

Zoran Horvat · Answer 2 · 2016-06-29T18:15:48.107

1

If you wish to construct another list with only distinct data from all partial lists, then you can do that easily by joining them together using Concat LINQ method, and then using the Distinct LINQ method in the end:

list1.Concat(list2).Concat(list3)...Concat(listN).Distinct();

This assumes that classes are value types. Since you're working with very simple classes, I would suggest you to implement value type semantics into them, and then this kind of list manipulation will become trivial.

Adding value type semantics means to override GetHashCode, Equals, add operator == and operator != and preferably implement IEnumerable with its own Equals(T). With all that done, as I said, list manipulation will become trivial.

Sorting the data can be added at the very end, like:

list1
    .Concat(list2)
    .Concat(list3)
    ...
    .Concat(listN)
    .Distinct()
    .OrderBy(x => x.ItemCode)
    .ThenBy(x => x.ShortName)
    .ThenBy(x => x.Description);

edited Jun 29 '16 at 18:15

answered Jun 29 '16 at 18:09

Zoran Horvat

10,924
3
31
43

But it's just a subset of the lists that are going into the final list (those being queried contain 11 members, whereas the one being populated only has three. – B. Clay Shannon-B. Crow Raven Jun 29 '16 at 18:17
What is the exact criterion you wish to implement? Queries I have written above are returning the list of distinct values. E.g. list1={1, 2}, list2={2, 3} will produce list {1, 2, 3}. – Zoran Horvat Jun 29 '16 at 18:19
Did you override GetHashCode and Equals in your data classes? Distinct method is using both GetHashCode and Equals to determine which objects are duplicate. – Zoran Horvat Jun 29 '16 at 18:57
Okay, that's the problem then; but it works with the simpler version user3.14 supplied. Thanks! – B. Clay Shannon-B. Crow Raven Jun 29 '16 at 18:58
1

I like to have proper value-type semantics built into data classes to prevent subsequent bugs. I wouldn't advise to mimic it on the calling end because every now and then you will forget to do that, or you will add one field and then have to modify all calling places, etc. – Zoran Horvat Jun 29 '16 at 19:01

score 1 · Answer 3 · answered Jun 29 '16 at 19:08

If you don't want to bother with Equals / GetHashCode overrides or implementing IEqualityComparer<PriceVarianceSupersetDisplayData, which are needed to make Distinct work correctly in other answers, the easiest is to use intermediate anonymous type projection (because the compiler implements automatically the correct compare by value semantics for anonymous types) like this:

var displayDataAmalgamated =
    new[] { craftworksPVDList, chophousePVDList, gordonbierschPVDList, oldchicagoPVDList, oldchifranchisePVDList, rockbottomPVDList }
    .SelectMany(list => list.Select(item => new { item.ShortName, item.ItemCode, item.Description }))
    .Distinct()
    .Select(item => new PriceVarianceSupersetDisplayData { ShortName = item.ShortName, ItemCode = item.ItemCode, Description = item.Description })
    .OrderBy(item => item.ShortName).ThenBy(item => item.ItemCode)
    .ToList();

or using query syntax with group by:

var displayDataAmalgamated = (
    from list in new[] { craftworksPVDList, chophousePVDList, gordonbierschPVDList, oldchicagoPVDList, oldchifranchisePVDList, rockbottomPVDList }
    from item in list
    group item by new { item.ShortName, item.ItemCode, item.Description } into g
    orderby g.Key.ShortName, g.Key.Description
    select new PriceVarianceSupersetDisplayData { ShortName = g.Key.ShortName, ItemCode = g.Key.ItemCode, Description = g.Key.Description }
    ).ToList();

How can I select distinct and ordered data into a generic list from multiple lists?

UPDATE

3 Answers3

Method 1 : Modifying the model

Method 2 : Without modifying the model