Get the first element and the last element if it exists from the group
I have a script where my data looks like this.
Books --------------------------------- title | returnedDate Great Gatsby | 2015-05-04 Great Gatsby | 2015-03-22 Great Gatsby | 2015-01-11 Life of PI | 2015-04-04 Life of PI | 2015-04-02 Clean Code | 2015-06-05
I would like to return the very first and last book in each group (grouped by title) in a single linq statement. I know I can get the first or last item with a linq query like this.
var books = dbContext.Books
.GroupBy(b => b.title)
.Select(g => g.OrderDescending().FirstOrDefault());
How can I get the last item if it exists?
My end result will look like this:
Books --------------------------------- title | returnedDate Great Gatsby | 2015-05-04 Great Gatsby | 2015-01-11 Life of PI | 2015-04-04 Life of PI | 2015-04-02 Clean Code | 2015-06-05
+3
source to share
3 answers
With a little tinkering, I came up with this. Not sure how efficient this would be when dealing with a large data table.
[Test]
public void FirstLastLinq()
{
var books = new List<Book>
{
new Book { Title = "Great Gatsby", Returned=new DateTime(2015,04,03) },
new Book { Title = "Great Gatsby", Returned=new DateTime(2015,04,02) },
new Book { Title = "Great Gatsby", Returned=new DateTime(2015,04,01) },
new Book { Title = "Life of PI", Returned=new DateTime(2015,03,05) },
new Book { Title = "Life of PI", Returned=new DateTime(2015,03,04) },
new Book { Title = "Clean Code", Returned=new DateTime(2015,02,02) },
};
var newBooks = books.GroupBy(b => b.Title).SelectMany(g => g.OrderByDescending(b => b.Returned)
.Where(b1 => b1.Returned == g.Min(b2 => b2.Returned) ||
(b1.Returned == g.Max(b3 => b3.Returned) && g.Min(b4 => b4.Returned) != g.Max(b5 => b5.Returned))));
Assert.IsNotNull(newBooks);
}
private class Book
{
public string Title { get; set; }
public DateTime Returned { get; set; }
}
0
source to share
var books = dbContext.Books
.GroupBy(b => b.title)
.Select(g=>new {
Title=g.Key,
First=g.OrderByDescending(x=>x).FirstOrDefault(),
Last=g.OrderBy(x=>x).FirstOrDefault()
});
Results:
title | First | Last
Great Gatsby | 2015-05-04 | 2015-01-11
Life of PI | 2015-04-04 | 2015-04-02
Clean Code | 2015-06-05 | 2015-06-05
If you really want this, as you asked, it gets a little tricky:
var books = dbContext.Books
.GroupBy(b => b.title)
.Select(g=>new {
title=g.Key,
returnedDate=g.OrderByDescending(x=>x).FirstOrDefault()
}).Concat(
dbContext.Books
.GroupBy(b => b.title)
.Where(g=>g.Count()>1)
.Select(g=>new {
title=g.Key,
returnedDate=g.OrderBy(x=>x).FirstOrDefault()
})
).OrderBy(c=>c.title).ThenDescendingBy(c=>c.returnedDate);
Ugh. Probably the best way, but it came to mind first.
+3
source to share
This is possible by getting the first and last return date, and then returning books that match the return dates:
from b in dbContext.Books
group b by b.title into bg
let first = bg.OrderByDescending (b => b.returnedDate).FirstOrDefault().returnedDate
let last = bg.OrderBy (b => b.returnedDate).FirstOrDefault().returnedDate
from b in bg
where b.returnedDate == first || b.returnedDate == last
orderby b.title, b.returnedDate
select b
+2
source to share