June, 2010

Check out who all using at http://msdn.microsoft.com/en-us/data/ff625830.aspx 

Namoskar!!!

I have published one video at Channel 9 on Model First approach.

http://channel9.msdn.com/posts/wriju/Model-First-in-ADONET-Entity-Framework-4/

Namoskar!!!

We are working with Relational Database and using ADO.NET Entity Framework 4.0, it is inevitable that we need to load data. EF4 has improved Lazy Loading feature as compared to its previous version. We can also combine both Lazy and Eager loading.

Based on the amount of data we bring in and type of application we develop, we need to decide which one to choose. There is no single solution.

I have used two tables from Northwind database – Customer and Order (1 to many).

Now, each Customer will have n number of orders, so ideal for Lazy or Immediate loading.

Where is the catch?

This uses a feature introduced in ADO.NET 2.0 known as MultipleActiveResultSets (MARS). This gets added to the connection string of EF4.

Scenario 1: When Lazy loading is enabled (default)

By default in EF4 Lazy Loading is enabled through the Model designer. Which means if you write the below code, to get for every Order, it would send a SQL statement to the SQL Server.

using (var ctx = new NorthwindEntities())

{

    var q = from c in ctx.Customers

            select c;

    foreach (var cust in q)

    {

        Console.WriteLine("Customer : {0}", cust.CompanyName);

        Console.WriteLine("Respective Orders...");

        foreach (var ord in cust.Orders)

        {

            Console.WriteLine("Order given on {0}", ord.OrderDate);

        }

        Console.WriteLine();

        Console.ReadKey();

    }

}

In the background for the first foreach loop it would execute the query in SQL Server to fetch the data from only Customers table. Then for the inner foreach loop it would run individual query to get respective set of orders for each customer. So if there are 100 customers and 1000 orders, the total number of queries would be 1001.

Pros and Cons

This is acceptable in situations like where you have paged navigation and only when user asks for it, you load it, else leave it. This involves multiple round trips.

But when you want to pass data thorough layers, you would not be using it as because there will be no active open context. So when it would try to navigate to the child information no data will be found.

Scenario 2: Explicit Loading or conditional Lazy Loading

If you want to control the lazy loading on your own by loading the data only if logic requires, you should be disabling the Lazy Loading option and explicitly call it.

In the code, you need to add the line ctx.ContextOptions.LazyLoadingEnabled = false; This will switch off the Lazy Loading.  

//Lazy loading OFF

ctx.ContextOptions.LazyLoadingEnabled = false;

var q = from c in ctx.Customers

        select c;

foreach (var cust in q)

{

    Console.WriteLine("Customer : {0}", cust.CompanyName);

    Console.WriteLine("Respective Orders...");

    //Conditionally Load the child information

    if (1 == 1)

        cust.Orders.Load();

    foreach (var ord in cust.Orders)

    {

        Console.WriteLine("Order given on {0}", ord.OrderDate);

    }

    Console.WriteLine();

    Console.ReadKey();

}

Pros and Cons

This helps us to control the number of roundtrips based on application need. However, we need to be extra careful about testing our code before final deployment.

Scenario 3: When Eager Loading is enabled

If we switch off the Lazy Loading as discussed in Scenario 2 and want all the records to be available in the memory, then we need to use .Include(Entity).

//Lazy loading OFF

ctx.ContextOptions.LazyLoadingEnabled = false;

var q = from c in ctx.Customers.Include("Orders")

        select c;

foreach (var cust in q)

{

    Console.WriteLine("Customer : {0}", cust.CompanyName);

    Console.WriteLine("Respective Orders...");

    foreach (var ord in cust.Orders)

    {

        Console.WriteLine("Order given on {0}", ord.OrderDate);

    }

    Console.WriteLine();

    Console.ReadKey();

}

Pros and Cons

This brings all the Customer and Order information via a single query. This is required when we pass data from one layer to another layer. It also reduces the number of roundtrips. However, it can capture large about of your memory space.

If we check in the profiler we would see a single query like (removed few characters)

SELECT

[Project1].[C1] AS [C1],

[Project1].[CustomerID] AS [CustomerID],...

FROM ( SELECT

      [Extent1].[CustomerID] AS [CustomerID], ...

      1 AS [C1],

      [Extent2].[OrderID] AS [OrderID], ...

      CASE WHEN ([Extent2].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]

      FROM  [dbo].[Customers] AS [Extent1]

      LEFT OUTER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]

)  AS [Project1]

ORDER BY [Project1].[CustomerID] ASC, [Project1].[C2] ASC

Scenario 4: Combination of Eager and Lazy Loading

We could also use .Include and enable lazy loading if we wish to load few data lazily and some one-shot. There is no harm doing it as we are taking the benefit of both the features.

MSDN References

Loading Related Objects (Entity Framework)

http://msdn.microsoft.com/en-us/library/bb896272(VS.100).aspx

I would like to present this blog post to my sweet daughter Wrishika and lovely wife Saswati – without them I would have never had so much of energy to continue.

Namoskar!!!

This post is to give an idea on how easily we can bind and create master details display in WPF.

Here I am using Northwind sample database and using Category and Products tables. These two tables are connected with each other through CategoryId. We will create a model using these two tables. Please ensure that you check the Foreign Key checkbox while selecting table in the EF4 model creation wizard.

Now, add two controls ListBox with id listBox1 and DataGrid with dataGrid1. Set the AutoGeneratedColumns to checked for the DataGrid.

Now open the Show Data Source from Visual Studio from Data Source menu.

Drop Category to listBox1 and Product (under Category) to dataGrid1. That’s all. Not a single line of code is required.

Namoskar!!!

This post is to give an idea on how easily we can bind and create master details display in Windows Forms Application.

Here I am using Northwind sample database and using Category and Products tables. These two tables are connected with each other through CategoryId. We will create a model using these two tables. Please ensure that you check the Foreign Key checkbox while selecting table in the EF4 model creation wizard.

Now, in the Forms1 add two controls. Combo Box with id comboBox1 and Data Grid View with id dataGridView1.

Add the below code

private void Form1_Load(object sender, EventArgs e)

{

    using (var ctx = new NorthwindEntities())

    {

        comboBox1.DataSource = ctx.Categories.ToList();

        comboBox1.ValueMember = "CategoryID";

        comboBox1.DisplayMember = "CategoryName";

    }

}

And

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

{

    int iCatID = 0;

    try

    {

        iCatID = (int)comboBox1.SelectedValue;

    }

    catch { }

    if(iCatID > 0)

    {

        using (var ctx = new NorthwindEntities())

        {

            var q = from p in ctx.Products

                    where p.CategoryID == iCatID

                    select p;

            dataGridView1.DataSource = q.ToList();

        }

    }

}

Namoskar!!!

This post is to give an idea on how easily we can bind and create master details display in ASP.NET.

Here I am using Northwind Sample database and using Category and Products tables. These two tables are connected with each other through CategoryId. We will create a model using these two tables. Please ensure that you check the Foreign key checkbox while selecting table in the EF4 model creation wizard.

Then in WebForm1.aspx page add two controls with following html

DropDownList

<asp:DropDownList ID="DropDownList1" AutoPostBack="true" runat="server"

            onselectedindexchanged="DropDownList1_SelectedIndexChanged">

        </asp:DropDownList>

GridView

<asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

Add the code to the Page_Load,

if (!Page.IsPostBack)

{

    using (var ctx = new NorthwindEntities())

    {

        DropDownList1.DataSource = ctx.Categories;

        DropDownList1.DataTextField = "CategoryName";

        DropDownList1.DataValueField = "CategoryID";

        DropDownList1.DataBind();

    }

}

And to DropDownList1_SelectedIndexChanged

using (var ctx1 = new NorthwindEntities())

{

    int cid = (int.Parse(DropDownList1.SelectedValue));

    GridView1.DataSource = (from p in ctx1.Products

                            where p.CategoryID == cid

                            select p).ToList();

    GridView1.DataBind();

I will follow with WindowsForms and WPF way to implement.

Namoskar!!!