write inline select query with LINQ to SQl

问题:

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

回答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()
});

回答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() };