June, 2010

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : Loading Data in 4 ways

    • 3 Comments

     

    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).

    image

    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!!!

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : DataBinding with Windows Forms

    • 1 Comments

    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.

    image

    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!!!

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : DataBinding with ASP.NET

    • 3 Comments

    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.

    image

     

    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!!!

     

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : DataBinding with Windows Presentation Foundation (WPF)

    • 1 Comments

    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.

    image

    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!!!

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : Case Studies

    • 0 Comments

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

    Namoskar!!!

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0 : Model First Channel 9

    • 0 Comments

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

    image

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

    Namoskar!!!

Page 1 of 1 (6 items)