ASP.NET MVC 5 – walk-through – (Part 3)

Performing Raw SQL Queries

The Entity Framework Code First API includes methods that enable you to pass SQL commands directly to the database. You have the following options:

  • Use the DbSet.SqlQuery method for queries that return entity types. The returned objects must be of the type expected by the DbSetobject, and they are automatically tracked by the database context unless you turn tracking off.
  • Use the Database.SqlQuery method for queries that return types that aren’t entities. The returned data isn’t tracked by the database context, even if you use this method to retrieve entity types.
  • Use the Database.ExecuteSqlCommand for non-query commands.

One of the advantages of using the Entity Framework is that it avoids tying your code too closely to a particular method of storing data. It does this by generating SQL queries and commands for you, which also frees you from having to write them yourself.

You can disable tracking of entity objects in memory by using the AsNoTracking method. Typical scenarios in which you might want to do that include the following:

  • A query retrieves such a large volume of data that turning off tracking might noticeably enhance performance.
  • You want to attach an entity in order to update it, but you earlier retrieved the same entity for a different purpose. Because the entity is already being tracked by the database context, you can’t attach the entity that you want to change. One way to handle this situation is to use the AsNoTracking option with the earlier query.

Many developers write code to implement the repository and unit of work patterns as a wrapper around code that works with the Entity Framework. These patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD). However, writing additional code to implement these patterns is not always the best choice for applications that use EF, for several reasons:

  • The EF context class itself insulates your code from data-store-specific code.
  • The EF context class can act as a unit-of-work class for database updates that you do using EF.
  • Features introduced in Entity Framework 6 make it easier to implement TDD without writing repository code.

Most of the time you don’t need to be aware of this use of proxies, but there are exceptions:

  • In some scenarios you might want to prevent the Entity Framework from creating proxy instances. For example, when you’re serializing entities you generally want the POCO classes, not the proxy classes. One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects, another way is to  disable proxy creation.
  • When you instantiate an entity class using the new operator, you don’t get a proxy instance. This means you don’t get functionality such as lazy loading and automatic change tracking. This is typically okay; you generally don’t need lazy loading, because you’re creating a new entity that isn’t in the database, and you generally don’t need change tracking if you’re explicitly marking the entity as Added. However, if you do need lazy loading and you need change tracking, you can create new entity instances with proxies using the Create method of the DbSet class.
  • You might want to get an actual entity type from a proxy type. You can use the GetObjectType method of the ObjectContext class to get the actual entity type of a proxy type instance.

To Create SQL Server Database Project you can download template from:

post deployment scripts

SQL Server Object Explorer

Models are classes that you will use to work with the data. Each model mirrors a table in the database and contains properties that correspond to the columns in the table.

New Scaffolded Item



HTTP Error 403.14 – Forbidden – The Web server is configured to not list the contents of this directory

data annotations and validation

You can add a metadata class that contains the attributes. When you associate the model class to the metadata class, those attributes are applied to the model. In this approach, the model class can be regenerated without losing all of the attributes that have been applied to the metadata class.


These attributes will not be lost when you regenerate the model classes because the metadata attribute is applied in partial classes that are not regenerated.

enabling SSL for web projects

RegisterBundle, adding bootstrap css files into existing bundle.

How to seed data with AddOrUpdate with a complex key

Group By Multiple Columns

EF: Include with where clause

Default configuration settings are specified in the Machine.config file located in the %SystemRoot%\Microsoft.NET\Framework\versionNumber\CONFIG\ directory. Values are inherited by child sites and applications. If there is a configuration file in a child site or application, the inherited values do not appear, but can be overridden and are available to the configuration API.

The following default <anonymousIdentification> element is not explicitly configured in the Machine.config file or in the root Web.config file. However, it is the default configuration that is returned by an application.




ASP.NET MVC 5 – walk-through – (Part 2)

For full documentation please visit:

Repository Pattern with C# and Entity Framework

Sorting Example:

X.PagedList This is fork of Troy’s project PagedList ( The main different is that X.PagedList is portable assembly. It means, that you can use it not only in Web projects, but in Winforms, Window Phone, Silverlight and etc. projects.

Implement paging in asp net mvc

Load Balancers

sample ILogger interface


You write method overrides that are automatically called when query is about to be executed. In these methods you can examine or log the query that is being sent to the database, and you can change the query before it’s sent to the database or return something to Entity Framework yourself without even passing the query to the database.


Application_Start method

These lines of code are what causes your interceptor code to be run when Entity Framework sends queries to the database. Notice that because you created separate interceptor classes for transient error simulation and logging, you can independently enable and disable them.

Another option is to put this code in the DbConfiguration class that you created earlier to configure the execution policy.

Wherever you put this code, be careful not to execute DbInterception.Add for the same interceptor more than once, or you’ll get additional interceptor instances. For example, if you add the logging interceptor twice, you’ll see two logs for every SQL query.

Entity Framework Code First Approach

Entity Framework Code First End To End

If you deploy a database by running migrations automatically and you are deploying to a web site that runs on multiple servers, you could get multiple servers trying to run migrations at the same time.  Migrations are atomic, so if two servers try to run the same migration, one will succeed and the other will fail (assuming the operations can’t be done twice). In that scenario if you want to avoid those issues, you can call migrations manually and set up your own code so that it only happens once.

By using data annotation attributes, you can make one code change that will fix the display format in every view that shows the data. DataType attributes do not provide any validation.

You can also use attributes to control how your classes and properties are mapped to the database.

It means we can use DataAnnotations for both client-side formatting/validation and for database formatting, validation and mapping.

The DatabaseGenerated attribute with the None parameter on the CourseID property specifies that primary key values are provided by the user rather than generated by the database.

Column mapping is generally not required, because the Entity Framework usually chooses the appropriate SQL Server data type based on the CLR type that you define for the property. The CLR decimal type maps to a SQL Server decimal type. But in this case you know that the column will be holding currency amounts, and the money data type is more appropriate for that.

By convention, the Entity Framework enables cascade delete for non-nullable foreign keys and for many-to-many relationships. This can result in circular cascade delete rules, which will cause an exception when you try to add a migration.

Example on foreign key constraint and delete cascade

Many to many relationship in entity framework code first

Fluent API

You can use the fluent API to specify most of the formatting, validation, and mapping rules that you can do by using attributes.

Entity Framework Relationships Fluent API

First look at the Fluent API

Lazy loading in LINQ to SQL

Eager loading in LINQ to SQL

Difference between eager loading and lazy loading

Eager loading often offers the best performance, because a single query sent to the database is typically more efficient than separate queries for each entity retrieved.

On the other hand, in some scenarios lazy loading is more efficient. Eager loading might cause a very complex join to be generated, which SQL Server can’t process efficiently.

Lazy loading might perform better because eager loading would retrieve more data than you need. If performance is critical, it’s best to test performance both ways in order to make the best choice.

One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects.

Here are some other ways to disable lazy loading:

  • For specific navigation properties, omit the virtual keyword when you declare the property.
  • For all navigation properties, set LazyLoadingEnabled to false, put the following code in the constructor of your context class:

The code specifies eager loading for the Instructor.OfficeAssignment and the Instructor.Courses navigation property.

Eager loading is better than lazy loading only if the page is displayed more often with a course selected than without.

Inserting Updating and Deleting entities in Entity Framework

Normally the scaffolder doesn’t scaffold a primary key because the key value is generated by the database and can’t be changed and isn’t a meaningful value to be displayed to users. For Course entities the scaffolder does include an text box for the CourseID field because it understands that the DatabaseGeneratedOption.None attribute means the user should be able enter the primary key value.

Stored Procedures in Entity Framework

Using stored procedures with entity framework code first approach

ASP.NET MVC5 Async Queries


A web server has a limited number of threads available, and in high load situations all of the available threads might be in use. When that happens, the server can’t process new requests until the threads are freed up. With synchronous code, many threads may be tied up while they aren’t actually doing any work because they’re waiting for I/O to complete. With asynchronous code, when a process is waiting for I/O to complete, its thread is freed up for the server to use for processing other requests. As a result, asynchronous code enables server resources to be use more efficiently, and the server is enabled to handle more traffic without delays.

The following highlights show what was added to the synchronous code for the Index method to make it asynchronous:

Some things to be aware of when you are using asynchronous programming with the Entity Framework:

  • The async code is not thread safe. In in other words, don’t try to do multiple operations in parallel using the same context instance. For example do not update bank account balance of an user in parallel. Because context is not thread safe, you may be updating bank account with different value than you are intending.
  • If you want to take advantage of the performance benefits of async code, make sure that any library packages that you’re using (such as for paging), also use async if they call any Entity Framework methods that cause queries to be sent to the database.

Use stored procedures for inserting, updating, and deleting

This code instructs Entity Framework to use stored procedures for insert, update, and delete operations on the Department entity.

More details for implementation visit: or check videos above.

Concurrency Check in Entity Framework

A concurrency conflict occurs when one user displays an entity’s data in order to edit it, and then another user updates the same entity’s data before the first user’s change is written to the database. If you don’t enable the detection of such conflicts, whoever updates the database last overwrites the other user’s changes. In many applications, this risk is acceptable: if there are few users, or few updates, or if isn’t really critical if some changes are overwritten, the cost of programming for concurrency might outweigh the benefit. In that case, you don’t have to configure the application to handle concurrency conflicts.

If your application does need to prevent accidental data loss in concurrency scenarios, one way to do that is to use database locks. This is called pessimistic concurrency. For example, before you read a row from a database, you request a lock for read-only or for update access. If you lock a row for update access, no other users are allowed to lock the row either for read-only or update access, because they would get a copy of data that’s in the process of being changed. If you lock a row for read-only access, others can also lock it for read-only access but not for update.

Difference between optimistic and pessimistic concurrency control

Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases. For these reasons, not all database management systems support pessimistic concurrency. The Entity Framework provides no built-in support for it.

optimistic concurrency

The alternative to pessimistic concurrency is optimistic concurrency. Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. For example,

John runs the Departments Edit page, changes the Budget amount for the English department from $350,000.00 to $0.00.

Before John clicks Save, Jane runs the same page and changes the Start Date field from 9/1/2007 to 8/8/2013.

John clicks Save first and sees his change when the browser returns to the Index page, then Jane clicks Save. What happens next is determined by how you handle concurrency conflicts. Some of the options include the following:

  • You can keep track of which property a user has modified and update only the corresponding columns in the database. In the example scenario, no data would be lost, because different properties were updated by the two users. The next time someone browses the English department, they’ll see both John’s and Jane’s changes — a start date of 8/8/2013 and a budget of Zero dollars.

    You can let Jane’s change overwrite John’s change. The next time someone browses the English department, they’ll see 8/8/2013 and the restored $350,000.00 value. This is called a Client Wins or Last in Wins scenario. (All values from the client take precedence over what’s in the data store.) As noted in the introduction to this section, if you don’t do any coding for concurrency handling, this will happen automatically.

  • You can prevent Jane’s change from being updated in the database. Typically, you would display an error message, show her the current state of the data, and allow her to reapply her changes if she still wants to make them. This is called a Store Wins scenario. (The data-store values take precedence over the values submitted by the client.)

You can resolve conflicts by handling OptimisticConcurrencyException exceptions that the Entity Framework throws. In order to know when to throw these exceptions, the Entity Framework must be able to detect conflicts.

The rowversion value is a sequential number that’s incremented each time the row is updated. In an Update or Delete command, the Where clause includes the original value of the tracking column (the original row version) . If the row being updated has been changed by another user, the value in the rowversion column is different than the original value, so the Update or Delete statement can’t find the row to update because of the Where clause.

The attribute is called Timestamp because previous versions of SQL Server used a SQL timestamp data type before the SQL rowversion replaced it. The .Net type for rowversion is a byte array.

fluent API:

code implementation:

Using ROWVERSION or TIMESTAMP to detect concurrency conflicts


ASP.NET MVC 5 – walk-through – (Part 1)

For full documentation please visit:

Recommended Books

ASP.NET MVC encourages you to specify functionality or behavior only  once, and then have it be reflected everywhere in an application. This reduces  the amount of code you need to write and makes the code you do write less error  prone and easier to maintain.

You don’t actually have to specify which database to use,  Entity Framework will default to using  LocalDB. In this section we’ll explicitly add  a connection string in the Web.config file of the application.

LocalDB is a lightweight version of the SQL Server Express Database Engine that starts on demand and runs in user mode. LocalDB runs in a special execution mode of SQL Server Express that enables you to  work with databases as .mdf files. Typically, LocalDB database files  are kept in the App_Data folder of a web project.

SQL Server Express is not recommended for use in production web applications. LocalDB in particular should not be used for production with a web application because it is not designed to work with IIS. However, a LocalDB  database can be easily migrated to SQL Server or SQL Azure.

If you don’t specify a connection string, Entity Framework will create a LocalDB database in the users directory with the fully qualified name of the DbContext class.

The ViewBag is a dynamic object that provides a convenient late-bound way to pass information to a view.

By including a @model statement at the top of the view template file, you can specify the type of object that the view expects.

Models and data annotations:

Data Annotations:

Data Annotations explained:

HttpPost attribute specifies that that overload of the Edit method can be invoked only for POST requests. You could apply the HttpGet attribute to the first edit method, but that’s not necessary because it’s the default. The Bind attribute is another important security mechanism that keeps hackers from over-posting data to your model. You should only include properties in the bind attribute that you want to change.

The ValidateAntiForgeryToken attribute is used to prevent forgery of a request and is paired up with @Html.AntiForgeryToken() :

Avoiding cross site request forgery attacks

Modifying data in a GET method also violates HTTP best practices and the architectural REST pattern, which specifies that GET requests should not change the state of your application. In other words, performing a GET operation should be a safe operation that has no side effects and doesn’t modify your persisted data.

To force your computer to use US English, you can add the globalization element to the projects root web.config file. The following code shows the globalization element with the culture set to United States English.

By default, when you use Entity Framework Code First to automatically create a database, Code First adds a table to the database to help track whether the schema of the database is in sync with the model classes it was generated from. If they aren’t in sync, the Entity Framework throws an error. This makes it easier to track down issues at development time that you might otherwise only find (by obscure errors) at run time.

The AddOrUpdate method in the following code performs an “upsert” operation:

When there is a model change in your code-base (like adding a new property) database schema and entity framework models should match. Otherwise run-time will throw errors. There are a few approaches to resolving the error:

  1. Have the Entity Framework automatically drop and re-create the database based on the new model class schema. This approach is very convenient early in the development cycle when you are doing active development on a test database; it allows you to quickly evolve the model and database schema together. The downside, though, is that you lose existing data in the database — so you don’t want to use this approach on a production database! Using an initializer to automatically seed a database with test data is often a productive way to develop an application.
  2. Explicitly modify the schema of the existing database so that it matches the model classes. The advantage of this approach is that you keep your data. You can make this change either manually or by creating a database change script.
  3. Use Code First Migrations to update the database schema.

The validation support provided by ASP.NET MVC and Entity Framework Code  First is a great example of the DRY principle in action. You can declaratively  specify validation rules in one place (in the model class) and the rules  are enforced everywhere in the application.

ValudationMessageFor (single error per place)

ValidationSummary usage (multiple errors in one place)


Post/Delete Sample (ActionName(“Delete”):

Another common way to avoid a problem with methods that have identical names and signatures is to artificially change the signature of the POST method to include an unused parameter. For example, some developers add a parameter type FormCollection that is passed to the POST method, and then simply don’t use the parameter:

In Entity Framework terminology, an entity set typically corresponds to a database table, and an entity corresponds to a row in the table.

Entity Set => Table
Entity => Row

To tell Entity Framework to use your initializer class, add an element to the entityFramework element in the application Web.config file (the one in the root project folder), as shown in the following example:

As an alternative to setting the initializer in the Web.config file is to do it in code by adding a Database.SetInitializer statement to the Application_Start method in in the Global.asax.cs file.

Create Page Example:

It’s a security best practice to use the Include parameter with the Bind attribute to white list fields. It’s also possible to use the Exclude parameter to blacklist fields you want to exclude. The reason Include is more secure is that when you add a new property to the entity, the new field is not automatically protected by an Exclude list.

A production quality application would log the exception.

An alternative way to prevent over posting that is preferred by many developers is to use view models rather than entity classes with model binding. Include only the properties you want to update in the view model. Once the MVC model binder has finished, copy the view model properties to the entity instance, optionally using a tool such as AutoMapper. Use db.Entry on the entity instance to set its state to Unchanged, and then set Property(“PropertyName”).IsModified to true on each entity property that is included in the view model. This method works in both edit and create scenarios.

What is the use of C# Automapper ?

Using AutoMapper with ASP.NET MVC Views

This is server-side validation that you get by default

Sample Edit Example:

These changes implement a security best practice to prevent over posting. Bind attribute clears out any pre-existing data in fields not listed in the Include parameter.

When the SaveChanges method is called, the Modified flag causes the Entity Framework to create SQL statements to update the database row. Concurrency conflicts are ignored, and all columns of the database row are updated, including those that the user didn’t change. If you only want individual fields to be updated in the database, you can set the entity to Unchanged and set individual fields to Modified.

As a best practice to prevent over posting, the fields that you want to be updateable by the Edit page are white listed in the TryUpdateModel parameters. Currently there are no extra fields that you’re protecting, but listing the fields that you want the model binder to bind ensures that if you add fields to the data model in the future, they’re automatically protected until you explicitly add them here.

Difference between updatemodel and tryupdatemodel

Remote validation in mvc

The DbContext that reads an entity is disposed after a page is rendered. When the HttpPost Edit action method is called, a new request is made and you have a new instance of the DbContext, so you have to manually set the entity state to Modified. Then when you call SaveChanges, the Entity Framework updates all columns of the database row, because the context has no way to know which properties you changed.

If you want the SQL Update statement to update only the fields that the user actually changed, you can save the original values in some way (such as hidden fields) so that they are available when the HttpPost Edit method is called.

Deleting an Entity:

This code instantiates a Student entity using only the primary key value and then sets the entity state to Deleted. That’s all that the Entity Framework needs in order to delete the entity.

Insert, Update and Delete using Entity Framework:

In this video, Julie Lerman uses Visual Studio 2010 to show you how Entity Framework automatically creates insert, update, and delete commands based on changes to your entities and sends them to the database.

Twitter: Julie Lerman (@julielerman) | Twitter
Books From Julie Lerman: