linq动态加入字段限制
A Few Words About LINQ
关于LINQ的一些话
LINQ is a high level querying lanquage. It was made in order to unify the way a programmer queries different sources, like databases, lists and objects. That is the power of it, as you may change datasource and your LINQ may remain the same producing the same results.
LINQ是高级查询语言。 这样做是为了统一程序员查询不同源（如数据库，列表和对象）的方式。 这就是它的强大功能，因为您可以更改数据源，而LINQ可以保持不变，从而产生相同的结果。
As it is a querying lanquage you may find similarities with T-SQL, such as keywords (SELECT, FROM, WHERE), but there are also differences. The major difference is the syntax, but also the simplicity of LINQ when it comes to complex queries.
由于它是一种查询语言，因此您可能会发现与T-SQL的相似之处，例如关键字（SELECT，FROM，WHERE），但也存在差异。 主要区别在于语法，而且涉及复杂查询时还具有LINQ的简单性。
Of course LINQ is not always the best choice. For instance LINQ is not good for handling bulk inserts.
当然，LINQ并非总是最佳选择。 例如，LINQ不适用于处理批量插入。
Now lets see how LINQ implements various cases of JOIN queries, compared with T-SQL.
现在，与T-SQL相比，让我们看一下LINQ如何实现各种JOIN查询情况。
Introduction
介绍
This article is meant to give a helping hand to people that have dificulties when it comes to joining their LINQ queries.
本文旨在为有困难的人加入LINQ查询时提供帮助。
In all examples we will use two tables names for simplicity TableA and TableB. I am using "tables" as I am fond of SQL, but this article also applies to whatever LINQ applies to.
在所有示例中，为简单起见，我们将使用两个表名TableA和TableB。 我喜欢SQL时使用的是“表”，但是本文也适用于LINQ所适用的任何内容。
Below are the the tables we are going to use through this article:
下面是我们将在本文中使用的表：
Code samples will be given both in VB and C#.
代码示例将在VB和C＃中给出。
Before we begin lets have a look at the Class constructors:
在开始之前，让我们看一下Class构造函数：
C#
C＃
public class TableA
{
public string Name { get; set; }
public string Pet{ get; set; }
}
public class TableB
{
public string Name { get; set; }
public string TransportationType { get; set; }
}
VB
VB
Public Class TableA
Public Property Name() As String
Get
Return m_Name
End Get
Set(value As String)
m_Name = Value
End Set
End Property
Private m_Name As String
Public Property Pet() As String
Get
Return m_Pet
End Get
Set(value As String)
m_Pet = Value
End Set
End Property
Private m_Pet As String
End Class
Public Class TableB
Public Property Name() As String
Get
Return m_Name
End Get
Set(value As String)
m_Name = Value
End Set
End Property
Private m_Name As String
Public Property TransportationType() As String
Get
Return m_TransportationType
End Get
Set(value As String)
m_TransportationType = Value
End Set
End Property
Private m_TransportationType As String
End Class
Basic Joins
基本联接
INNER JOIN 内部联接
The SQL Equivalent of an INNER JOIN is
INNER JOINSQL等效为
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Name = B.Name
The expected results are Name of all the persons that have both a Pet and a TransportationType. Results are shown in the image bellow:
预期结果是同时拥有Pet和TransportationType的所有人的姓名。 结果如下图所示：
Bellow is the code for creating the Inner Join using LINQ:
波纹管是用于使用LINQ创建内部联接的代码：
C#
C＃
static void InnerJoin(List<TableA> TableA,List<TableB> TableB)
{
var query = from a in TableA
join b in TableB
on a.Name equals b.Name
select new {a.Name,a.Pet,b.TransportationType };
foreach(var person in query)
{
Console.WriteLine(person);
}
}
VB
VB
Private Shared Sub InnerJoin(TableA As List(Of TableA), TableB As List(Of TableB))
Dim query = From a In TableA _
Join b In TableB _
On a.Name Equals b.Name
Select New With
{ _
a.Name, _
a.Pet, _
b.TransportationType _
}
For Each person In query
Console.WriteLine(person)
Next
End Sub
Both of these will output the following in the console:
两者都将在控制台中输出以下内容：
LEFT OUTER JOIN 左外连接
The SQL Equivalent of the LEFT OUTER JOIN is
LEFT OUTER JOINSQL等效为
SELECT *
FROM TableA A
LEFT OUTER JOIN Table B
ON A.Name = B.Name
In our example, the results should be Name of persons that do have a pet and if they have a TransportationType display that too.
在我们的示例中，结果应该是确实有宠物的人的姓名，并且如果他们也有TransportationType的显示。
In SQL the results are:
在SQL中，结果为：
In this example, in LINQ we are going to use the DefaultIfEmpty method, that returns a default value if the join doesn't have a match. Note: If we were using LINQ2SQL we wouldn't have to define the default value as it is defined by the system. In our example though we must define the default value to be null or nothing.
在此示例中，在LINQ中，我们将使用DefaultIfEmpty方法，如果联接不匹配，该方法将返回默认值。 注意：如果使用的是LINQ2SQL，则不必定义默认值，因为它是系统定义的。 尽管在我们的示例中，我们必须将默认值定义为null或什么都不做。
C#
C＃
static void LeftOuterJoin(List<TableA> TableA, List<TableB> TableB)
{
TableB DefaultTableB = new TableB { Name = null, TransportationType = null };
var query = from a in TableA
join b in TableB
on a.Name equals b.Name
into temp
from b in temp.DefaultIfEmpty(DefaultTableB)
select new
{
a.Name,
a.Pet,
TransportationType = b.TransportationType
};
foreach (var person in query)
{
Console.WriteLine(person);
}
}
VB
VB
Private Shared Sub LeftOuterJoin(TableA As List(Of TableA), TableB As List(Of TableB))
Dim DefaultTableB As New TableB() With { _
.Name = Nothing, _
.TransportationType = Nothing _
}
Dim query = From a In TableA _
Group Join b In TableB _
On a.Name Equals b.Name Into temp = Group _
From b In temp.DefaultIfEmpty(DefaultTableB) _
Select New With { _
a.Name, _
a.Pet, _
.TransportationType = b.TransportationType _
}
For Each person In query
Console.WriteLine(person)
Next
End Sub
The output if we run this code in a console application is:
如果我们在控制台应用程序中运行此代码，则输出为：
FULL OUTER JOIN 全外连接
The SQL Equivalent of a FULL OUTER JOIN is
FULL OUTER JOINSQL等效为
SELECT *
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Name = B.Name
The expected results of these would be a full list of all the persons in both Tables displaying their Name, Pet and/or TransportationType. Following are the results in SQL:
这些预期的结果将是两个表中所有人员的完整列表，并显示其姓名，宠物和/或运输类型。 以下是SQL中的结果：
In this case LINQ is a little bit more complicated. It actually consists of three queries with the third being the union of the first two. The first two queries are LEFT OUTER JOINs as seen previously.
在这种情况下，LINQ稍微复杂一些。 它实际上由三个查询组成，第三个查询是前两个查询的并集。 前两个查询是如左所示的LEFT OUTER JOIN。
C#
C＃
static void FullOuterJoin(List<TableA> TableA, List<TableB> TableB)
{
TableA DefaultTableA = new TableA { Name=null, Pet = null };
TableB DefaultTableB = new TableB { Name = null, TransportationType = null };
var leftOuterJoin = from a in TableA
join b in TableB
on a.Name equals b.Name
into temp
from b in temp.DefaultIfEmpty(DefaultTableB)
select new
{
a.Name,
a.Pet,
TransportationType=b.TransportationType
};
var rightOuterJoin = from b in TableB
join a in TableA
on b.Name equals a.Name
into temp
from a in temp.DefaultIfEmpty(DefaultTableA)
select new
{
b.Name,
a.Pet,
TransportationType = b.TransportationType
};
var query = leftOuterJoin.Union(rightOuterJoin);
foreach (var person in query)
{
Console.WriteLine(person);
}
}
VB
VB
Private Shared Sub FullOuterJoin(TableA As List(Of TableA), TableB As List(Of TableB))
Dim DefaultTableA As New TableA() With { _
.Name = Nothing, _
.Pet = Nothing _
}
Dim DefaultTableB As New TableB() With { _
.Name = Nothing, _
.TransportationType = Nothing _
}
Dim leftOuterJoin = From a In TableA _
Group Join b In TableB _
On a.Name Equals b.Name Into temp = Group _
From b In temp.DefaultIfEmpty(DefaultTableB) _
Select New With { _
a.Name, _
a.Pet, _
.TransportationType = b.TransportationType _
}
Dim rightOuterJoin = From b In TableB _
Group Join a In TableA _
On b.Name Equals a.Name Into temp = Group _
From a In temp.DefaultIfEmpty(DefaultTableA) _
Select New With { _
b.Name, _
a.Pet, _
.TransportationType = b.TransportationType _
}
Dim query = (From q In leftOuterJoin.Union(rightOuterJoin) _
Select q.Name, q.Pet, q.TransportationType).Distinct
For Each person In query
Console.WriteLine(person)
Next
End Sub
Again the results are:
同样，结果是：
Joins with exclusions
加入排除
Data that exist only on the first(LEFT) Table 仅在第一个（LEFT）表上存在的数据
In SQL we would have the following query:
在SQL中，我们将具有以下查询：
SELECT *
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.Name = B.Name
WHERE B.Name IS NULL
And the results would be:
结果将是：
In code we get the same code with LEFT OUTER JOIN but we add a where clause that the id of the second table should be null or nothing, as we have done in SQL.
在代码中，我们使用LEFT OUTER JOIN获得了相同的代码，但是我们添加了where子句，第二个表的id应该为null或不为空，就像在SQL中所做的那样。
C#
C＃
static void ExcludeTableB(List<TableA> TableA, List<TableB> TableB)
{
TableB DefaultTableB = new TableB { Name = null, TransportationType = null };
var query = from a in TableA
join b in TableB
on a.Name equals b.Name
into temp
from b in temp.DefaultIfEmpty(DefaultTableB)
where b.Name==null
select new
{
a.Name,
a.Pet,
TransportationType = b.TransportationType
};
foreach (var person in query)
{
Console.WriteLine(person);
}
}
VB
VB
Private Shared Sub ExcludeTableB(TableA As List(Of TableA), TableB As List(Of TableB))
Dim DefaultTableB As New TableB() With { _
.Name = Nothing, _
.TransportationType = Nothing _
}
Dim query = From a In TableA _
Group Join b In TableB _
On a.Name Equals b.Name Into temp = Group _
From b In temp.DefaultIfEmpty(DefaultTableB) _
Where b.Name Is Nothing
Select New With { _
a.Name, _
a.Pet, _
.TransportationType = b.TransportationType _
}
For Each person In query
Console.WriteLine(person)
Next
End Sub
The results of the above codes are:
以上代码的结果是：
Data that exist only either on the first(LEFT) or the second(RIGHT) Table 仅在第一个（LEFT）或第二个（RIGHT）表上存在的数据
The query in SQL would be:
SQL中的查询为：
SELECT *
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Name = B.Name
WHERE B.Name IS NULL
OR A.Name IS NULL
The results produced by that SQL query are:
该SQL查询产生的结果是：
This where clause is reflected in the LINQ expressions bellow:
下面的LINQ表达式反映了where子句：
C#
C＃
static void ExcludeMatches(List<TableA> TableA, List<TableB> TableB)
{
TableA DefaultTableA = new TableA { Name = null, Pet = null };
TableB DefaultTableB = new TableB { Name = null, TransportationType = null };
var leftOuterJoin = from a in TableA
join b in TableB
on a.Name equals b.Name
into temp
from b in temp.DefaultIfEmpty(DefaultTableB)
select new
{
NameA = a.Name,
a.Pet,
NameB = b.Name,
TransportationType = b.TransportationType
};
var rightOuterJoin = from b in TableB
join a in TableA
on b.Name equals a.Name
into temp
from a in temp.DefaultIfEmpty(DefaultTableA)
select new
{
NameA = a.Name,
a.Pet,
NameB = b.Name,
TransportationType = b.TransportationType
};
var query = from q in leftOuterJoin.Union(rightOuterJoin)
where (q.NameA ==null|| q.NameB==null)
select q;
foreach (var person in query)
{
Console.WriteLine(person);
}
}
VB
VB
Private Shared Sub ExcludeMatches(TableA As List(Of TableA), TableB As List(Of TableB))
Dim DefaultTableA As New TableA() With { _
.Name = Nothing, _
.Pet = Nothing _
}
Dim DefaultTableB As New TableB() With { _
.Name = Nothing, _
.TransportationType = Nothing _
}
Dim leftOuterJoin = From a In TableA _
Group Join b In TableB _
On a.Name Equals b.Name Into temp = Group _
From b In temp.DefaultIfEmpty(DefaultTableB) _
Select New With { _
.NameA = a.Name, _
a.Pet, _
.NameB = b.Name, _
.TransportationType = b.TransportationType _
}
Dim rightOuterJoin = From b In TableB _
Group Join a In TableA _
On b.Name Equals a.Name Into temp = Group _
From a In temp.DefaultIfEmpty(DefaultTableA) _
Select New With { _
.NameA = a.Name, _
a.Pet, _
.NameB = b.Name, _
.TransportationType = b.TransportationType _
}
Dim query = (From q In leftOuterJoin.Union(rightOuterJoin) _
Where q.NameA Is Nothing Or q.NameB Is Nothing
Select q.NameA, q.Pet, q.NameB, q.TransportationType).Distinct
For Each person In query
Console.WriteLine(person)
Next
End Sub
Running the console will produce:
运行控制台将产生：
References
参考资料
In this article i have used as a reference a very nice visual representation of SQL joins, that really have helped me understand the concept and it may be found here
在本文中，我作为参考很好地使用了SQL联接的可视化表示形式，它确实帮助我理解了该概念，可以在这里找到
Conclusion
结论
Using LINQ may be a little bit strange, especially if you are used to writing SQL. In the end you get used to the syntax and with the vast of resources out there you may accomplish almost everything. I have written this article just to give some examples and a starting point to whoever might need it. My examples may not be the best but they do work so you may find them helpful.
使用LINQ可能有点奇怪，特别是如果您习惯于编写SQL。 最后，您习惯了语法，并拥有大量资源，您几乎可以完成所有工作。 我写这篇文章只是为了给所有人提供一些示例和起点。 我的示例可能不是最好的，但是它们确实起作用，因此您可能会发现它们很有帮助。
翻译自: https://www.experts-exchange.com/articles/11369/Joining-in-LINQ.html
linq动态加入字段限制
3412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
