ADO.NET Entity Framework

Time to look in to ADO.NET Entity Framework.We will try to see what is it and how we can go ahead and implement with code samples.

Why Entity Framework ? 

Database development with the .NET framework has not changed a lot since its first release. Many of us usually start by designing our database tables and their relationships and then creating classes in our application then we operate this entities with the help of ADO.NET.This is going to be lengthy process and maintenance is not so easy. Here comes up ADO.NET Entity Framework; it allows you to deal with the entities represented in the database in your application code by abstracting the groundwork and maintenance code work away from you. A very light description of the ADO.NET Entity Framework would be "It allows you to deal with database concepts in your code.".So now we might be thinking the .net frame work and where the Entity Framework is going to fit, on what release,etc let me post an image , to make it lock.

ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. It is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.

                                             Lets see the drill down:

Requirements:

·   SP1 for .NET Framework 3.5/Visual Studio 2008 (which you can download here.)

·   MySQL server 5.0 or higher. Note that Entity Framework support is not available in Standard Edition of dot Connect for MySQL.

But l am having having Microsoft Visual Studio 2010, .NET Framework 4. Installed that is well and good.

Setting Up the Database:
Before we start coding, we will need to create the database and its objects that will be used and referred to in this. We are referring to the same DB that we used for LINQ to SQL.Nothing but TestDB.
Generating Model from Database.
Create a new console application in Visual Studio. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial.
1. In the Solution Explorer right-click on the project and choose Add | New Item.
2. In the dialog choose ADO.NET Entity Data Model, click Add. This launches Entity Data Model Wizard.
3. In the wizard choose Generate from database, click Next.

4. Pick an existing dotConnect for MySQL connection string or create a new one. When creating a new connection choose MySQL Server in the Data Source list, then dotConnect for MySQL in the Data provider combobox. This step is illustrated in the screenshot below.

6.    In the field Save entity connection settings... type TestDemo. This will be the name of the main data access class. Click Next.

7.  Choose database objects that will be used in the model. These are all objects from the Test_db script, including auxiliary tables.

8. Press Finish. The model will be generated and opened in EDM Designer.  

Now Querying Data:
The model we just generated is ready to use. Name it as Model1.edmx. You can inspect it visually in the designer or take a look behind the scenes with XML Editor.You can see the  wizard creates classes for all selected tables that represent entities. It also creates a descendant of System.Data.Objects.ObjectContext class, which controls the connection to the database, and the whole data flow. This class includes properties and methods named after your database objects.All Entity Framework operations are executed through the ObjectContext descendant. To retrieve data you have to first create an instance of the context, then prepare a query with LINQ to Entities or EntitySQL or their mix, and then access the object returned by the query, which may be a collection of objects or a single object.So lets start witting quires.We are posting the code samples for CURD operations.

EF vs Linq to SQL

There is a battle between LINQ to SQL and the (EF)Entity Framework. Some say that it is already settled and that LINQ to SQL is DOA, destined to be replaced by Entity Framework. Microsoft will continue to support L2S but will put more effort into the EF and the Linq to Sql shall be retired.Not the complete retairment but the scope is going to be limited.  The new term, I think, is LINQ to Entities (L2E). The entity framework is Microsoft's implementation of a full grown Object/Relation Mapper such as NHibernate already is. Linq to SQL on the other hand may have some similarities, but is not recognized (and will never be) as a full o/r m tool. It is however a quick and easy way for accessing relational data in a much better way than the classic ado.net and can be very suitable in small projects or applications. MSDN has an article comparing the two. I am currently evaluating the latest release of Entity Framework.I am trying in to dig in to EF more deeply and keep posting my findings here.

Lambda Expressions

Well, Lets go back to my previous code samples. click here. There we have used one expression something like this.

 Customer  customer = db.Customers.Single(c=> c.Name == "Mark Walker");

Lets take the expression out.So this will looks:

(Input parameters) => Expression

This is nothing but Lambda Expression. If you have been reading about LINQ and its related technologies at all, you’ve surely come across this term already. Lambda expressions are one of the features introduced in the C# 3.0.A lambda expression is an anonymous function that can contain expressions and statements, and can be  used to create delegates or expression tree types. All lambda expressions use the lambda operator =>, which is read as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block.Ok, if this complicates you lets take a small example. Let’s go back to the customer collection and query it.

var cust = new List<customer> {

new customer { Name="Bob"}

, new customer { Name="Sally" }

, new customer { Name="Jack"}

, new customer { Name="Sarah"}

, new customer { Name="Philbert"}           

};
string strResult = customer.Find(IsCustomer);

public static bool IsCustomer(string name)

{

   return name.Equals("Ken");

}

We can write the same thing in Anonymous method. The idea behind writing the anonymous methods is to write methods inline to the code without declaring a formal named method. Normally they used for small methods that don't require any reuse. So this will looks something like this.

string strResult = customer.Find(delegate(string cust)

{

   return cust.Equals("Ken");

});

Lambda Expressions makes the thing even more easier by allowing you to write avoid anonymous method and statement block So we can say it as something like modified version of Anonymous method from C# 2.0.

var customeres = cust.Where((c) => c.Name == "Ken").Select((c) => new { c.Name });

This is very common way of witting code, and if any plan to work with LINQ soon you will come across this. The biggest thing is that treating the code as data.

DLinq and XLinq

Hmmm. What can I say here? do not get afraid on these names.We have already seen what is LINQ and also  seen some code samples how LINQ can help .net developers who believed that Object Relational Mapping is a key to application development.LINQ is to .net what Hibernate is to Java. LINQ goes a few steps ahead and facilitates querying not only SQL databases, but also XML, objects, etc. For developing any application you need not search for any developers who are good in SQL rather you can search for .net developers.Ok now let us look in to this terms-LINQ,DLinq,XLinq:
The Linq project offers querying features integrated right into programming languages. This will allow executing strongly-typed queries on sources such as databases, XML documents or even object graphs in memory. This technology relies on innovations on programming languages like C# 3.
DLinq is an extension to Linq that allows querying a database (only SQL Server for the moment) and do object-relational mapping.
XLinq is an extension to Linq that allows querying XML documents, as well as creating or transforming XML.To make it more clear lets take the example from previous blog, take the business object customer

public class Customer

{

  public string CustomerID;

  public string Name;

}

Well, decorate it with a  few attributes and you get this.(This was nothing which done by the visual studio behind the seen of generating data context class in our last example) –

[Table(Name="Customers")]
public class Customer

{

  [Column(Id=true)]

  public string CustomerID;

  [Column]

  public string Name;

}

And now you can query your data like this –

// DataContext takes a connection string

DataContext db = new    DataContext("c:\\TestDB\\TestDB.mdf");

// Get a typed table to run queries

Table<Customer>Customers = db.GetTable<Customer>();

// Query for customers by Name

var q = from c in Customers
        where c.Name== "Mark Walker"
       select c;
Well , this is what we did in last blog.I think now you are clear on this.See the image too.

LINQ to SQL

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.We are trying to come up with a code sample were we can do all the CURD operations to a database using LINQ.In our example we are trying to come up with a solution somthing like below mentioned.

Solution structure:

For this code sample we will need two projects.Not a must have thing, but I would love to explain with the right architechure. One is a data layer (created as a Class Library) which we will generate and the other is a client application to use this.It can be either web/windowscdoes nt matter.The structure looks somthing like this in Solution Explorer.Also given the data model we are going to hit.

Creating Data Layer:

So, the database is modelled and our classes are created, Now Let's show some code-examples for working with all these classes.First we will try to populate a grid is nothing but pull the data from SampleDB.First create a class library solution and name it as DAL.Before we generate our data layer we must create a new connection in Server Explorer which points to TestDB database(This is the name that I given for DB).Right-click on the DAL project and select Add New Item. From the dialog choose ‘LINQ to SQL Classes’ and call the file SampleCustomer.dbml.The .dbml extension stands for Database Markup Language.Have a look on the autogenerated files , itself is essentially an XML file that describes the database which is used to generate our classes.When you add your new dbml file the Object Relational Designer (O/R Designer) should launch which is a visual tool for creating your model.Locate your database in the Server Explorer window (View -> Server Explorer), and drag and drop the tables to use onto the surface of the O/R Designer.Here I have created a sample data base and two tables on it.DataContext is the main object through wich we will communicate with our database.The properties of the DataContext class are the tables and stored procedures in the database we modelled/created.

Posting all the CURD operatoins code here:

private void PopulateStudents()

{

using (SampleDataContextDataContext context = new SampleDataContextDataContext())

{

   var customer = from c in context.Customers

   select new Customer

   {

      Name = c.Name,

      CustomerID = c.CustomerID,

      OrderID = c.OrderID,

      Zip = c.Zip,

      Order = c.Order

    };

    gvStudents.DataSource = customer.ToList<Customer>();

    gvStudents.DataBind();

   }

}

private void Create()

{

using (SampleDataContextDataContext context = new  SampleDataContextDataContext())

{

  Order newOrder = new Order()

  {

    OrderID = "2",

    Item = "HP Notebook",

    CustomerID = "11100001"

  };

  Customer newCustomer = new Customer()

  {

     Name = "Mark Walker",

     CustomerID = "11100001",

     OrderID = "100024",

     Zip = "90503",

     Order = newOrder

  };

  context.Customers.InsertOnSubmit(newCustomer);

  context.SubmitChanges();

  }

}

private void Update()

{

  SampleDataContextDataContext db = new SampleDataContextDataContext();

  Customer  customer = db.Customers.Single(c=> c.Name == "Mark Walker");

  customer.Zip = "85283";

  db.SubmitChanges();

}

private void Delete()

{

  SampleDataContextDataContext db = new SampleDataContextDataContext();

  db.Orders.DeleteOnSubmit(db.Orders.Where(c => c.OrderID.Contains("2")).Select(c => c).Single())

   db.SubmitChanges();

}

Likewice we can also call a stored procedure also.The code below demonstrates how to retrieve Customer entities not using LINQ query syntax, but rather by calling the "GetCustomers" stored procedure we added to our data model above. Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

INQ to SQL provides a nice, clean way to model the data layer of your application. Once you've defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it.I think you'll find that LINQ to SQL can dramatically improve your productivity when working with data, and enable you to write extremely clean object-oriented data access code.Hopefully the above introduction and code samples have helped whet your appetite to learn more. Over the next few weeks I'll be continuing this series to explore LINQ in more detail.If any quries please shoot post I will more than happy to help you out , more over it will be a learning curve for me too.

private void GetAllCustomeres()

{

 //Sample for calling an stored procedure

using (SampleDataContextDataContext context = new SampleDataContextDataContext()

{

   var customers = from c in context.GetCustomers()

                           select c;

   foreach (Customer  c in customer)

   {

     Console.WriteLine("{0} {1}",

     c.Name, c.Zip);

   }

 }

}

LINQ to Dataset

LINQ to Dataset enables you to query and update data in to ADO.NET dataset. Once the data is cashed to client side we can use this functionality to query against that. Your application typically needs to search, filter thru this data in order to display this data according to the need. Data View provides a very limited options .when it comes to creating a filtered view of the data. LINQ extends these capabilities to a greater extend.For Three tires as well as N tier architecture LINQ will be very good option as dataset is coming on middle tier and UI or client can query using LINQ. Please see the diagram below:

Well, let’s describe the solution with an example. Suppose we have following two tables containing data in the database.

Prerequisite:  Both this table is loaded to a data set called ds now we will try to run some sample LINQ quires to pull put the data. We will start with a simple Query.
var results = from customer in customersvar customers = ds.Tables["Customer"].AsEnumerable();

              orderby customer.Field("Name")

              select customers;

dataGridView1.DataSource = results.AsDataView();

We can also do join operations too. A sample query is given below.

var customers = ds.Tables["Customer"].AsEnumerable();

var orders = ds.Tables["Order"].AsEnumerable();

var results = from customer in customers

                    join order in orders

                      on order.Field("CustomerID")

                         equals customer.Field("Customer")

                           into custOrders

              select customers;

Now we will populate a grid from this.

dataGridView1.DataSource = results.AsDataView();

As we specified on earlier blog apart from all these we can have 

Restriction	Where
Projection	Select,Selectmany
Ordering	OrderBy, ThenBy
Grouping	GroupBy
Quantifiers	Any,All
Partitioning	Take,Skip
Sets	Distinct,Union,Intersect,Except
Elements	First,ElementAt,FirstorDefault
Aggregation	Count,Sum,Iin,Max,Avarage
Conversion	ToArray,ToList
Casting	OfType(T)

All the examples given above is just the tip of iceberg on what you can do with LINQ and Lambda expressions. There is tons of samples and articles available on Internet on this, if you are looking for a way to simplify and speed up your business processing logic in your middle tier, LINQ is something you need to adopt!

LINQ to XML

Now we will take the second one:-  LINQ to XML. Every programmer has had to parse an XML file.LINQ to XML makes it easy for you to query and modify XML content.NET includes a library to manage XML (System.XML), LINQ to XML extends this namespace bringing the standard query operators and integrating them with XML documents (System.XML.Linq). We can load an XML document into memory and then use LINQ syntax to query it. For a developer accustomed with the SQL queries, LINQ syntax may seem like an up-side down language, but the elements of this syntax have a very comprehensive logic and the query develops naturally. Let’s go through the CURD operations with simple examples so that we will be clearer.Let’s take a sample xml file. Something like:

Now we are going to make a LINQ Query to this xml file. Please see the query below.
private void ReadFromXml()

{

   try

   {

     XDocument xmlDoc = XDocument.Load(Server.MapPath("TestFile.xml"));

     var MyFriends = from friend in xmlDoc.Descendants("Friend")

     select new

     {

        Name = friend.Element("Name").Value,

        City = friend.Element("City").Value,

        Dob = DateTime.Parse(friend.Element("Dob").Value)

                                };

        foreach (var friend in MyFriends)

        {

           Console.WriteLine("{0} {1}, {2}",

           friend.Name, friend.City, friend.Dob);

        }

      }

      catch (Exception)

      {

         throw;

      }

  }

This method makes use of LINQ to select all the data from the XML file, and then display it in the Literal control by looping through each item. Now if needed we can have some custom collection something like this.This will also help us to maintain a interface between xml and Linq for complex data representations.Also will make maintanance easy.But afcource for our simple code sample this is an optional.

public class Friends

{

   public string Name { get; set; }

   public string City { get; set; }

   public DateTime Dob { get; set; }

}

And we can just attach this to data source.

_contactDataGrid.ItemsSource = MyFriends.ToList<Friends>();

Simple right? Now we will go ahead and see how we can add a node to this xml file using LINQ. Using the classes from the System.Xml.Linq namespace, and the features available in .NET 3.5, constructing an XML document is very easy and very readable.

private void AddtoXML()

{

  try

  {

    XDocument xdocumnet = XDocument.Load(Server.MapPath("TestFile.xml"));

    xdocumnet.Element("Friends").Add(new XElement("Friend",

    new XElement("name", "Geetha"),

    new XElement("City", "World bank"),

    new XElement("Dob", "Project Manager")));

    xdocumnet.Save(Server.MapPath("TestFile.xml"));

    ReadXml();

  }

  catch{}

}

Constructing a document in this way is possible because of the functional construction feature in LINQ to XML. Functional construction is simply a means of create an entire document tree in a single statement. Just try to recollect what you were doing in place of this simple 2 line of code!

Interesting right? Now come with me we can try to go ahead and update one.

private void UpdateXMLUsingLINQ()

{

   try

   {

     XDocument xdocument = XDocument.Load(Server.MapPath("TestFile.xml"));

     var MyFriends = (from person in xdocument.Descendants("Friend")

     where person.Element("Name").Value.Equals("Nelson")

     select person).Single();

     MyFriends.Element("City").Value = "Cochin";

     xdocument.Save(Server.MapPath("TestFile.xml"));

   }

   catch{}

}

Nothing but just simple and interesting! Now we will delete one.

private void DeleteXMLbyLINQ()

{

  try

  {

    XDocument xdocument = XDocument.Load(Server.MapPath("TestFile.xml"));

    var persondata = (from person in xdocument.Descendants("Friend")

    where person.Element("Name").Value.Equals("Nelson")

    select person).Single();

    persondata.Remove();

    xdocument.Save(Server.MapPath("TestFile.xml"));

  }

  catch{}

}

I hope that these short examples offer a clear view on some LINQ on XML capabilities and that you will have now the curiosity to further explore the ways in which you can read data from an XML document, and, even more, to create, modify or transform XML files using the power of LINQ.We will take the LINQ to Dataset in Next blog.