score:0

Accepted answer

with a bit of fiddling i came up with this. not sure how efficient this will be when dealing with a large table of data.

        [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; }
    }

score:2

it's possible by getting the first and last return date and then returning the books of which the return dates are equal to these:

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

score:3

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 it like you asked, then it becomes a bit more difficult:

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

yuck. probably a better way, but first that came to mind.


Related Query

More Query from same tag