Overview of Entity Framework 4.0

 "The Entity Framework bridges the gap between how developers commonly manipulate conceptual objects (customers, orders, products; posts, tags, members; wall posts, private messages, friend connections) and the way data is actually stored (records in database tables).  The technical term for a tool that provides this abstraction is object relational mapper (ORM)."

This blog gives a gist of what EF (Entity Framework 4) has to offer and how to program against it.

Best place to start http://msdn.microsoft.com/en-us/data/aa937723.aspx 
For beginners, I would recommend reading http://blogs.msdn.com/b/adonet/archive/2010/07/19/absolue-beginners-guide-to-entity-framework.aspx

I had been going through Entity framework lately to implement it in our next Project. To my surprise, the programming with Data (DB) has become very simple using this framework. It helps the developer to focus more on understanding the Business Domain and model the Business Entities than worry about the way to store and access the Database.

The framework provides ways to generate DB directly from the Modelled Entities. This approach is called Model-First approach and is usually recommended. However vice versa, DB to Entities can also be created. This helps if you already have the DB ready & still want to leverage on using the Framework.

LINQ queries or Lamda expressions are mostly used to perform CRUD operations against Business Entities instead directly against the Database.

If you open the .edmx file (Entity Model file) in an XML editor then you will basically see the following sections.
    * Storage Model - Defines the Entities, Entity Sets and Associations. All information required to create the Database will be picked from here.
    * Conceptual Model - Defines the Entities, Entities Sets and Associations that will be consumed from the Business Layer. Information for modelling (Diagram) will be picked from here.
    * Mappings/Associations - Mappings between the Storage and the Conceptual Model is defined here.

EntitySet is a pluralized version of the Entity.Few base classes that you need to be aware of are
    * ObjectContext is a base class for the Entity Container. Used like an container for Entites.
    * EntityContext is a base class for the Entity class.

By default, EF uses Lazy loading approach. It executes the queries as a single batch of commands. However there are ways to make explicit execution of the Query.


Few commands to Query, Update are like follows:
ctx.Contacts.Where(c=>c.SalesOrderHeaders.Any()).ToList()
ctx.Customers.AddObject(customer);
ctx.SalesOrderDetails.DeleteObject(order);
ctx.SaveChanges() - Triggers the execution of the Query.

There are 3 ways of programming against the Entitiy Framework.

• LINQ to Entities - Write LINQ queries to perform operations on Entities.
• Entity SQL - Use SQL strings as commands. However you are writing commands against the Entities and not DB.
• Query Builder - Use the Methods provided with Entities Framework instead LINQ.

There are times when you would like to do some complex set of operations on a varied set of tables while interacting with Entities. This is when you could leverage on Entities to Stored Procedures. However there are some limitations on using Stored Procedure with Entities. (Something like if one of the operation, say Insert is connected to Stored Proc then other operations also have to be linked through Stored Proc).

Tracing the SQL commands.
During debug you would like to see what is the DB query that is being converted to for your LINQ operation. Usual approach is to use the SQL Profiler.

This could be time consuming to switch between VS 2010 and SQL Server. You can leverage on the Programming model tracing using System.Data.Objects.ObjectQuery.ToTraceString and System.Data.EntityClient.EntityCommand.ToTraceString methods, which enable you to view these store commands at runtime without having to run a trace against the data source.

LINQ TO ENTITIES
// Define an ObjectSet to use with the LINQ query.
ObjectSet products = context.Products;
// Define a LINQ query that returns a selected product.
var result = from product in products
where product.ProductID == productID
select product;
// Cast the inferred type var to an ObjectQuery
// and then write the store commands for the query.
Console.WriteLine(((ObjectQuery)result).ToTraceString());


ENTITY SQL
// Define the Entity SQL query string.
string queryString =
@"SELECT VALUE product FROM AdventureWorksEntities.Products AS product
WHERE product.ProductID = @productID";
// Define the object query with the query string.
ObjectQuery productQuery =
new ObjectQuery(queryString, context, MergeOption.AppendOnly);
productQuery.Parameters.Add(new ObjectParameter("productID", productID));
// Write the store commands for the query.
Console.WriteLine(productQuery.ToTraceString());


QUERY BUILDER
int productID = 900;
// Define the object query for the specific product.
ObjectQuery productQuery =
context.Products.Where("it.ProductID = @productID");
productQuery.Parameters.Add(new ObjectParameter("productID", productID));
// Write the store commands for the query.
Console.WriteLine(productQuery.ToTraceString());


You can retrieve objects from Entities using GetObjectByKey and TryGetObjectByKey methods on ObjectContext. This will return an object with the specified EntityKey into the object context. When you use GetObjectByKey, you must handle an ObjectNotFoundException.

The abstraction EF

http://msdn.microsoft.com/en-us/library/cc853327.aspx
http://blogs.msdn.com/b/adonet/archive/2008/02/11/exploring-the-performance-of-the-ado-net-entity-framework-part-2.aspx

What one must do to improve performance is to use Compiled LINQ to precompile the LINQ queries to ensure faster operations.
http://msdn.microsoft.com/en-us/library/bb896297.aspx will provide more information on this.


EF gets only the Entities data without it's related assoicated entities data. Ex: There may be a case where you would want to retrieve SalesPersons along with their SalesOrders Details.

There are ways you could inform the EF to retrieve all the related entities's data so that you do not end up using a foreach loop and trying to fill the related entities data. This would have resulted in far too many DB calls.


Using Query Paths you can preload the related Entities.

//When a n-level details have to be retrieved.
var contacts = (from contact in context.Contacts.Include("SalesOrderHeaders.SalesOrderDetails")
select contact).FirstOrDefault();

//When unrelated tables has to be included.
ObjectQuery query =
context.SalesOrderHeaders.Include("SalesOrderDetails").Include("Address");


Security
Can't end the article without mentioning about Security. I Just evaluated on the classic problem of SQL Injection. Your old techniques of having Parameterized Query is still valid in Entity Framework.

--SQL Injection possible.
context.ExecuteStoreQuery("select * from Products where pid = {0}", 1);
--Guarded against SQL Injection
context.ExecuteStoreQuery("select * from Products where pid = @p0", new SqlParameter { ParameterName = "p0", Value = 1 })


Overall, I am sure this is going to reduce the developer's work however adds a little more decipline to ensure that developers do not just plainly treat the entities just as a set of tables.

So far that's it I was able to read and evaluate about, Entity Framework. Hopefully I will add an advanced version of this article where I will try to touch base on the Transaction and Concurrency related stuff and little more detail on the coding with more snippets. Till then, Happy blogging!! :)

Don't forget to start reading about http://msdn.microsoft.com/en-us/data/aa937723.aspx

Rapid Development of Web Pages

Yesterday, I set myself to develop a simple web application and targeted an hour for it. I used VS 2010 for my development purpose.

The application will do the following:

• List a set of users
• List a set of books
• Perform a simple search on the Users vs. Books borrowed (through IDs)
• Allow users to borrow & return books. Entries to be made accordingly

The output turned out to be something like this:

The following three layers were considered:

1. Database
2. Data Access Layer
3. UI/Presentation Layer

For brevity, I chose to ignore the Business Layer and any Service Layer.

Database Layer

Here I created a DB called Library and added three tables with the following constraints. The image below is self explanatory as to what each of these table do.

Data Access Layer

This was going to be a class library. I could have chosen to have this merged with the Web Application. But, thought adding some amount of modularizing won't hurt. 

Here I chose to add an ADO.Net Entity Data Model. This will give a data access view of the tables in your .Net application. Follow the steps below to create it:

Add New Item on the DAL > Visual Studio C# Items > Data > ADO.Net Entity Data Model > Specify a valid Model name say, LibraryModel.edmx and go to the next page.

Select Generate from Database and go to the next page.

Select the Database connection and rest leave it with defaults. You may want to change the Identifier for the connection string that will go to App.Config/Web.Config. Say, LibraryEntities

Select the Tables you would want to make it visible in the .Net application and specify the Model namespace say, LibraryModel and click on Finish.

Your class library should look something like this:

Now, I simply created a simple Facade to wrap some operations of the DB operations. I called that class as LibraryManager.cs

This class used Linq to Entities for faster development towards the tables. 

The file looked something like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class LibraryManager
    {
        private LibraryEntities dbContext;

        public LibraryManager()
        {
            dbContext = new LibraryEntities();
        }

        public List GetBooksList()
        {
            var query = from p in dbContext.tblBooks
                        select p;
            return query.ToList();
        }

        public List GetUsersList()
        {
            var query = from p in dbContext.tblUsers
                        select p;
            return query.ToList();
        }

        public bool AddBook(tblBook book)
        {
            dbContext.AddTotblBooks(book);
            dbContext.SaveChanges();
           
            return true;
        }

        public bool AddUser(tblUser user)
        {
            dbContext.AddTotblUsers(user);
            dbContext.SaveChanges();

            return true;
        }

        public bool BorrowBook(int nBookId, int nUserId, DateTime date)
        {
            tblUserBook userBook = new tblUserBook();
            userBook.nBookId = nBookId;
            userBook.nUserId = nUserId;
            userBook.sBorrowedDate = date;
            dbContext.AddTotblUserBooks(userBook);
            dbContext.SaveChanges();

            return true;
        }

        public List SearchBorrowedItemsByBookAndUser(int nBookId, int nUserId)
        {
            var query = from p in dbContext.tblUserBooks
                        where (p.nBookId == nBookId && p.nUserId == nUserId)
                        select p;
            return query.ToList();
        }

        public bool ReturnBook(int nUserBookId, DateTime date)
        {
            tblUserBook userBook = dbContext.tblUserBooks.Single(p => p.nId == nUserBookId);
            userBook.sReturnedDate = date;
            dbContext.SaveChanges();

            return true;
        }
    }
}
 

UI/Presentation Layer

Here I just created a simple Web Application and added a new item "Web Form using a master page" called User.aspx which is tied to the default master page.

In order to access the Database through the Entity Framework, you need to have the connection string set in your web.config.

Copy the connection string from the App.config of your Data Access Layer to your web.config

User.aspx had the following controls

Grid views to show a list of Users, Books & User vs Books Search results

Other simple controls to perform Borrow and Return book feature

Important to highlight is to see how easy it is to bind the List returned from DAL into the Grid.

Observe the ones highlighted in bold below.

Users.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Users.aspx.cs" Inherits="Library.Users" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false">
    <Columns>
       <asp:BoundField HeaderText="ID" DataField="nId" />
       <asp:BoundField HeaderText="Name" DataField="sName" />
    </Columns>
    </asp:GridView>

    <br />

    <asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="false">
    <Columns>
       <asp:BoundField HeaderText="ID" DataField="nId" />
       <asp:BoundField HeaderText="Name" DataField="sName" />
       <asp:BoundField HeaderText="ISBN" DataField="sISBN" />
       <asp:BoundField HeaderText="Is Active" DataField="bActive" />
    </Columns>
    </asp:GridView>

    <br />
    <asp:Label ID="lblBorrowMessage" runat="server" Text=""></asp:Label>
    <br />
    <table>
    <tr>
    <td><asp:Label ID="lblSearchBookId" runat="server" Text="BookId"></asp:Label></td>
    <td><asp:TextBox ID="txtSearchBookId" runat="server"></asp:TextBox></td>
    <td><asp:Label ID="lblSearchUserId" runat="server" Text="UserId"></asp:Label></td>
    <td><asp:TextBox ID="txtSearchUserId" runat="server"></asp:TextBox></td>
    <td><asp:Button ID="btnSearchBookUserId" runat="server" Text="Search"
            onclick="btnSearchBookUserId_Click" /></td>
    <td><asp:Button ID="btnBorrowBook" runat="server" Text="Borrow" onclick="btnBorrowBook_Click"
             /></td>
    </tr>   
    </table>
    <br />
    <asp:GridView ID="gvBookUserId" runat="server" AutoGenerateColumns="false">
    <Columns>
       <asp:BoundField HeaderText="ID" DataField="nId" />
       <asp:BoundField HeaderText="Borrowed Date" DataField="sBorrowedDate" />
       <asp:BoundField HeaderText="Returned Date" DataField="sReturnedDate" />
    </Columns>
    </asp:GridView>

   
    <br />
    <asp:Label ID="lblReturnMessage" runat="server" Text=""></asp:Label>
    <br />
    <table>
    <tr>
    <td><asp:Label ID="lblReturnBook" runat="server" Text="BookUserId"></asp:Label></td>
    <td><asp:TextBox ID="txtBookUserId" runat="server"></asp:TextBox></td>
    <td><asp:Button ID="btnReturnBook" runat="server" Text="Return"
            onclick="btnReturnBook_Click"/></td>
    </tr>   
    </table>
    <br />


</asp:Content>

Users.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DataAccess;

namespace Library
{
    public partial class Users : System.Web.UI.Page
    {
        private LibraryManager libraryMgr;

        protected void Page_Load(object sender, EventArgs e)
        {

            libraryMgr = new LibraryManager();
            gvUsers.DataSource = libraryMgr.GetUsersList();
            gvUsers.DataBind();

            gvBooks.DataSource = libraryMgr.GetBooksList();
            gvBooks.DataBind();

            lblBorrowMessage.ForeColor = System.Drawing.Color.Black;
            lblBorrowMessage.Text = "";
        }

        protected void btnSearchBookUserId_Click(object sender, EventArgs e)
        {
            int nBookId = Convert.ToInt32(txtSearchBookId.Text.Trim());
            int nUserId = Convert.ToInt32(txtSearchUserId.Text.Trim());
            gvBookUserId.DataSource = libraryMgr.SearchBorrowedItemsByBookAndUser(nBookId, nUserId);
            gvBookUserId.DataBind();
        }

        protected void btnBorrowBook_Click(object sender, EventArgs e)
        {
            int nBookId = Convert.ToInt32(txtSearchBookId.Text.Trim());
            int nUserId = Convert.ToInt32(txtSearchUserId.Text.Trim());
            if (libraryMgr.BorrowBook(nBookId, nUserId, DateTime.Now))
            {
                lblBorrowMessage.Text = @"Data has been successfully recorded. Have fun reading.";
                lblBorrowMessage.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                lblBorrowMessage.Text = @"There was an error while trying to Borrow the Book.
                                        Contact the Administrator.";
                lblBorrowMessage.ForeColor = System.Drawing.Color.Red;
            }
        }

        protected void btnReturnBook_Click(object sender, EventArgs e)
        {
            int nBookUserId = Convert.ToInt32(txtBookUserId.Text.Trim());
            if (libraryMgr.ReturnBook(nBookUserId, DateTime.Now))
            {
                lblReturnMessage.Text = @"Data has been successfully recorded. Hope you had fun reading.";
                lblReturnMessage.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                lblReturnMessage.Text = @"There was an error while trying to Return the Book.
                                        Contact the Administrator.";
                lblReturnMessage.ForeColor = System.Drawing.Color.Red;
            }
        }
    }
}

Just to ramp up, things that helped me in achieving my one hour target was:

• Using Mangement Studio for SQL Server 2008
• Creating ADO.Net Entity Data Model
• Using LINQ to do CRUD on the table data
• Using Grids and Binding data

Two links worth mentioning are:

http://www.telerik.com/help/silverlight/consuming-data-linq-to-ado-net-entity-data-model.html

http://www.mikepope.com/blog/DisplayBlog.aspx?permalink=1419&count=no 

Well just a little more than a hour, i was able to develop what I set out for. So Mission accomplished :)