Uncovering LINQ to SQL with Linqpad
You may be curious about the
T-SQL that LINQ is generating for this .NET expression. To reveal this
mystery, you can use two tools: SQL Server Profiler and Linqpad. To use
Profiler, launch the application from the Windows Start menu, create a
new trace using the File menu (select New Trace), and connect to your
database of choice. On the ensuing Trace Properties dialog, select the
trace template called T-SQL_SPs, start the trace, and then run the LINQ statement found in Listing 2. The T-SQL that LINQ generates is revealed in the TextData column.
Alternatively, you can download and use the amazing Linqpad from http://www.linqpad.net. Visit the site and download Linqpad.exe.
Save it to a folder that you will remember. As of this writing (version
1.35), Linqpad does not have or need an installation program; the
download itself is the entire self-contained application.
After you download Linqpad,
start it. Click on the Add Connection link at top left of the
application’s main window; then add a connection to your local server.
Next, select the AdventureWorks2008
database using the drop-down at the top left. In the query window, type
the following LINQ statement (Hint: This is the same as shown in Listing 2, minus the method signature, input parameter, and Context object):
from p in Products
join r in ProductReviews
on p.ProductID equals r.ProductID
select r
Click the green arrow button
or press F5. Your query results show up in a visually friendly tabular
format in the results area below the query window. Notice just above the
results area there is a button bar with four buttons: Results, Δ; (lambda expression), SQL, and IL. You’ve seen what shows up with the default setting, Results. Δ; reveals the underlying anonymous functions upon which LINQ relies. IL shows you the CLR Intermediate Language code that your LINQ expression generates. Finally, SQL
shows the resultant T-SQL, of main concern to you now. Select SQL and
press F5 again. Your results should match the code shown in Figure 3.
You can also use the Analyze SQL menu option (above the results area)
to jump into SSMS to run your query, or you can even run the T-SQL
itself via Linqpad.
Next, let’s examine an insert query using LINQ to SQL, as shown in Listing 3.
Listing 3. An Insert Query Using LINQ to SQL
public void AddProductReview( int ForProductId,
string Comments,
string Email,
int Rating,
string Reviewer
)
{
AdventureWorks2008DataContext Context =
new AdventureWorks2008DataContext();
ProductReview NewReview =
new ProductReview()
{
Comments = Comments,
EmailAddress = Email,
ModifiedDate = DateTime.Now,
ProductID = ForProductId,
Rating = Rating,
ReviewDate = DateTime.Now,
ReviewerName = Reviewer
};
Context.ProductReviews.InsertOnSubmit(NewReview);
Context.SubmitChanges();
//Check the new review ID:
if (NewReview.ProductReviewID > 0)
{
Debug.WriteLine(
string.Format(
"Success! Added new ProductReview with ID#{0}",
NewReview.ProductReviewID
)
);
}
}
|
Let’s go over this code in detail. First, you can see that the input parameters to the new AddProductReview method include a ProductReviewId as well as most of the properties that make up a row in Production.ProductReview. Next, using C#’s new new syntax, you instantiate a ProductReview object (NewReview) representing a row to be added to Production.ProductReview. To make the insertion happen, you again rely on the DataContext object (Context). The syntax Context.ProductReviews indicates that the target table is Production.ProductReview.
When you call InsertOnSubmit with your NewReview object as its parameter, your new review is added to the table when you call SubmitChanges (one line further down in the example). After that call, you can check your object to see if its ProductReviewID property was magically populated due to the fact that the row was created in the database (because ProductReviewID
is bound to the table’s primary key, which is an auto-incremental
identity column). LINQ is great in this way because it keeps the
contents of your objects and data tables in sync.
The next listing, Listing 4, illustrates how to perform a delete using LINQ to SQL.
Listing 4. Deleting Rows Using LINQ to SQL
public void DeleteProductReview(int ProductReviewId)
{
AdventureWorks2008DataContext Context =
new AdventureWorks2008DataContext();
ProductReview Review
= (from m in Context.ProductReviews
where m.ProductReviewID == ProductReviewId
select m).FirstOrDefault();
if (Review != null)
{
using (TransactionScope Tran = new TransactionScope())
{
Context.ProductReviews.DeleteOnSubmit(Review);
Context.SubmitChanges();
}
}
}
|
To run Listing 4, you need to add a reference to System.Transactions and then add a using
statement for that namespace. This addition to the code illustrates one
way to use transactions with LINQ and also preserves the integrity of
your AdventureWorks2008 data.
Going over the example, the DeleteProductReview method takes a ProductReviewID
value as input. It then looks up that record using a LINQ query, just
as you would in T-SQL. If the record was found (that is, if the query
returns a non-null value), you then create a new transaction, in which
you delete the record by calling DeleteOnSubmit. Note that because you do not call the Complete method of the Tran object, the transaction is implicitly rolled back.
Listing 5 rounds out our LINQ examples by showing you how to update a set of rows.
Listing 5. Updating Rows Using LINQ to SQL
public void UpdateProductInventories(int Threshold, short NewQty)
{
AdventureWorks2008DataContext Context =
new AdventureWorks2008DataContext();
List<ProductInventory> InventoryItems
= (from m in Context.ProductInventories
where m.Quantity < Threshold
select m).ToList();
if (InventoryItems.Count > 0)
{
using (TransactionScope Tran = new TransactionScope())
{
foreach (ProductInventory Item in InventoryItems)
{
Item.Quantity = NewQty;
}
Context.SubmitChanges();
}
}
}
|
In this listing, the query operates on a range of rows, rather than just one. Using LINQ’s ORM magic, you select rows from Production.ProductInventory as a List of ProductInventory by matching your Threshold criterion against the current Quantity value of objects in DataContext.ProductInventories.
(Notice how LINQ even performs grammatically correct pluralization of
table names.) You iterate through each returned object (again, within
the scope of a transaction so as to keep AdventureWorks2008 intact) and then update the Quantity for each. Then you submit your changes. Very simple, very powerful, very much a timesaver.
Although this LINQ to SQL
primer covers the essentials, we highly recommend you dive deeper into
the .NET namespaces you’ve seen to uncover all the possibilities LINQ to
SQL has to offer.
