CODEWITHSUNDEEP
entity-framework
josephzigler
josephzigler

Reputation: 285

Grabbing certain data out of database

I am struggling grabbing some data out of my database

My database design is like this: I have a hotel that has a hotelchainid, a building that has a hotelid, a floor that has a buildingid and a room that has a floorid.

I'm trying to grab all the rooms from a certain hotel out of the database

Databasehandler.cs method:

        public static List<Room> GetAllRoomsByHotel(string hotelname)
        {
        HotelDbContext context = new HotelDbContext();

        var hotel = context.Hotels.Where(h => h.Name == hotelname).Single();
        var buildings = context.Buildings.Where(b => b.Hotel.HotelID == hotel.HotelID).ToList();
        var floors = context.Floors.Where(f => f.Building.BuildingID == buildings.BuildingID).ToList();
        var rooms = context.Rooms.Where(r => r.Floor.FloorID == floors.FloorID).ToList();

        return rooms;
        }

I was thinking about adding the found objects to a List and looping through that list to get the id's and comparing them afterwards but i'm not sure how to accomplish that and if that would work.

Thank you for your time!

Upvotes: 1

Views: 46

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

Once you've got all navigation properties in place, you can achieve what you want by

context.Hotels.Where(h => h.Name == hotelname)
              .SelectMany(h => h.Buildings)
              .SelectMany(b => b.Floors)
              .SelectMany(f => f.Rooms)

Which will give you a collection of rooms belonging to the hotel specified by hotelname.

Often, esp. with SelectMany, query syntax is easier ...

from h in context.Hotels where h.Name == hotelName
from b in h.Buildings
from f in b.Floors
from r in f.Rooms
select new
{
    Hotel = h.Name,
    Building = b.Name,
    Floor = f.Number,
    Room = r.Number
}

... because, as you see, it's easier to include parent data in the end result if that's desired.

Upvotes: 1

Related Questions