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

Answer 1

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

Answer 2

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

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.

Answer 3

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