Using distinct on all columns in Entity Framework

Question

I am new to Entity Framework. I am trying to write select query in EF which selects distinct columns from a view. I am using following code

List<ClientDTO> oClients = new List<ClientDTO>();

oClients = (from p in context.vwClients
            where (p.ClientNumber == ClientNumber) && p.isDeleted == true
            select new ClientDTO
                   {
                       ClientNumber = p. ClientNumber,
                       IncomeClassId = p.IncomeClassId,
                       FullClientName = p. FullClientName,
                       CountryId = p.CountryId,
                       ProductId = p.ProductId,
                       ProductName = p.ProductName,
                       ProductShortName = p.ProductShortName,
                       isDeleted = p. isDeleted
                   }).OrderBy(x => x. FullClientName).Distinct().ToList();

It still returns duplicate rows. When I write the SQL query with distinct on all columns, it does return distinct rows.

Is there any way to get distinct of all columns used in above code?

score 0 · Answer 1 · answered May 17 '17 at 04:13

Using anonymous objects should work.

var oClients = (from p in context.vwClients where (p.ClientNumber == ClientNumber) && p.isDeleted == true
                                select new ClientDTO
                                {

                                   ClientNumber = p. ClientNumber,
                                   IncomeClassId = p.IncomeClassId,
                                   FullClientName = p. FullClientName,
                                   CountryId = p.CountryId,
                                   ProductId = p.ProductId,
                                   ProductName = p.ProductName,
                                   ProductShortName = p.ProductShortName,
                                   isDeleted = p. isDeleted
                                }
                        ).Select(i => new {i.ClientNumber, i.IncomeClassId, i.FullClientName, i.CountryId, i.ProductId, i.ProductName, i.ProductShortName, i.isDeleted}).Distinct().ToList();

score 0 · Answer 2 · edited May 23 '17 at 12:26

You can simply use the lambda expression for the required result :

List<ClientDTO> oClients =context.vwClients.Where(p=> p.ClientNumber == ClientNumber && p.isDeleted == true)        
                    .Select(p=> new ClientDTO
                    {
                                    ClientNumber = p. ClientNumber,
                                   IncomeClassId = p.IncomeClassId,
                                   FullClientName = p. FullClientName,
                                   CountryId = p.CountryId,
                                   ProductId = p.ProductId,
                                   ProductName = p.ProductName,
                                   ProductShortName = p.ProductShortName,
                                   isDeleted = p. isDeleted
                    }).OrderBy(p => p. FullClientName).Distinct().ToList();

For more example click

Using distinct on all columns in Entity Framework

2 Answers2