Linq to Entities .net4 EF

I have Bikes, Seats and BikeSeats tables. BikeSeats is mapper table, composite key from BikeID and SeatID, no other columns in it.

All of this is mapped in EF, .net 4 in only Bike, Seat (and bike.Seats and seat.Bikes)

I want to get all seat types that red color bikes would have:

    select distinct s.* 
    from seats s
          left join bikeSeats bs on s.seatID = bs.seatId
    where bs.bikeId in (select distinct bikeId from bikes where color=red)

using Linq to Entities

The really hard part for me is that the BikeSeats does not exist, it's been mapped to basically bike.Seats and/or seat.Bikes (which EF does because the table has only 2 ids)

Any help to convert this query would be so so appreciated

Thank开发者_开发百科s --MB

Elementary, my dear Boisson:

var seatsOnRedBikes = ctx.Seats.Where(s => s.Bikes.Any(b => b.Color == "Red"));

First have to declare an instance of your entity i don't know what your entity name is let's for example say it's BikeSeatEntity

List<Seat> GetSeats()
        {
            using (BikeSeatEntity bsEntity = new BikeSeatEntity())
            {
                var seats = from s in bsEntity.Seats.Include("Bikes").Where(s => s.Bike.Color = "Red")
                            select s;
                List<Seat> seatsList = seats.ToList();
                return seatsList;
            }
            return null;
        }

if this don't work for you please describe more about your entities.