人生記事本 @kevin的日記

資料模型 &&  取得假資料方法 :

using System;
using System.Collections.Generic;

namespace LinqDay3
{
    public class Model2
    {
        public static CustomersOrdersOderItems staticGetAllModelsList()
        {
            return new CustomersOrdersOderItems {
                Customers = staticGetCustomers(),
                Orders = staticGetOrders(),
                OrderItems = staticGetOrderItems()
            };
        }

        // 回傳 Customer 的假資料
        private static List<Customer> staticGetCustomers()
        {
            var Customers = new List<Customer>{
            new Customer {ID = 1, Name = "Leo"},
            new Customer {ID = 2, Name = "Rose"},
            new Customer {ID = 3, Name = "Alvin"},
            new Customer {ID = 4, Name = "Emy"},
            new Customer {ID = 5, Name = "Alice"},
            new Customer {ID = 6, Name = "Bobo"}
        };
            return Customers;
        }

        // 回傳 Order 的假資料
        private static List<Order> staticGetOrders()
        {
            var Orders = new List<Order>{
            new Order {ID = 1, CustomerID = 1, Date = new DateTime(2012,1,5), Description = "Mouse", Price = 480},
            new Order {ID = 2, CustomerID = 1, Date = new DateTime(2012,2,15), Description = "Books", Price = 880},
            new Order {ID = 3, CustomerID = 2, Date = new DateTime(2011,6,16), Description = "Keyboard", Price = 290},
            new Order {ID = 4, CustomerID = 2, Date = new DateTime(2012,3,25), Description = "NoteBook", Price = 16800},
            new Order {ID = 5, CustomerID = 3, Date = new DateTime(2012,8,15), Description = "Mouse", Price = 480},
            new Order {ID = 6, CustomerID = 4, Date = new DateTime(2011,6,22), Description = "NoteBook", Price = 16800},
            new Order {ID = 7, CustomerID = 4, Date = new DateTime(2011,10,10), Description = "Mouse", Price = 480},
            new Order {ID = 8, CustomerID = 4, Date = new DateTime(2012,9,8), Description = "Camera", Price = 29900},
        };
            return Orders;
        }

        // 回傳 OrderItem 的假資料
        private static List<OrderItem> staticGetOrderItems()
        {
            var OrderItems = new List<OrderItem>{
                new OrderItem {ID = 1, OrderID = 4, Detail = "AAA"},
                new OrderItem {ID = 2, OrderID = 4, Detail = "BBB"},
                new OrderItem {ID = 3, OrderID = 2, Detail = "CCC"},
                new OrderItem {ID = 3, OrderID = 2, Detail = "DDD"},
                new OrderItem {ID = 3, OrderID = 5, Detail = "EEE"}
            };
            return OrderItems;
        }

        public class CustomersOrdersOderItems
        {
            public List<Customer> Customers { get; set; }
            public List<Order> Orders { get; set; }
            public List<OrderItem> OrderItems { get; set; }
        }

        public class Customer
        {
            public int ID { get; set; }
            public string Name { get; set; }

            public override string ToString()
            {
                return String.Format("ID = {0}, Name = {1}", this.ID, this.Name);
            }
        }

        public class Order
        {
            public int ID { get; set; }
            public int CustomerID { get; set; }
            public DateTime Date { get; set; }
            public string Description { get; set; }
            public Decimal Price { get; set; }

            public override string ToString()
            {
                return String.Format("ID = {0}, CustomerID = {1}, Date = {2}, Description = {3}, Price = {4}",
                    this.ID, this.CustomerID, this.Date, this.Description, this.Price);
            }
        }

        public class OrderItem
        {
            public int ID { get; set; }
            public int OrderID { get; set; }
            public string Detail { get; set; }

            public override string ToString()
            {
                return String.Format("ID = {0}, OrderID = {1}, Detail = {2}",
                    this.ID, this.OrderID, this.Detail);
            }
        }
    }
}

 

兩表交集範例 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Data;

namespace LinqDay3
{
    public class Program
    {
        //JOIN 範例
        static void Main(string[] args)
        {
            var allModelsData = Model2.staticGetAllModelsList();
            var customers = allModelsData.Customers;
            var orders = allModelsData.Orders;
            customers.ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();
            orders.ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();

            var Program = new Program();
            // 使用兩表交集
            var query2 = Program.Join<Model2.Customer, Model2.Order>(customers, orders, tb1 => tb1.ID, tb2 => tb2.CustomerID,
                (tb1, tb2) => new { Id = tb1.ID, Name = tb1.Name, 商品 = tb2.Description });

            Console.WriteLine();
        }

        /// <summary>
        /// 此為 Join 兩張表的方法，請傳入正確的方法引數達到預期效果
        /// </summary>
        /// <typeparam name="TTable1">主表</typeparam>
        /// <typeparam name="TTable2">被交集表</typeparam>
        /// <param name="tb1">主表來源</param>
        /// <param name="tb2">被交集表來源</param>
        /// <param name="Tkey1">主表的關聯欄位</param>
        /// <param name="Tkey2">被交集表的關聯欄位</param>
        /// <param name="selector">輸出要有那些欄位</param>
        private IQueryable<object> Join<TTable1, TTable2>(List<TTable1> tb1, List<TTable2> tb2,
            Expression<Func<TTable1, object>> Tkey1, Expression<Func<TTable2, object>> Tkey2,
            Expression<Func<TTable1, TTable2, object>> selector)
        {
            var query = tb1.AsQueryable().Join(tb2,
                Tkey1, Tkey2,
                selector
                );

            Type t = tb1.GetType();

            //取得當前方法的 資訊
            //Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().ToString());
            Console.WriteLine("使用Join來做交集，最基本的方式");
            Console.WriteLine("Join Sytanx:  tb1.Join(tb2,");
            Console.WriteLine(" tKey1 => tKey1.columnName, tKey2=>tKey2.columnName, ");
            Console.WriteLine("(tInner,tOuter)=> new { tInner.column, tOuter.column });");
            Console.WriteLine("InnerTb: " + t + ", OuterTb: " + tb2.ToString());
            Console.WriteLine("Tkey= " + Tkey1.ToString() + ", Tkey2= " + Tkey2.ToString() + ", selector= " +
                selector.Body.ToString());
            query.ToList().ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();
            return query;

        }
    }
}

 

 

三表交集範例 : 直接使用Linq提供的方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Data;

namespace LinqDay3
{
    public class Program
    {
        //JOIN 範例
        static void Main(string[] args)
        {
            var allModelsData = Model2.staticGetAllModelsList();
            var customers = allModelsData.Customers;
            var orders = allModelsData.Orders;
            var orderItems = allModelsData.OrderItems;
            customers.ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();
            orders.ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();
            orderItems.ForEach(x => Console.WriteLine(x.ToString()));
            Console.WriteLine();

            var Program = new Program();

            // 使用三表交集 若篩選器 Selector 使用匿名類型 會在離開該方法或區域時轉型成Object
            // 這裡是將第一次交集中的兩表都取回後，在使用 orderItems 去關聯
            var query = customers.Join(orders, tb1 => tb1.ID, tb2 => tb2.CustomerID,
                    (tb1, tb2) => new { tb1 = tb1, tb2 = tb2 }).Join(orderItems,
                    ret => ret.tb2.ID, tb3 => tb3.OrderID, (ret, tb3) => new {
                        Customers = ret.tb1,
                        Order = ret.tb2,
                        OrderItem = tb3
                    });

            foreach (var item in query)
            {
                Console.WriteLine(item.Customers.ToString());
                Console.WriteLine(item.Order.ToString());
                Console.WriteLine(item.OrderItem.ToString());
                Console.WriteLine();
            }
        }
    }
}

 

自訂三表關聯方法遇到的問題 : 目前的解法是直接透過 Linq 的Join跑兩次

• 在第一次關聯過後得到的物件，無法在第二次關聯做應用(目前是直接將關聯後的兩表全部取得)
• 需要自定義每一次關聯過後對等的物件且搭配泛行做指定，變成傳入參數會過多。Ex: 三個表的物件、關聯兩次所需的兩個物件，Tkey更多這樣隨興加總最少也需九個參數，導致方法簽章過於複雜且過多額外建立的物件。

若有大大對於Linq-Join有更好的方法希望能請指導小弟。

 

 

---

多多指教!! 歡迎交流!!

你不知道自己不知道，那你會以為你知道