c# Entity framework and navigational property filtering.

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

Tables
--------
Hotels
Rooms
Beds.

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.

N
LVL 37
Neil RussellTechnical Development LeadAsked:
Who is Participating?
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.
0
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.
0
Neil RussellTechnical Development LeadAuthor Commented:
Model
The obvious extension of that would be what bed in what room in what hotel is "John Smith", guest Id = 992922
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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


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

Open in new window

0
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)
                           .Distinct().ToArray();

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.
0

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
0
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
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.