(For more resources related to this topic, see here.)

Creating the LINQNorthwind solution

The first thing we need to do is create a test solution. In this article, we will start from the data access layer. Perform the following steps:

1. Start Visual Studio.

2. Create a new class library project LINQNorthwindDAL with solution name LINQNorthwind (make sure the Create directory for the solution is checked to specify the solution name).

3. Delete the Class1.cs file.

4. Add a new class ProductDAO to the project.

5. Change the new class ProductDAO to be public.

Now you should have a new solution with the empty data access layer class. Next, we will add a model to this layer and create the business logic layer and the service interface layer.

Modeling the Northwind database

In the previous section, we created the LINQNorthwind solution. Next, we will apply LINQ to Entities to this new solution.

For the data access layer, we will use LINQ to Entities instead of the raw ADO.NET data adapters. As you will see in the next section, we will use one LINQ statement to retrieve product information from the database and the update LINQ statements will handle the concurrency control for us easily and reliably.

As you may recall, to use LINQ to Entities in the data access layer of our WCF service, we first need to add an entity data model to the project.

1. In the Solution Explorer, right-click on the project item LINQNorthwindDAL, select menu options Add | New Item…, and then choose Visual C# Items | ADO.NET Entity Data Model as Template and enter Northwind.edmx as the name.

2. Select Generate from database, choose the existing Northwind connection, and add the Products table to the model.

3. Click on the Finish button to add the model to the project.

4. The new column RowVersion should be in the Product entity . If it is not there, add it to the database table with a type of Timestamp and refresh the entity data model from the database

5. In the EMD designer, select the RowVersion property of the Product entity and change its Concurrency Mode from None to Fixed. Note that its StoreGeneratedPattern should remain as Computed.

This will generate a file called Northwind.Context.cs, which contains the Db context for the Northwind database. Another file called Product.cs is also generated, which contains the Product entity class. You need to save the data model in order to see these two files in the Solution Explorer.

In Visual Studio Solution Explorer, the Northwind.Context.cs file is under the template file Northwind.Context.tt and Product.cs is under Northwind.tt. However, in Windows Explorer, they are two separate files from the template files.

Creating the business domain object project

During Implementing a WCF Service in the Real World, we create a business domain object (BDO) project to hold the intermediate data between the data access objects and the service interface objects. In this section, we will also add such a project to the solution for the same purpose.

1. In the Solution Explorer, right-click on the LINQNorthwind solution.

2. Select Add | New Project… to add a new class library project named LINQNorthwindBDO.

3. Delete the Class1.cs file.

4. Add a new class file ProductBDO.cs.

5. Change the new class ProductBDO to be public.

6. Add the following properties to this class:

   • ProductID

   • ProductName

   • QuantityPerUnit

   • UnitPrice

   • Discontinued

   • UnitsInStock

   • UnitsOnOrder

   • ReorderLevel

   • RowVersion

The following is the code list of the ProductBDO class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LINQNorthwindBDO
{
public class ProductBDO
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public string QuantityPerUnit { get; set; }
public decimal UnitPrice { get; set; }
public int UnitsInStock { get; set; }
public int ReorderLevel { get; set; }
public int UnitsOnOrder { get; set; }
public bool Discontinued { get; set; }
public byte[] RowVersion { get; set; }
}
}

As noted earlier, in this article we will use BDO to hold the intermediate data between the data access objects and the data contract objects. Besides this approach, there are some other ways to pass data back and forth between the data access layer and the service interface layer, and two of them are listed as follows:

• The first one is to expose the Entity Framework context objects from the data access layer up to the service interface layer. In this way, both the service interface layer and the business logic layer—we will implement them soon in following sections—can interact directly with the Entity Framework. This approach is not recommended as it goes against the best practice of service layering.

• Another approach is to use self-tracking entities. Self-tracking entities are entities that know how to do their own change tracking regardless of which tier those changes are made on. You can expose self-tracking entities from the data access layer to the business logic layer, then to the service interface layer, and even share the entities with the clients. Because self-tracking entities are independent of entity context, you don’t need to expose the entity context objects. The problem of this approach is, you have to share the binary files with all the clients, thus it is the least interoperable approach for a WCF service. Now this approach is not recommended by Microsoft, so in this book we will not discuss it.

Using LINQ to Entities in the data access layer

Next we will modify the data access layer to use LINQ to Entities to retrieve and update products. We will first create GetProduct to retrieve a product from the database and then create UpdateProduct to update a product in the database.

Adding a reference to the BDO project

Now we have the BDO project in the solution, we need to modify the data access layer project to reference it.

1. In the Solution Explorer, right-click on the LINQNorthwindDAL project.

2. Select Add Reference….

3. Select the LINQNorthwindBDO project from the Projects tab under Solution.

4. Click on the OK button to add the reference to the project.

Creating GetProduct in the data access layer

We can now create the GetProduct method in the data access layer class ProductDAO, to use LINQ to Entities to retrieve a product from the database. We will first create an entity DbContext object and then use LINQ to Entities to get the product from the DbContext object. The product we get from DbContext will be a conceptual entity model object. However, we don’t want to pass this product object back to the upper-level layer because we don’t want to tightly couple the business logic layer with the data access layer. Therefore, we will convert this entity model product object to a ProductBDO object and then pass this ProductBDO object back to the upper-level layers.

To create the new method, first add the following using statement to the ProductBDO class:

using LINQNorthwindBDO;

Then add the following method to the ProductBDO class:

public ProductBDO GetProduct(int id)
{
ProductBDO productBDO = null;
using (var NWEntities = new NorthwindEntities())
{
Product product = (from p in NWEntities.Products
where p.ProductID == id
select p).FirstOrDefault();
if (product != null)
productBDO = new ProductBDO()
{
ProductID = product.ProductID,
ProductName = product.ProductName,
QuantityPerUnit = product.QuantityPerUnit,
UnitPrice = (decimal)product.UnitPrice,
UnitsInStock = (int)product.UnitsInStock,
ReorderLevel = (int)product.ReorderLevel,
UnitsOnOrder = (int)product.UnitsOnOrder,
Discontinued = product.Discontinued,
RowVersion = product.RowVersion
};
}
return productBDO;
}

Within the GetProduct method, we had to create an ADO.NET connection, create an ADO. NET command object with that connection, specify the command text, connect to the Northwind database, and send the SQL statement to the database for execution. After the result was returned from the database, we had to loop through the DataReader and cast the columns to our entity object one by one.

With LINQ to Entities, we only construct one LINQ to Entities statement and everything else is handled by LINQ to Entities. Not only do we need to write less code, but now the statement is also strongly typed. We won’t have a runtime error such as invalid query syntax or invalid column name. Also, a SQL Injection attack is no longer an issue, as LINQ to Entities will also take care of this when translating LINQ expressions to the underlying SQL statements.

Creating UpdateProduct in the data access layer

In the previous section, we created the GetProduct method in the data access layer, using LINQ to Entities instead of ADO.NET. Now in this section, we will create the UpdateProduct method, using LINQ to Entities instead of ADO.NET.

Let’s create the UpdateProduct method in the data access layer class ProductBDO, as follows:

public bool UpdateProduct(
ref ProductBDO productBDO,
ref string message)
{
message = "product updated successfully";
bool ret = true;
using (var NWEntities = new NorthwindEntities())
{
var productID = productBDO.ProductID;
Product productInDB =
(from p
in NWEntities.Products
where p.ProductID == productID
select p).FirstOrDefault();
// check product
if (productInDB == null)
{
throw new Exception("No product with ID " +
productBDO.ProductID);
}
NWEntities.Products.Remove(productInDB);
// update product
productInDB.ProductName = productBDO.ProductName;
productInDB.QuantityPerUnit = productBDO.QuantityPerUnit;
productInDB.UnitPrice = productBDO.UnitPrice;
productInDB.Discontinued = productBDO.Discontinued;
productInDB.RowVersion = productBDO.RowVersion;
NWEntities.Products.Attach(productInDB);
NWEntities.Entry(productInDB).State =
System.Data.EntityState.Modified;
int num = NWEntities.SaveChanges();
productBDO.RowVersion = productInDB.RowVersion;
if (num != 1)
{
ret = false;
message = "no product is updated";
}
}
return ret;
}

Within this method, we first get the product from database, making sure the product ID is a valid value in the database. Then, we apply the changes from the passed-in object to the object we have just retrieved from the database, and submit the changes back to the database. Let’s go through a few notes about this method:

1. You have to save productID in a new variable and then use it in the LINQ query. Otherwise, you will get an error saying Cannot use ref or out parameter ‘productBDO‘ inside an anonymous method, lambda expression, or query expression.

2. If Remove and Attach are not called, RowVersion from database (not from the client) will be used when submitting to database, even though you have updated its value before submitting to the database. An update will always succeed, but without concurrency control.

3. If Remove is not called and you call the Attach method, you will get an error saying The object cannot be attached because it is already in the object context.

4. If the object state is not set to be Modified, Entity Framework will not honor your changes to the entity object and you will not be able to save any change to the database.

Creating the business logic layer

Now let’s create the business logic layer.

1. Right click on the solution item and select Add | New Project…. Add a class library project with the name LINQNorthwindLogic.

2. Add a project reference to LINQNorthwindDAL and LINQNorthwindBDO to this new project.

3. Delete the Class1.cs file.

4. Add a new class file ProductLogic.cs.

5. Change the new class ProductLogic to be public.

6. Add the following two using statements to the ProductLogic.cs class file:

   using LINQNorthwindDAL;
   using LINQNorthwindBDO;

7. Add the following class member variable to the ProductLogic class:

   ProductDAO productDAO = new ProductDAO();

8. Add the following new method GetProduct to the ProductLogic class:

   public ProductBDO GetProduct(int id)
   {
   return productDAO.GetProduct(id);
   }

9. Add the following new method UpdateProduct to the ProductLogic class:

   public bool UpdateProduct(
   ref ProductBDO productBDO,
   ref string message)
   {
   var productInDB =
   GetProduct(productBDO.ProductID);
   // invalid product to update
   if (productInDB == null)
   {
   message = "cannot get product for this ID";
   return false;
   }
   // a product cannot be discontinued
   // if there are non-fulfilled orders
   if (productBDO.Discontinued == true
   && productInDB.UnitsOnOrder > 0)
   {
   message = "cannot discontinue this product";
   return false;
   }
   else
   {
   return productDAO.UpdateProduct(ref productBDO,
   ref message);
   }
   }

Build the solution. We now have only one more step to go, that is, adding the service interface layer.

Creating the service interface layer

The last step is to create the service interface layer.

1. Right-click on the solution item and select Add | New Project…. Add a WCF service library project with the name of LINQNorthwindService.

2. Add a project reference to LINQNorthwindLogic and LINQNorthwindBDO to this new service interface project.

3. Change the service interface file IService1.cs, as follows:

   • Change its filename from IService1.cs to IProductService.cs.

   • Change the interface name from IService1 to IProductService, if it is not done for you.

   • Remove the original two service operations and add the following two new operations:

     [OperationContract]
     [FaultContract(typeof(ProductFault))]
     Product GetProduct(int id);
     [OperationContract]
     [FaultContract(typeof(ProductFault))]
     bool UpdateProduct(ref Product product, ref string
     message);

   • Remove the original CompositeType and add the following data contract classes:

     [DataContract]
     public class Product
     {
     [DataMember]
     public int ProductID { get; set; }
     [DataMember]
     public string ProductName { get; set; }
     [DataMember]
     public string QuantityPerUnit { get; set; }
     [DataMember]
     public decimal UnitPrice { get; set; }
     [DataMember]
     public bool Discontinued { get; set; }
     [DataMember]
     public byte[] RowVersion { get; set; }
     }
     [DataContract]
     public class ProductFault
     {
     public ProductFault(string msg)
     {
     FaultMessage = msg;
     }
     [DataMember]
     public string FaultMessage;
     }

   • The following is the content of the IProductService.cs file:

     using System;
     using System.Collections.Generic;
     using System.Linq;
     using System.Runtime.Serialization;
     using System.ServiceModel;
     using System.Text;
     namespace LINQNorthwindService
     {
     [ServiceContract]
     public interface IProductService
     {
     [OperationContract]
     [FaultContract(typeof(ProductFault))]
     Product GetProduct(int id);
     [OperationContract]
     [FaultContract(typeof(ProductFault))]
     bool UpdateProduct(ref Product product,
     ref string message);
     }
     [DataContract]
     public class Product
     {
     [DataMember]
     public int ProductID { get; set; }
     [DataMember]
     public string ProductName { get; set; }
     [DataMember]
     public string QuantityPerUnit { get; set; }
     [DataMember]
     public decimal UnitPrice { get; set; }
     [DataMember]
     public bool Discontinued { get; set; }
     [DataMember]
     public byte[] RowVersion { get; set; }
     }
     [DataContract]
     public class ProductFault
     {
     public ProductFault(string msg)
     {
     FaultMessage = msg;
     }
     [DataMember]
     public string FaultMessage;
     }
     }
     

4. Change the service implementation file Service1.cs, as follows:

   • Change its filename from Service1.cs to ProductService.cs.

   • Change its class name from Service1 to ProductService, if it is not done for you.

   • Add the following two using statements to the ProductService.cs file:

     using LINQNorthwindLogic;
     using LINQNorthwindBDO;
     

   • Add the following class member variable:

     ProductLogic productLogic = new ProductLogic();

   • Remove the original two methods and add following two methods:

     public Product GetProduct(int id)
     {
     ProductBDO productBDO = null;
     try
     {
     productBDO = productLogic.GetProduct(id);
     }
     catch (Exception e)
     {
     string msg = e.Message;
     string reason = "GetProduct Exception";
     throw new FaultException<ProductFault>
     (new ProductFault(msg), reason);
     }
     if (productBDO == null)
     {
     string msg =
     string.Format("No product found for id {0}",
     id);
     string reason = "GetProduct Empty Product";
     throw new FaultException<ProductFault>
     (new ProductFault(msg), reason);
     }
     Product product = new Product();
     TranslateProductBDOToProductDTO(productBDO,
     product);
     return product;
     }
     public bool UpdateProduct(ref Product product,
     ref string message)
     {
     bool result = true;
     // first check to see if it is a valid price
     if (product.UnitPrice <= 0)
     {
     message = "Price cannot be <= 0";
     result = false;
     }
     // ProductName can't be empty
     else if (string.IsNullOrEmpty(product.ProductName))
     {
     message = "Product name cannot be empty";
     result = false;
     }
     // QuantityPerUnit can't be empty
     else if
     (string.IsNullOrEmpty(product.QuantityPerUnit))
     {
     message = "Quantity cannot be empty";
     result = false;
     }
     else
     {
     try
     {
     var productBDO = new ProductBDO();
     TranslateProductDTOToProductBDO(product,
     productBDO);
     result = productLogic.UpdateProduct(
     ref productBDO, ref message);
     product.RowVersion = productBDO.RowVersion;
     }
     catch (Exception e)
     {
     string msg = e.Message;
     throw new FaultException<ProductFault>
     (new ProductFault(msg), msg);
     }
     }
     return result;
     }

   • Because we have to convert between the data contract objects and the business domain objects, we need to add the following two methods:

     private void TranslateProductBDOToProductDTO(
     ProductBDO productBDO,
     Product product)
     {
     product.ProductID = productBDO.ProductID;
     product.ProductName = productBDO.ProductName;
     product.QuantityPerUnit = productBDO.QuantityPerUnit;
     product.UnitPrice = productBDO.UnitPrice;
     product.Discontinued = productBDO.Discontinued;
     product.RowVersion = productBDO.RowVersion;
     }
     private void TranslateProductDTOToProductBDO(
     Product product,
     ProductBDO productBDO)
     {
     productBDO.ProductID = product.ProductID;
     productBDO.ProductName = product.ProductName;
     productBDO.QuantityPerUnit = product.QuantityPerUnit;
     productBDO.UnitPrice = product.UnitPrice;
     productBDO.Discontinued = product.Discontinued;
     productBDO.RowVersion = product.RowVersion;
     }
     

5. Change the config file App.config, as follows:

   • Change Service1 to ProductService.

   • Remove the word Design_Time_Addresses.

   • Change the port to 8080.

   • Now, BaseAddress should be as follows: http://localhost:8080/LINQNorthwindService/ProductService/

   • Copy the connection string from the App.config file in the LINQNorthwindDAL project to the following App.config file:

     <connectionStrings>
     <add name="NorthwindEntities"
     connectionString="metadata=res://*/Northwind.
     csdl|res://*/Northwind.ssdl|res://*/Northwind.
     msl;provider=System.Data.SqlClient;provider
     connection string="data source=localhost;initial
     catalog=Northwind;integrated security=True;Multipl
     eActiveResultSets=True;App=EntityFramework""
     providerName="System.Data.EntityClient" />
     </connectionStrings>

     You should leave the original connection string untouched in the App.config file in the data access layer project. This connection string is used by the Entity Model Designer at design time. It is not used at all during runtime, but if you remove it, whenever you open the entity model designer in Visual Studio, you will be prompted to specify a connection to your database.

Now build the solution and there should be no errors.

Testing the service with the WCF Test Client

Now we can run the program to test the GetProduct and UpdateProduct operations with the WCF Test Client.

You may need to run Visual Studio as administrator to start the WCF Test Client.

First set LINQNorthwindService as the startup project and then press Ctrl + F5 to start the WCF Test Client. Double-click on the GetProduct operation, enter a valid product ID, and click on the Invoke button. The detailed product information should be retrieved and displayed on the screen, as shown in the following screenshot:

Now double-click on the UpdateProduct operation, enter a valid product ID, and specify a name, price, quantity per unit, and then click on Invoke.

This time you will get an exception as shown in the following screenshot:

From this image we can see that the update failed. The error details, which are in HTML View in the preceding screenshot, actually tell us it is a concurrency error. This is because, from WCF Test Client, we can’t enter a row version as it is not a simple datatype parameter, thus we didn’t pass in the original RowVersion for the object to be updated, and when updating the object in the database, the Entity Framework thinks this product has been updated by some other users.