How to use Linq To Sql query to show the data in my case?

i have a trouble to write a query with Linq i explain better my case, i have a database with 2 tables as follow :

it is the first table ;

• Hotel

• HotelID (Nvarchar(10) - PK)

• HotelName (Nvarchar (200))

and this one is the second table ;

• Period

• PeriodID (Int (inc) - PK)

• _From (Datetime )
• _To (Datetime)
• HotelID(Nvarchar(10) - FK)

then开发者_如何学JAVA in the second Table (Period) there is the FK (HotelID) to connect the 2 tables; Happen sometime i have a HotelName that gets more periods(PeriodID) so my purpose is to show the data in an only one Row into a DataGrid, i show you an example as i want show the data in my DataGrid if there are more periods in the same HotelName:

| HotelName | From | To | From(2) | To(2) | From(3) | To(3) | From(4)| To(4) |

| Excelsior |12/5/10 |3/6/10 | 2/8/10 | 9/9/10 | 23/9/10 | 1/10/10| 2/11/11| 1/12/10|

so i ask do you have any idea/suggest about how to show the data in a DataGrid inside one Row using Linq To Sql ?

thanks so much for your attention .

Have a good time .

Cheers

This article explains working with hierarchical data binding: http://msdn.microsoft.com/en-us/library/aa478959.aspx

Then, create an object model which roughly maps to your database tables:

Hotel
 - ID
 - Name
 - Bookings
  - Booking 1 { From, To }
  - Booking 2 { From, To }
  - Booking n { From, To }

Your Linq should look something like this:

var hotels = _db.Hotel.Select();

foreach(var hotel in hotels)
    hotel.Bookings = _db.Period.Where(x => x.HotelId == hotel.HotelId).Select();