EF6(not EF core) using Navigation property instead of Linq joins

Question

I have two tables that I need to join and filter. Orders and Customers. I have generated these classes using EF Code First from DB.

Generated classes for the tables -

Orders

    [Table("Orders")]
    public partial class Orders
    {
      
        [Key]
        [StringLength(17)]
        public string OrderID { get; set; }

        public int ShipToCustomerID { get; set; }

        //Navigation Property 
        public Customer Customer { get; set; }
    }

Customers

[Table("Customer")]
    public partial class Customer
    {
       
        public int CustomerID { get; set; }
        
        public string AccountNumber { get; set; }


        //Navigation prop

        public int ShipToCustomerID { get; set; } (not a part of the table, just attempting to get the navigation work)

        public Orders Order { get; set; }
   }

Method 1:

LINQ Joins

using (var context = new OrderDetailsGeneral1())
     {
        var data = (from p in context.Orders
                    join q in context.Customers 
                    on p.ShipToCustomerID equals q.CustomerID
                    where p.OrderID == "7150615"
                    select new
                         {
                             OrderID = p.OrderID,
                             CustomerID = q.AccountNumber
                         }
                     );

    var orders = data.ToList();
    return Json(orders);
 }

This works well and I get the following output -

[
    {
        "OrderID": "7150615",
        "CustomerID": "23320347       "
    }
]

Method 2: I read that it's better to use navigation properties than using joins and that's why I was trying to do so, as per that I added the navigation properties to the classes above.

I tried a bunch of ways to link them together. One of them is the way mentioned here and I came across a bunch of errors.

It would try to map Customers.CustomerID to Orders.OrderID instead of Orders.ShipToCustomerID.

What's the best way to achieve this? I am having a hard time figuring out linking this foreign key (Customers.CustomerID) to a non primary/alternate key (Orders.ShipToCustomerID)

Answer 1

you have to fix your classes

    [Table("Orders")]
    public partial class Order
    {
      
        [Key]
        [StringLength(17)]
        public string OrderID { get; set; }

        public int ShipToCustomerID { get; set; }
        [ForeignKey(nameof(ShipToCustomerID))]
        [InverseProperty("Orders")]
        public virtual Customer Customer { get; set; }
    }



   [Table("Customer")]
    public partial class Customer
    {
       [Key]
        public int CustomerID { get; set; }
         public string AccountNumber { get; set; }

        [InverseProperty(nameof(Order.Customer))]
        public virtual ICollection<Order> Orders { get; set; }
   }