Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Baker's Dozen: A 13-Step Crash Course for Using LINQ : Page 3

Start becoming familiar with the different areas of LINQ with the Microsoft Visual Studio "Orcas" Beta.


advertisement
Tip 4: Standard Querying Statements and Operators
Sometimes the best way to learn syntax is to see a number of different examples. Listing 2 demonstrates some fairly common database query requirements, such as sorting, string searching, date handling, and an equivalent of SELECT DISTINCT.

If you want to see the actual SQL code that LINQ to SQL generates, you can set a breakpoint over the anonymous type result and check the value after the statement executes.

Tip 5: More Querying Syntax and Operators
But wait, there's more! Listing 3 contains more code examples for common LINQ to SQL queries, such as UNION operations and EXISTS. LINQ to SQL also contains some additional nice features, such as Skip and Take functions and ANY/ALL operators. You can use Skip and Take to retrieve a range of rows within a result set (helpful for paging result sets) and ANY/ALL operators to return a result if a condition is true for any/all rows in a set of child records (e.g. find the vendors with ANY orders greater than 50,000, or find the vendors where ALL orders are greater than 50,000).

Tip 6: Converting an Anonymous Type Result to a DataTable
Many people who have written blogs on LINQ have discussed the fact that the result set, a new anonymous type object in .NET, cannot be used directly outside the method that creates it. Fellow MVP Don Demsak has a great quote about anonymous types: "it is just a projection…think of it as a shadow in a box—you can't take a shadow out of the box but you can take a picture of the shadow".

To work effectively with anonymous types in an application, especially a distributed one, you'll need the means to convert the anonymous type result set into something more usable. This tip shows you how to convert an anonymous type result to an ADO.NET DataTable, while the Tip 7 illustrates how to populate a custom List.

The early CTP versions of Orcas contained a function called ToDataTable() that allowed you to easily convert the result of a LINQ query to an ADO.NET DataTable. Unfortunately, Microsoft removed that function from the March 2007 CTP, as well as Orcas Beta 1. However, Listing 4 contains code for an extension method (ToADOTable) that can serve as a functional counterpart for ToDataTable. You can call it immediately after executing a query, as follows:

XML MVP Don Demsak has a great quote: "An anonymous type is just a projection, like a shadow in a box. You can't take a shadow out of the box, but you can take a picture of the shadow."

// Take the anonymous type result "NewResult" // and convert it to a DataTable DataTable dtResults = NewResult.ToADOTable( rec => new object[] {NewResult});

Tip 7: Converting Anonymous Types to IEnumerable or a List
Tip 6 just demonstrated how to direct the results of a LINQ query to an ADO.NET DataTable It seems that half the developers I meet use DataTables and DataSets, while the other half uses custom classes. Because the previous tip covered DataTables, I'll now show how to convert the results of a LINQ query to a custom class.

Listing 1 showed how to query for a list of vendors and the freight and order subtotals from each vendor's purchase orders. Suppose you want to pump those results into a collection of the following class:

public class VendorResults { public VendorResults(string Name, decimal TotFreight, decimal TotSubTot) { this.Name = Name; this.TotFreight = TotFreight; this.TotSubTot = TotSubTot; } public string Name { get; set; } public decimal TotFreight { get; set; } public decimal TotSubTot { get; set; } }

(Note that the class properties above don't contain any private fields. C# 3.0 provides a new capability called automatic properties that simplifies creating properties. Tip 12 covers automatic properties in more detail.)

Modify the query in Listing 1 so it returns an IEnumerable of type VendorResults. The method below returns an IEnumerable<VendorResults> type:



public IEnumerable<VendorResults> GetVendOrders() { IEnumerable<VendorResults> Orders = from Vendor in db.Purchasing_Vendor orderby Vendor.Name select new VendorResults ( Vendor.Name, Vendor.Purchasing_PurchaseOrderHeader.Sum( o => o.Freight), Vendor.Purchasing_PurchaseOrderHeader.Sum( o => o.SubTotal)); return Orders; }

I can then call the method as follows:

IEnumerable<VendorResults> oVendorResults = (IEnumerable<VendorResults>)this.GetVendOrders();

Finally, to pump the results into a List class, you simply need to iterate through the items in IEnumerable:

List<VendorResults> oListResults = new List<VendorResults>(); foreach (VendorResults oRec in oVendorResults) oListResults.Add(oRec);



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap