LINQ experience (13) -- convert the operators of the LINQ to SQL statements and convert the operators of ADO. NET and LINQ to SQL

Document directory

• 1. directly execute SQL query
• 2. directly execute SQL commands

Operator conversion 1. AsEnumerable: converts a type to a generic IEnumerable

You can use AsEnumerable <TSource> to return parameters of the generic IEnumerable type. In this example, LINQ to SQL (using the default generic Query) will try to convert the Query to SQL and execute it on the server. However, the where clause references the User-Defined client method (isValidProduct), which cannot be converted to SQL.
The solution is to specify the where client generic IEnumerable <T> to replace generic IQueryable <T>. You can call the AsEnumerable <TSource> operator to perform this operation.

var q =    from p in db.Products.AsEnumerable()    where isValidProduct(p)    select p;

Statement Description: In this example, AsEnumerable is used to implement IEnumerable on the Where client, instead of converting the default IQueryable to SQL on the server and executing the default Query <T>. This is necessary because the Where clause references the User-Defined client method isValidProduct, which cannot be converted to SQL.

2. ToArray: converts a sequence to an array.

Use ToArray <TSource> to create an array from the sequence.

var q =    from c in db.Customers    where c.City == "London"    select c;Customer[] qArray = q.ToArray();

Statement Description: In this example, the query is directly calculated as an Array Using ToArray.

3. ToList: converts a sequence to a generic list.

Use ToList <TSource> to create a generic list from the sequence. The following example uses ToList <TSource> to directly put the query results into a generic List <T>.

var q =    from e in db.Employees    where e.HireDate >= new DateTime(1994, 1, 1)    select e;List<Employee> qList = q.ToList();

4. ToDictionary: converts a sequence into a dictionary.

You can use the Enumerable. ToDictionary <TSource, TKey> method to convert a sequence into a dictionary. TSource indicates the type of the element in source; TKey indicates the type of the key returned by keySelector. It returns a Dictionary containing the key and value <TKey, TValue>.

var q =    from p in db.Products    where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued    select p;Dictionary<int, Product> qDictionary =    q.ToDictionary(p => p.ProductID);foreach (int key in qDictionary.Keys){    Console.WriteLine(key);}

Statement Description: In this example, the direct key expression of the query and key expression is directly calculated as a Dictionary <K, T>.

ADO. NET and LINQ to SQL

LINQ to SQL is based on the services provided by the ADO. NET provider program model. Therefore, we can mix the LINQ to SQL code with the existing ADO. NET application to migrate the current ADO. NET solution to the LINQ to SQL.

1. Connection

You can provide existing ADO. NET connections when creating a LINQ to SQL DataContext. All operations (including queries) on DataContext use the provided connection. If the connection has been enabled, the connection will remain in the open state after you use it. We can always access this Connection, and we can also use the Connection property to close it.

// Create a standard ADO. NET connection: SqlConnection nwindConn = new SqlConnection (connString); nwindConn. open ();//... other ADO. NET data operation code... //// use existing ADO. create a DataContext: Northwind interop_db = new Northwind (nwindConn); var orders = from o in interop_db.Orders where o. freight> 500.00 M select o; // return Freight> M Order nwindConn. close ();

Statement Description: In this example, the Northwind object is created using an existing ADO. NET connection. In this example, all orders with at least 500.00 freight are returned.

2. Transactions

When we have started our own database transactions and want to include DataContext, we can provide this transaction to DataContext.
The preferred way to create a transaction through. NET Framework is to use a TransactionScope object. By using this method, we can create distributed transactions executed across databases and other resource managers residing in the memory. The transaction scope can be started almost without resources. They promote themselves to distributed transactions only when there are multiple connections within the transaction scope.

using (TransactionScope ts = new TransactionScope()){    db.SubmitChanges();    ts.Complete();}

Note: you cannot use this method for all databases. For example, SqlClient connection cannot improve system transactions when used for SQL Server 2000 servers. The method is to automatically register the entire distributed transaction as long as it finds that there is a transaction scope in use.

The following example describes how to use a transaction. The same connection between the reusable ADO. NET command and DataContext is also described here.

Var q = from p in db. products where p. productID = 3 select p; // use LINQ to SQL to query Out // create a standard ADO. NET connection: SqlConnection nwindConn = new SqlConnection (connString); nwindConn. open (); // use existing ADO. create a DataContext: Northwind interop_db = new Northwind (nwindConn); SqlTransaction nwindTxn = nwindConn. beginTransaction (); try {SqlCommand cmd = new SqlCommand ("UPDATE Products SET" + "QuantityPerUnit = 'single item' WHERE ProductID = 3"); cmd. connection = nwindConn; cmd. transaction = nwindTxn; cmd. executeNonQuery (); interop_db.Transaction = nwindTxn; Product prod1 = interop_db.Products.First (p => p. productID = 4); Product prod2 = interop_db.Products.First (p => p. productID = 5); prod1.UnitsInStock-= 3; prod2.UnitsInStock-= 5; // This error cannot be negative interop_db.SubmitChanges (); nwindTxn. commit ();} catch (Exception e) {// if there is an error, all operations roll back the Console. writeLine (e. message);} nwindConn. close ();

Statement Description: This example uses an existing ADO. NET connection to create a Northwind object, and then shares an ADO. NET transaction with this object. This transaction is used to execute SQL commands through the ADO. NET connection and submit changes through the Northwind object. When a transaction is aborted due to a CHECK violation, all changes, including those made through SqlCommand and those made through the Northwind object, will be rolled back.

3. directly execute the SQL statement 1. directly execute the SQL query

If the LINQ to SQL query is insufficient to meet the needs of specialized tasks, we can use the ExecuteQuery method to execute the SQL query, and then directly convert the query results into objects.

var products = db.ExecuteQuery<Product>(    "SELECT [Product List].ProductID,"+    "[Product List].ProductName " +    "FROM Products AS [Product List] " +    "WHERE [Product List].Discontinued = 0 " +    "ORDER BY [Product List].ProductName;");

Statement Description: This example uses ExecuteQuery <T> to execute any SQL query and map the rows to the sequence of the Product object.

2. directly execute SQL commands

When using DataContext to connect, you can use ExecuteCommand to execute SQL commands that do not return objects.

db.ExecuteCommand    ("UPDATE Products SET UnitPrice = UnitPrice + 1.00");

Statement Description: execute any SQL command using ExecuteCommand. In this example, the unit price of all products is increased by 1.00 in batch.

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

 

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.