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") });查询结果