Entity Framework returning distinct records issue

Question

Entity Framework returning distinct records issue

asp.net c# complextype distinct entity-framework

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:

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 :

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

Mostafa

Popular Answer

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

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:

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.

Slauma

View more on Stack Overflow

Habib · Accepted Answer · 4/2/2012 4:32:52 PM

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

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

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

Entity Framework returning distinct records issue

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related