Dotneteers.net
All for .net, .net for all!

EF Code First Cookbook — Recipe #5: Reading Master-Detail Information

In the previous post (Recipe #4), you learned that Entity Framework Code First provides a simple way to create relationship between two entities. You have used the following definitions to describe the structure of Order and OrderDetail entities:

  1. [Table("Order")]
  2. public class Order
  3. {
  4.     public int Id { get; set; }
  5.  
  6.     [MaxLength(128)]
  7.     public string Customer { get; set; }
  8.  
  9.     public DateTime OrderDate { get; set; }
  10.     public decimal Total { get; set; }
  11.  
  12.     [MaxLength(5)]
  13.     [Column(TypeName = "varchar")]
  14.     public string Status { get; set; }
  15.  
  16.     public IEnumerable<OrderDetail> Details { get; set; }
  17. }
  18.  
  19. [Table("OrderDetail")]
  20. public class OrderDetail
  21. {
  22.     public int Id { get; set; }
  23.     public Order Order { get; set; }
  24.  
  25.     [MaxLength(64)]
  26.     public string Product { get; set; }
  27.     public int Amount { get; set; }
  28.     public decimal UnitPrice { get; set; }
  29. }

The relationship between these entities are described by the Details property of the Order class, and the Order property of OrderDetail class. Analyzing them, EF infers that there is a one-to-many relationship between Order and OrderDetail. The Details property is used as a navigation property to access the “many” OrderDetail instances belonging to an Order. Similarly, the Order property of OrderDetail is a navigation property to access the “one” Order instance owning an OrderDetail instance. In Recipe #4 you also learned that with a simple code snippet you can add orders with corresponding details:

  1. static void Main(string[] args)
  2. {
  3.     using (var context = new OrderDatabaseContext())
  4.     {
  5.         for (int i = 0; i < 5; i++)
  6.         {
  7.             var order = new Order
  8.                             {
  9.                                 Customer = "Customer " + i,
  10.                                 OrderDate = DateTime.Now,
  11.                                 Total = 10*i,
  12.                                 Status = "NEW",
  13.                             };
  14.             context.Orders.Add(order);
  15.             for (int j = 0; j < 3; j++)
  16.             {
  17.                 var detail = new OrderDetail
  18.                                  {
  19.                                      Amount = 10*j + i,
  20.                                      Product = "Product" + j,
  21.                                      UnitPrice = 10 + j,
  22.                                      Order = order
  23.                                  };
  24.                 context.Details.Add(detail);
  25.             }
  26.         }
  27.         context.SaveChanges();
  28.     }
  29. }

Here, setting the Order property of the detail instance provides the link used by EF to establish the physical relationship.

Recipe #5: Reading Orders with Related Details

It the last post you used SQL Server Management Studio to check that all orders and their details were correctly inserted into the database. This time you will use EF to display the orders added to the database.

Ingredients

  • LINQ queries
  • Property and constructor declarations
  • The Include method
  • Virtual Details property

Preparations

Download the source code and open the solution within the Start folder. It contains the same sample code as in Recipe #4, but the Recipe04 namespace of the original sample is reamed to Recipe05.

Instead of cooking the food at once, you will make experiments and try several techniques. At the end you will be able to find out which technique matches your gust.

How To Cook — First Experiment: Retrieving Details Manually

To display the newly added orders, follow these steps:

  1. Add the following code snippet at the beginning of the Main method:

    1. using (var context = new OrderDatabaseContext())
    2. {
    3.     foreach (var detail in context.Details)
    4.     {
    5.         context.Details.Remove(detail);
    6.     }
    7.  
    8.     foreach (var order in context.Orders)
    9.     {
    10.         context.Orders.Remove(order);
    11.     }
    12.     context.SaveChanges();
    13. }

    This code snippet removes all existing orders with their details, cleaning up the database for us. In a future recipe you will learn deleting entities from a database in details, right now simply accept that the code above — though it is not really elegant — does what it is supposed to do.

  2. Add the following code to the end of the Main method:

    1. // --- Read orders and the corresponding details
    2. using (var context = new OrderDatabaseContext())
    3. {
    4.     foreach (var order in context.Orders)
    5.     {
    6.         order.Details = from detail in context.Details
    7.                         where detail.Order.Id == order.Id
    8.                         select detail;
    9.         Console.WriteLine("Order with ID #{0} contains {1} details",
    10.             order.Id, order.Details.Count());
    11.     }
    12. }

    As you can see, this code iterates through the orders in the database and queries the details belonging to orders using LINQ.

Taste It (First Experiment)

Run the application with Ctrl + F5. The result is exactly what you expect, as shown in Figure 1.

f001

Figure 1: The results of the first experiment

Well, you can see the five orders inserted in the database, and each of them has three details, as expected. But wait for a moment! If EF is smart enough to infer the one-to-many relationship between Order and OrderDetail, why I need to write a LINQ query to retrieve the order details? Why doesn’t EF help me and does not retrieve details automatically?

As you may guess, EF can do it for you. There are several ways, and you can instruct EF about your intention.

How To Cook — Second Experiment: Retrieving Details Automatically

You do not need to add the LINQ query to retrieve order details. Entity Framework can do if for you by declaring your intention. To try it, change a few code details:

  1. Modify the definition of the Details property in the Order class to use IList instead of IEnumerable:

    1. public IList<OrderDetail> Details { get; set; }

  2. Add a default constructor to Order which initializes the Details collection:

    1. public Order()
    2. {
    3.     Details = new List<OrderDetail>();
    4. }

    Now, your Order class should look like this:

    1.     [Table("Order")]
    2.     public class Order
    3.     {
    4.         public Order()
    5.         {
    6.             Details = new List<OrderDetail>();
    7.         }
    8.  
    9.         public int Id { get; set; }
    10.  
    11.         [MaxLength(128)]
    12.         public string Customer { get; set; }
    13.  
    14.         public DateTime OrderDate { get; set; }
    15.         public decimal Total { get; set; }
    16.         [MaxLength(5)]
    17.         [Column(TypeName = "varchar")]
    18.         public string Status { get; set; }
    19.  
    20.         public IList<OrderDetail> Details { get; set; }
    21.     }

  3. Remove the code querying order details from the Main method, now the code snippet displaying orders should look like this:

    1. // --- Read orders and the corresponding details
    2. using (var context = new OrderDatabaseContext())
    3. {
    4.     foreach (var order in context.Orders)
    5.     {
    6.         Console.WriteLine("Order with ID #{0} contains {1} details",
    7.             order.Id, order.Details.Count());
    8.     }
    9. }

  4. Now run the application with Ctlr + F5. As shown in Figure 2, no details are read for any orders, due to the removal of the detail query code.

    f002

    Figure 2: No detail records are read

  5. Close the running application and add the following Include method call to the definition of the foreach statement:

    1. // --- Read orders and the corresponding details
    2. using (var context = new OrderDatabaseContext())
    3. {
    4.     foreach (var order in context.Orders.Include("Details"))
    5.     {
    6.         Console.WriteLine("Order with ID #{0} contains {1} details",
    7.             order.Id, order.Details.Count());
    8.     }
    9. }

Taste It (Second Experiment)

Now, start you application with Ctrl + F5, and check how the single Include method changed its behavior. As shown in Figure 3, this time all details are read again, just like in the first experiment.

f003

Figure 3: All order details are read due to the Include method

The Include method instructed Entity Framework that you want to read an Order instance with the corresponding details in a single operation. Entity Framework is smart enough to emit a query to the SQL Server and obtain orders with related details in one SQL SELECT statement.

How To Cook — Third Experiment: Lazy Loading

The behavior you have seen in the second experiment is great, but in certain situations may not be optimal. First, you must use the Include method when you want to obtain order details, second, although you transfer those details over the network, you may not use them. In situations, when you do not know in advance whether you will need the order detail information, lazy loading is a great solution. Lazy loading means that you read the details of an order exactly when you need to touch them.

Entity Framework supports lazy loading. To try how easy it is, follow these steps:

  1. Add the virtual modified to the Details property of the Order class:

    1. public virtual IList<OrderDetail> Details { get; set; }

  2. Remove the Include invocation from the code snippet listing the details:

    1. // --- Read orders and the corresponding details
    2. using (var context = new OrderDatabaseContext())
    3. {
    4.     foreach (var order in context.Orders)
    5.     {
    6.         Console.WriteLine("Order with ID #{0} contains {1} details",
    7.             order.Id, order.Details.Count());
    8.     }
    9. }

Taste It (Third Experiment)

Run your application again. It will display the same result as the second experiment (shown in Figure 3). Remember, the same code at the first attempt in the second experiment resulted in no detail record read. The key of the lazy loading behavior is the virtual keyword, as you will learn.

How It Works

In the first experiment you manually implemented the mechanism reading order details. However, you do not need to do it manually, because Entity Framework can do it for you.

In the second experiment the Include clause added to the Orders property of the data context instructed EF that you want to include details when reading orders. The SQL SELECT statements going to the database are generated so that orders and related details are retrieved with a single query. Using AnjLab profiler, you can observe the corresponding SELECT statement:

  1. SELECT
  2. [Project1].[Id] AS [Id],
  3. [Project1].[Customer] AS [Customer],
  4. [Project1].[OrderDate] AS [OrderDate],
  5. [Project1].[Total] AS [Total],
  6. [Project1].[Status] AS [Status],
  7. [Project1].[C1] AS [C1],
  8. [Project1].[Id1] AS [Id1],
  9. [Project1].[Product] AS [Product],
  10. [Project1].[Amount] AS [Amount],
  11. [Project1].[UnitPrice] AS [UnitPrice],
  12. [Project1].[Order_Id] AS [Order_Id]
  13. FROM ( SELECT
  14.     [Extent1].[Id] AS [Id],
  15.     [Extent1].[Customer] AS [Customer],
  16.     [Extent1].[OrderDate] AS [OrderDate],
  17.     [Extent1].[Total] AS [Total],
  18.     [Extent1].[Status] AS [Status],
  19.     [Extent2].[Id] AS [Id1],
  20.     [Extent2].[Product] AS [Product],
  21.     [Extent2].[Amount] AS [Amount],
  22.     [Extent2].[UnitPrice] AS [UnitPrice],
  23.     [Extent2].[Order_Id] AS [Order_Id],
  24.     CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
  25.     FROM  [dbo].[Order] AS [Extent1]
  26.     LEFT OUTER JOIN [dbo].[OrderDetail] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Order_Id]
  27.   )AS [Project1]
  28. ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

In the third experiment you added the virtual modifier to the definition of the Details property in the Order class. This single modifier opens new horizons for EF. Instead of using the Order class, EF creates a proxy class wrapping around Order on the fly.  In this proxy an overridden Details property is used. The getter method implementation of Details checks whether its content is already read from the database, and if not, it automatically reads it before returning the collection of details.

With AnjLab you can catch that in this case Orders are read without details, and when you’re about counting the details, a second SELECT statement reads them from the database:

  1. -- Statement reading orders
  2. SELECT
  3. [Extent1].[Id] AS [Id],
  4. [Extent1].[Customer] AS [Customer],
  5. [Extent1].[OrderDate] AS [OrderDate],
  6. [Extent1].[Total] AS [Total],
  7. [Extent1].[Status] AS [Status]
  8. FROM [dbo].[Order] AS [Extent1]
  9.  
  10. -- Statement reading the details of a specific order
  11. exec sp_executesql N'SELECT
  12. [Extent1].[Id] AS [Id],
  13. [Extent1].[Product] AS [Product],
  14. [Extent1].[Amount] AS [Amount],
  15. [Extent1].[UnitPrice] AS [UnitPrice],
  16. [Extent1].[Order_Id] AS [Order_Id]
  17. FROM [dbo].[OrderDetail] AS [Extent1]
  18. WHERE ([Extent1].[Order_Id] IS NOT NULL) AND ([Extent1].[Order_Id] = @EntityKeyValue1)',
  19. N'@EntityKeyValue1 int',
  20. @EntityKeyValue1=96

Going On

By now, you have learned a lot about reading entities from the database. The next recipes help you understand how you can modify information.


Posted Jul 20 2011, 08:00 AM by inovak
Filed under:
Attachment: Recipe05.zip

Comments

jay wrote re: EF Code First Cookbook — Recipe #5: Reading Master-Detail Information
on Thu, Jul 21 2011 8:51

cook book series was great. i should really appreciate your effort to write this article series. i am kind of new to this EF. Went and read through a lot of blogs, but could not get my head around the EF.i stumbled across your blog and now I get how EF works.

Kudos.........great article

Preet wrote re: EF Code First Cookbook — Recipe #5: Reading Master-Detail Information
on Sun, Aug 28 2011 6:01

Hy again. This now requires the connection string to support MultipleActiveResultSets=True

Allen wrote re: EF Code First Cookbook — Recipe #5: Reading Master-Detail Information
on Wed, Nov 2 2011 16:43

Great Cooking Recipe series for EF! Thanks for the tip on the SQL Express profiler tool too.

Anil Lakhani wrote re: EF Code First Cookbook — Recipe #5: Reading Master-Detail Information
on Sun, Mar 18 2012 9:55

This is really a great series, I want to know that how class modifications handled in this. That is suppose if we add a new property in order class once it is datatable created in database. Then how it will update database table?

Nestor wrote re: EF Code First Cookbook — Recipe #5: Reading Master-Detail Information
on Thu, Dec 6 2012 21:01

Are there any more follow ups to this???  Learned more here that in the FAT EF books I got!!!

Excellent teaching skills. we want more!!

unworkable wrote unworkable
on Sat, Oct 4 2014 22:22

EF Code First Cookbook — Recipe #5: Reading Master-Detail Information - DiveDeeper's blog - Dotneteers.net

mouse click the up coming website wrote mouse click the up coming website
on Mon, Nov 24 2014 16:11

EF Code First Cookbook — Recipe #5: Reading Master-Detail Information - DiveDeeper's blog - Dotneteers.net