Microsoft & .NET.NETCreating a Service with Windows Communication Foundation and LINQ to SQL

Creating a Service with Windows Communication Foundation and LINQ to SQL content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.


The .NET framework has thousands of discrete classes that are well-organized by namespaces. The .NET framework also has large chunky bits that are whole sub-systems within the framework. When you start combining these sub-systems, you get powerful solutions very quickly. In this article, you’ll combine Windows Communication Foundation (WCF) introduced in .NET 3.0 and LINQ to SQL introduced in .NET 3.5 and learn how to implement a service on top of LINQ and SQL server with astonishingly few lines of code.

Using the WCF Service Application Applet

When the concept of wizards first showed up, I didn’t like them that much. They seemed to produce a bunch of code that looked like magic, but this code was sometimes hard to modify and hard to reproduce. Newer wizards in Visual Studio with the .NET framework use framework code, producing lucid, and easy to follow bits of code. So, although you could create a WCF solution from scratch, let Visual Studio generate the starter code for you.

As a refresher, WCF is a general upgrade to Web Services and .NET Remoting as a homogenous solution. That is, you no longer write ASP.NET Web Services one way and use .NET Remoting another way; instead, you use WCF and the same style of programming and attributes regardless of your transport protocol (or, regardless of how you move the data around).

WCF uses the concept of a ServiceContract and a DataContract. The ServiceContract specifies the behaviors provided by your service and the DataContract describes data that will be introduced by the service. If you select File|New Project and pick WCF Service Library—in Visual Studio 2008—the templates and wizards will stub out all of the basic elements, demonstrating a sample ServiceContract and DataContract. This stubbed solution is ready to compile and test.

The WCF template defines an interface attributed with the ServiceContractAttribute, a class that is attributed with the DataContractAttribute, and a second a class that implements the ServiceContract interface. The DataContract class is a dummy class representing a composite type showing you how to implement custom types to be used in your service. Each behavior that you want to publish is described in the ServiceContract as a method and attributed with the OperationContractAttribute. Each property that you want to be serialized in the composite type—the DataContract—is attributed with the DataMemberAttribute. In short, OperationContractAttributes are callable by service consumers and DataMemberAttributes are serialized and accessible by those same consumers.

If you change the name of service class,you must update the reference in the web.config and in the associated .svc file.

Because Visual Studio stubs out all of this for you, you can move on to the code you have to write to implement a useful service.

Programming with LINQ to SQL Behind a WCF Service

Suppose now that you want to use LINQ to SQL behind the service. You will need a database (you’ll use Northwind) and some basic plumbing for LINQ to SQL. The two basic LINQ to SQL elements you will need are a DataContext, representing the connection to the database, and an entity class (or DataSet) attribute with the TableAttribute. The TableAttribute indicates that the annotated class can be populated by the LINQ to SQL plumbing.

The key here is that the DataContract and the LINQ to SQL entity can be the same composite type. That is, you define one class and attribute it with WCF and LINQ to SQL attributes, resulting in both technologies being supported in the same class.

For your purposes, you will define a service that looks up a customer in the Northwind Traders database. The code that follows is bootstrapped with the WCF Visual Studio wizard template and the LINQ to SQL code was added. Listing 1 starts you off with the WCF ServiceContract.

Listing 1: The WCF ServiceContract definition (an interface).

<ServiceContract()> _
Public Interface ICustomerService

   <OperationContract()> _
   Function GetCustomer(ByVal customerID As String) As Customer

End Interface

Listing 2 implements the ServiceContract by providing an implementation of for GetCustomer.

Listing 2: A simple WCF ServiceContract that means you will be defining a service that returns customers by CustomerID.

Imports System.Data.Linq

Public Class CustomerService
   Implements ICustomerService

   Public Sub New()
   End Sub

   Public Function GetCustomer(ByVal customerID As String) _
      As Customer Implements ICustomerService.GetCustomer

      Dim northwind As Northwind = New Northwind()
      Dim customers As Table(Of Customer) = _
          northwind.GetTable(Of Customer)()

      Dim customer = (From cust In customers _
                      Where cust.CustomerID = customerID _
                      Select cust).First()

      Return customer

   End Function
End Class

To suport the LINQ code, a refernce to System.Data.Linq is added to the WCF library project. The LINQ code is a SQL-like query that shows (among other things) that you will need a class Northwind and a Table Customer. The rest is a LINQ query.

LINQ queries start with the From clause and end with a Select statement. You can think of this query as slecting customer by CustomerID. The .First() method call is an agrgegate operation that returns the first customer from the result set. (Because CustomerIDs are unique, this code should return just one Customer as long as the ID is valid.)

Breaking down the LINQ query, the From clause defines a range cust and the searchable collection, customers. The Where predicate filters the query, and the Select clause builds the result collection. The parentheses means to treat the LINQ query result as an object and call First on it. The return type of the LINQ query in this example is IEnumerable(Of Customer) and the extension method First is defined for IEnumerable(Of T). For all intents and purposes, this means any class that implements IEnumerable(Of T) can return from a LINQ query, which is basically any collection. (For more on LINQ, LINQ to SQL, and extension methods check out my new book LINQ Unleashed for C# from Sams, August 2008.)

The remaining two steps are to define the DataContract and entity Table representing Customer objects and implement the DataContext to work with LINQ to SQL. All of these elements are implemented in the WCF service. Listing 3 implements the DataContext.

Listing 3: The DataContext, which represents all of the SQL and ADO.NET plumbing you no longer need to write if you use LINQ to SQL.

Public Class Northwind
   Inherits DataContext

   Private Shared ReadOnly connectionString As String = _
      "Data Source=BUTLER;Initial Catalog=Northwind; _
                          Integrated Security=True"

   Public Sub New()
   End Sub
End Class

It doesn’t look like much, but if you assign Console.Out to Northwind.Log you will see that the LINQ to SQL plumbing handles connections to the database and conversions of LINQ queries to SQL queries. You are not required to write the code that uses command, adapter, or connection objects.

Listing 4 defines the DataContract for WCF and the entity Table for LINQ to SQL in a single class, Customer.

Listing 4: The DataContract and LINQ to SQL entity class are defined as a basic custom class with a few additional attributes.

<DataContract()> _
<Table(Name:="Customers")> _
Class Customer

   Private _customerID As String
   <DataMember()> _
   <Column()> _
   Public Property CustomerID() As String
         Return _customerID
      End Get
      Set(ByVal Value As String)
         _customerID = Value
      End Set
   End Property

   Private _customerName As String
   <DataMember()> _
   <Column(Name:="CompanyName")> _
   Public Property CustomerName() As String
         Return _customerName
      End Get
      Set(ByVal Value As String)
         _customerName = Value
      End Set
   End Property
End Class

Listing 4 contains a basic class that maps two properties to two columns in the Northwind Customers table. The TableAttribute on the class tells LINQ to SQL which table (or view or stored procedure) to get the data from. The ColumnAttribute maps properties to columns in that table. If the property types and names match the columns, no named arguments are needed for the ColumnAttribute. The DataContract on the class and DataMember on the properties are present for WCF. These tell WCF that this type needs to be represented when clients consume this web service.

This is all you need to do to get data from a SQL database and return it from a service.

Consuming the LINQ to SQL and WCF Service

Now, create a console application. You can, of course, consume a service with any application, but keep it simple for now.

In your console application, add a reference to the CustomerService using Project|Add Service Reference in Visual Studio (see Figure 1). (Your port will change, but that doesn’t matter.)

Figure 1: Add as Service reference to your client application.

If you want to debug from your client into your service, set debug=true in the WCF service web.config file.

Finally, add an Imports statement for your service, create an instance of the service client object, and invoke the service. Listing 4 demonstrates.

Listing 4: Add a reference to the service, create the client wrapper object, and call the method—GetCustomer—defined by the service contract.

Imports CallService.CustomerServiceReference

Module Module1

   Sub Main(

      Dim service As CustomerServiceClient = _
         New CustomerServiceClient


   End Sub

End Module

I didn’t count the number of lines for the whole sample, but it’s not very many. What is interetsing is that the example goes from a console client, through a service, to a SQL Server database with very modest effort. The upshot is you get to focus on the problem space and the enhancements to the .NET framework do a lot of the heavy lifting for you.

Updates are supported by sending the Customer back to the service, attaching it to the DataContext, and calling SaveChanges. For roundtrip examples, stay tuned for future articles or pick up a copy of my book LINQ Unleashed for C#. The book is self-explanatory, so VB programmers should be able to decipher the C# code.


In this article, you touched up generics, extension methods, interfaces, services, LINQ to SQL, SQL Server, and distributed programming but the technical effort on the programmer’s part is relatively modest.

Ten years ago, to do the same thing might have taken a week of effort. In .NET 3.5, more of the plumbing is handled by the framework and that’s a very good thing.

About the Author

Paul Kimmel is the VB Today columnist for and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#; preorder your copy today at Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories