Get distinct records using linq to entity

Question

Hi I'm using linq to entity in my application. I need to get distinct records based on one column value "Name"

So I have a table similar like you can see below:

(User)
ID
Name
Country
DateCreated

I need to select all this items but uniques based on Name (unique). Is it possible to accomplish using linq, if so please show me how.

var items = (from i in user select new  {i.id, i.name, i.country, i.datecreated}).Distinct();

You've implied there are multiple records for each 'Name'. If that is true, then the question should also indicate how to choose the other fields (id, country, datecreated). For example, for each Name, do you want the record with the minimum datecreated? — crokusek, Aug 16 '17 at 21:21

score 28 · Answer 1 · answered Aug 22 '10 at 16:29

28

The Distinct() method doesn't perform well because it doesn't send the DISTINCT SQL predicate to the database. Use group instead:

var distinctResult = from c in result
             group c by c.Id into uniqueIds
             select uniqueIds.FirstOrDefault();

LINQ's group actually creates subgroups of entities keyed by the property you indicate:

Smith
  John
  Mary
  Ed
Jones
  Jerry
  Bob
  Sally

The syntax above returns the keys, resulting in a distinct list. More information here:

http://imar.spaanjaars.com/546/using-grouping-instead-of-distinct-in-entity-framework-to-optimize-performance

answered Aug 22 '10 at 16:29

Dave Swersky

34,502
9
78
118

I forgot I need to use inner join to another table. Could you show me please how to change code? – Boommer Aug 22 '10 at 16:34
4

Distinct() does in fact cause the SQL to include the DISTINCT predicate. Why would you think it doesn't? Of course it will only do so if you're still working with IQUeryables.. but it does. – Erik Funkenbusch Aug 22 '10 at 19:16
This answer totally saved me :D Thanks, Dave! – programad Jan 17 '12 at 11:43
Distinct is passed through (+1 @ErikFunkenbusch) even when there is an intervening select new {} (anonymous type). However it may not pass through when a custom comparer is specified (like in the cited link). The cited link adds a ToList() to materialize the SQL result causing the distinct to be run in C#. – crokusek Aug 16 '17 at 21:43
@crokusek - That's why I said "it will only do so if you're still working with IQueryable" – Erik Funkenbusch Aug 17 '17 at 15:00
I felt your comment from 7 years ago must need some help because the accepted answer still claims that distinct() (no args) does not pass through to SQL. There was no mention about Distinct(IEqualityComparer), which does returns an IQueryable but may not pass through to SQL. – crokusek Aug 18 '17 at 01:27

Jon Hanna · Answer 2 · 2016-05-17T11:24:22.977

The purely LINQ way that occurs is to group by name, select distinct groups by key, then select based on that.

from i in user
group new {i.ID, i.Country, i.DateRecord} by i.Name into byNmGp
select byNmGp.First();

Edit: Entity Framework is of course a very popular linq provider, but it doesn't handle First() well here, though the logically equivalent (in this case) FirstOrDefault() will work fine. I prefer First() when not forced into FirstOrDefault() by EF's limitations, because its meaning better matches what is sought here.

Another way is to define a helper class:

private class MyRecord : IEquatable<MyRecord>
{
  public int ID;
  public string Name;
  public string Country;
  public DateTime DateCreated;
  public bool Equals(MyRecord other)
  {
    return Name.Equals(other.Name);
  }
  public override bool Equals(object obj)
  {
    return obj is MyRecord && Equals((MyRecord)obj);
  }
  public override int GetHashCode()
  {
    return Name.GetHashCode();
  }
}
/*...*/
var items = (from i in user select new MyRecord {i.ID, i.Name, i.Country, i.DateRecord}).Distinct();

This simply defines distinct differently. Performance will differ by whether the query provider can interpret that definition of equality or not. Convenience will differ based on whether you've similar LINQ queries doing much the same thing or not.

`i from iSrc join j from jSrc on i.someField equals j.someField`. It's not the same question as this though, it can be better to ask separate questions rather than adding and adding to the same one. People look at questions based on the question, and knowing the answer to one doesn't always entail knowing the answer to another. — Jon Hanna, Aug 22 '10 at 16:58
U misunderstood me. I'm asking how to group or just how to accomplish the same that I ask but using inner join (multiple tables). — Boommer, Aug 22 '10 at 18:37
U misunderstood me, as the above explains that perfectly. If you need more detail, ask a new question. This only allows a few hundred characters. — Jon Hanna, Aug 22 '10 at 21:12

score 2 · Answer 3 · edited Oct 19 '12 at 21:39

2

You can use something like this:

var distinctReports = reports.Select(c => c.CompanyCode)
                             .Distinct()
                             .Select(c => reports.FirstOrDefault(r => r.CompanyCode == c))
                             .ToList();

edited Oct 19 '12 at 21:39

Adi Lester

24,731
12
95
110

answered May 05 '12 at 11:19

Svetlana Gurskaya

179
1
6

score 2 · Answer 4 · answered Jul 21 '15 at 18:32

2

Here's another variation I ended up using which was based off the response from Svetlana. Shows an example of populating a GridView control with unique values. Thanks!

        dataGridView_AnalyzeTestSuites.DataSource = (
            from tr in _db.TestResults
            where tr.TaskId == taskId
            select new { TestSuiteName = tr.Test.TestSuite.Name }
            ).Distinct().ToList();

answered Jul 21 '15 at 18:32

GrayDwarf

2,469
2
20
22

This is the proper answer, since the OP asked about linq syntax, not lambda. – Tsar Bomba Dec 23 '16 at 17:11

score 1 · Answer 5 · answered Aug 22 '10 at 18:57

Hi here is how you can select distinct records with inner join. Hope it helps

var distinctrecords = 
(entity.Table.Join(entity.Table2, x => x.Column, y => y.Column, (x, y) => new {x, y})
             .Select(@t => new {@t.x.Column2, @t.y.Column3}))
             .GroupBy(t => t.Column2)
             .Select(g => g.FirstOrDefault());

Get distinct records using linq to entity

5 Answers5

Linked