Integrating LINQ-to-SQL queries within a WCF Service

Introduction

This article shows how to define LINQ-to-SQL queries inside a WCF Service and create WCF clients to display data returned by a WCF Service method. In the sample application walkthrough given below, the WCF Service that includes a LINQ-to-SQL query is hosted on IIS, and a console application is used as a WCF client.

The development of this application integrating LINQ-to-SQL in a WCF service involves three major tasks:

1. Creating a WCF Service Website hosted on IIS
2. Configuring the WCF Service
3. Creating a WCF Console client using SvcUtil.exe

Note

The sample uses a SQL Server Express database file Sales.mdf in the web site’s App_Data folder. This database contains the “Product” table. Creating a database file and the relevant table in SQL Server 2005 Express is not the scope of this article. It is presumed that the readers have prior knowledge on how to do those things.

Task 1: Creating a WCF Service Website Hosted on IIS

• To create and host a WCF service on IIS, open Visual Studio 2008, select File -> New Web Site -> WCF Service; select HTTP, and specify http://localhost/MyLinqWcfServiceHost/ as location, and click OK. This would create a new web site, and the project files are stored in the [drive:]\inetpub\wwwroot folder.

Visual Studio generates two files: IService.cs and Service.cs in the App_Code folder. These two files contain the definitions for the WCF Service interface and the class implementing the interface, respectively. Modify these files accordingly to create a LINQ-to-SQL based WCF Service.

• Add a reference to System.Data.Linq.dll to the web site.
• Modify the IService.cs file as below. Note that the class definitions are created under the namespace MyWCFLinqService.
C#

using System.Data.Linq; 
using System.Data.Linq.Mapping; 
namespace MyWCFLinqService 
{ 
    [ServiceContract] 
    public interface ILinqService 

    { 
        [OperationContract] 
        List<Product> GetProductList(); 
    } 
    //...

In the above code, ILinqService is an interface that defines a method GetProductList. Note that the class has a ServiceContract attribute to specify that it is a service contract in WCF, and the method has a OperationContract attribute to indicate that it is a service operation. The method returns the generic, typed list of items from the Product table. In order to use the database and the tables, LINQ to SQL data access techniques are used.

The DataContext class is a LINQ to SQL class that acts as a conduit between a SQL Server database and the LINQ to SQL entity classes mapped to that database. The DataContext class contains methods and properties for connecting to a database and manipulating the data in the database.

• Include the following code in the IService.cs file; the Sales.mdf database is represented as a Sales class derived from DataContext. It includes the table definition for the Product table and a constructor that takes the connection string as a parameter.
C#

[DataContract] 
public class Sales : DataContext 
{ 
    public Table<Product> Products; 
    public Sales(string connstr) : base(connstr) 
    {
    }
}

You must apply the DataContractAttribute attribute to the types (classes, structures, or enumerations) that are used in the serialization and deserialization operations by the DataContractSerializer. A Windows Communication Foundation (WCF) infrastructure sends or receives messages by using the DataContractAttribute to any classes that hold and manipulate data sent in messages.

In order to access the Product table in the database, the TableAttribute attribute is used to designate a class as an entity class associated with a database table. You must also apply the DataMemberAttribute to any field, property, or event that holds values you want to serialize. By applying the DataContractAttribute, you explicitly enable the DataContractSerializer to serialize and deserialize the data.

• Add the following code in the IService.cs to create a class that associates to a database table:
C#

[DataContract] 
[Table(Name = "Product")] 
public class Product 

{ 
    int prodid; 
    string prodname, desc; 
    [DataMember] 
    [Column(IsPrimaryKey = true, Storage = "prodid")] 
    public int productid 
    { 
        get { return prodid; } 

        set { prodid = value; } 
    } 
    [DataMember] 
    [Column(Storage = "prodname")] 
    public string productname 
    { 
        get { return prodname; } 

        set { prodname = value; } 
    } 
    [DataMember] 
    [Column(Storage = "desc")] 
    public string description 
    { 
        get { return desc; } 

        set { desc = value; } 
    } 
}

• In the Service.cs file, define the class “LinqService” implementing “ILinqService”. While implementing the GetProductList method, write a LINQ query that retrieves the Product table data.
C#

using MyWCFLinqService; 
public class LinqService : ILinqService 

{ 
    public List<Product> GetProductList() 
    { 
        Sales db = new Sales(@"D:\MyLinqWCFServiceHost\App_Data\sales.mdf"); 
        IEnumerable<Product> productlist = from prod in db.Products select prod; 

        return productlist.ToList(); 
    } 
}

Task 2: Configuring the WCF Service

After defining the service contract and the data contract definitions, you need to configure the service to activate and host within a runtime environment that creates it and controls its context and lifetime. Typically, running a basic WCF Service involves the following steps:

1. Configuring a base address for the service.
2. Configuring endpoints for the service.
3. Hosting and opening the service.
4. Compiling and running the service.

Since our WCF Service, “LinqService”, is hosted as a web site on IIS, steps 3 and 4 can be ignored. To configure the base address and endpoints, add the following elements in the Web.Config file:

<system.serviceModel>
<services>
<service behaviorConfiguration="mexBehavior" name="LinqService">
<endpoint binding="wsHttpBinding" 
  bindingConfiguration="" name="myEndpoint"

contract="MyWCFLinqService.ILinqService">
</endpoint>
<host>
<timeouts closeTimeout="00:03:10" />

</host>
</service>
</services>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior">

<serviceMetadata httpGetEnabled="true"/>
<serviceDebug includeExceptionDetailInFaults="false"/>
</behavior>
<behavior name="mexBehavior">
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="false"/>

</behavior>
</serviceBehaviors>
</behaviors>
</system.serviceModel>

Actually, the base address is not specified here in the configuration as it takes the default URL location of the website. It corresponds to the following element inside the <host> element:

<baseAddresses>
    <add baseAddress="http://localhost/MyLinqWcfServiceHost/Service"/>
</baseAddresses>

Optionally, you may use the WCF SvcConfig Editor command in the Tools menu to set the configuration settings more visibly using the dialog window.

Now, build and run the website http://localhost/MyLinqWcfServiceHost/Service.svc on Internet Explorer, and you would see the following output:

Task 3: Creating the WCF Console Client Using SvcUtil.exe

In this walk-through, a Console based WCF client application is created, and it makes calls to the Linqservice created in the previous two steps. To create a client application for a WCF Service requires the following steps:

1. Obtain the service contract, bindings, and address information for a service endpoint.
2. Create a WCF client using that information.
3. Call operations.
4. Close the WCF client object.

Create a console application named “MyComServiceConsoleClient” in the projects folder as in the sample application.

To connect to a service in a client application, you need to obtain the type information for the service contract. Typically, you do this by using the ServiceModel Metadata Utility Tool (Svcutil.exe), which downloads metadata from the service, converts it to a managed source code file in the language of your choice, and creates a client application configuration file that you can use to configure your WCF client object.

Open the Visual Studio 2008 command window and change the current folder to the console application folder. Type the following command in the prompt:

>svcutil /language:cs /out:ServiceClient.cs /config:app.config 
   http://localhost/MyLinqWcfServiceHost/Service.svc?wsdl

Note: SvcUtil.Exe is found in the [drive:]\program files\microsoft sdks\windows\v6.0a\bin folder. This utility, by default, checks for a signed assembly for the services. To skip this verification, you need to issue the following command at the command prompt:

>Sn.exe –Vr "d:\program files\microsoft sdks\windows\v6.0a\bin\Svcutil.exe"

On successful execution of the svcutil.exe command, the two files mentioned above, ServiceClient.cs and app.config, are created in the application folder.

Now, switch on to the Visual Studio IDE, add these two files to the Console application “MyComServiceConsoleClient” by selecting the Project -> Add Existing Item menu.

Add a reference to System.Runtime.Serialization.dll and System.ServiceModel.dll.

• In Program.cs, add the following code to create a proxy object to call the service operations.
C#

class Program
{
    static void Main(string[] args)
    {
        LinqServiceClient client = new LinqServiceClient();

        client.Open();
        MyWCFLinqService.Prod[] items= 
          (MyWCFLinqService.Product[]) client.GetProductList();
        foreach (MyWCFLinqService.Product s in items)

            Console.WriteLine("{0} is {1}", s.productid, s.productname );
        client.Close();

        Console.Read();
    }
}

• Build the app and find the output as shown below:

Conclusion

The LINQ to SQL query based WCF service is created, configured, and hosted on IIS as a web site. A console client application is created using the code and configuration files generated by SvcUtil.exe to call the service operations.