此篇簡單介紹使用Linq做關聯查詢。
資料模型 && 取得假資料方法 :
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有更好的方法希望能請指導小弟。
多多指教!! 歡迎交流!!
你不知道自己不知道,那你會以為你知道