Programming with LINQ to SQL, Page 2
Query SQL Server with LINQ to SQL
Now, all you have to do to query orders or details is to write some queries. Listing 2 is the complete example. This listing uses the ORMs defined in Listing 1, the DataContext, and a LINQ query to display all of the ProductID's from the [Order Details] table to the console.
Listing 2: The complete listing including the TableAttributes, ColumnAttributes, ORM definition, the DataContexts, and a LINQ query to select the data.
Imports System.Data.Linq Imports System.Data.Linq.Mapping Imports System.IO Module Module1 Public connectionString As String = _ "Data Source=.\SQLEXPRESS;AttachDbFilename=" + _ "C:\Books\Addison Wesley\LINQ\Northwind\northwnd.mdf;" + _ "Integrated Security=True;Connect Timeout=30; User Instance=True" Sub Main() ' Use LINQ to SQL to get the data - context represents ' the database Dim orderContext As DataContext = New DataContext(connectionString) Dim detailsContext As DataContext = New DataContext(connectionString) ' generic table does the ORM association Dim orders As Table(Of Order) = orderContext.GetTable(Of Order)() Dim details As Table(Of OrderDetail) = orderContext.GetTable(Of OrderDetail)() Dim allDetails = From detail In details _ Select detail For Each d In allDetails Console.WriteLine(d.ProductID) Next Console.ReadLine() End Sub End Module <Table(Name:="Orders")> _ Public Class Order <Column()> _ Public OrderID AsInteger <Column()> _ Public CustomerID As String <Column()> _ Public EmployeeID As Integer <Column()> _ Public OrderDate As DateTime <Column()> _ Public ShipCity As String End Class <Table(Name:="Order Details")> _ Public Class OrderDetail <Column()> _ Public OrderID As Integer <Column()> _ Public ProductID As Integer <Column()> _ Public UnitPrice As Decimal <Column()> _ Public Quantity As Int16 <Column()> _ Public Discount As Single End Class
I hope you see that this is quite a bit easier than using connections and DataReaders to populate entity classes.
LINQ to SQL is completely compatible with ADO.NET 2.0 so you can mix in LINQ with your existing ADO.NET code and blend both styles—LINQ and plain vanilla ADO.NET. And, of course, you can still use straight ADO.NET with stored procedures if you want.
LINQ to SQL lightens the load of ADO.NET programming. All you need to do is define entity maps—which you have done before—with the TableAttribute and ColumnAttribute, and use a DataContext to get the data. LINQ writes the SQL. In Part 2 of this article, you'll use this code to define a left join on the Orders and Order Details objects.
LINQ is completely compatible with ADO.NET 2.0, so you can use these new capabilities with or to replace existing code.
About the Author
Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C# from Sams due in July 2008. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at firstname.lastname@example.org.
If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org. Glugnet opened a users group branch in Flint, Michigan in August 2007. If you are interested in attending, check out the www.glugnet.org web site for updates.
Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.