Join in LINQ to SQL (reprint)

Http://www.cnblogs.com/ASPNET2008/archive/2008/12/21/1358152.html

Join is more practical and easy to accept for friends who like to write SQL. In LINQ to SQL, write a multi-table query, you can also write a join, but it has its own syntax requirements, the semantics are the same, below I would like to the LINQ to SQL join in the most basic form: is the simplest, of course, there are other aspects of the content, such as: How to add filter conditions, How to group, how to sort and so on, in order to simply say the use of join, here is simplified.

From C in Customers joins P in Purchases on c.id equals P.customerid select C.name + " Bought a " + p.description

Generated sql:

SELECT ([t0].[ Name] + @p0) + [t1].[ Description] As [value] from [Customer] as [t0] INNER joins [Purchase] as [T1] on ([t0].[ ID]) = [t1].[ CustomerID]

By generating SQL, we can see very clearly that the inner join is shown, and its basic requirements are good: 1: Contains the join and on keywords, and if only join is not on, it will report a syntax error.

2: Foreign key is associated with the keyword equals, but not as an equal to SQL.

3: The field that the call is displayed must be displayed. That is, the Select field appears, or an error occurs.

the same point of LINQ to SQL as traditional sql:

The use of 1:join does not concern whether the primary table is in front or behind, and the final result is the same. We can also write the above query.

From P in Purchases joins C in Customers on P.customerid equals c.id select C.name + " bought a " + p.description;

2:LINQ to SQL also supports more than two tables of associations,

From cin Customers join P in< Span style= "color: #000000;" > Purchases on c.id equals p.customerid           // first Join join Pi Span style= "color: #0000ff;" >in Purchaseitems on p.id equals pi. purchaseid     // second join< Span style= "color: #008000;" > select new {    c.name, P.description, Pi. Detail}

the difference between LINQ to SQL and traditional sql:

1:LINQ to SQL join, if withinto, can be in advance to filter the table operation, instead of waiting for the two tables to Cartesian product generated virtual table after the join on the conditional filtering.

Code From the C in Customers joins P in purchases.where (P2 = P2. Price > c.id, equals P.customerid into custpurchases where custpurchases.any () s Elect new {CustName = c.name, custpurchases} /c8>

Generated sql:

Code SELECT [T0]. [Name] As [CustName], [T1]. [ID], [T1]. [CustomerID], [T1]. [Date], [T1]. [Description], [T1]. [Price], (SELECT COUNT (*) from [Purchase] as [T3] WHERE ([t0].[ ID])= [t3].[ CustomerID]) and ([t3].[ Price] > @p0)) as [value] from [Customer] as [t0] the left OUTER joins [Purchase] as [T1] on ([t0].[ ID]) = [t1].[ CustomerID]) and ([t1].[ Price] > @p0) where EXISTS (SELECT NULL as [EMPTY] from [Purchase] as [T2] where ([t0].[ ID]) = [t2].[ CustomerID]) and ([t2].[ Price] > @p0)), ORDER by [T0]. [ID], [T1]. [ID]

2:linq to SQL contains the concept of a groupjoin, which is defined by MSDN:

Queryable..::. GroupJoin Method: Associates two sequences of elements based on key equality and groups the results. This generally occurs in a one-to-many situation, with the primary key of the main table as the grouping object, and the primary key of the main table to query out the collection of child tables.

Take the guests and the consumer records: a guest will have a lot of spending, we want to get all the customer's consumption and guest name, that is, the outermost collection object is all guest information, and the guest's consumption information through a EntitySet collection to reflect. In traditional SQL, it is impossible to pass a query, because the traditional SQL returns only a specific collection, which can no longer contain child records. LINQ to SQL can do this by using the EntitySet property of the primary table record to contain a collection of child tables.

var list = (from C in DB. Customers join P in db. Purchases on c.id equals P.customerid to custpurchases select new {custpurchases,c.name}). ToList ();

A

It can be very clear that the contents of the puchase of the child table appear in the final result with the collection puchase, and the other column is the customer name. We can easily access some of the user's information.

//All consumption records for the first user List<Purchase> _list = List[0].custpurchases. Tolist<purchase // Take the first user's first consumption record price decimal Dprice = _list[0]. Price;

It can be seen that the LINQ mode has a great improvement to the development efficiency, which is completely object-oriented, logical and readable.

The corresponding SQL:

SELECT [T1]. [ID], [T1]. [CustomerID], [T1]. [Date], [T1]. [Description], [T1]. [Price], (SELECT COUNT (*) from [Purchase] as [T2] WHERE ([t0].[ ID]) = [t2].[ CustomerID]) as [value], [t0]. [Name] From [Customer] as [t0] left OUTER JOIN [Purchase] as [T1] on ([t0].[ ID]) = [t1].[ CustomerID] ORDER by [t0]. [ID], [T1]. [ID]

: Two, traditional SQL appears to be a complete collection, and it has no concept of sub-objects.

Summary: All the above statements can be replaced with equivalent lambda, but if it is like me beginners, total sense of lambda expression is particularly awkward, with SQL-like statements will be easy to accept. Joins can be converted to each other SelectMany the previous article. To create a left-out join for a join, you can also use DefaultIfEmpty () to achieve it, and interested friends can try it.

Join in LINQ to SQL (reprint)