In this article by Sergey Barskiy, author of the book Code-First Development using Entity Framework, you will learn how to integrate Entity Framework with additional database objects, specifically views and stored procedures. We will see how to take advantage of existing stored procedures and functions to retrieve and change the data. You will learn how to persist changed entities from our context using stored procedures. We will gain an understanding of the advantages of asynchronous processing and see how Entity Framework supports this concept via its built-in API. Finally, you will learn why concurrency is important for a multi-user application and what options are available in Entity Framework to implement optimistic concurrency.

In this article, we will cover how to:

Get data from a view
Get data from a stored procedure or table-valued function
Map create, update, and delete operations on a table to a set of stored procedures
Use the asynchronous API to get and save the data
Implement multi-user concurrency handling

Working with views

Views in an RDBMS fulfill an important role. They allow developers to combine data from multiple tables into a structure that looks like a table, but do not provide persistence. Thus, we have an abstraction on top of raw table data. One can use this approach to provide different security rights, for example. We can also simplify queries we have to write, especially if we access the data defined by views quite frequently in multiple places in our code. Entity Framework Code-First does not fully support views as of now. As a result, we have to use a workaround. One approach would be to write code as if a view was really a table, that is, let Entity Framework define this table, then drop the table, and create a replacement view. We will still end up with strongly typed data with full query support. Let's start with the same database structure we used before, including person and person type. Our view will combine a few columns from the Person table and Person type name, as shown in the following code snippet:

public class PersonViewInfo
{
public int PersonId { get; set; }
public string TypeName { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}

Here is the same class in VB.NET:

Public Class PersonViewInfo
Public Property PersonId() As Integer
Public Property TypeName() As String
Public Property FirstName() As String
Public Property LastName() As String
End Class

Now, we need to create a configuration class for two reasons. We need to specify a primary key column because we do not follow the naming convention that Entity Framework assumes for primary keys. Then, we need to specify the table name, which will be our view name, as shown in the following code:

public class PersonViewInfoMap :
EntityTypeConfiguration<PersonViewInfo>
{
public PersonViewInfoMap()
{
   HasKey(p => p.PersonId);
   ToTable("PersonView");
}
}

Here is the same class in VB.NET:

Public Class PersonViewInfoMap
Inherits EntityTypeConfiguration(Of PersonViewInfo)
Public Sub New()
   HasKey(Function(p) p.PersonId)
   ToTable("PersonView")
End Sub
End Class

Finally, we need to add a property to our context that exposes this data, as shown here:

public DbSet<PersonViewInfo> PersonView { get; set; }

The same property in VB.NET looks quite familiar to us, as shown in the following code:

Property PersonView() As DbSet(Of PersonViewInfo)

Now, we need to work with our initializer to drop the table and create a view in its place. We are using one of the initializers we created before. When we cover migrations, we will see that the same approach works there as well, with virtually identical code. Here is the code we added to the Seed method of our initializer, as shown in the following code:

public class Initializer : 
 DropCreateDatabaseIfModelChanges<Context>
{
protected override void Seed(Context context)
{
   context.Database.ExecuteSqlCommand("DROP TABLE 
 PersonView");
   context.Database.ExecuteSqlCommand(
     @"CREATE VIEW [dbo].[PersonView]
     AS
     SELECT
       dbo.People.PersonId,
       dbo.People.FirstName,
       dbo.People.LastName,
       dbo.PersonTypes.TypeName
     FROM
       dbo.People
     INNER JOIN dbo.PersonTypes
       ON dbo.People.PersonTypeId = 
 dbo.PersonTypes.PersonTypeId
     ");
}
}

In the preceding code, we first drop the table using the ExecuteSqlCommand method of the Database object. This method is useful because it allows the developer to execute arbitrary SQL code against the backend. We call this method twice, the first time to drop the tables and the second time to create our view.

The same initializer code in VB.NET looks as follows:

Public Class Initializer
Inherits DropCreateDatabaseIfModelChanges(Of Context)
Protected Overrides Sub Seed(ByVal context As Context)
   context.Database.ExecuteSqlCommand("DROP TABLE 
 PersonView")
   context.Database.ExecuteSqlCommand( <![CDATA[
     CREATE VIEW [dbo].[PersonView]
     AS
     SELECT
       dbo.People.PersonId,
       dbo.People.FirstName,
       dbo.People.LastName,
       dbo.PersonTypes.TypeName
     FROM
       dbo.People
     INNER JOIN dbo.PersonTypes
       ON dbo.People.PersonTypeId = 
 dbo.PersonTypes.PersonTypeId]]>.Value())
End Sub
End Class

Since VB.NET does not support multiline strings such as C#, we are using XML literals instead, getting a value of a single node. This just makes SQL code more readable.

We are now ready to query our data. This is shown in the following code snippet:

using (var context = new Context())
{
var people = context.PersonView
   .Where(p => p.PersonId > 0)
   .OrderBy(p => p.LastName)
   .ToList();
foreach (var personViewInfo in people)
{
   Console.WriteLine(personViewInfo.LastName);
}

As we can see, there is literally no difference in accessing our view or any other table. Here is the same code in VB.NET:

Using context = New Context()
Dim people = context.PersonView _
     .Where(Function(p) p.PersonId > 0) _
     .OrderBy(Function(p) p.LastName) _
     .ToList()
For Each personViewInfo In people
   Console.WriteLine(personViewInfo.LastName)
Next
End Using

Although the view looks like a table, if we try to change and update an entity defined by this view, we will get an exception.

If we do not want to play around with tables in such a way, we can still use the initializer to define our view, but query the data using a different method of the Database object, SqlQuery. This method has the same parameters as ExecuteSqlCommand, but is expected to return a result set, in our case, a collection of PersonViewInfo objects, as shown in the following code:

using (var context = new Context())
{
var sql = @"SELECT * FROM PERSONVIEW WHERE PERSONID > {0} ";
var peopleViaCommand = 
 context.Database.SqlQuery<PersonViewInfo>(
   sql,
   0);
foreach (var personViewInfo in peopleViaCommand)
{
   Console.WriteLine(personViewInfo.LastName);
}
}

The SqlQuery method takes generic type parameters, which define what data will be materialized when a raw SQL command is executed. The text of the command itself is simply parameterized SQL. We need to use parameters to ensure that our dynamic code is not subject to SQL injection. SQL injection is a process in which a malicious user can execute arbitrary SQL code by providing specific input values. Entity Framework is not subject to such attacks on its own. Here is the same code in VB.NET:

Using context = New Context()
Dim sql = "SELECT * FROM PERSONVIEW WHERE PERSONID > {0} "
Dim peopleViaCommand = context.Database.SqlQuery(Of 
 PersonViewInfo)(sql, 0)
   For Each personViewInfo In peopleViaCommand
   Console.WriteLine(personViewInfo.LastName)
Next
End Using

We not only saw how to use views in Entity Framework, but saw two extremely useful methods of the Database object, which allows us to execute arbitrary SQL statements and optionally materialize the results of such queries. The generic type parameter does not have to be a class. You can use the native .NET type, such as a string or an integer.

It is not always necessary to use views. Entity Framework allows us to easily combine multiple tables in a single query.

Working with stored procedures

The process of working with stored procedures in Entity Framework is similar to the process of working with views. We will use the same two methods we just saw on the Database object—SqlQuery and ExecuteSqlCommand. In order to read a number of rows from a stored procedure, we simply need a class that we will use to materialize all the rows of retrieved data into a collection of instances of this class. For example, to read the data from the stored procedure, consider this query:

CREATE PROCEDURE [dbo].[SelectCompanies]
@dateAdded as DateTime
AS
BEGIN
SELECT CompanyId, CompanyName
FROM Companies
WHERE DateAdded > @dateAdded
END

We just need a class that matches the results of our stored procedure, as shown in the following code:

public class CompanyInfo
{
public int CompanyId { get; set; }
public string CompanyName { get; set; }
}

The same class looks as follows in VB.NET:

Public Class CompanyInfo
Property CompanyId() As Integer
Property CompanyName() As String
End Class

We are now able to read the data using the SqlQuery method, as shown in the following code:

sql = @"SelectCompanies {0}";
var companies = context.Database.SqlQuery<CompanyInfo>(
sql,
DateTime.Today.AddYears(-10));
foreach (var companyInfo in companies)
{

We specified which class we used to read the results of the query call. We also provided a formatted placeholder when we created our SQL statement for a parameter that the stored procedure takes. We provided a value for that parameter when we called SqlQuery. If one has to provide multiple parameters, one just needs to provide an array of values to SqlQuery and provide formatted placeholders, separated by commas as part of our SQL statement. We could have used a table values function instead of a stored procedure as well. Here is how the code looks in VB.NET:

sql = "SelectCompanies {0}"
Dim companies = context.Database.SqlQuery(Of CompanyInfo)(
sql,
DateTime.Today.AddYears(-10))
For Each companyInfo As CompanyInfo In companies

Another use case is when our stored procedure does not return any values, but instead simply issues a command against one or more tables in the database. It does not matter as much what a procedure does, just that it does not need to return a value. For example, here is a stored procedure that updates multiple rows in a table in our database:

CREATE PROCEDURE dbo.UpdateCompanies
@dateAdded as DateTime,
@activeFlag as Bit
AS
BEGIN
UPDATE Companies
Set DateAdded = @dateAdded,
IsActive = @activeFlag
END

In order to call this procedure, we will use ExecuteSqlCommand. This method returns a single value—the number of rows affected by the stored procedure or any other SQL statement. You do not need to capture this value if you are not interested in it, as shown in this code snippet:

var sql = @"UpdateCompanies {0}, {1}";
var rowsAffected =
context.Database.ExecuteSqlCommand(
   sql, DateTime.Now, true);

We see that we needed to provide two parameters. We needed to provide them in the exact same order the stored procedure expected them. They are passed into ExecuteSqlCommand as the parameter array, except we did not need to create an array explicitly. Here is how the code looks in VB.NET:

Dim sql = "UpdateCompanies {0}, {1}"
Dim rowsAffected =
context.Database.ExecuteSqlCommand( _
   sql, DateTime.Now, True)

Entity Framework eliminates the need for stored procedures to a large extent. However, there may still be reasons to use them. Some of the reasons include security standards, legacy database, or efficiency. For example, you may need to update thousands of rows in a single operation and retrieve them through Entity Framework; updating each row at a time and then saving those instances is not efficient. You could also update data inside any stored procedure, even if you call it with the SqlQuery method.

Developers can also execute any arbitrary SQL statements, following the exact same technique as stored procedures. Just provide your SQL statement, instead of the stored procedure name to the SqlQuery or ExecuteSqlCommand method.

Create, update, and delete entities with stored procedures

So far, we have always used the built-in functionality that comes with Entity Framework that generates SQL statements to insert, update, or delete the entities. There are use cases when we would want to use stored procedures to achieve the same result. Developers may have requirements to use stored procedures for security reasons. You may be dealing with an existing database that has these procedures already built in.

Entity Framework Code-First now has full support for such updates. We can configure the support for stored procedures using the familiar EntityTypeConfiguration class. We can do so simply by calling the MapToStoredProcedures method. Entity Framework will create stored procedures for us automatically if we let it manage database structures. We can override a stored procedure name or parameter names, if we want to, using appropriate overloads of the MapToStoredProcedures method. Let's use the Company table in our example:

public class CompanyMap :
EntityTypeConfiguration<Company>
{
public CompanyMap()
{
   MapToStoredProcedures();
}
}

If we just run the code to create or update the database, we will see new procedures created for us, named Company_Insert for an insert operation and similar names for other operations. Here is how the same class looks in VB.NET:

Public Class CompanyMap
Inherits EntityTypeConfiguration(Of Company)
Public Sub New()
   MapToStoredProcedures()
End Sub
End Class

Here is how we can customize our procedure names if necessary:

public class CompanyMap :
EntityTypeConfiguration<Company>
{
public CompanyMap()
{
   MapToStoredProcedures(config =>
     {
       config.Delete(
         procConfig =>
         {
           procConfig.HasName("CompanyDelete");
           procConfig.Parameter(company => 
 company.CompanyId, "companyId");
         });
       config.Insert(procConfig => 
 procConfig.HasName("CompanyInsert"));
       config.Update(procConfig => 
 procConfig.HasName("CompanyUpdate"));
     });
}
}

In this code, we performed the following:

Changed the stored procedure name that deletes a company to CompanyDelete
Changed the parameter name that this procedure accepts to companyId and specified that the value comes from the CompanyId property
Changed the stored procedure name that performs insert operations on CompanyInsert
Changed the stored procedure name that performs updates to CompanyUpdate

Here is how the code looks in VB.NET:

Public Class CompanyMap
Inherits EntityTypeConfiguration(Of Company)
Public Sub New()
   MapToStoredProcedures( _
     Sub(config)
       config.Delete(
         Sub(procConfig)
           procConfig.HasName("CompanyDelete")
           procConfig.Parameter(Function(company) 
 company.CompanyId, "companyId")
         End Sub
       )
       config.Insert(Function(procConfig) 
 procConfig.HasName("CompanyInsert"))
       config.Update(Function(procConfig) 
 procConfig.HasName("CompanyUpdate"))
     End Sub
   )
End Sub
End Class

Of course, if you do not need to customize the names, your code will be much simpler.

Summary

Entity Framework provides a lot of value to the developers, allowing them to use C# or VB.NET code to manipulate database data. However, sometimes we have to drop a level lower, accessing data a bit more directly through views, dynamic SQL statements and/or stored procedures. We can use the ExecuteSqlCommand method to execute any arbitrary SQL code, including raw SQL or stored procedure. We can use the SqlQuery method to retrieve data from a view, stored procedure, or any other SQL statement, and Entity Framework takes care of materializing the data for us, based on the result type we provide. It is important to follow best practices when providing parameters to those two methods to avoid SQL injection vulnerability.

Entity Framework also supports environments where there are requirements to perform all updates to entities via stored procedures. The framework will even write them for us, and we would only need to write one line of code per entity for this type of support, assuming we are happy with naming conventions and coding standards for such procedures.