Linq 集合操作
Linq 集合操作
演示代码
两个对象一个是Person,一个Address, AddressId是外键,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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语法
123456789101112131415161718192021var
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:
12345678910111213141516171819202122232425var
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的扩展方法
123456789101112131415161718192021222324252627282930313233public
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:
12345678910111213141516171819202122232425var
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:
123456789101112131415161718192021222324252627282930313233public
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:
12345678910111213141516171819202122232425var
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:
12345678910111213141516171819202122232425public
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:
12345678910111213141516171819202122232425var
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:
1234567891011121314151617181920212223242526272829303132333435public
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:
12345678910111213141516171819202122232425var
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:
1234567891011121314151617181920212223242526272829303132333435public
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:
12345678910111213141516171819202122232425var
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:
1234567891011121314151617181920212223public
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:
12345678910111213141516171819202122232425var
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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
2015-02-13 Asp.net mvc 5 CRUD代码自动生成工具- vs.net 2013 Saffolding功能扩展