LINQ Experience (--LINQ) to SQL statement operator Conversions and ADO. LINQ to SQL

The operator converts 1. AsEnumerable: Converting a type to a generic IEnumerable

Use asenumerable<tsource> to return the number of parameters typed as generic IEnumerable. In this demo sample, LINQ to SQL (using the default generic query) attempts to convert the query to SQL and run it on the server.

But the WHERE clause refers to the user-defined client method (Isvalidproduct), and this method cannot be converted to SQL.
The workaround is to specify where's client-generic ienumerable<t> implementation to replace the generic iqueryable<t>. You can run this operation by calling the asenumerable<tsource> operator.

Q =    db. Products.asenumerable ()    isvalidproduct (p)    p;

Statement Description: This example is to use asenumerable to use the where clientienumerable implementation, instead of the default IQueryable will be converted to SQL on the server and run the default query<t> implementation. This is necessary because the WHERE clause refers to the user-defined client method Isvalidproduct, which cannot be converted to SQL.

2.ToArray: Converting a sequence to an array

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

Q =    db. Customers    "London"    C;  Customer[] Qarray = Q.toarray ();

Statement Description: This sample uses ToArray to calculate the query directly to an array.

3.ToList: Converting a sequence to a generic list

Use tolist<tsource> to create a generic list from a sequence. The following demo sample uses Tolist<tsource> to put the computed results of a query directly into the generic list<t>.

Q =    db.    EmployeesDateTime(1994, 1, 1)    e;  List<Employee> qlist = q.tolist ();

4.ToDictionary: Converting a sequence into a dictionary

Using Enumerable.todictionary<tsource, the Tkey> method can convert a sequence into a dictionary. TSource represents the type of the element in source; TKey represents the type of key returned by Keyselector. It returns a Dictionary<tkey that includes a key and a value, tvalue>.

varQ = fromPinchDb. ProductswhereP.unitsinstock <= p.reorderlevel &&!p.discontinuedSelectPDictionary<int,Product> qdictionary = q.todictionary (p = p.productid);foreach(intKeyinchQdictionary.keys) {Console. WriteLine (key);}

Statement Description: This sample uses todictionary to directly evaluate the query and key expression direct key expressions as dictionary<k, t>.

Ado. NET and LINQ to SQLLINQ to SQL is based on the services provided by the ADO provider model. As a result, we were able to mix LINQ to SQL code with existing ADO-out applications to migrate the current ADO solution to LINQ to SQL.

1. Connect

When you create a LINQ to SQL DataContext, you can provide an existing ADO connection. All operations on DataContext (including queries) Use this connection as provided.

Assuming that this connection is already open, LINQ to SQL keeps its open state when you are done with this connection. We are always able to access this connection. You can also use the Connection property to close it yourself.

//Create a new standard ADO connection:SqlConnectionNwindconn =NewSqlConnection(connstring); Nwindconn.open ();// ... Other ADO Data Manipulation code ...////uses an existing ADO connection to create a DataContext:Northwindinterop_db =NewNorthwind(nwindconn);varOrders = fromOinchinterop_db. OrderswhereO.freight > 500.00MSelectO//return order to freight>500.00mNwindconn.close ();

Statement Description: This sample creates a Northwind object using a pre-existing ADO connection. The query in this example returns all orders with a minimum shipping cost of 500.00.

2. Business

When we have started our own database transactions and we want DataContext to be included. We are able to provide this transaction to DataContext.

The preferred method for creating transactions through the. NET Framework is to use the TransactionScope object. By using this method, we are able to create a distributed transaction that runs across databases and other resource managers residing in memory.

The scope of the transaction is almost no resource required to start. They promote themselves to distributed transactions only if there are multiple connections within the scope of the transaction.

(TransactionScope()) {    db. SubmitChanges ();    Ts.complete ();}

Note: This method cannot be used for all databases.

Like what. SqlClient connections cannot elevate system transactions when used against SQL Server server. The way it takes is. Only if it finds out that a transaction scope is used, it will voluntarily register itself with the full distributed transaction.

Use the following example to illustrate the use of transactions. Over here. It also shows the reuse of the same connection between the ADO and DataContext.

varQ = fromPinchDb. ProductswhereP.productid = = 3SelectP//using LINQ to SQL queries//create a new standard ADO:SqlConnectionNwindconn =NewSqlConnection(connstring); Nwindconn.open ();//Use an existing ADO connection to create a DataContext:Northwindinterop_db =NewNorthwind(nwindconn);SqlTransactionNwindtxn = Nwindconn.begintransaction ();Try{SqlCommandcmd =NewSqlCommand("UPDATE Products SET"+"QuantityPerUnit = ' Single item ' WHERE ProductID = 3"); Cmd.    Connection = nwindconn; Cmd.    Transaction = NWINDTXN; Cmd.    ExecuteNonQuery (); interop_db. Transaction = NWINDTXN;ProductProd1 = interop_db. Products.first (p = P.productid = = 4);ProductPROD2 = interop_db.    Products.first (p = P.productid = = 5); Prod1.    UnitsInStock-= 3; ProD2. UnitsInStock-= 5;//This has an error and cannot be negativeinterop_db.    SubmitChanges (); Nwindtxn.commit ();}Catch(ExceptionE) {//Suppose there is an error. Rollback of all operationsConsole. WriteLine (e.message);} Nwindconn.close ();

Statement Description: This sample creates a Northwind object using a pre-existing ADO connection and then shares an ADO transaction with this object. This transaction is used both to run SQL commands over an ADO connection. It is also used to commit changes through the Northwind object. When a transaction is aborted because of a CHECK constraint violation. All changes are rolled back. Contains changes made through SqlCommand. and changes made through the Northwind object.

3. Run SQL statements directly 1. Run SQL queries directly

Assuming that LINQ to SQL queries are insufficient to meet the needs of specialized tasks, we can use the ExecuteQuery method to run SQL queries and then convert the results of the query directly into objects.

var  products = db. Executequery<product  > ( " SELECT [Product List]. ProductID, " + " [Product List]. ProductName " + " from the products as [Product List] " + " WHERE [Product List]. Discontinued = 0 " + " ORDER by [Product List]. ProductName; "); 

Statement Description: This sample runs random SQL queries using executequery<t> and maps the resulting rows to a sequence of Product objects.

2. Run the SQL command directly

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

Db. ExecuteCommand    ("UPDATE products SET UnitPrice = UnitPrice + 1.00");

LINQ Experience (--LINQ) to SQL statement operator Conversions and ADO. LINQ to SQL