CODEWITHSUNDEEP
c#sql-serverentity-frameworklinqentity-framework-6
Michael Kirkegaard
Michael Kirkegaard

Reputation: 379

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

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();
    }
}

Upvotes: 5

Views: 13063

Answers (3)

LLI
LLI

Reputation: 56

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

Upvotes: 0

Aimnox
Aimnox

Reputation: 899

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();
    }

Upvotes: 6

David
David

Reputation: 218827

You're not checking the s variable in your predicate. You're basically asking the DB for any row where "any Reservation row in the DB matches the ID". Since there's always one that matches, all rows will evaluate to true in that predicate.

It sounds like you're looking for something more like this:

.Where(s => s.Reservation.Id == reservationId)

Upvotes: 0

Related Questions