Converting SQL Server query into linq

Question

The query I'm trying to translate is:

SELECT 
    MIN(BookCopies.id) as id, Books.title, Authors.name, Publishers.name
FROM 
    dbo.BookCopies
INNER JOIN 
    Books ON BookCopies.bookId = Books.id
INNER JOIN 
    Authors ON Books.authorId = Authors.id
INNER JOIN 
    Publishers ON BookCopies.publisherId = Publishers.id
WHERE 
    BookCopies.sold = 0 
GROUP BY 
    Books.title, Authors.name, Publishers.name;

I'm trying to solve this problem for 3 hours and I can't... :/

The Linq code:

var query = (from bc in db.BookCopies
            join b in db.Books on bc.bookId equals b.id
            join a in db.Authors on b.authorId equals a.id
            join p in db.Publishers on bc.publisherId equals p.id
            where (bc.sold == false && bc.price != null && bc.price != 0)
            group bc by new {b.title, author = a.name, publisher = p.name} into gr
            select new {gr.Key.title, gr.Key.author, gr.Key.publisher});

But it's not correct.

Yes, this: var query = (from bc in db.BookCopies join b in db.Books on bc.bookId equals b.id join a in db.Authors on b.authorId equals a.id join p in db.Publishers on bc.publisherId equals p.id where (bc.sold == false && bc.price != null && bc.price != 0) group bc by new {b.title, a.name} into gr select new {bc.id, gr.Key.title, gr.Key.name}).Min(); — kryski
– kryski, Commented Sep 26, 2013 at 10:18

Sergey Berezovskiy · Accepted Answer · 2013-09-26 10:47:05Z

2

Trick here is selecting result of joining into intermediate data type (row) which will be grouped later:

from bc in db.BookCopies
join b in db.Books on bc.bookId equals b.id
join a in db.Authors on b.authorId equals a.id
join p in db.Publishers on bc.publisherId equals p.id
where bc.sold == 0
select new { bc.id, b.title, author = a.name, publisher = p.name } into row
group row by new { row.title, row.author, row.publisher } into g
select new {
   id = g.Min(x => x.id),
   g.Key.title,
   g.Key.author,
   g.Key.publisher
}

This produces exactly same query you are looking for.

edited Sep 26, 2013 at 10:47

answered Sep 26, 2013 at 10:33

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting SQL Server query into linq

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related