Problem

You want to get an entity collection from a stored procedure using a code-second approach.

Solution

Code second refers to the practice of applying Code-First techniques to model an existing database schema.

Let’s say that you have a POCO model like the one shown in Listing 10-1.

Listing 10-1.  The Customer POCO Model

    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Company { get; set; }
        public string ContactTitle { get; set; }
    }

We’ve set up our DbContext subclass and have configured our Customer entities in Listing 10-2.

Listing 10-2.  The DbContext Subclass for Customer Entities

    public class EF6RecipesContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }
 
        public EF6RecipesContext() : base("name=EF6CodeFirstRecipesContext")
        {
             
        }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
 
            modelBuilder.Types<Customer>()
                        .Configure(c =>
                           {
                               c.HasKey(cust => cust.CustomerId);
                                            
                               c.Property(cust => cust.CustomerId)                                           .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                                            
                               c.Property(cust => cust.Name)
                                .HasMaxLength(50);
 
                               c.Property(cust => cust.Company)
                                .HasMaxLength(50);
 
                               c.Property(cust => cust.ContactTitle)
                                .HasMaxLength(50);
 
                               c.ToTable("Customer", "Chapter10");
                           });
        }
    }

In the database, we have defined the stored procedure in Listing 10-3, which returns customers for given a company name and customer title.

Listing 10-3.  GetCustomers Returns All of the Customers with the Given Title in the Given Company.

create procedure Chapter10.GetCustomers
(@Company varchar(50),@ContactTitle varchar(50))
as
begin
select * from
chapter10.Customer where
(@Company is null or Company = @Company) and
(@ContactTitle is null or ContactTitle = @ContactTitle)
End

To use the GetCustomers stored procedure in the model, do the following.

1. Create a new public method called GetCustomers in the DbContext subclass that takes two string parameters and returns a collection of Customer objects, as shown in Listing 10-4.

   Listing 10-4.  A New Method to Return a Collection of Customer Objects

   public ICollection<Customer> GetCustomers(string company, string contactTitle)
   {
       throw new NotImplementedException();
   }

2. Implement the GetCustomers() method by calling SqlQuery on the DbContext.Database object (see Listing 10-5).

   Listing 10-5.  DbContext Subclass with GetCustomers() Implementation

   public class EF6RecipesContext : DbContext
   {
       public DbSet<Customer> Customers { get; set; }
    
       public EF6RecipesContext() : base("name=EF6CodeFirstRecipesContext")
       {
                
       }
    
       protected override void OnModelCreating(DbModelBuilder modelBuilder)
       {
           base.OnModelCreating(modelBuilder);
    
           modelBuilder.Types<Customer>()
                       .Configure(c =>
                              {
                                  c.HasKey(cust => cust.CustomerId);
                                               
                                  c.Property(cust => cust.CustomerId)
                                      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                                               
                                  c.Property(cust => cust.Name)
                                   .HasMaxLength(50);
    
                                  c.Property(cust => cust.Company)
                                   .HasMaxLength(50);
    
                                  c.Property(cust => cust.ContactTitle)
                                   .HasMaxLength(50);
    
                                  c.ToTable("Customer", "Chapter10");
                              });
       }
    
       public ICollection<Customer> GetCustomers(string company, string contactTitle)
       {
           return Database.SqlQuery<Customer>("EXEC Chapter10.GetCustomers @Company,                         @ContactTitle"
                                     , new SqlParameter("Company", company)
                                     , new SqlParameter("ContactTitle", contactTitle))
                                     .ToList();
       }
   }

3. Follow the pattern in Listing 10-6 to use the GetCustomers stored procedure.

   Listing 10-6.   Querying the Model with the GetCustomers Stored Procedure via the GetCustomers() Method

   //Add customers to the database that we will query with our stored procedure.
   using (var context = new EF6RecipesContext())
   {
       var c1 = new Customer {Name = "Robin Steele", Company = "GoShopNow.com",
                              ContactTitle="CEO"};
       var c2 = new Customer {Name = "Orin Torrey", Company = "GoShopNow.com",
                              ContactTitle="Sales Manager"};
       var c3 = new Customer {Name = "Robert Lancaster", Company = "GoShopNow.com",
                              ContactTitle = "Sales Manager"};
       var c4 = new Customer { Name = "Julie Stevens", Company = "GoShopNow.com",
                              ContactTitle = "Sales Manager" };
       context.Customers.Add(c1);
       context.Customers.Add(c2);
       context.Customers.Add(c3);
       context.Customers.Add(c4);
       context.SaveChanges();
   }
    
   using (var context = new EF6RecipesContext())
   {
       var allCustomers = context.GetCustomers("GoShopNow.com", "Sales Manager");
       Console.WriteLine("Customers that are Sales Managers at GoShopNow.com");
       foreach (var c in allCustomers)
       {
           Console.WriteLine("Customer: {0}", c.Name);
       }
   }

The following is the output of the code in Listing 10-6:

Customers that are Sales Managers at GoShopNow.com
Customer: Orin Torrey
Customer: Robert Lancaster
Customer: Julie Stevens

How It Works

To retrieve an entity collection from a stored procedure in the database, we implemented a new method in the DbContext subclass called GetCustomers(). Within the method implementation, we call DbContext.Database.SqlQuery<T>() to execute the GetCustomers stored procedure, which we defined in Listing 10-3.

The SqlQuery() method can be used to execute nearly any DML statement that returns a result set. The method takes a string parameter to specify the query to execute, as well as additional SQL parameters to be substituted in the query itself. The SqlQuery<T>() generic method will return a strongly-typed collection of T entities, which allows the developer to avoid enumerating and casting a collection of objects.

Problem

You want to retrieve values from one or more output parameters of a stored procedure.

Solution

Let’s say you have a model like the one shown in Figure 10-1.

For a given date, you want to know the total number of rentals, the total rental payments made, and the vehicles rented. The stored procedure in Listing 10-7 is one way to get the information you want.

Listing 10-7.  A Stored Procedure for the Vehicles Rented, the Number of Rentals, and the Total Rental Payments

create procedure [chapter10].[GetVehiclesWithRentals]
(@date date,
@TotalRentals int output,
@TotalPayments decimal(18,2) output)
as
begin
  select @TotalRentals = COUNT(*), @TotalPayments = SUM(payment)
  from chapter10.Rental
  where RentalDate = @date
 
  select distinct v.*
  from chapter10.Vehicle v join chapter10.Rental r
  on v.VehicleId = r.VehicleId
end

To use the stored procedure in Listing 10-7 in the model, do the following.

1. Right-click the design surface, and select Update Model From Database. In the dialog box, select the GetVehiclesWithRentals stored procedure. Click Finish to add the stored procedure to the model.
2. Right-click the design surface, and select Add Function Import. Select the GetVehiclesWithRentals stored procedure from the Stored Procedure Name drop-down. In the Function Import Name text box, enter GetVehiclesWithRentals. This will be the name used for the method in the model. Select the Entities Return Type, and select Vehicle in the drop-down. Click OK.
3. Follow the pattern in Listing 10-8 to use the GetVehiclesWithRentals stored procedure.

Listing 10-8.  Querying the Model Using the GetVehiclesWithRentals Stored Procedure via the GetVehiclesWithRentals() method

using (var context = new EF6RecipesContext())
{
    var car1 = new Vehicle { Manufacturer = "Toyota", Model = "Camry",
                             Year = 2013 };
    var car2 = new Vehicle { Manufacturer = "Chevrolet", Model = "Corvette",
                             Year = 2013 };
    var r1 = new Rental { Vehicle = car1,
                          RentalDate = DateTime.Parse("5/7/2013"),
                          Payment = 59.95M };
    var r2 = new Rental { Vehicle = car2,
                          RentalDate = DateTime.Parse("5/7/2013"),
                          Payment = 139.95M };
    context.AddToRentals(r1);
    context.AddToRentals(r2);
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    string reportDate = "5/7/2013";
    var totalRentals = new ObjectParameter("TotalRentals", typeof(int));
    var totalPayments = new ObjectParameter("TotalPayments", typeof(decimal));
    var vehicles = context.GetVehiclesWithRentals(DateTime.Parse(reportDate),
                     totalRentals, totalPayments);
    Console.WriteLine("Rental Activity for {0}",reportDate);
    Console.WriteLine("Vehicles Rented");
    foreach(var vehicle in vehicles)
    {
        Console.WriteLine("{0} {1} {2}",vehicle.Year.ToString(),
                           vehicle.Manufacturer, vehicle.Model);
    }
    Console.WriteLine("Total Rentals: {0}",
                       ((int)totalRentals.Value).ToString());
    Console.WriteLine("Total Payments: {0}",
                       ((decimal)totalPayments.Value).ToString("C"));
}

The following is the output of the code in Listing 10-8:

Rental Activity for 5/7/2013
Vehicles Rented
2013 Toyota Camry
2013 Chevrolet Corvette
Total Rentals: 2
Total Payments: $200.00

How It Works

When we updated the model with the GetVehiclesWithRentals stored procedure, the wizard updated the store model with the stored procedure. By importing the function (in Step 2), we updated the conceptual model. The result is that the stored procedure is exposed as the GetVehiclesWithRentals() method, which has a signature semantically similar to the stored procedure.

There is one important thing to note when calling the GetVehiclesWithRentals() method: the returned entity collection must be materialized before the output parameters will become available. This should not be too surprising to those who have used multiple result sets in ADO.NET. The data reader must be advanced (with the NextResult() method) to the next result set. Similarly, the entire returned entity collection must be accessed or disposed before the output parameters can be accessed.

In our example, it is not enough to materialize the first vehicle for the output parameters to become available. The entire collection must be materialized. This means moving the lines that print the total rentals and total payments to a position after the foreach loop. Alternatively, we could materialize the entire collection with the ToList() method and then iterate through the list. This would allow us to access the output parameters prior to iterating through the collection.

Problem

You want to use a stored procedure that returns a result set containing a single scalar value.

Solution

Let’s say you have a model like the one shown in Figure 10-2.

You want to use a stored procedure that returns the total amount withdrawn from a given ATM on a given date. The code in Listing 10-9 is one way to implement this stored procedure.

Listing 10-9.  The GetWithdrawals Stored Procedure That Returns the Total Amount Withdrawn from a Given ATM on a Given Date

create procedure [Chapter10].[GetWithdrawals]
(@ATMId int, @WithdrawalDate date)
as
begin
        select SUM(amount) TotalWithdrawals
        from Chapter10.ATMWithdrawal
        where ATMId = @ATMId and [date] = @WithdrawalDate
end

To use the stored procedure in Listing 10-9 in the model, do the following:

1. Right-click the design surface, and select Update Model From Database. In the dialog box, select the GetWithdrawals stored procedure. Click Finish to add the stored procedure to the model.
2. Right-click the design surface, and select Add Function Import. Select the GetWithdrawals stored procedure from the Stored Procedure Name drop-down. In the Function Import Name text box, enter GetWithdrawals. This will be the name used for the method in the model. Select the Scalars Return Type, and select Decimal in the drop-down. Click OK.
3. Follow the pattern in Listing 10-10 to use the GetWithdrawals stored procedure.

   Listing 10-10.  Querying the Model with the GetWithdrawals Stored Procedure via the GetWithdrawals() Method

   DateTime today = DateTime.Parse("5/7/2013");
   DateTime yesterday = DateTime.Parse("5/6/2013");
   using (var context = new EF6RecipesContext())
   {
       var atm = new ATMMachine { ATMId = 17, Location = "12th and Main" };
       atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 20.00M, Date= today});
       atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 100.00M, Date = today});
       atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 75.00M, Date = yesterday});
       atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 50.00M, Date=  today});
       context.ATMMachines.Add(atm);
       context.SaveChanges();
   }
    
   using (var context = new EF6RecipesContext())
   {
       var forToday = context.GetWithdrawals(17, today).FirstOrDefault();
       var forYesterday = context.GetWithdrawals(17, yesterday).FirstOrDefault();
       var atm = context.ATMMachines.Where(o => o.ATMId == 17).FirstOrDefault();
       Console.WriteLine("ATM Withdrawals for ATM at {0} at {1}",
                atm.ATMId.ToString(), atm.Location);
       Console.WriteLine("	{0} Total Withdrawn = {1}",
                yesterday.ToShortDateString(), forYesterday.Value.ToString("C"));
       Console.WriteLine("	{0} Total Withdrawn = {1}", today.ToShortDateString(),
                forToday.Value.ToString("C"));
   }

The following is the output from the code in Listing 10-10:

ATM Withdrawals for ATM at 17 at 12th and Main
        5/6/2013 Total Withdrawn = $75.00
        5/7/2013 Total Withdrawn = $170.00

How It Works

Notice that Entity Framework expects the stored procedure to return a collection of scalar values. In our example, our store procedure returns just one decimal value. We use the FirstOrDefault() method to extract this scalar from the collection.

Problem

You want to use a stored procedure that returns a complex type in the model.

Solution

Let’s say that you have a model with an Employee entity. Employee contains the employee’s ID, name, and a complex address type that holds the address, city, state, and ZIP code for the employee. The name of the complex type is EmployeeAddress. The property in the Employee entity is simply Address. The Employee entity is shown in Figure 10-3.

You want to use a stored procedure to return a collection of instances of the EmployeeAddress complex type. The stored procedure that returns the addresses might look like the one shown in Listing 10-11.

Listing 10-11.  A Stored Procedure to Return the Addresses for Employees in a Given City

create procedure [Chapter10].[GetEmployeeAddresses]
(@city varchar(50))
as
begin
        select [address], city, [state], ZIP
        from Chapter10.Employee where city = @city
end

To use the stored procedure in Listing 10-11 in the model, do the following.

1. Right-click the design surface, and select Update Model From Database. In the dialog box, select the GetEmployeeAddresses stored procedure. Click Finish to add the stored procedure to the model.
2. Right-click the design surface, and select Add Function Import. Select the GetEmployeeAddresses stored procedure from the Stored Procedure Name drop-down. In the Function Import Name text box, enter GetEmployeeAddresses. This will be the name used for the method in the model. Select the Complex Return Type, and select EmployeeAddress in the drop-down. Click OK.
3. Follow the pattern in Listing 10-12 to use the GetEmployeeAddresses stored procedure.

Listing 10-12.  Querying the Model Using the GetEmployeeAddresses Stored Procedure via the GetEmployeeAddresses() Method

using (var context = new EF6RecipesContext())
{
    var emp1 = new Employee { Name = "Lisa Jefferies",
                    Address = new EmployeeAddress {
                                 Address = "100 E. Main",
                                 City = "Fort Worth", State = "TX",
                                 ZIP = "76106" } };
    var emp2 = new Employee { Name = "Robert Jones",
                    Address = new EmployeeAddress {
                                Address = "3920 South Beach",
                                City = "Fort Worth", State = "TX",
                                ZIP = "76102" } };
    var emp3 = new Employee { Name = "Steven Chue",
                    Address = new EmployeeAddress {
                                Address = "129 Barker",
                                City = "Euless", State = "TX",
                                ZIP = "76092" } };
    var emp4 = new Employee { Name = "Karen Stevens",
                    Address = new EmployeeAddress {
                                Address = "108 W. Parker",
                                City = "Fort Worth", State = "TX",
                                ZIP = "76102" } };
    context.Employees.Add(emp1);
    context.Employees.Add(emp2);
    context.Employees.Add(emp3);
    context.Employees.Add(emp4);
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    Console.WriteLine("Employee addresses in Fort Worth, TX");
    foreach (var address in context.GetEmployeeAddresses("Fort Worth"))
    {
        Console.WriteLine("{0}, {1}, {2}, {3}", address.Address,
                           address.City, address.State, address.ZIP);
    }
}

The following is the output of the code in Listing 10-12:

Employee addresses in Fort Worth, TX
100 E. Main, Fort Worth, TX, 76106
3920 South Beach, Fort Worth, TX, 76102
108 W. Parker, Fort Worth, TX, 76102

How It Works

Complex types offer a convenient way to refactor repeated groups of properties into a single type that can be reused across many entities. In this recipe, we created a stored procedure that returned the address information for employees in a given city. In the model, we mapped these returned columns to the fields of the EmployeeAddress complex type. The GetEmployeeAdresses() method is defined by the Function Import Wizard to return a collection of instances of the EmployeeAddress type.

Complex types are often used to hold arbitrarily shaped data returned from a stored procedure. The data is not required to map to any entity in the model. Because complex types are not tracked by the object context, they are both a lightweight and efficient alternative to handling shaped data in the model.

Problem

You want to define a custom function inside the model rather than a stored procedure in the database.

Solution

Let’s say that you have a database that keeps track of members and the messages they have sent. Figure 10-4 shows one representation of this database.

It may be the case that, as an entry-level programmer, you have not been granted access to the database to create stored procedures. However, being wise and productive, you want to encapsulate the query logic for finding the members with the highest number of messages into a reusable custom function in the storage model procedure. The model looks like the one shown in Figure 10-5.

To define the custom function in the storage model, do the following:

1. Right-click the .edmx file, and select Open With XML (Text) Editor. This will open the .edmx file in the XML editor.

   Add the code in Listing 10-13 into the <Schema> element. This defines the custom function.

   Listing 10-13.  The Definition of the Custom Function MembersWithTheMostMessages

   <Function Name="MembersWithTheMostMessages" IsComposable="false">
     <CommandText>
       select m.*
       from chapter10.member m
       join
       (
       select msg.MemberId, count(msg.MessageId) as MessageCount
       from chapter10.message msg where datesent = @datesent
       group by msg.MemberId
       ) temp on m.MemberId = temp.MemberId
       order by temp.MessageCount desc
     </CommandText>
     <Parameter Name="datesent" Type="datetime" />
   </Function>

2. Open the .edmx file in the Designer. Right-click the design surface, and select Add Function Import. In the dialog box, select the MembersWithTheMostMessages in the Stored Procedure Name drop-down. Enter MembersWithTheMostMessages in the Function Import Name text box. Finally, select Entities as the return type and choose Member as the entity type. Click OK.
3. Follow the pattern in Listing 10-14 to use the MembersWithTheMostMessages() method, which exposes the MembersWithTheMostMessages custom function.

   Listing 10-14.  Using the MembersWithTheMostMessages Function via the MembersWithTheMostMessages() method

   DateTime today = DateTime.Parse("5/7/2013");
   using (var context = new EF6RecipesContext())
   {
       var mem1 = new Member { Name = "Jill Robertson" };
       var mem2 = new Member { Name = "Steven Rhodes" };
       mem1.Messages.Add(new Message { DateSent = today,
                                       MessageBody = "Hello Jim",
                                       Subject = "Hello" });
       mem1.Messages.Add(new Message { DateSent = today,
                                       MessageBody = "Wonderful weather!",
                                       Subject = "Weather" });
       mem1.Messages.Add(new Message { DateSent = today,
                                       MessageBody = "Meet me for lunch",
                                       Subject = "Lunch plans" });
       mem2.Messages.Add(new Message { DateSent = today,
                                       MessageBody = "Going to class today?",
                                       Subject = "What's up?" });
       context.Members.Add(mem1);
       context.Members.Add(mem2);
       context.SaveChanges();
   }
    
   using (var context = new EF6RecipesContext())
   {
       Console.WriteLine("Members by message count for {0}",
                          today.ToShortDateString());
       var members = context.MembersWithTheMostMessages(today);
       foreach (var member in members)
       {
           Console.WriteLine("Member: {0}", member.Name);
       }
   }

Following is the output of the code in Listing 10-14:

Members by message count for 5/7/2013
Member: Jill Robertson
Member: Steven Rhodes

How It Works

A custom function is different from a model-defined function (see Chapter 11) in that a custom function is defined in the storage model. This makes the custom function much more like a traditional stored procedure in a database. Just like a DefiningQuery in the storage model defines a “virtual” table that doesn’t really exist in the database, a custom function in the storage model is like a “virtual” stored procedure. Some in the Entity Framework community refer to custom functions as native functions. The Microsoft documentation uses the term “custom function,” so we’ll go with that.

The code in Listing 10-13 defines our custom function. We put this in the storage model section of the .edmx file by directly editing the file using the XML editor. Note that if you use the Update From Database Wizard to update the model with new objects from your database, the wizard will overwrite this section. So be careful to save out any changes that you’ve made to the storage model before you use the Update From Database Wizard.

Just like with the stored procedures in the previous recipes, we used the Function Import Wizard to map the custom function to a CLR method. This defines the name of the CLR method and the expected return type. In our case, the Custom Function returns a collection of instances of the Member entity.

In Listing 10-14, the code uses the MembersWithTheMostMessages() method to invoke the custom function. This is the same pattern we used with stored procedures.

Custom functions can be helpful in the following scenarios:

• You don’t have permissions to create the stored procedures you need in the database.
• You want to manage deployments of the code and the database separately. Using one or more custom functions, you can deploy your code without deploying new stored procedures for the database.
• The existing stored procedures in the database have parameters that are incompatible with your entities. Using custom functions, you can create an abstraction layer that drops, adds, or changes types between the stored procedure parameters and the properties on your entity.

Problem

You want to use a stored procedure to populate entities in a Table per Type inheritance model.

Solution

Let’s say the model looks like the one shown in Figure 10-6. In this model, the entities Magazine and DVD extend the base entity Media. In the underlying database, we have a table for each of these entities. We have modeled these tables using Table per Type inheritance. We want to use a stored procedure to obtain the data for this model from the database.

To create and use a stored procedure that returns these entities, do the following.

1. In your database, create the stored procedure in Listing 10-15.

   Listing 10-15.  The GetAllMedia Stored Procedure That Returns a Rowset with a Discriminator Column

   create procedure [Chapter10].[GetAllMedia]
   as
   begin
   select m.MediaId,c.Title,m.PublicationDate, null PlayTime,'Magazine' MediaType
   from chapter10.Media c join chapter10.Magazine m on c.MediaId = m.MediaId
   union
   select d.MediaId,c.Title,null,d.PlayTime,'DVD'
   from chapter10.Media c join chapter10.DVD d on c.MediaId = d.MediaId
   end

2. Right-click the design surface, and select Update Model from Database. Select the GetAllMedia stored procedure. Click Finish to add the stored procedure to the model.
3. Right-click the design surface, and select Add Function Import. In the dialog box, select the GetAllMedia stored procedure. Enter GetAllMedia in the Function Import Name text box. Select Entities as the type of collection and Media as the type of entity returned. Click OK. This will create the skeleton <FunctionImportMapping>.
4. Right-click the .edmx file, and select Open With XML Editor. Edit the <FunctionImportMapping> tag in the mapping section of the .edmx file to match the code in Listing 10-16. This maps the rows returned by the stored procedure either to the Magazine or to the DVD entity based on the MediaType column.

   Listing 10-16.  This FunctionImportMapping Conditionally Maps the Returned Rows to Either the Magazine or the DVD Entity.

   <FunctionImportMapping FunctionImportName="GetAllMedia"
    FunctionName="EF6RecipesModel.Store.GetAllMedia">
     <ResultMapping>
       <EntityTypeMapping TypeName="EF6RecipesModel.Magazine">
         <ScalarProperty ColumnName="PublicationDate" Name="PublicationDate"/>
         <Condition ColumnName="MediaType" Value="Magazine"/>
       </EntityTypeMapping>
       <EntityTypeMapping TypeName="EF6RecipesModel.DVD">
         <ScalarProperty ColumnName="PlayTime" Name="PlayTime"/>
         <Condition ColumnName="MediaType" Value="DVD"/>
       </EntityTypeMapping>
     </ResultMapping>
   </FunctionImportMapping>

5. Follow the pattern in Listing 10-17 to use the GetAllMedia stored procedure via the GetAllMedia() method.

   Listing 10-17.  Using the GetAllMedia Stored Procedure via the GetAllMedia() Method

   Using (var context = new EF6RecipesContext())
   {
       context.MediaSet.Add(new Magazine { Title = "Field and Stream",
                           PublicationDate = DateTime.Parse("6/12/1945") });
       context.MediaSet.Add(new Magazine { Title = "National Geographic",
                           PublicationDate = DateTime.Parse("7/15/1976") });
       context.MediaSet.Add(new DVD { Title = "Harmony Road",
                           PlayTime = "2 hours, 30 minutes" });
       context.SaveChanges();
   }
    
   using (var context = new EF6RecipesContext())
   {
       var allMedia = context.GetAllMedia();
       Console.WriteLine("All Media");
       Console.WriteLine("=========");
       foreach (var m in allMedia)
       {
           if (m is Magazine)
               Console.WriteLine("{0} Published: {1}", m.Title,
                               ((Magazine)m).PublicationDate.ToShortDateString());
           else if (m is DVD)
               Console.WriteLine("{0} Play Time: {1}", m.Title, ((DVD)m).PlayTime);
       }
   }

The following is the output of the code in Listing 10-17:

All Media
=========
Field and Stream Published: 6/12/1945
National Geographic Published: 7/15/1976
Harmony Road Play Time: 2 hours, 30 minutes

How It Works

The two key parts to the solution are the discriminator column injected into the result set by the stored procedure and the conditional mapping of the results to the Magazine and DVD entities.

The stored procedure in Listing 10-15 forms a union of rows from the Magazine and DVD tables, and it injects the strings Magazine or DVD into the MediaType discriminator column. For each select, we join to the Media table, which is represented in the model by the base entity, to include the Title column. All of the rows from all three tables are now in the result set with each row tagged to indicate the table from where it came.

With each row tagged with either Magazine or DVD, we conditionally map the rows either to the Magazine or DVD entities based on the tag or value in the discriminator column. This is done in the <FunctionImportMapping> section.

In Listing 10-17, we call the CLR method GetAllMedia(), which we mapped to the GetAllMedia stored procedure when we added the Function Import. When we call GetAllMedia(), the entire object graph is materialized with the inheritance hierarchy intact. We iterate through the collection, alternately printing out the Magazine and DVD entities.

Problem

You want to use a stored procedure to populate entities in a Table per Hierarchy inheritance model.

Solution

Suppose you have a model like the one shown in Figure 10-7. We have two derived entities: Instructor and Student. Because this model is using Table per Hierarchy inheritance, we have just one table in the database. The Person table has a discriminator column that is used to map the table to the derived entities. You want to populate the entities with a stored procedure.

To create and use a stored procedure that returns these entities, do the following:

1. In your database, create the stored procedure in Listing 10-18. This stored procedure returns all of the people in the hierarchy.

   Listing 10-18.  The GetAllPeople Stored Procedure, Which Returns All the People, Both Students and Instructors, in the Model

   create procedure [Chapter10].[GetAllPeople]
   as
   begin
   select * from chapter10.Person
   end

2. Right-click the design surface, and select Update Model from Database. Select the GetAllPeople stored procedure. Click Finish to add the stored procedure to the model.
3. Right-click the design surface, and select Add   Function Import. In the dialog box, select the GetAllPeople stored procedure. Enter GetAllPeople in the Function Import Name text box. Select Entities as the type of collection and Person as the type of entity returned. Click OK. This will create the skeleton <FunctionImportMapping> section.
4. Right-click the .edmx file, and select Open With   XML Editor. Edit the <FunctionImportMapping> tag in the mapping section of the .edmx file to match the code in Listing 10-19. This maps the rows returned by the stored procedure either to the Instructor or to Student entity based on the PersonType column.

   Listing 10-19.  The FunctionImportMapping Conditionally Maps Rows to Either the Instructor or Student Entity

   <FunctionImportMapping FunctionImportName="GetAllPeople"
           FunctionName="EF6RecipesModel.Store.GetAllPeople">
     <ResultMapping>
       <EntityTypeMapping TypeName="EFRecipesModel.Student">
         <ScalarProperty Name="Degree" ColumnName="Degree" />
         <Condition ColumnName="PersonType" Value="Student"/>
       </EntityTypeMapping>
       <EntityTypeMapping TypeName="EF6RecipesModel.Instructor">
         <ScalarProperty Name="Salary" ColumnName="Salary"/>
         <Condition ColumnName="PersonType" Value="Instructor"/>
       </EntityTypeMapping>
     </ResultMapping>
   </FunctionImportMapping>

5. Follow the pattern in Listing 10-20 to use the GetAllPeople stored procedure via the GetAllPeople() method

   Listing 10-20.  Querying the Model Using the GetAllPeople Stored Procedure via the GetAllPeople() Method.

   using (var context = new EF6RecipesContext())
   {
       context.People.Add(new Instructor { Name = "Karen Stanford",
                                                 Salary = 62500M });
       context.People.Add(new Instructor { Name = "Robert Morris",
                                                 Salary = 61800M });
       context.People.Add(new Student { Name = "Jill Mathers",
                                              Degree = "Computer Science" });
       context.People.Add(new Student { Name = "Steven Kennedy",
                                              Degree = "Math" });
       context.SaveChanges();
   }
    
   using (var context = new EF6RecipesContext())
   {
       Console.WriteLine("Instructors and Students");
       var allPeople = context.GetAllPeople();
       foreach (var person in allPeople)
       {
           if (person is Instructor)
               Console.WriteLine("Instructor {0} makes {1}/year",
                                   person.Name,
                                   ((Instructor)person).Salary.ToString("C"));
           else if (person is Student)
               Console.WriteLine("Student {0}'s major is {1}",
                                   person.Name, ((Student)person).Degree);
       }
   }

The following is the output of the code in Listing 10-20:

Instructors and Students
Instructor Karen Stanford makes $62,500.00/year
Instructor Robert Morris makes $61,800.00/year
Student Jill Mathers's major is Computer Science
Student Steven Kennedy's major is Math

How It Works

Using a stored procedure to populate entities in a Table per Hierarchy inheritance model turns out to be a little easier than for Table per Type (see Recipe 10-6). Here the stored procedure just selected all rows in the Person table. The PersonType column contains the discriminator value that we use in <FunctionImportMapping> in Listing 10-19 to map the rows conditionally either to the Student or to the Instructor entity. In Recipe 10-6, the stored procedure had to create the column. In this recipe as well as in Recipe 10-6, the key part is the conditional mapping in the <FunctionImportMapping> tag.

Problem

You want to map the Insert, Update, and Delete actions to stored procedures.

Solution

Let’s say you have a model with the Athlete entity shown in Figure 10-8. The underlying database has the Athlete table shown in Figure 10-9. You want to use stored procedures for the Insert, Update, and Delete actions.

To map stored procedures to the Insert, Update, and Delete actions for the Athlete entity, do the following:

1. In your database, create the stored procedures in Listing 10-21.

   Listing 10-21.   The Stored Procedures for the Insert, Update, and Delete Actions

   create procedure [chapter10].[InsertAthlete]
   (@Name varchar(50), @Height int, @Weight int)
   as
   begin
           insert into Chapter10.Athlete values (@Name, @Height, @Weight)
          select SCOPE_IDENTITY() as AthleteId
   end
   go
   
   create procedure [chapter10].[UpdateAthlete]
   (@AthleteId int, @Name varchar(50), @Height int, @Weight int)
   as
   begin
           update Chapter10.Athlete set Name = @Name, Height = @Height, [Weight] = @Weight
           where AthleteId = @AthleteId
   end
   go
   
   create procedure [chapter10].[DeleteAthlete]
   (@AthleteId int)
   as
   begin
           delete from Chapter10.Athlete where AthleteId = @AthleteId
   end

2. Right-click the design surface and select Update Model from Database. Select the new stored procedures from Listing 10-19 and click Finish. This will add the stored procedures to the model.
3. Right-click the Athlete Entity, and select Stored Procedure Mapping. Select the stored procedures for each of the actions. For the Insert action, map the return column AthleteId for the Insert action to the AthleteId property (see Figure 10-10).

How It Works

We updated the model with the stored procedures we created in the database. This makes the stored procedures available for use in the model. Once we have the stored procedures available in the model, we mapped them to the Insert, Update, and Delete actions for the entity.

In this recipe, the stored procedures are about as simple as you can get. They take in properties as parameters and perform the action. For the Insert stored procedure, we need to return the stored generated key for the entity. In this recipe, the stored generated key is just an identity column. We need to return this from the stored procedure for the Insert action and map this returned value to the AthleteId property. This is an important step. Without this, Entity Framework would not be able to get the entity key for the instance of the Athlete entity just inserted.

You may ask, “When do I map stored procedures to the actions?” In most cases, Entity Framework will generate efficient code for the Insert, Update, and Delete actions. You may also be wondering, “When would I ever need to replace this with my own stored procedures?” Here are the best-practice answers to this question.

• Your company requires you to use stored procedures for some or all of the Insert, Update, or Delete activity for certain tables.
• You have additional tasks to do during one or more of the actions. For example, you might want to manage an audit trail or perform some complex business logic, or perhaps you need to leverage a user’s privileges to execute stored procedures for security checking.
• Your entity is based on a QueryView (see Chapter 6 and Chapter 15) that requires you to map some or all of the actions to stored procedures.

The code in Listing 10-22 demonstrates inserting, deleting, and updating in the model. The code isn’t any different because of the mapping of the actions, and that’s fine. The fact that we have replaced the code that Entity Framework would have dynamically generated with our own stored procedures will not affect the code that uses the entity.

Listing 10-22.  Executing the Insert, Update, and Delete Actions

using (var context = new EF6RecipesContext())
{
    context.Athletes.Add(new Athlete { Name = "Nancy Steward",
                                Height = 167, Weight = 53 });
    context.Athletes.Add(new Athlete { Name = "Rob Achers",
                                Height = 170, Weight = 77 });
    context.Athletes.Add(new Athlete { Name = "Chuck Sanders",
                                Height = 171, Weight = 82 });
    context.Athletes.Add(new Athlete { Name = "Nancy Rodgers",
                                Height = 166, Weight = 59 });
    context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
    // do a delete and an update
    var all = context.Athletes;
    context.Delete(all.First(o => o.Name == "Nancy Steward"));
    all.First(o => o.Name == "Rob Achers").Weight = 80;
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    Console.WriteLine("All Athletes");
    Console.WriteLine("============");
    foreach (var athlete in context.Athletes)
    {
        Console.WriteLine("{0} weighs {1} Kg and is {2} cm in height",
         athlete.Name, athlete.Weight, athlete.Height);
    }
}

The following is the output of the code in Listing 10-22:

All Athletes
============
Rob Achers weighs 80 Kg and is 170 cm in height
Chuck Sanders weighs 82 Kg and is 171 cm in height
Nancy Rodgers weighs 59 Kg and is 166 cm in height

Problem

You want to use stored procedures for the Insert and Delete actions in a payload-free, many-to-many association. These stored procedures affect only the link table in the association and not the associated entities.

Solution

Let’s say that you have a many-to-many relationship between an Author table and a Book table. The link table, AuthorBook, is used as part of the relationship, as shown in Figure 10-11.

When you import these tables into a model, you get a model that looks like the one shown in Figure 10-12.

To use stored procedures for the Insert and Delete actions, do the following.

1. In your database, create the stored procedures in Listing 10-23.

   Listing 10-23.  The stored Procedures for the Insert and Delete Actions

   create procedure [chapter10].[InsertAuthorBook]
   (@AuthorId int,@BookId int)
   as
   begin
           insert into chapter10.AuthorBook(AuthorId,BookId) values (@AuthorId,@BookId)
   end
   go
     
   create procedure [chapter10].[DeleteAuthorBook]
   (@AuthorId int,@BookId int)
   as
   begin
           delete chapter10.AuthorBook where AuthorId = @AuthorId and BookId = @BookId
   end

2. Right-click the design surface, and select Update Model from Database. Select the new stored procedures from Listing 10-23 and click Finish. This will add the stored procedures to the model.
3. The current release of Entity Framework does not have designer support for mapping stored procedures to the Insert and Delete actions for an association. To perform this mapping manually, right-click the .edmx file and select Open With † XML Editor. Add the code in Listing 10-24 in the Mappings section inside the <AssociationSetMapping> tag.

   Listing 10-24.  Mapping the Stored Procedures to the Insert and Delete Actions for the Many-to-Many Association

   <ModificationFunctionMapping>
     <InsertFunction FunctionName="EF6RecipesModel.Store.InsertAuthorBook">
       <EndProperty Name="Author">
         <ScalarProperty Name="AuthorId" ParameterName="AuthorId"  />
       </EndProperty>
       <EndProperty Name="Book">
         <ScalarProperty Name="BookId" ParameterName="BookId" />
       </EndProperty>
     </InsertFunction>
     <DeleteFunction FunctionName="EF6RecipesModel.Store.DeleteAuthorBook">
       <EndProperty Name="Author">
         <ScalarProperty Name="AuthorId" ParameterName="AuthorId"  />
       </EndProperty>
       <EndProperty Name="Book">
         <ScalarProperty Name="BookId" ParameterName="BookId" />
       </EndProperty>
     </DeleteFunction>
   </ModificationFunctionMapping>

The code in Listing 10-25 demonstrates inserting into and deleting from the model. As you can see from the SQL Profiler output that follows, our InsertAuthorBook and DeleteAuthorBook stored procedures are called when Entity Framework updates the many-to-many association.

Listing 10-25.  Inserting into the Model

using (var context = new EF6RecipesContext())
{
    var auth1 = new Author { Name = "Jane Austin"};
    var book1 = new Book { Title = "Pride and Prejudice",
                           ISBN = "1848373104" };
    var book2 = new Book { Title = "Sense and Sensibility",
                           ISBN = "1440469563" };
    auth1.Books.Add(book1);
    auth1.Books.Add(book2);
    var auth2 = new Author { Name = "Audrey Niffenegger" };
    var book3 = new Book { Title = "The Time Traveler's Wife",
                           ISBN = "015602943X" };
    auth2.Books.Add(book3);
    context.Authors.Add(auth1);
    context.Authors.Add(auth2);
    context.SaveChanges();
    context.Delete(book1);
    context.SaveChanges();
}

Here is the output of the SQL Profiler showing the SQL statements that are executed by the code in Listing 10-25:

exec sp_executesql N'insert [Chapter10].[Author]([Name])
values (@0)
select [AuthorId]
from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',
 @0='Jane Austin'
 
exec sp_executesql N'insert [Chapter10].[Author]([Name])
values (@0)
select [AuthorId]
from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',
 @0='Audrey Niffenegger'
 
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
 @1 varchar(50)',@0='Pride and Prejudice',@1='1848373104'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
 @1 varchar(50)',@0='Sense and Sensibility',@1='1440469563'
 
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
 @1 varchar(50)',@0='The Time Traveler''s Wife',@1='015602943X'
 
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=1
 
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=2
 
exec [Chapter10].[InsertAuthorBook] @AuthorId=2,@BookId=3
 
exec [Chapter10].[DeleteAuthorBook] @AuthorId=1,@BookId=1
 
exec sp_executesql N'delete [Chapter10].[Book]
 where ([BookId] = @0)',N'@0 int',@0=7

How It Works

To map the stored procedures to the Insert and Delete actions for the many-to-many association, we created the stored procedures in our database and then updated the model with the stored procedures.

Because Entity Framework’s designer does not currently support mapping stored procedures to the Insert and Delete actions for associations, we need to edit the .edmx file directly. In the Mappings section, we added a <ModificationFunctionMapping> tag that maps the Insert and Delete actions for the association to our stored procedures. In this tag, we refer to the InsertAuthorBook and DeleteAuthorBook stored procedures, which are defined in the Store model because we updated the model with these stored procedures from the database.

In the trace from Listing 10-25, we can see not only the expected inserts for the Author and Book tables, but we can also see that our stored procedures are used to insert and delete the association.

Problems

You have a model that uses Table per Hierarchy inheritance, and you want to map the Insert, Update, and Delete actions to stored procedures.

Solution

Let’s say that your database contains a Product table that describes a couple of different kinds of products (see Figure 10-14). You have created a model with derived types for each of the product types represented in the Product table. The model looks like the one shown in Figure 10-14.

To map stored procedures to the Insert, Update, and Delete actions for this model, do the following:

1. In your database, create the stored procedures in Listing 10-26. These stored procedures will handle the Insert, Update, and Delete actions for the Book and DVD entities.

   Listing 10-26.  The Stored Procedure We Map to the Insert, Update, and Delete Actions for the Model

   create procedure [chapter10].[InsertBook]
   (@Title varchar(50), @Publisher varchar(50))
   as
   begin
           insert into Chapter10.Product (Title, Publisher, ProductType) values
              (@Title,@Publisher, 'Book')
           select SCOPE_IDENTITY() as ProductId
   end
   go
   create procedure [chapter10].[UpdateBook]
   (@Title varchar(50), @Publisher varchar(50), @ProductId int)
   as
   begin
           update Chapter10.Product set Title = @Title, Publisher = @Publisher
             where ProductId = @ProductId
   end
   go
     
   create procedure [chapter10].[DeleteBook]
   (@ProductId int)
   as
   begin
           delete from Chapter10.Product where ProductId = @ProductId
   end
   go
     
   create procedure [chapter10].[InsertDVD]
   (@Title varchar(50), @Rating varchar(50))
   as
   begin
           insert into Chapter10.Product (Title, Rating, ProductType) values
              (@Title, @Rating, 'DVD')
           select SCOPE_IDENTITY() as ProductId
   end
   go
     
   create procedure [chapter10].[DeleteDVD]
   (@ProductId int)
   as
   begin
           delete from Chapter10.Product where ProductId = @ProductId
   end
   go
     
   create procedure [chapter10].[UpdateDVD]
   (@Title varchar(50), @Rating varchar(50), @ProductId int)
   as
   begin
           update Chapter10.Product set Title = @Title, Rating = @Rating
             where ProductId = @ProductId
   end

2. Right-click the design surface, and select Update Model from Database. Select the newly created stored procedures, and click Finish to add them to the model.
3. Right-click the Book entity and select Stored Procedure Mapping. Map the InsertBook, UpdateBook, and DeleteBook stored procedures to the corresponding actions for the entity. Map the Result Column Binding for the Insert action to the ProductId property (see Figure 10-15).

   

   Figure 10-15. Mapping the stored procedures to the Insert, Update, and Delete actions for the Book entity. Be particularly careful to map the Result Column Binding to the ProductId property for the Insert action

4. Right-click the DVD entity, and select Stored Procedure Mapping. Map the InsertDVD, UpdateDVD, and DeleteDVD stored procedures to the corresponding actions for the entity. Map the Result Column Binding for the Insert action to the ProductId property (see Figure 10-16).

   

   Figure 10-16. Mapping the stored procedures to the Insert, Update, and Delete actions for the DVD entity

How It Works

We created the stored procedures for the Insert, Update, and Delete actions for both the Book and DVD entities and imported them into the model. Once we have these stored procedures in the model, we mapped them to the corresponding actions, being careful to map the Result Column Binding for the Insert action to the ProductId property. This ensures that the store generated key for the Product is mapped to the ProductId property.

The Table per Hierarchy inheritance is supported by the implementation of the Insert stored procedures. Each of them inserts the correct ProductType value. Given these values in the tables, Entity Framework can correctly materialize the derived entities.

The code in Listing 10-27 demonstrates inserting, updating, deleting, and querying the model.

Listing 10-27.  Exercising the Insert, Update, and Delete Actions

using (var context = new EF6RecipesContext())
{
    var book1 = new Book { Title = "A Day in the Life",
                           Publisher = "Colorful Press" };
    var book2 = new Book { Title = "Spring in October",
                           Publisher = "AnimalCover Press" };
    var dvd1 = new DVD { Title = "Saving Sergeant Pepper", Rating = "G" };
    var dvd2 = new DVD { Title = "Around The Block", Rating = "PG-13" };
    context.Products.Add(book1);
    context.Products.Add(book2);
    context.Products.Add(dvd1);
    context.Products.Add(dvd2);
    context.SaveChanges();
 
    // update a book and delete a dvd
    book1.Title = "A Day in the Life of Sergeant Pepper";
    context.Delete(dvd2);
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    Console.WriteLine("All Products");
    Console.WriteLine("============");
    foreach (var product in context.Products)
    {
        if (product is Book)
            Console.WriteLine("'{0}' published by {1}",
                  product.Title, ((Book)product).Publisher);
        else if (product is DVD)
            Console.WriteLine("'{0}' is rated {1}",
                  product.Title, ((DVD)product).Rating);
    }
}

The following is the output of the code in Listing 10-27:

All Products
============
'Spring in October' published by AnimalCover Press
'A Day in the Life of Sergeant Pepper' published by Colorful Press
'Saving Sergeant Pepper' is rated G