LINQ to SQL queries and SQL commands

ArticleDirectory

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

Here we will introduce that the query by using LINQ to SQL is insufficient to meet the needs of specialized tasks. We can use the executequery method to execute the query by using LINQ to SQL, and then convert the query results to objects directly.

There are a lot of things worth learning about using LINQ to SQL. Here we will mainly introduce how to directly execute SQL commands and so on.

Directly execute the query using LINQ to SQL

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

 
 

  
  
 
  
VaRProducts=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 LINQ to SQL query and map the rows to the sequence of the product object.

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 setUnitpriceUnitprice= 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.

 

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.

VaRQ =
FromPInDB. Products. asenumerable ()
WhereIsvalidproduct (P)
SelectP;
 

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.

 
VaRQ =
FromCInDB. MERs
WhereC. City ="London"
SelectC;
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>.

 
VaRQ =
FromEInDB. Employees
WhereE. hiredate> =NewDatetime(1994, 1, 1)
SelectE;
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>.

VaRQ =
FromPInDB. Products
WhereP. unitsinstock <= P. reorderlevel &&! P. discontinued
SelectP;
Dictionary<Int,Product> Qdictionary =
Q. todictionary (P => P. productid );
Foreach(IntKeyInQdictionary. 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

Based on ADO. netProgramThe service provided by the model. Therefore, we canCodeIntegrates with existing ADO. Net Applications to migrate the current ADO. Net solution to 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:
SqlconnectionNwindconn =NewSqlconnection(Connstring );
Nwindconn. open ();
//... Other ADO. NET data operation code ...//
// Create a datacontext using the existing ADO. net connection:
NorthwindInterop_db =NewNorthwind(Nwindconn );
VaROrders =
FromOInInterop_db.orders
WhereO. Freight> 500.00 m
SelectO;
// Return freight> MB 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 the transaction scope memory is connected to multiple connections.

 
Using(TransactionscopeTS =NewTransactionscope())
{
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;
 // Query data by using LINQ to SQL
// Create a standard ADO. net connection:
  Sqlconnection Nwindconn = New  Sqlconnection (Connstring );
Nwindconn. open ();
 // Create a datacontext using the existing ADO. net connection:
  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 an error occurs, all operations are rolled back.
  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.

 
VaRProducts = 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.