Selectnames in LINQ to SQL)

Selectnames in LINQ to SQL

First, let's look at the definition of selectmany:

Selectmany method in queryable: project each element of the sequence to an ienumerable <(of <(T>) combine the result sequence into an iqueryable <(of <(T>)> sequence. (Reference msdn)

When writing a query statement using LINQ to SQL, there is a selectmany statement, which indicates a one-to-many relationship.ArticleI would like to talk about the usage of several types of SQL statements that can be equivalent to selectworkflow.

The conditions for the system to convert to selectmany:
1: The statement does not contain join,;
2: more than two from tables are required. The following uses two tables as an example: for example, the first table from C in table 1
1): If the from object uses the table name (from C in Table 2), it is converted to cross join;
2) if the name of the second table appears in the form of a sub-table of the first table, that is, similar to C. Table 2, there are two cases,
1>: from o in C. Table 2, inner join is formed.
2>: from P in C. Table 2. defaultifempty (), left out join is formed.

The example table structure is described as follows:The customer table and the purchase table are associated with the customerid by ID.

Create Table [DBO]. [Customer] (
[ID] [ Int ] Not null,
[Name] [nvarchar] ( 30 ))

Create Table [DBO]. [purchase] (
[ID] [Int] Not null,
[Customerid] [Int] Null,
[Date] [datetime] not null,
[Description] [varchar] (30))

Let's implement three very classic join methods in SQL.

First: cross join, whose result set is the dikar product of all tables.

// Cross join
From C In MERs
From o In Purchases
Select o

 

In LINQ to SQL, if the following from is specified as the table name, the following statement is generated:

Select [T1]. [ID], [T1]. [customerid], [T1]. [date], [T1]. [description], [T1]. [price]
From [Customer] as [t0], [purchase] as [T1]

Second, inner join.

// Inner join
From C In MERs
From o In C. Purchases
Select o

The generated SQL statement is as follows:

Select [T1]. [ID], [T1]. [customerid], [T1]. [date], [T1]. [description], [T1]. [price]
From [Customer] as [t0], [purchase] as [T1]
Where [T1]. [customerid] = [T0]. [ID]

 

Although inner join is not displayed, it has the same functions. the writing method and the preceding cross join seem very similar. The only difference is that when cross join is used, the table name purchases is used directly, and the inner join is changed to C. pruchasex forms a one-to-many situation.

Third: left Outer Join

From C In MERs
From P In C. Purchases. defaultifempty ()
Select New {C. Name, P. Description, price = ( Decimal ? ) P. Price}

 

The generated SQL statement is as follows:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] on [T1]. [customerid] = [T0]. [ID]

 

Left Outer Join actually adds a condition on the basis of inner join. With defaultifempty (), if the record does not match, null is returned.
We add a filter condition to the query.

From C In MERs
From P In C. Purchases. Where (P => P. Price >   1000 ). Defaultifempty ()
Select New
{
C. Name,
P. description,
Price = ( Decimal ? ) P. Price
}

 

Corresponding SQL:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] On ([T1]. [price] > @ P0) and ([T1]. [customerid] = [T0]. [ID])

In this case, the above statement is still the standard left out join. What if we change the position of the next condition?

From C In MERs
From P In C. Purchases. defaultifempty ()
Where P. Price > 1000
Select New
{
C. Name,
P. description,
Price = ( Decimal ? ) P. Price
}

Corresponding SQL:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] on [T1]. [customerid] = [T0]. [ID]
Where [T1]. [price] > @ P0

 

After a condition changes its position, it does not change the nature of join. It is left out join, but the query results are different. From the result set, the effect of the next join operation is the same as that of the inner join operation.

Conclusion: The preceding query statement can also be queried using the displayed join statement. I prefer the displayed join statement because it looks closer than SQL statements. In the next article, I will summarize and display the use of join queries. In fact, the final display results are the same, but they are written differently.

Note:
The examples in this article are from

First, let's look at the definition of selectmany:

Selectmany method in queryable: project each element of the sequence to an ienumerable <(of <(T>) combine the result sequence into an iqueryable <(of <(T>)> sequence. (Reference msdn)

When writing a query statement using LINQ to SQL, there is a selectmany statement, which indicates a one-to-many relationship, in this article, I would like to explain the usage of several types of SQL statements that can be equivalent to selectworkflow.

The conditions for the system to convert to selectmany:
1: The statement does not contain join,;
2: more than two from tables are required. The following uses two tables as an example: for example, the first table from C in table 1
1): If the from object uses the table name (from C in Table 2), it is converted to cross join;
2) if the name of the second table appears in the form of a sub-table of the first table, that is, similar to C. Table 2, there are two cases,
1>: from o in C. Table 2, inner join is formed.
2>: from P in C. Table 2. defaultifempty (), left out join is formed.

The example table structure is described as follows:The customer table and the purchase table are associated with the customerid by ID.

Create Table [DBO]. [Customer] (
[ID] [ Int ] Not null,
[Name] [nvarchar] ( 30 ))

Create Table [DBO]. [purchase] (
[ID] [Int] Not null,
[Customerid] [Int] Null,
[Date] [datetime] not null,
[Description] [varchar] (30))

Let's implement three very classic join methods in SQL.

First: cross join, whose result set is the dikar product of all tables.

// Cross join
From C In MERs
From o In Purchases
Select o

 

In LINQ to SQL, if the following from is specified as the table name, the following statement is generated:

Select [T1]. [ID], [T1]. [customerid], [T1]. [date], [T1]. [description], [T1]. [price]
From [Customer] as [t0], [purchase] as [T1]

Second, inner join.

// Inner join
From C In MERs
From o In C. Purchases
Select o

The generated SQL statement is as follows:

Select [T1]. [ID], [T1]. [customerid], [T1]. [date], [T1]. [description], [T1]. [price]
From [Customer] as [t0], [purchase] as [T1]
Where [T1]. [customerid] = [T0]. [ID]

 

Although inner join is not displayed, it has the same functions. the writing method and the preceding cross join seem very similar. The only difference is that when cross join is used, the table name purchases is used directly, and the inner join is changed to C. pruchasex forms a one-to-many situation.

Third: left Outer Join

From C In MERs
From P In C. Purchases. defaultifempty ()
Select New {C. Name, P. Description, price = ( Decimal ? ) P. Price}

 

The generated SQL statement is as follows:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] on [T1]. [customerid] = [T0]. [ID]

 

Left Outer Join actually adds a condition on the basis of inner join. With defaultifempty (), if the record does not match, null is returned.
We add a filter condition to the query.

From C In MERs
From P In C. Purchases. Where (P => P. Price >   1000 ). Defaultifempty ()
Select New
{
C. Name,
P. description,
Price = ( Decimal ? ) P. Price
}

 

Corresponding SQL:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] On ([T1]. [price] > @ P0) and ([T1]. [customerid] = [T0]. [ID])

In this case, the above statement is still the standard left out join. What if we change the position of the next condition?

From C In MERs
From P In C. Purchases. defaultifempty ()
Where P. Price > 1000
Select New
{
C. Name,
P. description,
Price = ( Decimal ? ) P. Price
}

Corresponding SQL:

Select [t0]. [name], [T1]. [description] as [description], [T1]. [price] as [price]
From [Customer] as [t0]
Left Outer Join [purchase] as [T1] on [T1]. [customerid] = [T0]. [ID]
Where [T1]. [price] > @ P0

 

After a condition changes its position, it does not change the nature of join. It is left out join, but the query results are different. From the result set, the effect of the next join operation is the same as that of the inner join operation.

Conclusion: The preceding query statement can also be queried using the displayed join statement. I prefer the displayed join statement because it looks closer than SQL statements. In the next article, I will summarize and display the use of join queries. In fact, the final display results are the same, but they are written differently.

Note:
The examples in this article are from