c# Entity framework and navigational property filtering.

If I have a database with for example.....


A hotel has as part of its definition a navigational property in EF of Room
Each Room has a Navigational Property of Bed

Now theres lots of hotels and each has lots of rooms and some rooms have 1,2 or 3 beds.

How can I do something like

Hotel[] h = context.Hotels.where( c => c.Rooms.Beds =3)

I have tried many alternatives and looked at a gazillion web pages on the matter and am yet to find a solution.

LVL 37
Neil RussellTechnical Development LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Can you post a screenshot of the three tables as shown by the Entity Framework designer.
Najam UddinCommented:
var result = from hotel in Hotels
                     join room in Rooms
                       on hotel.HotelId equals Rooms.HotelId
                     join bed in Beds
                        on room.roomId equals bed.bedId
                  where room.number == 3

Open in new window

And as Fernando Soto said, this is just a guess, if you could provide ef diagram then we can give you correct code.
Neil RussellTechnical Development LeadAuthor Commented:
The obvious extension of that would be what bed in what room in what hotel is "John Smith", guest Id = 992922
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Najam UddinCommented:
var result = from hotel in entities.Hotel
join room in entities.Room on hotel.Id equals room.HotelId
where room.Number = 3
 select hotel 

Open in new window

var result =    context.Hotel.SelectMany(h => h.room.Where(r => r.Number == 3));

Open in new window

Fernando SotoRetiredCommented:
Hi Neilsr;

Because of the relationships in the database the query can be written as follows.
int sleepsHowMany = 3;
var hotels = dbContext.Beds.Where(b => b.Sleeps == sleepsHowMany)
                           .Select(b => b.Room.Hotel)

Open in new window

The above query returns an Array of Hotel objects which has the wanted bed count. If you like you can change the ToArray() to ToList() to get a type safe collection.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil RussellTechnical Development LeadAuthor Commented:
Both pointed me at what I needed. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.