Saturday, June 11, 2011

WCF, Entity Framework, and LINQ in 15 Minutes

In this tutorial we'll use Entity Framework in VB.NET to create a Data Model from our SQL database. Then, we'll create a WCF Service to fetch a customer using LINQ. Finally, we'll create a Windows Form as a client application to call the WCFService and get back some data.

-Visual Studio 2010
-Sql Server Express
-Sql Management Studio (Installed with Sql Express)

1. Creating the Database

To create a simple database for this tutorial:
  1. Open Microsoft Sql Server Management Studio.

  2. Right-Click on the databases folder and choose New Database. Name the database "Testeco".

  3. Right-Click on Tables and choose New Table. Create a table with the following columns:

    IDPrimary key, datatype as numeric(18,0) and allow nulls false. Set Identity to true in properties of column.

  4. Name the table "Customer" and hit Save. I named mine "tblCustomer" out of habit, but if you usually prefix your database objects (e.g. tbl, vw, sp) you may want to stop doing this because these names now bubble up into the object names in the solution.

  5. Right-click your table and choose "edit top 200 rows" which will bring up an interface to add rows. Insert five test rows, hit Save and your database should be ready.
2. Creating the Solution

Open Visual Studio 2010. Create a new project. Under Project Templates Choose Other Project Types->Visual Studio Solutions: Blank Solution. Name your project "Testeco" and hit OK.

3. Creating the Data Model Project
  1. Right-Click your Testeco Solution and choose Add->New Project.
    Choose the Visual Basic->Class Library and name it "Testeco Data Model" and hit ok.

  2. Delete the Class1.vb file.

  3. Right-Click the new project and choose Add->New Item. From the list select ADO.Net Entity Data Model, and name it "TestecoDataModel" and hit ok.

  4. A dialog box will appear. Select "Generate from Database" and hit next.

  5. The next dialog requires that you specify the database connection. Select new connection. If you're using sqlexpress you will type the name of the database as localhost\SQLExpress. Select the testeco database and hit ok. Save the connection string as "TestecoEntities" and hit next.

  6. To select the database objects, drill down to tables and check the Customer table. Uncheck the option for Pluralize or Singularize generated object names. This option is not working correctly in the current version but is supposed to be fixed on the next service pack. Name the namespace as "TestecoModelContext" and hit finish.

At this point, you have an Entity Framework Data Model of your table. You should see a new file called TestecoDataModel.edmx and a diagram of the data model in the designer. It has generated the classes to handle updating the database with your object changes for you. Now we are ready to use that data in a service.

4. Creating the WCF Service

In this step we will create a web project that will host our WCF Service.
  1. Right-Click the new project and choose Add->New Item.
    Select WCF->WCF Service Application from the options, and name it "WCFTestecoService". Hit OK.

  2. Delete the IService1 and Service1 files as we will be creating new ones.

  3. Select this project, right-click and select Add->New Item and select WCF Service. Name the file "WCFTestecoService" and hit Add. It creates the Data Contract(Interface) and Service which implements the contract.

  4. Open the Data Contract called "IWCFTestecoService". It created a sample function called DoWork which we will replace with a function to fetch a customer from our Data Model. Replace the Sub DoWork function with:

    Function GetCustomer(ByVal id As Integer) As Customer

    The compiler will flag the return type Customer as unknown but we will fix that on the next step.

  5. Right-click on the project and add->Class... and name it Customer. At this point our compiler will be happy about returning a Customer type. But, we need to add two public properties to the class file. Add two properies to the Customer file for Last and First making your class look like this:

    Public Class Customer
    Public PropertyFirst As String
    Public PropertyLast As String

    End Class

    Something to keep in mind here is that this is not the same class as the Customer coming out of the Entity Framework. We are creating a simple wrapper to return from the service.

  6. Open the WCFTestecoService file. There should be a function in there still that says dowork. We need to rewrite this function so that it implements the new constract we created. Just delete the dowork function completly, place your cursor at the end of the line that says: Implements IWCFTestecoService and hit enter. It will reimplement the interface in your class and write the new function GetCustomer.

  7. Now we need to write the function to get the data out of our datamodel. But, we need to add a reference to the data model project. Right-Click on your service project and select Add Reference. A dialog will appear. Make sure the Project tab is active and select TestecoDataModel and hit OK. We have now added the reference to our data model project.

  8. Back to the GetCustomer function, we need to instantiate the datamodel to query against. Add the following to your function:

    Public Function GetCustomer(ByVal id As Integer) As Customer Implements IWCFTestecoService.GetCustomer
    Dim context = New TestecoDataModel.TestecoEntities()

    Dim result = (span class="blue">From cust In context.Customers
    Where = id
    Select cust).First

    Return New Customer() With {.First = result.First, .Last = result.Last}

    End Function

    When you update your function two things need to be checked. First, it may complain about the context.Customers. If so, remove the .Customers and use intellisense to add the proper table name (yours may be a little different than mine). Next, it will pick up a missing reference. if you click on the error it will tell you you're missing a reference to System.Data.Entity. And if you click it, it will fix it for you. Great tool!(Wishing C# did this..) The code should now compile correctly. But, before we move on let's review this code.

    The context is our datamodel. We are using Linq to query the context for a customer with a matching ID. We are returning a new customer and setting the properties of the object before we send it back.

  9. One last thing to do is to copy the connection string from our app.config file in the EntityFrameWork project into the web.config for our WCFService. Copy the connectionstring tag out and paste into your web.config. Now, our code can properly reach into the configuration for the connection string it will need.

  10. Build the entire solution and make sure it compiles.

5. Creating the Client Application

We will use a windows application to consume the WCF Service and provide an interface to get our data.

  1. Add a new project to your solution and choose type Visual Basic->Windows->Windows Forms Application and name it ClientApp.

  2. Create a simple form that looks like the image above.
    Name the textboxes as txtID,txtFirst, and txtLast.

  3. We need to add a web reference to our WCF Service. We do this by right clicking the project and selecting "Add Service Reference" which will bring up a dialog. Click the Discover button which will find your web project. Click on the little arrow next to TestecoService.svc which will show our service contract under it. Selectit in the left (nothing will appear in the right which is okay) Name it TestecoService.

  4. Double-Click the Go button on your form to go to the gobehind code. We need to write code that will take the data form the txtID.Text, get a result from our service, and load it into our textboxes. The function should look like this:

    Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
    Dim svs = New TestecoService.TestecoServiceClient

    Dim cust = svs.GetCustomer(CInt(txtID.Text))

    txtFirst.Text = cust.First

    txtLast.Text = cust.Last

    End Sub

    We first get a reference to a proxy class that was generated when we added the reference. we then call the function and get our data back. That's it.

  5. Set the startup project as your ClientApp project and run the program.
So, in a few minutes we've used the Entity Framework to create a data model, which we accessed using LINQ in our WCFService, that was used in a simple windows application.

Happy coding!


  1. Anonymous10/10/2011

    very cool

  2. This comment has been removed by the author.

  3. error coding on enclosed coding in the following chunk of code:

    Dim result = (span class="blue">From cust In context.Customers Where = id Select cust).First

    Return New Customer() With {.First = result.First, .Last = result.Last}
    Why you had span class="blue"> inside code? This is caused the error. This is small project, please attached working project via link.