Cannot select most recent ticket from a collection of tickets

Question

I have a list of advanced ticket objects that has a structure like this:

AdvancedTicket
-Id
-BasicTicket
-CreatedDate

BasicTicket
-Id

And when I query my advanced ticket table I get this:

1, BasicTicketId1, 10/11/12
2, BasicTicketId2, 10/11/12
3, BasicTicketId1, 10/12/13

...

I want to be able to say "Give me all of the AdvancedTickets but only show me the most recent one for each advanced ticket".

I have this code that is not working:

from item in allAdvancedTickets
group item by item.BasicTicket.Id
into basicTicket
let d = basicTicket.OrderByDescending(c => c.CreatedDate)
orderby d descending
select basicTicket;

I am running into an error that says at least one item needs to implement iComparable.

I think this query is wrong to begin with but I have never used this function in linq before and I would appreciate some help.

What data type is item.BasicTicket.Id? There needs to be a comparer for that type. — jcwrequests, Oct 12 '13 at 00:14

Ahmad Mageed · Accepted Answer · 2013-10-12T02:47:29.077

2

You should sort the items by CreatedDate first, group them, then select the first item in each group. That item would be the most recent one since you've already sorted them earlier.

That sequence would yield this query:

var query = from item in allAdvancedTickets
            orderby item.CreatedDate descending
            group item by item.BasicTicket.Id
            into basicTickets
            select basicTickets.First();

Per the comments, if you're using NHibernate there's a bug when it comes to grouping. You might be able to work-around it by using two queries in the following manner:

var idDateQuery = from item in _session.GetAllAdvacnedTickets()
                  orderby item.BasicTicket.Id, item.CreatedDate descending
                  select item;

var query = from item in _session.GetAllAdvacnedTickets()
            let top = idDateQuery.First(o => o.BasicTicket.Id == item.BasicTicket.Id)
            where item.Id == top.Id
            select item;

Note that I haven't tested this against NHibernate. You might need to drop to the SQL level instead if this work-around isn't possible.

edited Oct 12 '13 at 02:47

answered Oct 12 '13 at 00:20

Ahmad Mageed

94,561
19
163
174

That totally worked! This helps me so much and it makes sense why it was having trouble before :) – segFault Oct 12 '13 at 00:26
When I inline by call to get allAdvacnedTickets I get an error saying Column 'AdvancedTickets.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. If I say _session.GetAllAdvacnedTickets.ToList() it works just fine. – segFault Oct 12 '13 at 00:36
@segFault what does `GetAllAdvancedTickets` do? That error typically relates to some SQL query that groups by one column and doesn't group or aggregate other columns that are being selected. Take a look at these two questions and read their answers and comments for more detail: [this post](http://stackoverflow.com/questions/16814343/), and [another](http://stackoverflow.com/questions/13999817/). – Ahmad Mageed Oct 12 '13 at 00:52
its just _session.Query() – segFault Oct 12 '13 at 00:55
@segFault that's odd. Are you using NHibernate by any chance? If so, this appears to be a [bug in NHibernate](https://nhibernate.jira.com/browse/NH-3027) that has been around for awhile. It also appears to happen with HQL. You might need to drop to the SQL level for such queries. I do have a work-around idea, but I'm not testing against NHibernate, so you can try it out and see if it helps. The `ToList` approach works but it's not ideal since you're pulling all the records from the database down. I'll update my post shortly with the work-around idea. – Ahmad Mageed Oct 12 '13 at 02:13
Ok I am going to test it and I will let you know. Thanks for your help – segFault Oct 12 '13 at 03:40

Cannot select most recent ticket from a collection of tickets

1 Answers1