Using Include() method EF Code First Approach

Question

I'm new to EF code first approach,

The assignment I'm trying to cover here is to include master and child records,

My problem statement is unable to include child inside the master model, rather master can be included in the child records.

ie..,

Below expression is possible, but the master is repated inside the child records

var orderItems = _context.OrderItems.Include(o => o.Order).ToList();

where as the below code throws exception,

var orders = _context.Orders.Include(o => o.OrderItems).ToList();

Exception :

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: A specified Include path is not valid. The EntityType 'Dsms.Data.EF.OrderModel' does not declare a navigation property with the name 'OrderItems'.

Please advise

//Model classes
public abstract class BaseModel : IDisposable
{
    public BaseModel()
    {
        DrivingSchoolId = Guid.NewGuid();
        UserId = Guid.NewGuid();
        IsActive = true;
        CreatedOn = DateTime.Now;
        ModifiedOn = DateTime.Now;
    }

    [Display(Name = "Driving School")]
    public Guid DrivingSchoolId { get; set; }

    [Display(Name = "User")]
    public Guid UserId { get; set; }

    [Display(Name = "Active")]
    public bool IsActive { get; set; }

    [Display(Name = "Created By")]
    public Guid CreatedBy { get; set; }

    [Display(Name = "Created On")]
    public DateTime CreatedOn { get; set; }

    [Display(Name = "Modified By")]
    public Guid ModifiedBy { get; set; }

    [Display(Name = "Modified On")]
    public DateTime ModifiedOn { get; set; }

    public void Dispose()
    {
    }
}

public class OrderModel : BaseModel
{
    public OrderModel()
    {
        OrderId = Guid.NewGuid();
    }

    [Key]
    [Column(Order = 1)]
    public Guid OrderId { get; set; }

    [Display(Name = "Customer")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Customer'")]
    public Guid CustomerId { get; set; }

    [Display(Name = "Bill Number")]
    [DataType(DataType.Text)]
    //[Required(ErrorMessage = "Please fill the 'Bill Number'")]
    [MaxLength(25, ErrorMessage = "Maximum character lenght of 'Bill Number' is 25.")]
    public string BillNumber { get; set; }

    [Display(Name = "Age Proof")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Age Proof'")]
    public Guid AgeProofGeneralItemId { get; set; }

    [Display(Name = "Address Proof")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Address Proof'")]
    public Guid AddressProofGeneralItemId { get; set; }

    [Display(Name = "Status")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Status'")]
    public Guid StatusGeneralItemId { get; set; }

    [Display(Name = "Training Instructor")]
    [DataType(DataType.Text)]
    public Guid TrainingInstructorId { get; set; }

    [Display(Name = "Training Vehicle")]
    [DataType(DataType.Text)]
    public Guid TrainingVehiclesGeneralItemId { get; set; }

    [Display(Name = "Notes")]
    [DataType(DataType.MultilineText)]
    [MaxLength(500, ErrorMessage = "Maximum character lenght of 'Notes' is 500.")]
    public string Notes { get; set; }

    [Display(Name = "Total")]
    [DisplayFormat(DataFormatString = "{0:n2}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please fill the 'Total'")]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Total Amount'")]
    public decimal Total { get; set; }

    [Display(Name = "Discount")]
    [DisplayFormat(DataFormatString = "{0:n2}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Text)]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Discount Amount'")]
    public decimal Discount { get; set; }

    //EF Navigation properties starts   
    public virtual CustomerModel Customer { get; set; }
    public virtual DrivingSchoolModel DrivingSchool { get; set; }
    public virtual GeneralItemModel AgeProofGeneralItem { get; set; }
    public virtual ICollection<GeneralItemModel> AgeProofGeneralItems { get; set; }
    public virtual GeneralItemModel AddressProofGeneralItem { get; set; }
    public virtual ICollection<GeneralItemModel> AddressProofGeneralItems { get; set; }
    public virtual GeneralItemModel StatusGeneralItem { get; set; }
    public virtual ICollection<GeneralItemModel> StatusGeneralItems { get; set; }
    public virtual InstructorModel TrainingInstructor { get; set; }
    public virtual ICollection<InstructorModel> TrainingInstructors { get; set; }
    public virtual GeneralItemModel TrainingVehicleGeneralItem { get; set; }
    public virtual ICollection<GeneralItemModel> TrainingVehicleGeneralItems { get; set; }
    public virtual ICollection<OrderItemModel> OrderItems { get; set; }
    public virtual ICollection<OrderTransactionModel> OrderTransactions { get; set; }
}

public class OrderItemModel : BaseModel
{
    public OrderItemModel()
    {
        OrderItemId = Guid.NewGuid();
        NoOfTrainingDays = 1;
    }

    [Key]
    [Column(Order = 1)]
    public Guid OrderItemId { get; set; }

    [Display(Name = "Order")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Order'")]
    public Guid OrderId { get; set; }

    [Display(Name = "Service")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Service'")]
    public Guid ServiceId { get; set; }

    [Display(Name = "Training Days")]
    [DataType(DataType.Text)]
    public int NoOfTrainingDays { get; set; }

    [Display(Name = "Service Cost")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please fill the 'Service Cost'")]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Service Cost'")]
    public decimal ServiceCost { get; set; }

    [Display(Name = "Total Service Cost")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please fill the 'Total Service Cost'")]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Service Cost'")]
    public decimal TotalServiceCost { get; set; }

    //EF Navigation properties starts   
    public virtual OrderModel Order { get; set; }
    public virtual ServiceModel Service { get; set; }       
    public virtual ICollection<OrderFormModel> OrderForms { get; set; }
}

public class OrderTransactionModel : BaseModel
{
    public OrderTransactionModel()
    {
        OrderTransactionId = Guid.NewGuid();
    }

    [Key]
    [Column(Order = 1)]
    public Guid OrderTransactionId { get; set; }

    [Display(Name = "Order")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Order'")]
    public Guid OrderId { get; set; }

    [Display(Name = "Payment Type")]
    [DisplayFormat(DataFormatString = "{0:n2}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please select the 'Payment Type'")]
    public Guid PaymentTypeGeneralItemId { get; set; }

    [Display(Name = "Credited Amount")]
    [DisplayFormat(DataFormatString = "{0:n2}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please fill the 'Credited Amount'")]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Credited Amount'")]
    public decimal Credited { get; set; }

    [Display(Name = "Debited Amount")]
    [DataType(DataType.Text)]
    [Required(ErrorMessage = "Please fill the 'Debited Amount'")]
    [RegularExpression(@"\d+(\.\d{1,2})?", ErrorMessage = "Please enter valid 'Debited Amount'")]
    public decimal Debited { get; set; }

    [Display(Name = "Notes")]
    [DataType(DataType.MultilineText)]
    [MaxLength(500, ErrorMessage = "Maximum character lenght of 'Notes' is 500.")]
    public string Notes { get; set; }

    //EF Navigation properties starts   
    public virtual ICollection<OrderModel> Orders { get; set; }
    public virtual GeneralItemModel PaymentTypeGeneralItem { get; set; }
    public virtual ICollection<GeneralItemModel> PaymentTypeGeneralItems { get; set; }
}

//EF's DB Config's

public class OrderConfiguration : EntityTypeConfiguration<OrderModel>
{
    public OrderConfiguration()
    {
        this.ToTable("Orders", Constants.Database.DEFAULT_DB_SCHEMA);

        this.HasKey(o => o.OrderId);

        this.HasRequired(o => o.Customer).WithMany().HasForeignKey(o => o.CustomerId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.DrivingSchool).WithMany().HasForeignKey(o => o.DrivingSchoolId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.AgeProofGeneralItem).WithMany().HasForeignKey(o => o.AgeProofGeneralItemId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.AddressProofGeneralItem).WithMany().HasForeignKey(o => o.AddressProofGeneralItemId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.StatusGeneralItem).WithMany().HasForeignKey(o => o.StatusGeneralItemId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.TrainingInstructor).WithMany().HasForeignKey(o => o.TrainingInstructorId).WillCascadeOnDelete(false);
        this.HasRequired(o => o.TrainingVehicleGeneralItem).WithMany().HasForeignKey(o => o.TrainingVehiclesGeneralItemId).WillCascadeOnDelete(false);

        this.Property(o => o.CustomerId).IsRequired().HasColumnOrder(2);
        this.Property(o => o.DrivingSchoolId).IsRequired().HasColumnOrder(3);
        this.Property(o => o.BillNumber).HasMaxLength(25).IsRequired().HasColumnOrder(4);
        this.Property(o => o.AgeProofGeneralItemId).IsRequired().HasColumnOrder(5);
        this.Property(o => o.AddressProofGeneralItemId).IsRequired().HasColumnOrder(6);
        this.Property(o => o.StatusGeneralItemId).IsRequired().HasColumnOrder(7);
        this.Property(o => o.TrainingInstructorId).IsOptional().HasColumnOrder(8);
        this.Property(o => o.TrainingVehiclesGeneralItemId).IsOptional().HasColumnOrder(9);          
        this.Property(o => o.Total).IsRequired().HasColumnOrder(10);
        this.Property(o => o.Discount).IsOptional().HasColumnOrder(11);
        this.Property(o => o.Notes).HasMaxLength(500).IsOptional().HasColumnOrder(12);
        this.Property(o => o.IsActive).IsRequired().HasColumnOrder(13);
        this.Property(o => o.CreatedBy).IsRequired().HasColumnOrder(14);
        this.Property(o => o.CreatedOn).IsRequired().HasColumnOrder(15);
        this.Property(o => o.ModifiedBy).IsOptional().HasColumnOrder(16);
        this.Property(o => o.ModifiedOn).IsOptional().HasColumnOrder(17);

        this.Ignore(o => o.UserId);
        this.Ignore(o => o.AgeProofGeneralItems);
        this.Ignore(o => o.AddressProofGeneralItems);
        this.Ignore(o => o.StatusGeneralItems);
        this.Ignore(o => o.TrainingInstructors);
        this.Ignore(o => o.TrainingVehicleGeneralItems);
        this.Ignore(o => o.OrderItems);
        this.Ignore(o => o.OrderTransactions);

    }
}

 public class OrderItemConfiguration : EntityTypeConfiguration<OrderItemModel>
{
    public OrderItemConfiguration()
    {
        this.ToTable("OrderItems", Constants.Database.DEFAULT_DB_SCHEMA);

        this.HasKey(oi => oi.OrderItemId);

        this.HasRequired(oi => oi.Order).WithMany().HasForeignKey(oi => oi.OrderId).WillCascadeOnDelete(false);
        this.HasRequired(oi => oi.Service).WithMany().HasForeignKey(oi => oi.ServiceId).WillCascadeOnDelete(false);            

        this.Property(oi => oi.OrderId).IsRequired().HasColumnOrder(2);
        this.Property(oi => oi.ServiceId).IsRequired().HasColumnOrder(3);            
        this.Property(oi => oi.NoOfTrainingDays).IsOptional().HasColumnOrder(4);
        this.Property(oi => oi.ServiceCost).IsRequired().HasColumnOrder(5);
        this.Property(oi => oi.TotalServiceCost).IsRequired().HasColumnOrder(6);
        this.Property(oi => oi.IsActive).IsRequired().HasColumnOrder(7);
        this.Property(oi => oi.CreatedBy).IsRequired().HasColumnOrder(8);
        this.Property(oi => oi.CreatedOn).IsRequired().HasColumnOrder(9);
        this.Property(oi => oi.ModifiedBy).IsOptional().HasColumnOrder(10);
        this.Property(oi => oi.ModifiedOn).IsOptional().HasColumnOrder(11);

        this.Ignore(oi => oi.DrivingSchoolId);
        this.Ignore(oi => oi.UserId);
        this.Ignore(oi => oi.OrderForms);            
    }
}

public class OrderTransactionConfiguration : EntityTypeConfiguration<OrderTransactionModel>
{
    public OrderTransactionConfiguration()
    {
        this.ToTable("OrderTransactions",Constants.Database.DEFAULT_DB_SCHEMA);

        this.HasKey(ot => ot.OrderTransactionId);

        this.HasRequired(ot => ot.Orders).WithMany().HasForeignKey(ot => ot.OrderId).WillCascadeOnDelete(false);
        this.HasRequired(ot => ot.PaymentTypeGeneralItem).WithMany().HasForeignKey(ot => ot.PaymentTypeGeneralItemId).WillCascadeOnDelete(false);

        this.Property(ot => ot.OrderId).IsRequired().HasColumnOrder(2);
        this.Property(ot => ot.PaymentTypeGeneralItemId).IsRequired().HasColumnOrder(3);
        this.Property(ot => ot.Credited).IsOptional().HasColumnOrder(4);
        this.Property(ot => ot.Debited).IsOptional().HasColumnOrder(5);
        this.Property(ot => ot.Notes).HasMaxLength(500).IsOptional().HasColumnOrder(6);
        this.Property(ot=> ot.IsActive).IsRequired().HasColumnOrder(7);
        this.Property(ot=> ot.CreatedBy).IsRequired().HasColumnOrder(8);
        this.Property(ot=> ot.CreatedOn).IsRequired().HasColumnOrder(9);
        this.Property(ot=> ot.ModifiedBy).IsOptional().HasColumnOrder(10);
        this.Property(ot=> ot.ModifiedOn).IsOptional().HasColumnOrder(11);

        this.Ignore(ot => ot.DrivingSchoolId);
        this.Ignore(ot => ot.UserId);
        this.Ignore(ot => ot.PaymentTypeGeneralItems);
    }
}

Answer 1

The problem you are experiencing is caused by the fluent configuration:

OrderConfigiration

this.Ignore(o => o.OrderItems);

With this line you are telling EF to ignore OrderItems property, or in other words, OrderItems is not a navigation property - exactly what the Include error message says.

OrderItemConfiguration

this.HasRequired(oi => oi.Order).WithMany().HasForeignKey(oi => oi.OrderId).WillCascadeOnDelete(false);

Here you tell EF that the association is unidirectional with only reference navigation property at the many side and no inverse collection navigation at the one side.

In general, always configure the relationship only in one place and make sure it reflects the exact presence/absence of the navigation / FK properties.

So to fix the issue, remove the first line (with Ignore) and modify the second as follows:

this.HasRequired(oi => oi.Order)
    .WithMany(o => o.OrderItems) // <- here
    .HasForeignKey(oi => oi.OrderId)
    .WillCascadeOnDelete(false);