Programming with LINQ to SQL

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.

Summary

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 pkimmel@softconcepts.com.

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.

Page 2 of 2