Using IN subquery in Entity Framework using Linq

Question

I have the following tables in my database:

- Reservation
- TrainStation
- Train
- Traveller
- Reservation_Traveller
- TrainSeats: Attributes are: Train_Id, Seat, Traveller_Id

I want to find the TrainSeats rows of a particular Reservation

I have a Reservation object that contains an ICollection<Traveller> property containing the travellers of which I want to remove their seats from the TrainSeats table. I fetched the reservation object from the database like this:

var reservation = db.Reservation.Where(r => r.Id == id).FirstOrDefault();

So I want to do something like this:

db.TrainSeats.Where(ts => ts.Traveller_Id IN reservation.Travellers)

ocuenca · Accepted Answer · 2017-05-16 18:38:31Z

1

First select the travelers id:

var ids=reservation.Travellers.Select(e=>e.Id);

Then use Contains extension method which is translated into IN in sql:

var query=db.TrainSeats.Where(ts => ids.Contains(ts.Traveller_Id));

I guess if you use the FK property and navigation properties you can do it in one query:

var query= db.Travellers.Where(e=>e.ReservationId==id).SelectMany(t=>t.TrainSeats);

edited May 16, 2017 at 18:38

answered May 16, 2017 at 18:28

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ferre12 Over a year ago

This is causing a NotSupportedException, the message is: {"Unable to create a constant value of type 'Traveller'. Only primitive types or enumeration types are supported in this context."}

ocuenca Over a year ago

Did you select the ids? you need to select an scalar property to use Contains as I show in my answer, in your case it would be traveler ids. That error is because you are using Contains comparing with entities instead of scalar value

Ferre12 Over a year ago

I got it working, it was another condition that caused the exception. Thanks.

Collectives™ on Stack Overflow

Using IN subquery in Entity Framework using Linq

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related