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();
}
}
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();
}
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow