[EF] 導覽屬性

透過 LINQ 和導覽屬性來產生預期結果

Model-NavigationContext

namespace NavigationProperty.Models
{
    using System.Data.Entity;

    public partial class NavigationContext : DbContext
    {
        public NavigationContext()
            : base("name=NavigationContext")
        {
        }

        public virtual DbSet<Customer> Customer { get; set; }
        public virtual DbSet<Order> Order { get; set; }
        public virtual DbSet<OrderDetail> OrderDetail { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>()
                .HasMany(e => e.Order)
                .WithRequired(e => e.Customer)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Order>()
                .HasMany(e => e.OrderDetail)
                .WithRequired(e => e.Order)
                .WillCascadeOnDelete(false);
        }
    }
}

Model-Customer

namespace NavigationProperty.Models
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("Customer")]
    public partial class Customer
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Customer()
        {
            Order = new HashSet<Order>();
        }

        [Key]
        public int CustID { get; set; }

        [StringLength(50)]
        public string CustName { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Order> Order { get; set; }
    }
}

Model-Order

namespace NavigationProperty.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("Order")]
    public partial class Order
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Order()
        {
            OrderDetail = new HashSet<OrderDetail>();
        }

        public int OrderID { get; set; }

        [Column(TypeName = "date")]
        public DateTime OrderDate { get; set; }

        public int CustID { get; set; }

        // 該範例依慣理原則會自動建立 FK，故意透過資料註記來設定 FK
        [ForeignKey("CustID")]
        public virtual Customer Customer { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<OrderDetail> OrderDetail { get; set; }
    }
}

Model-OrderDetail

namespace NavigationProperty.Models
{
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("OrderDetail")]
    public partial class OrderDetail
    {
        public int ID { get; set; }

        public int OrderID { get; set; }

        [Required]
        [StringLength(50)]
        public string ProductName { get; set; }

        public int Qty { get; set; }

        public virtual Order Order { get; set; }
    }
}

練習 Code

using NavigationProperty.Models;

namespace NavigationProperty
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new NavigationContext())
            {
                // 以 TSQL Join 思維來產生結果
                var LinqJoin = context.Order
                    .Where(o => o.OrderID == 3)
                    .Join(context.Customer,
                            o => o.CustID, c => c.CustID,
                            (o, c) => new { Order = o, customer = c })
                    .Join(context.OrderDetail, oc => oc.Order.OrderID, od => od.OrderID,
                            (oc, od) => new { oc.Order.OrderID, oc.Order.OrderDate, oc.customer.CustName, od.ProductName, od.Qty });

                // 透過 NavigatioProperty 來產生結果
                var NavigationResult = context.Order
                    .Where(o => o.OrderID == 3)
                    .SelectMany(
                        o => o.OrderDetail,
                        (o, od) => new { o.OrderID, o.OrderDate, o.Customer.CustName, od.ProductName, od.Qty });

                Console.WriteLine("----- 產生 TSQL 語法 -----");
                Console.WriteLine(NavigationResult.ToString());

                Console.WriteLine("----- 顯示資料 -----");
                foreach (var item in NavigationResult)
                {
                    Console.WriteLine($"{item.OrderID} - {item.OrderDate.ToString("yyyy/MM/dd")} - {item.CustName} - {item.ProductName} - {item.Qty}");
                }
            }
        }
    }
}