Linq 集合操作
演示程式碼
兩個物件一個是Person，一個Address, AddressId是外來鍵，
public class Person
{
public string ID { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public double Salary { get; set; }
public DateTime Born { get; set; }
public int IdAddress { get; set; }
}
public class Address
{
public int IdAddress { get; set; }
public string Street { get; set; }
public int Num { get; set; }
public string City { get; set; }
}
測試資料如下
Person類
Address類
下面我會用7個方式實現7中集合操作
-
INNER JOIN 內連結
-
LEFT JOIN 左連線
-
RIGHT JOIN 右連結
-
FULL OUTER JOIN 所有
-
LEFT JOIN EXCLUDING INNER JOIN 左空
-
RIGHT JOIN EXCLUDING INNER JOIN 右空
-
FULL OUTER JOIN EXCLUDING INNER JOIN ？？
學校數學沒學好不知道專業術語！哈哈
INNER JOIN
最常用的方法，兩表關聯查詢
標準Linq語法
var result = from p in Person.BuiltPersons() join a in Address.BuiltAddresses() on p.IdAddress equals a.IdAddress select new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street };Lambda Expression:
var resultJoint = Person.BuiltPersons().Join( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });Lambda表示式主要有5部分
-
Is the main Collection.
-
Is the inner Collection.
-
Is the PK.
-
Is the FK.
-
Is the type for the result collection.
查詢結果如下
LEFT JOIN
新增一個LeftJoin的擴充套件方法
public static IEnumerable<TResult> LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from s in source join i in inner on pk(s) equals fk(i) into joinData from left in joinData.DefaultIfEmpty() select result(s, left); return _result; }Lambda Expression:
var resultJoint = Person.BuiltPersons().LeftJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });注意：如果address為空Null需要做一個替換，否則會報錯
查詢結果如下
RIGHT JOIN
Extension Method:
public static IEnumerable<TResult> RightJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from i in inner join s in source on fk(i) equals pk(s) into joinData from right in joinData.DefaultIfEmpty() select result(right, i); return _result; }Lambda Expression:
var resultJoint = Person.BuiltPersons().RightJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });查詢結果如下
FULL OUTER JOIN
Extension Method:
public static IEnumerable<TResult> FullOuterJoinJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { var left = source.LeftJoin(inner, pk, fk, result).ToList(); var right = source.RightJoin(inner, pk, fk, result).ToList(); return left.Union(right); }Lambda Expression:
var resultJoint = Person.BuiltPersons().FullOuterJoinJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });注意：每個物件都需要驗證Null
查詢結果如下
LEFT EXCLUDING JOIN
Extension Method:
public static IEnumerable<TResult> LeftExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from s in source join i in inner on pk(s) equals fk(i) into joinData from left in joinData.DefaultIfEmpty() where left == null select result(s, left); return _result; }Lambda Expression:
var resultJoint = Person.BuiltPersons().LeftExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });查詢結果如下
RIGHT EXCLUDING JOIN
Extension Method:
public static IEnumerable<TResult> RightExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from i in inner join s in source on fk(i) equals pk(s) into joinData from right in joinData.DefaultIfEmpty() where right == null select result(right, i); return _result; }Lambda Expression:
var resultJoint = Person.BuiltPersons().RightExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });查詢結果
FULL OUTER EXCLUDING JOIN
Extension Method:
public static IEnumerable<TResult> FulltExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) { var left = source.LeftExcludingJoin(inner, pk, fk, result).ToList(); var right = source.RightExcludingJoin(inner, pk, fk, result).ToList(); return left.Union(right); }Lambda Expression:
var resultJoint = Person.BuiltPersons().FulltExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });查詢結果
