Implementation and difference of LINQ join & Group join & left join

Usage of the join keyword in the LINQ query expression:

 

Here I will first create an experiment case:

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; }}static List<Customer> customers;static List<Product> products;static List<Order> orders;public static void CreateEntities(){    customers = new List<Customer>{         new Customer(){ CustomerId = 1, Name = "CA", Age=13},        new Customer(){ CustomerId = 2, Name = "CB", Age=13},        new Customer(){ CustomerId = 3, Name = "CC", Age=13},        new Customer(){ CustomerId = 4, Name = "CD", Age=13}    };    products = new List<Product>{        new Product(){ ProductId = 1, Name = "PA", Origin="P1" },        new Product(){ ProductId = 2, Name = "PB", Origin="P2" },        new Product(){ ProductId = 3, Name = "PC", Origin="P1" },        new Product(){ ProductId = 4, Name = "PD", Origin="P3" }    };    orders = new List<Order>{        new Order(){ OrderId = 1 , CustomerId =1,             Products = new List<Product>{                 new Product(){ ProductId = 2, Name = "PB", Origin="P2" },                new Product(){ ProductId = 3, Name = "PC", Origin="P1" }            }},        new Order(){ OrderId = 2 , CustomerId =1,             Products = new List<Product>{                 new Product(){ ProductId = 3, Name = "PC", Origin="P1" },                new Product(){ ProductId = 4, Name = "PD", Origin="P3" }            }},        new Order(){ OrderId = 3 , CustomerId =3,             Products = new List<Product>{                 new Product(){ ProductId = 4, Name = "PD", Origin="P3" }            }},        new Order(){ OrderId = 4 , CustomerId =2,             Products = new List<Product>{                 new Product(){ ProductId = 1, Name = "PA", Origin="P1" },                new Product(){ ProductId = 4, Name = "PD", Origin="P3" }            }}    };}

 

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.

 

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

1. Inner join:

CreateEntities();var query = from c in customers            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 condition: C. customerid equals O. customerid can only use equals and cannot be expressed as =, =, 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) condition order: the order before C. 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 customers            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 customers            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:

When traversing the query results, we can find that left join is similar to inner join results that are "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