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();
1
12
4/2/2012 4:49:00 PM

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(); 
11
4/2/2012 4:32:52 PM

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





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow