Remove Duplicate items from linq query

Question

I read information from the database with ef core

var dbQuery = db.ManifestTable.Select(x => new
{
    x.Id,
    x.Uri,
    x.Code,
    x.Version
});
var data = await dbQuery.ToListAsync();

return data
    .GroupBy(x => x.Id)
    .OrderBy(x => x.Key)
    .Select(g => new myModel
    {
        Id = g.Key,
        Uri = g.Select(x => x.Uri).First(),
        Code = g.Select(x => x.Code).First(),
        myVersion = g.Select(x => new myVersion { Version = x.Version, Uri = x.Uri }).OrderByDescending(x => x.Version).First(),
        Versions = g.Select(x => new myVersion { Version = x.Version, Uri = x.Uri }).OrderByDescending(x => x.Version).ToList()
    });

The problem is that there are duplicates items in the Versions field

I tried to use the Distinct method but it did not work How can I remove duplicates versions from the Versions field?

What does "duplicated record" means for you? An object with Id, Uri, Code and version that are identical? — user1624411, Jun 19 '21 at 12:46
As far as I can see you only group by `ID`. So why wouldn't you get duplicate values in the `Version` field? — Barns, Jun 19 '21 at 13:12
Does this answer your question? [Group By Multiple Columns](https://stackoverflow.com/questions/847066/group-by-multiple-columns) OR [C# Linq Group By on multiple columns](https://stackoverflow.com/questions/5231845/c-sharp-linq-group-by-on-multiple-columns) — Barns, Jun 19 '21 at 13:18

score 0 · Accepted Answer · answered Jun 19 '21 at 14:46

For Distinct you need to implement IEqualityComparer Interface. A C#9 code sample that is on a simple list (not your data, just a simple mockup) but should work for you. Note I used a unit test project for validation that the comparers work.

I would first look at this consider @Barns recommendation on GroupBy.

Another idea if possible is to prevent duplicates from being entered if business rules permit this.

Container

public class Resident
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
}

Comparers

using System;
using System.Collections.Generic;

/*
 * Each class should be in their own class file, here they are
 * in one file for easily comparing (excuse the pun) implementation
 * of a string comparer vs a int comparer.
 */
namespace QueryOperatorsTest.Classes
{
    /// <summary>
    /// Comparer against Name property
    /// </summary>
    public class ResidentNameComparer : IEqualityComparer<Resident>
    {
        public bool Equals(Resident x, Resident y) => 
            string.Equals(x.Name, y.Name, StringComparison.OrdinalIgnoreCase);
        public int GetHashCode(Resident obj) => obj.Name.GetHashCode();
    }
    /// <summary>
    /// Comparer against primary key Id
    /// </summary>
    public class ResidentIdComparer : IEqualityComparer<Resident>
    {
        public bool Equals(Resident x, Resident y) => Equals(x.Id, y.Id);
        public int GetHashCode(Resident obj) => obj.Id.GetHashCode();
    }
}

Test methods (which reside in a unit test project/class)

[TestMethod]
public void NoDuplicateNameExample()
{
    var residents = new List<Resident>
    {
        new() {Id = 1, Name = "Yelena", City = "Portland"},
        new() {Id = 2, Name = "Mary", City = "Portland"},
        new() {Id = 3, Name = "Lisa", City = "Portland"},
        new() {Id = 4, Name = "Jon", City = "Portland"},
        new() {Id = 5, Name = "Mary", City = "Salem"},
        new() {Id = 6, Name = "Bill", City = "Salem"},
        new() {Id = 7, Name = "Anne", City = "Salem"},
        new() {Id = 8, Name = "Jon", City = "Salem"}
    };

    var noDuplicateNames = residents.Distinct(new ResidentNameComparer());
    Assert.IsTrue(noDuplicateNames.Count() == 6);
}
[TestMethod]
public void NoDuplicateIdExample()
{
    var residents = new List<Resident>
    {
        new() {Id = 1, Name = "Yelena", City = "Portland"},
        new() {Id = 2, Name = "Mary", City = "Portland"},
        new() {Id = 1, Name = "Lisa", City = "Portland"},
        new() {Id = 1, Name = "Jon", City = "Portland"},
        new() {Id = 5, Name = "Mary", City = "Salem"},
        new() {Id = 1, Name = "Bill", City = "Salem"},
        new() {Id = 7, Name = "Anne", City = "Salem"},
        new() {Id = 1, Name = "Jon", City = "Salem"}
    };

    var noDuplicateIdentifiers = residents.Distinct(new ResidentIdComparer());
    Assert.IsTrue(noDuplicateIdentifiers.Count() == 4);

}

Remove Duplicate items from linq query

1 Answers1