write inline select query with LINQ to SQl

Question

Pardon if title is improper..

How to write below sql query with linq to sql without dividing it in multiple parts?

select BookName,
       Author,
       TotalCopies=(select COUNT(*) 
                    from tbEHBookCopies c 
                    where c.BookID=b.BookID) 
from dbo.tbEHBooks b

Answer 1

I'm not sure what you mean by multiple parts. If you mean that it should be on one single query then you could do something like:

var allBooks = db.tbEHBooks.Select(x => new 
{
    x.BookName,
    x.Author,
    x.TotalCopies = db.tbEHBookCopies.Select(y => x.BookID == y.BookID).Count()
});

Answer 2

Using query syntax joining and grouping. Linq-sql is just a little different when it comes to grouping. The nice thing about this syntax is that it is easy to join in more tables. And it looks more like a SQL query only the "from" comes first, and the "select" comes last.

var bookQry = from b in db.tbEHBooks
              join c in db.tbEHBookCopies on b.BookID equals c.BookID
              group b by b into books
              select new { books.Key.BookName,
                           books.Key.Author,
                           TotalCopies = books.Count() };