List Distinct is not working in LINQ instead of GroupBy

Question

Thanks in advance. I can get required output when using var but i want to get required output by using Distinct in List<>.

InventoryDetails.cs

public class InventoryDetails
{
        public int? PersonalInventoryGroupId { get; set; }
        public int? PersonalInventoryBinId { get; set; }
}

InventoryController.cs

[HttpGet("GetInventory")]
public IActionResult GetInventory(int id)
{
    //Below code will return distinct record
    var inventory = (from i in _context.TempTbl
                      where i.TempId == id
                      select new
                      {
                          PersonalInventoryBinId = i.PersonalInventoryBinId,
                          PersonalInventoryGroupId = i.PersonalInventoryGroupId,
                      }).ToList().Distinct().ToList();

    //Below code is not doing distinct
    List<InventoryDetails> inventory = (from i in _context.TempTbl
                  where i.TempId == id
                  select new InventoryDetails
                  {
                      PersonalInventoryBinId = i.PersonalInventoryBinId,
                      PersonalInventoryGroupId = i.PersonalInventoryGroupId,
                  }).ToList().Distinct().ToList();
}

If i use var as return type, then i am able to get distinct records. Could some one assist it.

How is the method `.Equals` of `InventoryDetails` implemented? You may want to implement it, for Distinct to work properly. Check out how Distinct works here: http://stackoverflow.com/questions/15423632/how-does-distinct-work — Ori Nachum, Mar 20 '17 at 11:01
I didn't say anything about var. I only asked how is the method `.Equals` of `InventoryDetails` implemented. If you didn't override it, you should — Ori Nachum, Mar 20 '17 at 11:05
I didnt override it. I am trying to get record from dbcontext with distinct — Venkateswaran R, Mar 20 '17 at 11:07
Remove the first `.ToList()` (before `Distinct()`) and you'll get `DISTINCT` applied at SQL level. — Ivan Stoev, Mar 20 '17 at 11:49

Ammar Ahmed · Answer 1 · 2017-03-20T11:36:51.690

0

Please try like this it may help.

IList<InventoryDetails> inventory = _context.InventoryDetails.Where(x=>x.TempId == id).GroupBy(p => new {p.PersonalInventoryGroupId, p.PersonalInventoryBinId } )
  .Select(g => g.First())
  .ToList();

edited Mar 20 '17 at 11:36

answered Mar 20 '17 at 10:53

Ammar Ahmed

126
8

When i am using your code, it is throwing casting exception from view model to table model – Venkateswaran R Mar 20 '17 at 11:01
Are you using EntityFramework then this code should work and _context should be your db contaxt. – Ammar Ahmed Mar 20 '17 at 11:04
Yes i am using entity framework only, you didn't mention about selection – Venkateswaran R Mar 20 '17 at 11:05
try replace "TempTbl" with "InventoryDetails" like this _context.InventoryDetails.Where(x=>x.TempId == id).Distinct().ToList(); – Ammar Ahmed Mar 20 '17 at 11:07
I've edit the answer it should work now, Try it please, if it is resolving your problem then i have another better way to achieve it. – Ammar Ahmed Mar 20 '17 at 11:14
No, it is not distinct it – Venkateswaran R Mar 20 '17 at 11:16
which field you want to distinct? Like which column of your table should be distinct? – Ammar Ahmed Mar 20 '17 at 11:18
Both field i want to distinct – Venkateswaran R Mar 20 '17 at 11:22
Actually, i dont want to use GroupBy – Venkateswaran R Mar 20 '17 at 11:27
Then you can use DistinctBy property, Let me edit my answer – Ammar Ahmed Mar 20 '17 at 11:30
I dont have DistinctBy property, do i want to add system.core.dll assembly? – Venkateswaran R Mar 20 '17 at 11:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138534/discussion-between-venkateswaran-r-and-ammar-ahmed). – Venkateswaran R Mar 20 '17 at 12:51

Liu · Answer 2 · 2017-03-20T12:03:03.127

You need to override Equals and GetHashCode. First, let's see the AnonymousType vs InventoryDetails

        var AnonymousTypeObj1 = new { PersonalInventoryGroupId = 1, PersonalInventoryBinId = 1 };
        var AnonymousTypeObj2 = new { PersonalInventoryGroupId = 1, PersonalInventoryBinId = 1 };

        Console.WriteLine(AnonymousTypeObj1.Equals(AnonymousTypeObj2)); // True

        var InventoryDetailsObj1 = new InventoryDetails { PersonalInventoryBinId = 1, PersonalInventoryGroupId = 1 };
        var InvertoryDetailsObj2 = new InventoryDetails { PersonalInventoryBinId = 1, PersonalInventoryGroupId = 1 };

        Console.WriteLine(InventoryDetailsObj1.Equals(InvertoryDetailsObj2)); // False

You can see the Equals behave differently which make Distinct behave differently. The problem is not var you mentioned in your question but AnonoymizeType

To make Distinct works as you expect, you need to override Equals and GetHashCode

    public class InventoryDetails
    {
        public int? PersonalInventoryGroupId { get; set; }
        public int? PersonalInventoryBinId { get; set; }

        public override bool Equals(object obj)
        {

            if (obj == null) return false;

            if (obj is InventoryDetails)
            {
                if (PersonalInventoryGroupId == (obj as InventoryDetails).PersonalInventoryGroupId 
                    && PersonalInventoryBinId == (obj as InventoryDetails).PersonalInventoryBinId)
                    return true;
            }

            return false;
        }

        public override int GetHashCode()
        {
            int hash = 17;
            hash = hash * 23 + PersonalInventoryBinId.GetHashCode();
            hash = hash * 23 + PersonalInventoryGroupId.GetHashCode();
            return hash;
        }
    }

i dont want to test whether fetched record is having duplicate or not. I want to remove duplicate record from my list without groupby. That is my requirement. — Venkateswaran R, Mar 20 '17 at 11:26
have updated my answer, you also need to override `GetHashCode()` — Liu, Mar 20 '17 at 12:03

Abdul A. Chaudhary · Answer 3 · 2017-03-21T10:13:51.940

Another approach would be

 List<InventoryDetails> inventory = (from i in TempTbl
                                             where i.TempId == id
                                             select new InventoryDetails
                                             {                                                   
                                                 PersonalInventoryBinId = i.PersonalInventoryBinId,
                                                 PersonalInventoryGroupId = i.PersonalInventoryGroupId,
                                             }).AsQueryable().ToList().Distinct(new customComparer()).ToList();

 public class customComparer:IEqualityComparer<InventoryDetails>
{
    public bool Equals(InventoryDetails x, InventoryDetails y)
    {
        if (x.TempId == y.TempId && x.PersonalInventoryBinId == y.PersonalInventoryBinId 
            && x.PersonalInventoryGroupId == y.PersonalInventoryGroupId)
        {
            return true;
        }
        return false;
    }
    public int GetHashCode(InventoryDetails obj)
    {
        return string.Concat(obj.PersonalInventoryBinId.ToString(),
                            obj.PersonalInventoryGroupId.ToString(),
                            obj.TempId.ToString()).GetHashCode();
    }
}

score 0 · Answer 4 · edited May 23 '17 at 12:00

As said in a comment by Ivan, you make your life difficult by calling ToList before Distinct. This prevents the SQL provider from incorporating the Distinct call into the generated SQL statement. But that leaves the question: what causes the difference?

The first query generates anonymous type instances. As per the C# specification, by default anonymous types (in C#) are equal when their properties and property values are equal (structural equality). Conversely, by default, reference types (like InventoryDetails) are equal when their reference (say memory address) is equal (reference equality or identity). They can be made equal by overriding their Equals and GetHashcode methods, as some people suggested to do.

But that's not necessary if you remove the first ToList():

var inventory = (from i in _context.TempTbl
    where i.TempId == id
    select new InventoryDetails
    {
        PersonalInventoryBinId = i.PersonalInventoryBinId,
        PersonalInventoryGroupId = i.PersonalInventoryGroupId,
    }).Distinct().ToList();

Now the whole statement until ToList() is an IQueryable that can be translated into SQL. The SQL is executed and the database returns a distinct result set of raw records from which EF materializes InventoryDetails objects. The C# runtime code was even never aware of duplicates!

List Distinct is not working in LINQ instead of GroupBy

4 Answers4