Linq Nested Inner Joins

Question

I want to join the following Tables

1. B_Book[1st Table]
      -B_BID (Book ID)(PK)
      -B_Name
      -B_CategroyID (FK)
2. BI_BookInstance [2nd Table]
      -BI_IID(Instance ID)
      -BI_BID (FK)
      -BI_Price
3. BC_BookCategory [3rd Table]
      -BC_CategoryID (PK)
      -BC_CategoryName

First Join B_Book and BI_BookInstance then join the result of those both with BookCategory. (1st join)[B_BID equals BI_BID] (2nd nested join)[result of 1st join B_CategoryID equals BC_CategoryID]

Edit

SQL would be something like the following:

SELECT * FROM
   (SELECT * FROM B_Book b JOIN BI_BookInstance bi on b.B_BID = bi.BI_BID) as t1
   JOIN BC_BookCategoryID bc on bc.BC_CategoryID = t1.B_CategoryID

Answer 1

What matches your query in LINQ would be the following (and you'll notice the similarity with SQL). I've also included some examples on how to rename the fields returned, such as Price or CategoryName:

var results = from b in B_Book
    join bi in BI_BookInstance
    on b.B_BID equals bi.BI_BID
    join bc in BC_BookCategory
    on b.B_CategoryID equals bc.BC_CategoryID
    select new
      {
        // put in whatever fields you want returned here:
    b.B_BID, 
    b.B_CategoryID, 
    b.B_Name,
    bi.BI_BID,
    bi.BI_IID,
    Price = bi.BI_Price,
    bc.BC_CategoryID,
    CategoryName = bc.BC_CategoryName
      };

Answer 2

I have supposed inner joins (your FKs is not null), so i would like query like this:

    var ctx = new YourEntities();

    var query = from b in ctx.B_Book
                from bi in ctx.BI_BookInstance
                from bc in ctx.BC_BookCategory
                where b.B_BID == bi.BI_BID && b.B_CategoryID == bc.BC_CategoryID
                select new 
                {
                    BInstID = bi.BI_IID,
                    BName = b.B_Name,
                    BPrice = bi.BI_Price,
                    BCategory = bc.BC_CategoryName
                };

    foreach (var item in query)
    {
        Console.WriteLine(item.BInstID);
        Console.WriteLine(item.BName);
        Console.WriteLine(item.BPrice);
        Console.WriteLine(item.BCategory);
        Console.WriteLine("");
    } 

Answer 3

You can do this without explicitly using linq's join statement, provided that navigation properties are in place:

from b in ctx.B_Book
from bi in b.BookInstances
select new { b.Property1, bi.Property2, b.BookCategory.Name }