CODEWITHSUNDEEP
c#linq-to-entities
Boommer
Boommer

Reputation: 261

Get distinct records using linq to entity

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();

Upvotes: 26

Views: 59006

Answers (5)

Jon Hanna
Jon Hanna

Reputation: 113222

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.

Upvotes: 8

GrayDwarf
GrayDwarf

Reputation: 2737

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();

Upvotes: 2

Svetlana Gurskaya
Svetlana Gurskaya

Reputation: 189

You can use something like this:

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

Upvotes: 2

Eugene
Eugene

Reputation: 1057

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());

Upvotes: 1

Dave Swersky
Dave Swersky

Reputation: 34810

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

Upvotes: 28

Related Questions