Identifying Unique Values in a C# List

Question

I have created a class, as below, to represent a Composite Primary Key model:

public class PrimaryKeyModel
{
    public string ColumnName { get; set; }
    public string ColumnValue { get; set; }
    public int RowNumber { get; set; } // always unique
}

It basically represents the names/values of the columns which together make up the primary key, plus the row number where this combination belongs; originally in a Spreadsheet.

I have then put this model in a List and populated it with data from a spreadsheet:

List<PrimaryKeyModel> primaryKeysList = new List<PrimaryKeyModel>; 

I would like to check primaryKeysList and see if it has any duplicated values, and if it has, I would like to know the Row numbers where these values are duplicated.

I have tried different ways such as loading this list into a HashSet, a dictionary and to use this solution here at this link but non of it worked. Is there anyway I could resolve this.

Thanks.

Update - Here is a sample data display. UniqueColumnsModel is the same as PrimaryKeyModel; I have changed it here to make it clearer.

Edit: Clarification of the question

I am trying to import data from a spreadsheet (which can have many types(one for sales, one for quotes ..etc.)) into a database. A configuration table in the database determines which column(s) in a spreadsheet will constitute the primary key in the destination table. My task is to create a routine which validate spreadsheet data before being it being uploaded (imported) into the database using my application. I want ot validate that the columns set as the composites of the primary key, do not contain any duplicated data, so that the primary key constraint is NOT violated in the destination table on insert..

The list mentioned here (PrimaryKeyModel) contains the name of the column in the spreadsheet (which together with others constitutes the primary key), the value of the column in the spreadsheet and the row number in the spreadsheet where this value exists. The list gets populated via a foreach row/ foreach column loops. So I hope this elaborates the question better.

Answer 1

GroupBy works well for this:

primaryKeysList.GroupBy(pk => new {pk.ColumnName, pk.ColumnValue})
               .Where(g => g.Count() > 1)
               .SelectMany(g => g);   // flatten the groups into a single list

Answer 2

EDIT: I may well have misread the question, and inferred too much from your class name being PrimaryKeyModel - I had interpreted that to be a model for a primary key, and that you wanted to find duplicate primary keys. If that's not the case, I urge you to reconsider your naming... at that point, D Stanley's answer is probably what you want, but you should consider ColumnName/ColumnValue to be the "primary key" here - the row number is not part of the key, logically.

---

Original answer

You appear not to have overridden Equals(object) or GetHashCode - which means every object is considered different to every other one. You probably want something like:

public sealed class PrimaryKeyModel : IEquatable<PrimaryKeyModel>
{
    // TODO: Make these read-only (mutable keys are a bad idea...)
    public string ColumnName { get; set; }
    public string ColumnValue { get; set; }
    public int RowNumber { get; set; }

    public override bool Equals(object other)
    {
        return Equals(other as PrimaryKeyModel);
    }

    public bool Equals(PrimaryKeyModel other)
    {
        return other != null &&
               ColumnName == other.ColumnName &&
               ColumnValue == other.ColumnValue &&
               RowNumber == other.RowNumber;
    }

    public override int GetHashCode()
    {
        int hash = 23;
        hash = hash * 31 + ColumnName == null ? 0 : ColumnName.GetHashCode();
        hash = hash * 31 + ColumnValue == null ? 0 : ColumnValue.GetHashCode();
        hash = hash * 31 + RowNumber;
        return hash;
    }
}

This is assuming you really want all three fields to be the same - if you only care about RowNumber, you can simplify those implementations (but at that point it's an odd primary key).

After that, you can use Distinct(), or a HashSet, or a Dictionary etc. Of course, an alternative is to explicitly group by different properties - but it feels like this ought to implement equality sensibly. As noted in comments, I'd urge you to make the properties read-only though.

Answer 3

If your class represents this kind of structure:

ColumnName    ColumnValue   RowNumber
Id            3             1
Id2           1             1 
Id            1             2 
Id2           2             2
Id            3             3 
Id2           1             3 //duplicate

Then all other answers so far are incorrect and you need to do it differently, group by row number and then compare each field one by one. Because equality is commutative we can speed up the loop slightly so we don't compare each item twice.

List<PrimaryKeyModel> keys = new List<PrimaryKeyModel>()
{
        new PrimaryKeyModel("Id", "3", 1),
        new PrimaryKeyModel("Id2", "1", 1),
        new PrimaryKeyModel("Id", "1", 2),
        new PrimaryKeyModel("Id2", "1", 2),
        new PrimaryKeyModel("Id", "3", 3),
        new PrimaryKeyModel("Id2", "1", 3),
};

var groupedKeys = keys.OrderBy(pk => pk.ColumnName).GroupBy(k => k.RowNumber).ToList();
HashSet<int> duplicateRowNumbers = new HashSet<int>();

for (int i = 0; i < groupedKeys.Count - 1; i++)
{
    for (int j = i + 1; j < groupedKeys.Count; j++)
    {
        if (AreTheSame(groupedKeys[i], groupedKeys[j]))
        {
            duplicateRowNumbers.Add(groupedKeys[i].First().RowNumber);
            duplicateRowNumbers.Add(groupedKeys[j].First().RowNumber);
        }
    }
}

private static bool AreTheSame(IEnumerable<PrimaryKeyModel> a, IEnumerable<PrimaryKeyModel> b)
{
    var leftEnumerator = a.GetEnumerator();
    var rightEnumerator = b.GetEnumerator();
    while (leftEnumerator.MoveNext() | rightEnumerator.MoveNext())
    {
        if (leftEnumerator.Current == null) return false;
        if (rightEnumerator.Current == null) return false;
        if (leftEnumerator.Current.ColumnValue != rightEnumerator.Current.ColumnValue) return false;
    }

    return true;
}

Answer 4

This was the final solution which worked for me. This ensures that duplicates do not exist in a row of a list i.e., list of list. It basically pours the list's contents into a hashset which returns false if a newly added item is already existing in the list:

Thanks for all those who contributed to solving this above!

HashSet<string> primaryKeyChecker = new HashSet<string>();

foreach (var row in rows)
{

    StringBuilder primaryKey = new StringBuilder();
    //Get rowCount;

    foreach (var column in columns)
    {
        (if column is a composite of a primaryKey)
        {
            get column value;
            append it to stringBuilder to form the primaryKey
        }   
    }

                            var addOutcome = primaryKeyChecker.Add(primaryKey.ToString());

                            if (!addOutcome)
                            {
                                //Report a duplicate record and give the rowNumber where this occured.
                            }


}

Update

To come out of the issue highlighted with @Bas below, just make sure that when concatenate the primary keys; to seperate them with a coma or 0 so that the highlighted scenario wont occurre.. so do something like this:

  primaryKey.Append(currentValue + ",");