LINQ and joins?

Question

I have two tables Cars and Bookings and I wish to return the startdate and enddate from the Bookings table based on carId in the Cars table. here are my tables

Cars

ID          Make            Model           Size
----------- --------------- --------------- ---------------
1           BMW             5Series         Medium
2           Dacia           Duster          Large
3           Ford            Mustang         Large

Bookings

ID          CarID       StartDate  EndDate
----------- ----------- ---------- ----------
1           1           2017-02-01 2011-04-05
2           1           2017-05-11 2011-09-15
3           1           2017-09-09 2012-12-15
4           1           2017-07-12 2017-11-14
5           2           2017-01-16 2017-08-28

I was trying this but keep getting errors and am not sure how to proceed

string query3 = from c in db.Cars
                join b in db.Bookings on c.ID equals b.CarID
                select new { b.StartDate ,b.EndDate}.ToString();

Possible duplicate of [What is the syntax for an inner join in LINQ to SQL?](http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — itsme86, May 08 '17 at 23:26
If this hits a database, which LINQ provider/ORM do you use? If you are using EF or LINQ to SQL, you don't need to manually join, you can just dot (`.`) into collections when there are foreign keys declared. — Aluan Haddad, May 09 '17 at 00:00

score 1 · Accepted Answer · answered May 08 '17 at 23:29

1

I believe you want something like this:

var query3 = from c in db.Cars
join b in db.Bookings on c.ID equals b.CarID
select new { b.StartDate, b.EndDate};

var results = query3.ToList(); // ToList executes the query on the database
// results will be a List<a'> where a'> DateTime StartDate, DateTime EndDate

answered May 08 '17 at 23:29

Camilo Terevinto

31,141
6
88
120

Yes thanks you, An anonymous type is what was required ! – Ger Mc May 08 '17 at 23:31

score 1 · Answer 2 · answered May 08 '17 at 23:36

1

From what I understand, you want to get start date and end date according a given car id right?

Try this:

var carId = 1;
var result = db.Bookings
    .Where(booking => booking.CarID == carId)
    .Select(b => new { StartDate = b.StartDate, EndDate = b.EndDate })
    .ToList();

result object has a list of all start and end dates for car id 1. If no bookings are found, result will be empty.

answered May 08 '17 at 23:36

rmpt

606
1
4
24

Thanks, thats great, I dont like lambda though :P – Ger Mc May 08 '17 at 23:39
It's a boosted LINQ version. I find it much more clear, but no hard feelings :) – rmpt May 08 '17 at 23:43
It's Query Syntax vs. Method Syntax. I prefer Method Syntax, but find that Query Syntax is nicer when joins are involved. – Zac Faragher May 09 '17 at 00:17

LINQ and joins?

2 Answers2