Distinct not working in entity framework

Question

function for get list of documents name only with distinct...

public static List<DocumentTypeModel> GetUploadedDocumentsName(int TicketId)
{
    List<DocumentTypeModel> documents = new List<DocumentTypeModel>();
    using (var db = new UnitOfWork())
    {
        documents = db.tbl_TrnTicketDocument.Get(x => x.FK_TicketId == TicketId && x.IsActive == true).Select(s => new DocumentTypeModel()
        {
            DocumentTypeNameEnglish = s.tbl_MstDocumentType.DocumentTypeNameEnglish
        }).Distinct().ToList();
    }
    return documents;
}

currently result is this -

Affidavit in Case of Cancelled Will/No Will

Affidavit in Case of Cancelled Will/No Will

Allotment Letter

Allotment Letter

Death Certificate

Death Certificate

Lease Deed

Lease Deed

Photo Identity of Applicant

Photo Identity of Applicant

Possession Letter

Possession Letter

Registered/Unregistered Will

Registered/Unregistered Will

probably http://stackoverflow.com/questions/17401772/iqueryable-distinct-vs-listt-distinct should help. — Vladimir, Mar 30 '16 at 11:45

score 16 · Accepted Answer · answered Mar 30 '16 at 11:45

16

You can use groupby and select first option like this:

List<DocumentTypeModel> documents = new List<DocumentTypeModel>();
using (var db = new UnitOfWork())
{
   documents = db.tbl_TrnTicketDocument.Get(x => x.FK_TicketId == TicketId && x.IsActive == true).Select(s => new DocumentTypeModel()
   {
   DocumentTypeNameEnglish = s.tbl_MstDocumentType.DocumentTypeNameEnglish
   }).ToList();

   documents = documents.GroupBy(x => x.DocumentTypeNameEnglish).Select(g => g.First());
}

answered Mar 30 '16 at 11:45

Navoneel Talukdar

4,393
5
21
42

2 questions, doesn't this return all results in first enumeration of the data? and shouldn't you call .GroupBy before the first select to reduce the number of enumerations to 1? – Kentonbmax Mar 30 '16 at 14:00
Thing is - there is no such think as DistictByField in SQL, so grouping by is probably best bet. Agree with you Neel. Voted up - answer is defenetly right. – Vladimir Apr 01 '16 at 12:09

M.S. · Answer 2 · 2016-03-30T11:53:31.333

Distinct() doesn't work like you tried on objects. Use IComparer to get this working https://support.microsoft.com/en-us/kb/320727

Create a comparer class

public class DocumentTypeModelComparer: IComparer
{
  int IComparer.Compare(object a, object b)
 {
   if(a.Id == b.ID)
     return 0;
   else
     return 1;
  }
}

Now in your lambda expression

 documents = db.tbl_TrnTicketDocument.Get(x => x.FK_TicketId == TicketId && x.IsActive == true).Select(s => new DocumentTypeModel()
        {
            DocumentTypeNameEnglish = s.tbl_MstDocumentType.DocumentTypeNameEnglish
        }).ToList().Distinct(new DocumentTypeModelComparer()).ToList();

Might try to use DistinctBy (it distinct by given field) but it won`t work on IQueryable (it present in MoreLinq - can be found in NuGet) — Vladimir, Mar 30 '16 at 11:52

score 0 · Answer 3 · edited May 23 '17 at 12:09

You are selecting the documenttypemodel and this is the distinct part (so all fields are checked), you probably want the distinct by via https://github.com/morelinq/MoreLINQ, or you can use group by with first see(linq distinct or group by multiple properties).
The other option is to select only the DocumentTypeNameEnglish field and you will get the unique documents.

 documents = db.tbl_TrnTicketDocument.Get(x => x.FK_TicketId == TicketId && x.IsActive == true).Select(s => new  { documentTypes =s.tbl_MstDocumentType.DocumentTypeNameEnglish}).Distinct().ToList();

Hopefully this is what you want, if not can you post more details?

score 0 · Answer 4 · answered Mar 30 '16 at 12:22

0

https://social.msdn.microsoft.com/Forums/en-US/6303da53-c412-4ce6-a983-bd6bd87f516b/how-to-do-a-select-distinct-with-ef?forum=adodotnetentityframework

Kindly refer this link Its useful to you

answered Mar 30 '16 at 12:22

Mahesh Gawhane

61
8

score -2 · Answer 5 · answered Mar 30 '16 at 11:52

-2

Switch to the below per Vladimir's comment:

 .ToList().Distinct();

answered Mar 30 '16 at 11:52

Kentonbmax

938
1
10
16

1

Note that this has the effect of pulling *all* records from DB into memory and only then filtering out duplicates. If you have a lot of duplicates, this will involve a lot more network UI and object instantition than necessary. – Avner Shahar-Kashtan Mar 30 '16 at 12:15
Please see the checked answer above and let me know how that is any different? – Kentonbmax Mar 30 '16 at 12:25
Nope, buddy! This will take unwanted memory and could slow down your API! – Mohaimenul Islam Joy Aug 13 '23 at 09:48

Distinct not working in entity framework

5 Answers5