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中集合操作
标准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部分
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") }); 查询结果