EF Query Help. Difficult query with interhited types and self referencing.

Question

I'm still new to EF, and I have figured out some basic queries. I'm stumped on one of the more difficult things I am trying to acheive.

I have 4 entities User, Tenant, Building, Apartment

Tenant inherits from User

At this point, a user who is not a Tenant can be thought of as a Landlord.

User (Landlord) has one to many Buildings

Buildings have one to many Apartments

Apartment has one to many Tenants (one active at any given time)

I am trying to figure out how to create an EF query that will give me the list of Tenants for a given User (Landlord).

The SQL that gives me what I want is:

SELECT u2.User_ID AS TenantUser_ID, u2.UserName  
FROM Users u  
LEFT JOIN rt_UserBuilding ub ON u.User_ID = ub.User_ID  
LEFT JOIN Buildings b ON ub.Building_ID = b.Building_ID  
LEFT JOIN Apartments a ON a.Building_ID  = b.Building_ID  
LEFT JOIN Tenants t ON a.Apartment_ID = t.Apartment_ID  
LEFT JOIN Users u2 ON t.User_ID = u2.User_ID  
WHERE u.User_ID = 1 AND t.User_ID IS NOT NULL 

Answer 1

Assuming your Building class has property

User Landlord

the simplest way is to do this:

context.Tenants.Where(tenant => tenant.
                              Apartment.
                              Building.
                              Landlord.Id == yourLandlord.Id
                   ).ToList();