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.




Leave a Reply

Your email address will not be published. Required fields are marked *