How I can retrieve all records with 1-2 queries using regular SQL and Linq-To-Entities?

I uploaded simplified SQL ERD on Picasa. I tried to do it but I manage to it with more than 2 queries.

I need to retrieve all records from table D with specific E_Id of table E and specific TypeId of table A

UPDATED: I need also that all records from B that mapped to TypeId will be alse retrieved even if not all of them are mapped in table C (some kind of LEFT-RIGHT JOIN)

I need to do it a开发者_开发问答t most 2 queries and then convert it to Linq-To-Entites statement If it can be done with 1 query I prefer with 1 query

Thank you

---

SQL query:

SELECT D.*
FROM D 
INNER JOIN C ON C.Id = D.C_Id
INNER JOIN B ON B.Id = C.B_Id
INNER JOIN A ON A.Id = B.A_Id
WHERE D.E_Id = @eId AND A.TypeId = @typeId

SELECT B.*
FROM B
INNER JOIN A ON A.Id = B.A_Id
WHERE A.TypeId = @typeId

Linq directly (if navigation properties don't exists):

var query = from d in context.D
            join c in context.C on c.Id equals d.cId
            join b in context.B on b.Id equals c.bId
            join a in context.A on a.Id equals b.aId
            where d.eId == eId && a.TypeId == typeId
            select d;

var query2 = from b in context.B
             join a in context.A on a.Id equals b.aId
             where a.TypeId == typeId
             select b;

Linq if navigation properties are correctly set up:

var query = from d in context.D
            where d.E.Id == eId &&
                  d.C.B.A.TypeId == typeId
            select d;  

var query2 = from b in context.B
             where b.A.TypeId == typeId
             select b; 

---

Any reason why the below won't work for you?

SELECT D.*
FROM D
INNER JOIN C
ON C.Id = D.C_Id
INNER JOIN B
ON B.Id = C.B_Id
INNER JOIN A
ON A.Id = B.A_Id
WHERE D.E_id = <The E_Id> AND A.Typeid = <The Typeid>