Working with Views, Stored Procedures, the Asynchronous API, and Concurrency - Code-First Development with Entity Framework (2015)

Code-First Development with Entity Framework (2015)

Chapter 6. Working with Views, Stored Procedures, the Asynchronous API, and Concurrency

In this chapter, 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 chapter, 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

Tip

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.

Tip

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.

Note

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.

The asynchronous API

So far, all of our database operations with Entity Framework have been synchronous. In other words, our .NET program waited for any given database operation, such as a query or an update, to complete before moving forward. In many cases, there is nothing wrong with this approach. There are use cases, however, where an ability to perform such operations asynchronously is important. In these cases, we let .NET use its execution thread while the software waits for the database operation to complete. For example, if you are creating a web application utilizing the asynchronous approach, we can be more efficient with server resources, releasing web worker threads back to the thread pool while we are waiting for the database to finish processing a request, whether it is a save or retrieve operation. Even in a desktop application, the asynchronous API is useful because the user can potentially perform other tasks in the application, instead of waiting on a possibly time-consuming query or save operation. In other words, the .NET thread does not need to wait for a database thread to complete its work. In a number of applications, the asynchronous API does not provide benefits and could even be harmful from the performance perspective due to the thread context switching. Before using the asynchronous API, developers need to make sure it will benefit them.

Entity Framework exposes a number of asynchronous operations. By convention, all such methods end with the Async suffix. For save operations, we can use the SaveChangesAsync method on DbContext. There are many methods for query operations. For example, many aggregate functions have asynchronous counterparts, such as SumAsync or AverageAsync. We can also asynchronously read a result set into a list or an array using ToListAsync or ToArrayAsync, respectively. Also, we can enumerate through the results of a query using ForEachAsync. Let's look at a few examples.

This is how we can get the list of objects from a database asynchronously:

private static async Task<IEnumerable<Company>> GetCompaniesAsync()

{

using (var context = new Context())

{

return await context.Companies

.OrderBy(c => c.CompanyName)

.ToListAsync();

}

}

It is important to notice that we follow typical async/await usage patterns. Our function is flagged as async and returns a task object, specifically a task of a list of companies. We create a DbContext object inside our function. Then, we create creating a query that returns all companies, ordered by their names. Then, we return the results of this query wrapped inside an asynchronous list generation. We have to await this return value, as we need to follow async/await patterns. Here is how this code looks in VB.NET:

Private Async Function GetCompaniesAsync() As Task(Of IEnumerable(Of Company))

Using context = New Context()

Return Await context.Companies.OrderBy( _

Function(c) c.CompanyName).ToListAsync()

End Using

End Function

Note

Any Entity Framework query can be converted to its asynchronous version using ToListAsync or ToArrayAsync.

Next, let's create a new record asynchronously:

private static async Task<Company> AddCompanyAsync(Company company)

{

using (var context = new Context())

{

context.Companies.Add(company);

await context.SaveChangesAsync();

return company;

}

}

Again, we are wrapping the operation inside the async function. We are accepting a parameter, company in our case. We are adding this company to the context. Finally, we save asynchronously and return the saved company. Here is how the code looks in VB.NET:

Private Async Function AddCompanyAsync(company As Company) As Task(Of Company)

Using context = New Context()

context.Companies.Add(company)

Await context.SaveChangesAsync()

Return company

End Using

End Function

Next, we can locate a record asynchronously. We can use any number of methods here, such as Single or First. Both of them have asynchronous versions. We will use the Find method in our example, as shown in the following code snippet:

private static async Task<Company> FindCompanyAsync(int companyId)

{

using (var context = new Context())

{

return await context.Companies

.FindAsync(companyId);

}

}

We see the familiar asynchronous pattern in this code snippet as well. We just use FindAsync, which takes the exact same parameters as the synchronous version. In general, all asynchronous methods in Entity Framework have the same signature, as far as parameters are concerned, as their synchronous counterparts.

Here is how the same method looks in VB.NET:

Private Async Function FindCompanyAsync(companyId As Integer) As Task(Of Company)

Using context = New Context()

Return Await context.Companies.FindAsync(companyId)

End Using

End Function

As we mentioned before, aggregate functions have the async versions as well. For example, here is how we compute count asynchronously:

private static async Task<int> ComputeCountAsync()

{

using (var context = new Context())

{

return await context.Companies

.CountAsync(c => c.IsActive);

}

}

We use the CountAsync method and pass in a condition, which is exactly what we would have done if we were to call the synchronous version, the Count function. Here is how the code looks in VB.NET:

Private Async Function ComputeCountAsync() As Task(Of Integer)

Using context = New Context()

Return Await context.Companies.CountAsync( _

Function(c) c.IsActive)

End Using

End Function

If you would like to loop asynchronously through query results, you can also use ForEachAsync, which you can attach to any query as well. For example, here is how we can loop through Companies:

private static async Task LoopAsync()

{

using (var context = new Context())

{

await context.Companies.ForEachAsync(c =>

{

c.IsActive = true;

});

await context.SaveChangesAsync();

}

}

In the preceding code, we run through all type Companies, but we could have just as easily run through a result of any query, after applying an order or a filter. Here is what this method looks like in VB.NET:

Private Async Function LoopAsync() As Task

Using context = New Context()

Await context.Companies.ForEachAsync( _

Sub(c)

c.IsActive = True

End Sub)

Await context.SaveChangesAsync()

End Using

End Function

We followed the accepted naming conventions, adding the Async suffix to our asynchronous functions. Usually, these functions would be called from a method that is also flagged as async and we would have awaited a result of our asynchronous functions. If this is not possible, we can always use the Task API and wait for a task to complete. For example, we can access the result of a task, causing the current thread to pause and let the task finish executing, as shown in the following code snippet:

Console.WriteLine(

FindCompanyAsync(companyId).Result.CompanyName);

In this example, we call the previously defined function and then access the Result property of the task to cause the asynchronous function to finish executing. This is how the code would look in VB.NET:

Console.WriteLine(FindCompanyAsync(companyId).Result.CompanyName)

When deciding whether or not to use the asynchronous API, we need to research and make sure that there is a reason to do so. We should also ensure that the entire calling chain of methods is asynchronous to gain maximum coding benefits. Finally, resort to the Task API when you need to.

Handling concurrency

Most applications have to deal with concurrency. Concurrency is a circumstance where two users modify the same entity at the same time. There are two types of concurrency handling: optimistic and pessimistic. There is no concurrency where the last user always wins. When this happens, there is a silent data loss, where the first user's changes are overwritten without notice, so it is not frequently used. In the case of pessimistic concurrency, only one user can edit a record at a time and the second user gets anerror, stating that they cannot make any changes at that time. Although this approach is safe, it does not scale well and results in poor user experience. As a result, most applications use optimistic concurrency, allowing multiple users to make changes, but checking for a concurrency situation at the time changes are being saved. At that time if two users changed the same row of data, applications issue an error to the second user, letting them know that they need to redo the changes. Some developers at times go an extra mile and assist users in redoing their changes. Entity Framework comes with a built-in optimistic concurrency API. A developer has to pick a column that will play the role of the row version. The row version is incremented every time the row of data is updated. Any time an update query is issued against a row with concurrency columns, the current row version is put into the where clause; thus if data has changed since it was first retrieved, no rows are updated as the result of such SQL statement. Entity Framework checks the number of rows updated, and if this number is not 1, a concurrency exception is thrown. In the case of the SQL Server RowVersion, also known as TimeStamp, a column is used for concurrency. SQL Server automatically increments this column for all updates to each row. The matching type for the TimeStamp column in SQL Server is byte array in .NET. Let's start by updating our Person object to support concurrency. We are going to omit some properties for brevity, as shown in the following code snippet:

public class Person

{

public int PersonId { get; set; }

public byte[] RowVersion { get; set; }

}

We added a new property called RowVersion using the Byte array as the type. Here is how this change looks in VB.NET:

Public Class Person

Property PersonId() As Integer

Property RowVersion() As Byte()

End Class

We also need to configure this property using our EntityTypeConfiguration class to let Entity Framework know that we added a concurrency property, as shown in the following code snippet:

public class PersonMap : EntityTypeConfiguration<Person>

{

public PersonMap()

{

Property(p => p.RowVersion)

.IsFixedLength()

.HasMaxLength(8)

.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

.IsRowVersion();

}

}

We omitted some properties again, but configured RowVersion to be our concurrency column. We flagged it as such by calling the IsRowVersion method, as well as configuring the size for SQL Server and flagging it for Entity Framework as database generated. Technically, we only need to call the IsRowVersion method, but this code makes it clear as to how the property is configured. We can and should remove other method calls, as they are not needed. Here is how VB.NET code looks:

Public Class PersonMap

Inherits EntityTypeConfiguration(Of Person)

Public Sub New()

Me.Property(Function(p) p.RowVersion) _

.IsFixedLength() _

.HasMaxLength(8) _

.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed) _

.IsRowVersion()

End Sub

End Class

Now we are ready to write some code to ensure our concurrency configuration works. It is hard to simulate two users in a single routine, so we will play some tricks, using the knowledge we gained previously, as shown in the following code:

private static void ConcurrencyExample()

{

var person = new Person

{

BirthDate = new DateTime(1970, 1, 2),

FirstName = "Aaron",

HeightInFeet = 6M,

IsActive = true,

LastName = "Smith"

};

int personId;

using (var context = new Context())

{

context.People.Add(person);

context.SaveChanges();

personId = person.PersonId;

}

//simulate second user

using (var context = new Context())

{

context.People.Find(personId).IsActive = false;

context.SaveChanges();

}

//back to first user

try

{

using (var context = new Context())

{

context.Entry(person).State = EntityState.Unchanged;

person.IsActive = false;

context.SaveChanges();

}

Console.WriteLine("Concurrency error should occur!");

}

catch (DbUpdateConcurrencyException)

{

Console.WriteLine("Expected concurrency error");

}

Console.ReadKey();

}

This method is a bit lengthy, so let's walk through it. In the first few lines, we created a new person instance and added it to the database by adding it to the People collection, and then calling SaveChanges on our context. We then pretend to be a second user, updating the same row by calling the Find method, changing one property, and then issuing the SaveChanges call. This action will increment the row version inside the database. Next, we are pretended to be the first user, using the original person instance that still has the original row version value. We set the state to unmodified, thus attaching it to the context. Then, we changed a single property and saved the changes again. This time we get a specific concurrency exception of the DbUpdateConcurrencyException type. This is how the code looks in VB.NET:

Private Sub ConcurrencyExample()

Dim person = New Person() With {

.BirthDate = New DateTime(1970, 1, 2),

.FirstName = "Aaron",

.HeightInFeet = 6D,

.IsActive = True,

.LastName = "Smith"

}

Dim personId As Integer

Using context = New Context()

context.People.Add(person)

context.SaveChanges()

personId = person.PersonId

End Using

'simulate second user

Using context = New Context()

context.People.Find(personId).IsActive = False

context.SaveChanges()

End Using

'back to first user

Try

Using context = New Context()

context.Entry(person).State = EntityState.Unchanged

person.IsActive = False

context.SaveChanges()

End Using

Console.WriteLine("Concurrency error should occur!")

Catch exception As DbUpdateConcurrencyException

Console.WriteLine("Expected concurrency error")

End Try

Console.ReadKey()

End Sub

This exception handling code is something we always need to write when implementing concurrency. We need to show the user a nice descriptive message. At that point, they will need to refresh their data with the current database values and then redo the changes. If we, as developers, want to assist users in this task, we can use Entity Framework's DbEntityEntry class to get the current database values.

Self-test questions

Q1. You cannot get data from a view inside Entity Framework, true or false?

Q2. Which database method can be used to query and retrieve data using SQL statements?

1. ExecuteSqlCommand

2. Execute

3. SqlQuery

Q3. You have to write all SQL Server stored procedures by hand if you want to map CRUD operations for an entity type to a set of stored procedures, true or false?

Q4. There is no downside to using the asynchronous API, true or false?

Q5. Which method can be used to asynchronously save changes in DbContext?

1. SaveChanges

2. SaveChangesAsync

3. SaveChangesAsynchonously

Q6. What method needs to be called to mark a property as a concurrency property inside the entity type configuration class?

1. RowVersion()

2. HasDatabaseGenerationOption()

3. IsRowVersion()

Q7. What exception type indicates a concurrency error?

1. DbUpdateConcurrencyException

2. ConcurrencyException

3. OptimisticConcurrencyException

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.

Entity Framework now provides support for asynchronous operations, including both query and updates. Developers must take care when using such techniques to avoid potential performance implications. In some technologies, the asynchronous API fits really well, the Web API being a good example.

We must always take care of our data, avoiding data loss at all costs. This is where concurrency handling built into Entity Framework comes in. It allows us to provide users with appropriate feedback, while helping us to avoid silent data loss. We just need to mark a property as concurrency check property, and Entity Framework will throw an exception when two users make changes to the same entity at the same time. We just need to handle this exception and provide users with an application-specific error message.

In the next chapter, we will conclude our discussion of Entity Framework by learning how to update production database structure without data loss using, migrations.