WCF and LINQ to SQL

Now, I may be doing this wrong, but at the moment, it works :)

I’m writing a service to communicate with a database, and provide a consistent front end for the applications that will be using it.

In the past that has meant writing tonnes of CRUD (Create Read Update Delete) code, usually via Stored Procs, which are then accessed by a ‘DbAccess’ helper class of some variety. Not to mention the added bonus of writing the classes to store the data as it comes out.

Fortunately the database I’m accessing is small, and so I though it to be a good candidate for messing around with LINQ to SQL, and right I was too! Onto teh meaty stuff….

Lets say I have a database with 3 tables:

3_Car_Tables

 

To insert into this via the medium of SQL we’re looking at something like:

insert into Car (ID, Make, Model) values (1, 'Volvo', 'V40')
insert into Owner (CarId, Title, Surname) (1, 'Mr', 'Skardon')
insert into PreviousOwners(CarId, WhenBought, Title, Surname) values (1, '2003-02-02', 'Ms', 'Taylor')
insert into PreviousOwners(CarId, WhenBought, Title, Surname) values (1, '2001-02-02', 'Miss', 'Polton')

Which inevitably ends up becoming wrapped up in stored procs, probably one to Add the car and owner, and another to add previous owners. Eurgh.

Anyways, we all know that stuff, how about we LINQ it up…

LINQ to SQL – Really basic edition

First, we want a new VS2008 window…

Got it?

Ace – Let us proceed…. Create a new Console App, I’m calling mine ‘LinqToCar’, but you can call it whatever you like :)

AddNewItem

Once we have that, we add a new item to the project, and select the ‘LINQ to SQL Classes’ type, naming it (again) as you like.

AddNewItem2

In our new ‘dbml’ window we have we’ll have an empty tableau, let’s get filling it… Open up the ‘Server Explorer’ window in Visual Studio (CTRL+ALT+S), and add a Data connection to the Car database. Open up the ‘Tables’ folder and select all the tables, dragging them into the Object Relational Designer pane. You should end up with something like this:

ORD_View

Now we can switch back to our Program.cs file and start querying the database…

But first! We need a datacontext… Luckily we’ve already got one: ‘CarsDataContext’. So, let’s simply enter the following code:

 

class Program
{
    static void Main(string[] args)
    {
        CarsDataContext db = new CarsDataContext();
        var allCars = from c in db.Cars
                      select c;
 
        allCars.WriteCars();
        
        Console.WriteLine("Press ENTER to exit.");
        Console.ReadLine();
    }
}
 
internal static class CarsExtensions
{
    internal static void WriteCars(this IEnumerable<Car> cars)
    {
        foreach (var car in cars)
            Console.WriteLine(car.ID + ", " + car.Make + ", " + car.Model + ", " + car.Owner.Surname);
 
    }
}

The ‘CarExtensions’ class just adds an Extension method to be able to print the contents of an IEnumerable<Car> collection to the console. The important code is the actual selection, first we create our DataContext:

CarsDataContext db = new CarsDataContext();

Then we perform the query:

var allCars = from c in db.Cars
                      select c;

This (as the cunningly named variable suggests) gets us all the cars (nb, we could have written this in lambda stylee by doing:

var allCars = db.Cars.Where(c => c);

Anyhews, this is starting to come across as an introduction to LINQ to SQL – which it’s not meant to be. Needless to say, we can perform pretty much any query we want to on this now, experiment with it!

But where does the WCF stuff come into it?

Well, we have all our classes created by the designer - ‘Car’, ‘Owner’ and ‘PreviousOwner’, what would be great would be if we could have access to these via a service and not have to create ‘mapping’ classes.

Soooo… how do we go about doing that?

 

Converting the ConsoleApp to a WCFService

To be honest, the easiest thing to do here is actually start a new ‘WCF Service Library’ project and add a dbml to it in the same way we did above. So, lets do that. (remember to add the LINQ to SQL file as above!!!)

Assuming you are now staring at an ‘IService1.cs’ file in your VS window, what we should do is go to the Solution Explorer and rename the ‘IService1.cs’ file to something better - ‘ICarsService’ seems a good start. Once we’ve done that, let’s clear out the ‘CompositeType’ and the methods defined for us in the ICarsService interface. So we should end up with a file with the following contents:

 

[ServiceContract]
public interface ICarsService
{
}

 

Let’s add two methods, one to get a car, one to submit a car…

 

[ServiceContract]
public interface ICarsService
{
    [OperationContract]
    Car GetCar(int id);
 
    [OperationContract]
    void SubmitCar(Car car);
}

Ok, so now we need to go to the Service1.cs file and rename it to something like ‘CarsService’ and then open it for editing… Same deal as before, delete all teh methods given to us and implement the ones defined in the ICarsService.

public class CarsService : ICarsService
{
    public Car GetCar(int id)
    {
        throw new System.NotImplementedException();
    }
 
    public void SubmitCar(Car car)
    {
        throw new System.NotImplementedException();
    }
}

Let’s add some simple LINQ code here…

public class CarsService : ICarsService
{
    private static readonly CarsDataContext _db = new CarsDataContext();
 
    public Car GetCar(int id)
    {
        return _db.Cars.Where(c => c.ID == id).Single();
    }
 
    public void SubmitCar(Car car)
    {
        _db.Cars.InsertOnSubmit(car);
        _db.SubmitChanges();
    }
}

 

Cool, so… let’s press F5 and load up the WCF Test Client and invoke that bad boy…

‘GetCar’ – passing ID 1 (as I know it’s there)…

Gah! WebException…

‘An error occurred while receiving the HTTP response to http://localhost:8731/Design_Time_Addresses/CarsLibrary/Service1/. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down).’

Hmmm… Ah, wait a minute… Sure, we’re attempting to pass down a ‘Car’ object, but we’ve not defined it as a DataContract… Let’s get onto that!

 

We need to open the Cars.designer.cs file and modify the ‘Car’ class.

First, we need to add the ‘DataContract’ attribute to the class…

[DataContract]
[Table(Name="dbo.Car")]
public partial class Car : INotifyPropertyChanging, INotifyPropertyChanged {...}

 

Next, we need to modify the properties, so, let’s find the properties, (in our case ‘ID’, ‘Make’, ‘Model’, ‘PreviousOwners’ and ‘Owner’ and add the ‘DataMember’ attribute to them:

[DataMember]
[Column(Storage="_ID", DbType="Int NOT NULL", IsPrimaryKey=true)]
public int ID {...}

 

Ace, now we’re rocking, let’s try again…

Gah! No we’re not… same error… oh wait, ahh yes, some of our properties refer to other classes (Owner etc) so we’d best DataContract / DataMember those bad boys as well…

Oh noes! Still doesn’t work.. Let’s have a look at those classes a bit closer…

What we’ve blindly done (I admit, I made you do this so please forgive me :)) is add the [DataMember] attribute to all the properties. Now, for the ‘Car’ class that is fine, desired in fact.. But what about the other classes? Well, inside those we have properties which are classed as ‘Association’ properties, (look at the attributes). How do these work?

Well… Lets say we’ve done a query, and have a Car object..

Car car = _db.Cars.Where(c => c.ID == 1).Single();

If we now use intellisense we can start skipping through the class…

car.Owner

Get’s us the Owner of the car.. but wait! There’s more!!

car.Owner.Car

??? Eh? Well, that gets us the owner of the car’s car…. but wait!! There’s even more!!!!

car.Owner.Car.Owner.......

A never ending stream of accessibility…!!! This is great internally to the service, but causes an infinite loop when trying to send the data down, (which is why you’ll get a ‘timeout’ exception).. The solution? Remove the ‘DataMember’ attributes from the [Association] properties…

Done?

F5 and try once more…

Huzzah!

We have a winner!!!

So, we can now Get a LINQ to SQL object from the service to our client.

I think that’ll be enough for this post… :) The next post will cover the uploading of data to our service…

Print | posted @ Tuesday, March 10, 2009 11:03 AM

Comments on this entry:

Gravatar # re: WCF and LINQ to SQL
by rei at 1/8/2010 8:44 PM

Hmm... strange, I have circular references in my ORM too, but it gives me no trouble on my local machine. My shared hosting on the other hand pukes at it with that cryptic communication exception.

I wonder if it has to do with the .NET/ASP.NET version. Have you tried it on version 2.0.50727.4927? My webhost is running .NET 2.0.50727.3603 and ASP.NET 2.0.50727.4049.
Gravatar # re: WCF and LINQ to SQL
by Mark at 2/4/2010 7:49 PM

Good stuff, thanks for posting. I setup my project in the same way and for some reason when I view the data in the browser, it's only showing the top level object. Any ideas why I am not seeing the associated object?

Also, I noticed there's an easy way to annotate your data objects instead of one by one.

Set the "Serialzation Mode" of your data context to "UniDirectional". --> Right click on your dbml, go to properties and find "Serialzation Mode".

Gravatar # WCF and LINQ to SQL
by Kanchan Mankar at 2/17/2013 5:02 PM

Its helpful
Post A Comment
Title:
Name:
Email:
Comment:
Verification: