Include() is not working as LEFT JOIN (Entity Framework 6)

Question

Thank you in advance for your help. I'm a little confused about a situation that occurred to me when using the include() method Entity Framework 6. As I understand it, the include method works as LEFT JOIN when the enclosed object is NULL and as OUTER JOIN when the object has match.

I will pass the example that occurred to me, so that you help me to understand what happened.

I have the following models to my tables:

public class Booking
    {
        [Key]
        public int ID{ get; set; }

        public string Description{ get; set; }

        public decimal Amount{ get; set; }

        public decimal AmoutPaid{ get; set; }

        public DateTime? Checkin { get; set; }

        public DateTime? Checkout { get; set; }

        [ForeignKey("SourceBooking ")]
        public int SourceBookingId { get; set; }

        public SourceBooking SourceBooking { get; set; }
    }





public class SourceBooking
{
    [Key]
    public int ID{ get; set; }

    public string Name{ get; set; }

    public decimal CommissionFee{ get; set; }
}

Below is the DbContext:

 public class BookingContext:DbContext
{
    public BookingContext():base("bookingConnection")
    {
    }

    public DbSet<Booking> Bookings{ get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<SourceBooking>().ToTable("sourcebookings", "public");

        modelBuilder.Entity<Booking>().ToTable("bookings", "public");

    }
}

The situation that is not clear has occurred in the use of the following block of code:

var db = new BookingContext ();

var bookings = db.Bookings.Include (b => b.SourceBooking);

I tended because in the results the records did not come whose SourceBooking is NULL, in which case a LEFT JOIN would be made.

Could someone explain this to me, and give me a possible solution to this situation?

Thanks.

Answer 1

EF generates LEFT OUTER JOIN for optional relationships and INNER JOIN for required relationships.

By using non nullable int type here

public int SourceBookingId { get; set; }

you are telling EF that the relationship is required, i.e. the column value cannot be NULL and there must be always a matching record in SourceBooking table. Hence it generates INNER JOIN.

if that's not the case, simply change the FK property type to nullable

public int? SourceBookingId { get; set; }