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

EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity

It is very seldom that your applications work only with a single entity represented by a single database table. Almost all applications use several entities with relationships among them — and these relationships are reflected in the database structure as well.

In this post you will extend Recipe #2 into an application that manages not only simple orders, but also order details.

Recipe #4: Creating Relationship between Orders and Order Details

In Recipe #2 you used the following definition to describe the Order entity:

  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.         [MaxLength(5)]
  12.         [Column(TypeName = "varchar")]
  13.         public string Status { get; set; }
  14.     }

To implement a scenario closer to the real world, you must represent order details as well. You also must establish a relationship between orders and their corresponding details.

Ingredients

  • OrderDetail entity class
  • Reference to OrderDetail form Order
  • Reference from Order to OrderDetail
  • New order detail instances assigned to appropriate orders

Preparations

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

How to Cook

In order to complete this recipe, follow these steps:

  1. Open the Recipe04 solution, and add a new class file to the Recipe04 project, name it OrderDetail.cs. Copy the following code into this new file:

    1. using System.ComponentModel.DataAnnotations;
    2.  
    3. namespace Recipe04
    4. {
    5.     [Table("OrderDetail")]
    6.     public class OrderDetail
    7.     {
    8.         public int Id { get; set; }
    9.         public Order Order { get; set; }
    10.  
    11.         [MaxLength(64)]
    12.         public string Product { get; set; }
    13.         public int Amount { get; set; }
    14.         public decimal UnitPrice { get; set; }
    15.     }
    16. }

    As you learned in Recipe #2, the MaxLenght annotation on the Product property constraints the maximum size of the string that can be put into the database. The Table attribute decorating the class is required to avoid the default OrderDetails plural table name assigned by EF Code First to the entity by default.

    The Order property references the order this detail belongs to. Using this property you will be able to navigate from a detail to the order owning it.

  2. Open the Order.cs file and add the following property definition right after the Status property:

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

    This property represents a set of order detail items belonging to the Order instance. Now, the definition of the Order class is the following:

    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.         [MaxLength(5)]
    12.         [Column(TypeName = "varchar")]
    13.         public string Status { get; set; }
    14.  
    15.         public  IEnumerable<OrderDetail> Details { get; set; }
    16.     }

  3. Open the OpenDatabaseContext.cs file and add the following property to the class:

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

    Now, the context definition looks like this:

    1. public class OrderDatabaseContext: DbContext
    2. {
    3.     public DbSet<Order> Orders { get; set; }
    4.     public DbSet<OrderDetail> Details { get; set; }
    5. }

    You’re ready with the refactoring of your entities. With the Order property added to the OrderDetail class, and Details property added to the Order class you have established the relationship among them.

  4. Copy the following code snippet into the Main method of the Program.cs file:

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

    This code snippet creates five orders and adds three details to each of them. Observe, the relationship between orders and corresponding details is created by setting the Order property of the new OrderDetail instance. Orders are addet to the Orders collection of the context, whilst order details to the Details collection. EF takes care of setting up the relationships when SaveChanges is called.

Taste It

Now, this recipe is ready to taste. To let its gust melt in your mouth, follow these steps:

  1. Run the application with Ctrl + F5. It completes in a few seconds, then close it by pressing a key.
  2. Launch SQL Server Management Studio. (In Recipe #3 you could learn installing it.)
  3. Expand the Databases node, then expand Recipe04.OrderDatabaseContext.
  4. Query the content of the dbo.Order and dbo.OrderDetail tables by right-clicking the corresponding node and running the Select Top 1000 Rows command. You can see the five order records and the related fifteen order details records as shown in Figure 1 and Figure 2.

f001

Figure 1: The content of the Order table

f002

Figure 2: The content of the OrderDetail table

You can observe that the OrderDetail table has an Order_Id field that points to the identifier of the appropriate Order record.

How It Works

Entity Framework recognizes your intention from the definition of entities, as the following code snippet illustrates:

  1. public class Order
  2. {
  3.     // ...
  4.     public IEnumerable<OrderDetail> Details { get; set; }
  5. }
  6.  
  7. public class OrderDetail
  8. {
  9.     // ...
  10.     public Order Order { get; set; }
  11.     // ...
  12. }
  13.  
  14. public class OrderDatabaseContext : DbContext
  15. {
  16.     public DbSet<Order> Orders { get; set; }
  17.     public DbSet<OrderDetail> Details { get; set; }
  18. }

From the OrderDatabaseContext definition EF infers that corresponding tables should be created for the Order and OrderDetail entities. According to the Details property in Order and the Order property in OrderDetail, it infers that there is a one-to-many relationship between Order and OrderDetail. EF automatically creates this relationship and adds an Order_Id foreign key field to the OrderDetail table pointing to the appropriate Order identifier.

In the Main method you have used the following pattern to insert orders and related details:

  1. using (var context = new OrderDatabaseContext())
  2. {
  3.     for (int i = 0; i < 5; i++)
  4.     {
  5.         var order = new Order // ...
  6.         context.Orders.Add(order);
  7.         for (int j = 0; j < 3; j++)
  8.         {
  9.             var detail = new OrderDetail
  10.                              {
  11.                                  // ...
  12.                                  Order = order
  13.                              };
  14.         }
  15.     }
  16.     context.SaveChanges();
  17. }

The SaveChanges operation recognizes that five new Order and fifteen new OrderDetail entities have been added to the context. Each OrderDetail explicitly references the Order record it belong to (by setting its Order property to the order instance). EF translates is to an SQL INSERT operation that sets the Order_Id field of the OrderDetail table when inserting the entities into the table. Using AnjLab (as you learned in Recipe #3) you can catch how an OrderDetail record is inserted by EF, as an example is shown in the following code snippet:

  1. exec sp_executesql N'insert [dbo].[OrderDetail]([Product], [Amount], [UnitPrice], [Order_Id])
  2. values (@0, @1, @2, @3)
  3. select [Id]
  4. from [dbo].[OrderDetail]
  5. where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 nvarchar(64),@1 int,@2 decimal(18,2),@3 int',
  6. @0=N'Product0',@1=1,@2=10.00,@3=2

Going On

Of course, it’s easy to retrieve entities from the database using the same straightforward style you have used when inserting new entities. Also, entity modification can be even more natural with a few changes. In the next recipe you will learn these features.


Posted Jul 18 2011, 06:00 AM by inovak
Filed under:
Attachment: Recipe04.zip

Comments

Simon Stewart wrote re: EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity
on Tue, Aug 16 2011 8:55

This is a great step-by-step guide.

Please carry on with the good work!

http://clomidnoprescription.beep.com/ wrote re: EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity
on Mon, Feb 25 2013 11:24

B5vG3J Say, you got a nice blog post.Really looking forward to read more. Cool.

buy clomid wrote re: EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity
on Fri, Mar 1 2013 15:22

zGTzPL I really like and appreciate your blog article.Much thanks again. Really Cool.

Boom Beach Diamond Hack wrote Boom Beach Diamond Hack
on Mon, Nov 24 2014 14:59

EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity - DiveDeeper's blog - Dotneteers.net

BUY GoPro HERO4 SILVER AT AMAZON HERE wrote BUY GoPro HERO4 SILVER AT AMAZON HERE
on Sun, Nov 30 2014 12:14

EF Code First Cookbook — Recipe #4: Adding Details to a Master Entity - DiveDeeper's blog - Dotneteers.net