I want full row detail with use of distinct in my linq query

Question

I have a table with the details of property

------------------------------------------------
Id | Propertyname |date_created |Price |agent_name
------------------------------------------------
1  |xxxxxx        |17-06-2015   |10000 |abc
2  |xxyyyyy       |15-06-2015   |10000 |abc
3  |xxyyyyy       |16-06-2015   |10000 |bcd
4  |xxyyyyy       |15-06-2015   |10000 |bcd
5  |xxyyyyy       |17-06-2015   |10000 |cde
6  |xxyyyyy       |15-06-2015   |10000 |cde

I want a result at

------------------------------------------------
    Id | Propertyname |date_created |Price |agent_name
    ------------------------------------------------
    1  |xxxxxx        |17-06-2015   |10000 |abc
    3  |xxyyyyy       |16-06-2015   |10000 |bcd
    5  |xxyyyyy       |17-06-2015   |10000 |cde

I want a result with distinct of agent_name with full row details

I tried below code

var property = 
    from l in properties
    group l by l.agent_name into g
    let lastUsed = g.OrderBy(x => x.date_created).Last()
    select lastUsed;

Adove code displayed error

LINQ to Entities does not recognize the method 'properties Lastproperties' method, and this method cannot be translated into a store expression.

Anyone know how to solve this

Answer 1

var property=(from n in bc.db.properties select n).GroupBy(x => x.agent_name)
      .Where(g => g.Count() == 1)
      .Select(g => g.FirstOrDefault()).OrderByDescending(x=>x.date_created)

Answer 2

Created following code on LinqPad and it works, let me figure out if there's a better and much more optimized version of the solution

void Main()
{   
    var result = Detail.Create();

    var final = result.GroupBy(s=>s.agent).ToDictionary(p=>p.Key,p=>p.OrderBy(n=>n.dateTime).Last())
                                          .Select(a=>new 
                                            { 
                                            a.Value.id,
                                            a.Value.prop,
                                            a.Value.dateTime,
                                            a.Value.price,
                                            agent = a.Key
                                            });


    foreach(var x in final)
    {
      Console.WriteLine(x.id + " :: " + x.prop + " :: " + x.dateTime+ " :: " + x.price+ " :: " +x.agent);
    }
}

public class Detail
{
 public Detail(int i, string p, DateTime d, int pr, string a)
 {
   id= i;
   prop = p;
   dateTime = d;
   price = pr;
   agent = a;
 }
  public int id;
  public string prop;
  public DateTime dateTime;
  public int price;
  public string agent;

  public static  List<Detail> Create()
  {
    List<Detail> list = new List<Detail>();

    list.Add(new Detail(1, "xxxxxx", DateTime.Parse("17-06-2015"), 10000,"abc"));
    list.Add(new Detail(2, "xxyyyyy", DateTime.Parse("15-06-2015"), 10000,"abc"));
    list.Add(new Detail(3, "xxyyyyy", DateTime.Parse("16-06-2015"), 10000,"bcd"));
    list.Add(new Detail(4, "xxyyyyy", DateTime.Parse("15-06-2015"), 10000,"bcd"));
    list.Add(new Detail(5, "xxyyyyy", DateTime.Parse("17-06-2015"), 10000,"cde"));
    list.Add(new Detail(6, "xxyyyyy", DateTime.Parse("15-06-2015"), 10000,"cde"));

    return list;
   }
}