LINQ to SQL learning experience

This article introduces LINQ to SQL, there may be many people do not understand LINQ to SQL, there is no relationship, after reading this article you certainly have a lot of gains, hope this article can teach you more things.

Linq to SQL is a new technology developed by Microsoft to address data!=object issues. In the author's series of articles, it has been done a lot of introduction. Now, the author will discuss its merits and demerits from the perspective of experience.

1. Advantages of LINQ to SQL

Before LINQ to SQL was introduced, we just formed a string of SQL statements, and then, via ADO, passed to SQL Server, Returns the result set. The flaw here is that if you have a problem with your SQL statement, you will only know it until you run it. And not all of them understand the database. Linq to SQl is available in all projects around data. Especially when a SQL Server expert is missing from the project, the powerful features of LINQ to SQL can help us quickly complete the project. The introduction of LINQ to SQL is a relief from cumbersome technical details and a greater focus on the logic of the project. The advent of LINQ to SQL greatly reduces the door of the development of database applications, which in essence is to frame the data access layer beforehand, which will accelerate the development of database application. LINQ to SQL frees up a lot of programmers to put more effort into business logic and code, not databases. For beginners, Linq to SQL allows them to quickly enter the field of database application development, saving training costs.

The implementation of LINQ to SQl is based on the ADO and c#2.0. It automatically translates the SQL statement and creates the result set as an object and returns. As we can see here, the SQL statements sent to the SQL Server side are automatically generated by LINQ to SQL. This is undoubtedly a boon for those who do not understand SQL. Second, LINQ to SQL statements are checked during compilation. Instead of a run-time check. In this way, there is a problem and can be changed in time, not until the runtime discovers the problem. Third, Linq to SQL is for object manipulation, more in line with today's OO calls.

Before LINQ to SQl, there was hibernate in the Java domain, with nhibernate technology in the net domain to implement object/relational persistence and query services. It has those advantages over nhibernate. First, the innuendo code is automatically generated. VS2008 provides a sqlmetal and or designer two tools to complete this step. And in NHibernate, you have to write it by hand. Second, the innuendo code has more options. NHibernate can only configure the database information in one XML, and LINQ to SQL in two ways, one is put into XML, we call Externl Mapping, another is in the form of attribute, exist in the various property. Of course, I have not used the nhibernate, but from the information to get these messages, so I can not give more comparisons.

2. The drawbacks of LINQ to SQL

A long time ago, a netizen asked such a question. He has a DataView on the interface, which is bound with some columns, and then he tick that column to sort by a column. The parameter it returns is the name of the column. Then ask me how to use the dlinq to achieve.

In the previous era of splicing SQL statements, this is very simple, an "order by" + string, you want to press what row to row. And now DLinq is a property of an object, it is impossible to splice. The answer I gave him was this.

LINQ uses GROUP by experience summary

When you learn LINQ, you often encounter LINQ using the group by problem, which explains how LINQ uses the group by problem.

1. Counting

1. var Q =
2. from P in db. Products
3. Group p by P.categoryid into G
4. Select New {
5. G.key,
6. numproducts = g. Count ()
7. };

Statement Description: LINQ uses Group by and count to get the number of products in each CategoryID.

Description: First by CategoryID classification, remove the CategoryID value and the number of individual products.

2. With conditional count

1. var Q =
2. from P in db. Products
3. Group p by P.categoryid into G
4. Select New {
5. G.key,
6. numproducts = g. Count (p => p.discontinued)
7. };

Statement Description: LINQ uses Group by and count to get the number of discontinued products per CategoryID.

Description: First by CategoryID classification, remove the CategoryID value and the number of broken goods of each category. In the Count function, a lambda expression is used, and p in the lambda expression represents an element or object in the group, which is a product.

3.Where limit

1. var Q =
2. from P in db. Products
3. Group p by P.categoryid into G
4. where G.count () >= 10
5. Select New {
6. G.key,
7. ProductCount = g. Count ()
8. };

Statement Description: Based on the product's ―id grouping, the product number is queried for more than 10 of the ID and product quantity. This example uses the WHERE clause after the GROUP BY clause to find all categories with at least 10 products.

Description: A Where condition is nested at the outermost layer when translated into an SQL statement.

4. Multi-column (multiple Columns)

1. var Categories =
2. from P in db. Products
3. Group p by New
4. {
5. P.categoryid,
6. P.supplierid
7. }
8. into G
9. Select New
10. {
11. G.key,
12. G
13. };

Statement Description: LINQ uses Group by to group products by CategoryID and SupplierID.

Description: Both by product classification, and by supplier classification. After by, new comes out an anonymous class. Here, key is essentially a class object, and key contains two Property:categoryid, SupplierID. With G. Key.categoryid can traverse the value of CategoryID.

5. Expressions (expression)

1. var Categories =
2. from P in db. Products
3. Group p by new { Criterion = p. UnitPrice > ten} into G
4. Select G;

Statement Description: LINQ uses GROUP by to return two series of products. The first sequence contains products with a unit price greater than 10. The second sequence contains products with a unit price less than or equal to 10.

Description: The product unit price is greater than 10 classification. The results are divided into two categories, greater than the one, less than and equal to another class.