CODEWITHSUNDEEP
visual-studio-2010linqentity-frameworklinq-to-entities
IT ppl
IT ppl

Reputation: 2647

LINQ query not working properly

LINQ query not working properly

I want to do as following, I want to access the visitor records from visitor table on certain conditions

I want to get list of last 5 distinct visitor which have visited the place

I am using the follwing query to get the result, but when it comes to distinct() it causes the problem, distinct() destroys the existing order.

var result = ObjPlace.visitorlist
.where(visitpalce => visitplace.name == Placename)
.OrderByDescending(visitor => visitor.visitdate)
.Select(x => x.visitorID)
.Distinct()
.take(5);

when i removes distinct() it works perfectly...

how can I achieve the same?

Upvotes: 0

Views: 274

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236308

 var query = from v in ObjPlace.visitorlist
             where v.name == Placename
             group v by v.visitorID into g
             let lastVisit = g.Max(x => x.visitdate)
             orderby lastVisit descending
             select g.First(v => v.visitdate == lastVisit);

This will return distinct list of entities oredered by last visit. Then just take top five of them:

query.Take(5)

You need grouping here, because one visitor could have several visits in top 5 records. So, first you are filtering your list by placename. Then group all visits by visitor. I.e. you have groups of visits. In each group you define last visit time of visitor, and then just select first visit entity in group, which has time equal to last visitdate of that visitor.

Upvotes: 2

Joanna Derks
Joanna Derks

Reputation: 4063

What is the distinct data that you are trying to get ?

If your object implements IEquatable then all the fields will be compared - i.e. if the dates are different in two objects they will be distinct, but also if any other fields are different the objects will be considered different.

Can you post a sample of your input data, the output you get and the expected output ?

Upvotes: 0

akatakritos
akatakritos

Reputation: 9858

You might try changing the order:

var result = ObjPlace.visitorlist
               .Where(visitpalce => visitplace.name == Placename)
               .Distinct()
               .OrderByDescending(visitor => visitor.visitdate)
               .Select(x => x.visitorID)
               .Take(5);

Upvotes: 0

Related Questions