元哥的筆記

最近接手Entity Framework 由於有點忘記關係導覽部分，先用簡單的銷售範例來建置導覽屬性

分別有四個檔案Customer ,Order,OrderDetail,Product ，Customer對Order是一對多，Product對 OrderDetail是一對多，Order對OrderDetail是一對多

    public partial class Customer
    {
        public int Id { get; set; }
        public string ContactName { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }

        public virtual  ICollection<Order> Orders { get; set; }
    }

    public partial class Order
    {
        public int Id { get; set; }
        public DateTime OrderDate { get; set; }

        public int CustomerId { get; set; }

        public  virtual ICollection<OrderDetail> OrderDetails { get; set; }

        public virtual Customer Customers { get; set; }
    }

    public partial class OrderDetail
    {
        public int Id { get; set; }
        public int OrderId { get; set; }
        public int ProductId { get; set; }

        public int Quantity { get; set; }

        public int Price { get; set; }

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

    public partial class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }

        public virtual ICollection<OrderDetail> OrderDetails { get; set; }

    }

    public class KTStoreModel:DbContext
    {
        public KTStoreModel() : base("name=KTStoreModelConn")
        {
        }

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

        public virtual DbSet<Product> Products { get; set; }

        public virtual DbSet<Customer> Customers { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<
                 System.Data.Entity.ModelConfiguration.Conventions.
                 PluralizingTableNameConvention>();
        }

    }

測試查詢
 

            KTStoreModel model = new KTStoreModel();
            var orderlist =
                from orderdetail in model.OrderDetails
                join order in model.Orders on
                    orderdetail.OrderId equals order.Id
                select new
                {
                     id = order.Id.ToString(),
                     product = orderdetail.Product.Name
                };

            foreach (var ordertail in orderlist)
            {
             
                Console.WriteLine($"訂單編號:{ordertail.id} 訂購商品:{ordertail.product}");
            }
            Console.WriteLine();

            IEnumerable<OrderReport> report =
                from orderdetail in model.OrderDetails
                join order in model.Orders on
                    orderdetail.OrderId equals order.Id
                select new OrderReport
                {
                    Id = order.Id,
                    CustomerName = order.Customer.ContactName,
                    OrderDate = order.OrderDate,
                    ProductName = orderdetail.Product.Name,
                    OPrice = orderdetail.Price,
                    Price = orderdetail.Product.Price,
                    Quantity = orderdetail.Quantity,
                    Sum = orderdetail.Price * orderdetail.Quantity
                };

            foreach (var order in report)
            {
                Console.WriteLine($"{order.Id} " +
                                  $"{order.OrderDate.ToShortDateString()}\t" +
                                  $"{order.ProductName} " +
                                  $"定價:{order.Price}\t" +
                                  $"單價:{order.OPrice}" +
                                  $"數量:{order.Quantity}" +
                                  $"總計:{order.Sum}");
            }

            int total = report.Sum(o => o.Sum);
            Console.WriteLine($"\n總金額:{total}");

 

元哥的筆記