Linq 集合操作

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

    LeftJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func result)

{

IEnumerable _result = Enumerable.Empty();

 

_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

    RightJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func result)

{

IEnumerable _result = Enumerable.Empty();

 

_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

    FullOuterJoinJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func 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

    LeftExcludingJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func result)

{

IEnumerable _result = Enumerable.Empty();

 

_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

RightExcludingJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func result)

{

IEnumerable _result = Enumerable.Empty();

 

_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

FulltExcludingJoin(this IEnumerable source,

IEnumerable inner,

Func pk,

Func fk,

Func 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")

});

查询结果

.Net

Linq 集合操作

演示代码

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT EXCLUDING JOIN

RIGHT EXCLUDING JOIN

FULL OUTER EXCLUDING JOIN

相关

一、Asp.Net Core WebAPI——修改默认监听端口

.net mvc 获取acion 返回类型

.NetCore简单封装基于IHttpClientFactory的HttpClient请求

基于java.net.socket包的对象传递

.net core 2.2 部署CentOS7（3）安装Xshell操控CentOS7

Centos 8.x 安装.net 6 sdk/runtime

.net core 部署在Centos 7/8 的几个小坑

甲骨文ARM架构云服务器部署宝塔+.net 5.0

通俗易懂，什么是.NET?什么是.NET Framework？什么是.NET Core?

ASP.NET中WebService的两种身份验证方法

.NET Core3.1 Dotnetty实战第二章

C# string 加号与StringBuilder .netCore3.1与 .netCore5.0速度测试

标签