Entity Framework returning distinct records issue

Question

I have a PC Enity which have some Properties , I would like to return a list of distinct Object (PC or Complex Type or whatever ) based on a property in order to bind it to server controls like DropDownList . And Because my method located in BLL I can't return anonymous type , So I created a Branch ComplexType which has two peroperties.

I wrote like this but it have repeative records:

Click to copy

List<Branch> result = ( from p in _context.PCs
                        where p.UserId== userId
                        select new Branch()
                                   {
                                      BranchId= p.BranchId,
                                      BranchName=p.BranchName
                                   }).Distinct().ToList();

Edit : Thank you all , This worked :

Click to copy

List<PC> result = _context.PCs
                  .GroupBy(p=>p.BranchName , p.BranchId})
                  .select(g=>g.First())
                  .ToList();

Answer 1

this will return distinct rows for all the columns in the select statement. If you want distinct rows for a particular column just specify that particular column

Click to copy

List<Branch> result = ( from p in _context.PCs
                        where p.UserId== userId
                        select new Branch()
                                   {
                                      BranchId= p.BranchId,
                                    }).Distinct().ToList();

If you want to get distinct values based on multiple columns, then you have to create a group and then pick first value from that group. In that case you will not use Distinct, for example

Click to copy

List<Branch> distinctResult = _context.PCs
  .GroupBy(p => new Branch {p.BranchId, p.BranchName} )
  .Select(g => g.First())
  .ToList(); 

Answer 2

I cannot reproduce the problem (tested with SQL Server 2008 R2 and EF 4.1/DbContext). The query in your question...

Click to copy

List<Branch> result = ( from p in _context.PCs
                        where p.UserId== userId
                        select new Branch()
                        {
                            BranchId = p.BranchId,
                            BranchName = p.BranchName
                        })
                        .Distinct()
                        .ToList();

... generates the following SQL:

Click to copy

SELECT 
[Distinct1].[C1] AS [C1],
[Distinct1].[BranchId] AS [BranchId],
[Distinct1].[BranchName] AS [BranchName]
FROM ( SELECT DISTINCT 
       [Extent1].[BranchId] AS [BranchId], 
       [Extent1].[BranchName] AS [BranchName], 
       1 AS [C1]
       FROM [dbo].[PCs] AS [Extent1]
) AS [Distinct1]

It is a DISTINCT on both columns and I get the expected distinct result - no duplicates in BranchId and BranchName.

Answer 3

You get duplicates because Distinct() is unable to recognize two of your complex Branch objects as identical from their properties. It will just compare for object equality, which will return false (because you create two different objects, but with the same values).

You can use Distinct(IQueryable, IEqualityComparer) to provide your own Comparer or implement the IEquatable Interface.