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

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. Just as we did in the previous chapter, 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;
}

You will recall that from Chapter 4, Implementing a WCF Service in the Real World, 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.

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: