Get records via foreign key in another table in Entity Framework 6

Question

Get records via foreign key in another table in Entity Framework 6

c# entity-framework entity-framework-6 linq sql-server

Question

I need to get all seats attached to a specific reservation.

I have these classes:

public class Seat
{
    public Guid Id { get; set; }
    public string RowNumber { get; set; }
    public int SeatNumber { get; set; }
}

public class ReservationSeat
{
    public Guid Id { get; set; }
    public Guid ReservationId { get; set; }
    public Guid SeatId { get; set; }

    public Reservation Reservation { get; set; }
    public Seat Seat { get; set; }
}

I have tried with this linq to entities statement but with no luck. It seems to return all the seats from the seats table.

public static List<Seat> GetSeatsForReservation(Guid reservationId)
{
    using (var db = new EntityContext())
    {
        return db.Seats.Where(s => db.ReservationSeat
                                     .Select(rs => rs.ReservationId)
                                     .Contains(reservationId)).ToList();
    }
}

Michael Kirkegaard

Popular Answer

In EF Code-First ForeignKey can be applied to properties of an class and the default Code-First convention for ForeignKey relationship expects foreign key property name match with primary key property. Thus, you can create you model as bellow:

public class Seat
{
    public Guid Id { get; set; }

    public string RowNumber { get; set; }

    public int SeatNumber { get; set; }

    public virtual ICollection<Reservation> Reservations { get; set; }
}

public class Reservation
{
    public Guid Id { get; set; }

    public virtual ICollection<Seat> Seats { get; set; }
}

public static List<Seat> GetSeatsForReservation(Guid reservationId)
{
  List<Seat> result = null;
 using (var db = new EntityContext())
  {
     result = db.Seats.Where(
            s => s.Reservations.Id == reservationId).ToList();
    }
   return result ;
 }`

Note: 1. this is many to many relationship, you can change it to 1 to many 2. navigation property must be declared as public, virtual

LLI

View more on Stack Overflow

Aimnox · Accepted Answer · 5/13/2016 11:07:59 AM

Accepted Answer

Try:

public static List<Seat> GetSeatsForReservation(Guid reservationId)
    {
        var db= new  EntityContext();
        return (from s in db.ReservationSeat
                where s.ReservationID==Guid
                select s.seat).ToList();
    }

Aimnox

Get records via foreign key in another table in Entity Framework 6

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related