Issue with Entity Framework returning distinct records
asp.net c# complextype distinct entity-framework
Question
I have a PC entity that has several properties. In order to attach it to server controls like DropDownList, I would need to return a list of different Objects (PC or Complex Type or whatever) depending on a property. I also couldn't return an anonymous type since my function is in BLL, so I created a Branch ComplexType with two properties.
This is what I typed, although it had repetitive 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 to everyone, it worked:
List<PC> result = _context.PCs
.GroupBy(p=>p.BranchName , p.BranchId})
.select(g=>g.First())
.ToList();
Accepted Answer
this will produce different rows for each of the select statement's columns. Just mention that specific column if you want unique rows for that column.
List<Branch> result = ( from p in _context.PCs
where p.UserId== userId
select new Branch()
{
BranchId= p.BranchId,
}).Distinct().ToList();
You must first construct a group and then choose the first value from that group if you wish to get separate values depending on various columns. In such situation, you won't employ Distinct, for instance.
List<Branch> distinctResult = _context.PCs
.GroupBy(p => new Branch {p.BranchId, p.BranchName} )
.Select(g => g.First())
.ToList();
Popular Answer
(Tested with SQL Server 2008 R2 and EF 4.1/DbContext) I am unable to duplicate the issue. The question you ask is...
List<Branch> result = ( from p in _context.PCs
where p.UserId== userId
select new Branch()
{
BranchId = p.BranchId,
BranchName = p.BranchName
})
.Distinct()
.ToList();
... produces the SQL statement below:
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]
There are no duplicates in either column since it is a DISTINCT on both of them.BranchId
and BranchName
.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
