Join & Group join & left join in LINQ

ArticleFrom: http://www.cnblogs.com/c-jquery-linq-sql-net-problem/archive/2011/01/17/LINQ_Inner_Join_Group_Join_Left_Join.html

We often use inner join, left join, Cartesian Product, and so on during SQL queries. I don't need to explain the concept of the connection method too much,

Today, we focus on familiarizing you with how to use join to implement commonly used table joins.

Create a test class:

 
Class Customer {public int customerid {Get; set;} public string name {Get; set;} public int age {Get; Set ;}} class product {public int productid {Get; set;} public string name {Get; set;} Public String origin {Get; Set ;}} class order {public int orderid {Get; Set ;} public int customerid {Get; set ;}public list <product> Products {Get; Set ;}}
 

We use the following example to familiarize ourselves with the use of join keywords.

1.Inner join:

Createentities (); var query = from C in mers MERs join o in orders on C. customerid equals O. customerid where o. orderid = 2 select C; foreach (VAR customer in query) {console. writeline ("ID: {0}, name: {1}", customer. customerid, customer. name );}
 

Running result:

ID: 1, name: CA

The above is a common internal connection example, which is similar to the SQL syntax, but there are the following points to note:

(1). Connection conditions:C. customerid equals O. customerid can only be represented by equals, but not =, =, or equal.
I think that almost all of the connection conditions of LINQ are = the condition will not appear>, <,! =. Therefore, a keyword is used to describe the table connection conditions.

(2). Conditional order:C. The order before customerid equals O. customerid, range variable: C and B cannot be reversed.

 

2.Group join:

Maybe you don't know much about the concept of group join. It's okay to let us know it through examples:

 
Createentities (); var query = from C in mers MERs join o in orders on C. customerid equals O. customerid into OS select new {C, OS}; foreach (VAR item in query) {console. writeline ("Customer ID: {0}, name: {1}", item. c. customerid, item. c. name); foreach (VAR o in item. OS) {console. writeline ("-- order ID: {0}", O. orderid );}}
 

Result:

Customer ID: 1, name: CA
-- Order ID: 1
-- Order ID: 2
Customer ID: 2, name: CB
-- Order ID: 4
Customer ID: 3, name: CC
-- Order ID: 3
Customer ID: 4, name: CD
Press any key to continue...

The results returned by the preceding query are very similar to those returned by group by: a key object corresponds to a set.

To implement Group join, we need to introduce a keyword:.

However, pay attention to the following points:

(1). After the into keyword is used, the range variable: O after join will lose the scope in the expression block.

(2). range variable: Generally, OS is ienumerable <t> type.

3.Left join:

Left join is often used in SQL. Let's see how to implement it in LINQ:

 
Createentities (); var query = from C in mers MERs join o in orders on C. customerid equals O. customerid into OS from O2 in OS. defaultifempty (New Order {orderid = 0, customerid = 0, products = new list <product> ()}) Select New {C, O2}; foreach (VAR item in query) {console. writeline ("Customer ID: {0}, name: {1} -- order ID: {0}", item. c. customerid, item. o2.orderid );}
 

Result:

Customer ID: 1, name: 1 -- order ID: 1
Customer ID: 1, name: 2 -- order ID: 1
Customer ID: 2, name: 4 -- order ID: 2
Customer ID: 3, name: 3 -- order ID: 3
Customer ID: 4, name: 0 -- order ID: 4
Press any key to continue...

We can see that the syntax of left Outer Join is further complicated and the results are slightly different.

(1). Syntax:

From O2 in OS. defaultifempty (
New Order {orderid = 0, customerid = 0, products = new list <product> ()})

The main difference is that the above one sentence. The defaultifempty method is used to define the predefined default value when the query record is empty. Then retrieve the child element from the collection.

(2). From the result: WeTraversalWhen querying results, we can find that left join is similar to inner join results in "flat", but the results returned by Group join are hierarchical.

 

Question:

Because C # Is Object-Oriented, the relationship between data is often realized through the external system between objects. Sometimes two relationships can be expressed without the join keyword,

Therefore, not many join keywords are used in the actual LINQ query expression.

 

Intra-site connection:

Advantages of LINQ