Issue with Entity Framework returning distinct records
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();
Accepted Answer
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();
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
