Can I use Projections in EF Core…Absolutely!!

Before we drill into Projections, let’s find out what Projections really are:

Projection(s) is a powerful feature of LINQ that enables you to perform query operations on a data source, collection etc. It shapes the results of the data being queried into a different structure/format that the original data source is in.

It is a process to express queries in order to return a desired set of data other than the entire entity. Projection queries improve the efficiency of your application by only retrieving specific fields from your database.

A projection query lets you select only the data that is required from your model. It is done, by loading the data into a custom or anonymous type. This significantly improves the performance of your application, especially when you filter out the fields that contain huge amounts of data.

When you use the Select method, you can retrieve scalar properties and also navigation properties to retrieve data in the same query. Please see the code snippet below:

   private static void Projections()
            using (var db = new demo_departmentContext())
                var dept = db.Departments.Select(d => new { d.DepId, d.DepName, d.Location })
                    .Where(d => d.DepName.Contains("IT"))
                foreach (var item in dept)
                    Console.WriteLine("{0} - {1} - {2}", item.DepId, item.DepName, item.Location);

And following is the output:

EFCoreProjectionsOutputAs  you saw above using projections allow us to retrieve a subset of the properties from an object as opposed to the entire object. This causes a smaller amount of data to be returned in the query.

Posted in ORM | Comments Off on Can I use Projections in EF Core…Absolutely!!

Using Migrations in EF Core

If you are an Entity Framework veteran who has worked extensively with EF 6.x and prior versions, then Migrations should come as no surprise to you!

The good news is that as you transition from EF 6.x to EF Core, Migrations are not much different to what you are used to using with EF 6.x. So it begs the question, how do I use Migrations in EF Core. Well, this is exactly what this article is about.

PM>add-migration init
To undo this action, use Remove-Migration.

This action would create a migration script as seen in the image below. Pay close attention to the Migrations folder in that the add-migration command created a migration file xxx_init.cs. It also created a ModelSnapshot. This file is used by Entity Framework to keep state of the current model which is then used to apply changes from your model to the database when migrations are run the next time.

In EF 6.x, when you run the add-migration command, the ModelSnapshot file is created in the database which makes it harder to version control this file in Source Control. So the EF team decided to lump this file with the migration file in the Migrations folder instead to facilitate version control.



public partial class init : Migration
        protected override void Up(MigrationBuilder migrationBuilder)
                name: "Departments",
                columns: table => new
                    depID = table.Column(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    depName = table.Column(nullable: true),
                    location = table.Column(nullable: true)
                constraints: table =>
                    table.PrimaryKey("PK_Departments", x => x.depID);

                name: "Employees",
                columns: table => new
                    empID = table.Column(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    age = table.Column(nullable: false),
                    depID = table.Column(nullable: false),
                    empName = table.Column(nullable: true),
                    EmployeesempID = table.Column(nullable: true),
                    MiddleName = table.Column(nullable: true),
                    title = table.Column(nullable: true)

As part of the migration file, it creates a bunch of code including the Down method below which is used in the event the migration needs to be restored to its previous state:

protected override void Down(MigrationBuilder migrationBuilder)
                name: "Employees");

                name: "Departments");

The next step is for the migrations update the database, but before that, let’s take a quick look at some of the EF Core commands:


You can also script the migration by simply using Script-Migration command which is also listed in the image above.

PM> script-migration

This will create T-SQL that matches the shape of your entity. You can then use this script to execute directly against SQL Server. This also gives the developer or the DBA some flexibility to tweak the script if necessary. If you rather not use the script-migration command, you are welcome to use the good’ol update-database command instead.

IF OBJECT_ID(N'__EFMigrationsHistory') IS NULL
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])


CREATE TABLE [Departments] (
    [depID] int NOT NULL IDENTITY,
    [depName] nvarchar(max),
    [location] nvarchar(max),
    CONSTRAINT [PK_Departments] PRIMARY KEY ([depID])


CREATE TABLE [Employees] (
    [empID] int NOT NULL IDENTITY,
    [age] int NOT NULL,
    [depID] int NOT NULL,
    [empName] nvarchar(max),
    [EmployeesempID] int,
    [MiddleName] nvarchar(max),
    [title] nvarchar(max),
    CONSTRAINT [PK_Employees] PRIMARY KEY ([empID]),
    CONSTRAINT [FK_Employees_Departments_depID] FOREIGN KEY ([depID]) REFERENCES [Departments] ([depID]) ON DELETE CASCADE,
    CONSTRAINT [FK_Employees_Employees_EmployeesempID] FOREIGN KEY ([EmployeesempID]) REFERENCES [Employees] ([empID]) ON DELETE NO ACTION


That’s all there is to it. Hope you liked it!


Posted in ORM | Comments Off on Using Migrations in EF Core

Database First Workflow in EF Core

In EF 6.x, there are at least two options for you to scaffold your database to a model using the following:

  • Visual Tooling using the EDMX designer
  • Code First From Database option

However, in EF Core, we don’t have the luxury of using the designer anymore and I doubt Microsoft will add it anytime soon.

What we do have though is the scaffolding feature that can be used after installing the following packages from NuGet. I prefer using the console window:

  • Install-Package Microsoft.EntityFrameworkCore.SqlServer
  • Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design
  • Install-Package Microsoft.EntityFrameworkCore.Tools

Run the following command in the Package Manager like so:

PM>Scaffold-dbcontext “server=myServerName; Database = MyDatabaseName; Trusted_Connection= True;” Microsoft.EntityFrameworkCore.SqlServer

DbScaffoldSuccessIn my case, I had a small database with only two tables.

By default, the ‘Scaffold-Dbcontext’ will scaffold everything that is there in the database. However, you can control what tables to scaffold by using the parameters or switches in order to keep your database context lean.

I will cover those topics in another blog post, so please stay tuned!




Posted in ORM | Comments Off on Database First Workflow in EF Core

Fluent API in EF Core

We have a lot to thank Microsoft, especially the Entity Framework team for coming out with a new shiny EF Core ORM. We have long enjoyed Entity Framework 6.x and its previous versions since it was first released back in 2008. The team has worked relentlessly in making EF 6.x a full-featured tool that every .NET developer needs to have on his/her belt.

Let’s face it, we all write Line of Business apps and just about all applications have business rules buried in them. If you use an ORM such as Entity Framework, you can define some of the validation rules right inside your POCO classes.

In the example below I have a model called Department where I am annotating some of its properties like so:

public class Department
public int depID { get; set; }
public string depName { get; set; }
public string location { get; set; }
public List  Employees { get; set; }

However, an alternative to Data Annotations is the Fluent API. To do that you can simply override the OnModelCreating method in your context class which is derived by DbContext. You can use the ModelBuilder API to configure your model. This is also a very powerful method of configuration and it takes precedence over your entity classes including data annotations. It must be noted, that this is the same method what you are used to using in EF 6.X. See code below:

public class DepartmentContext : DbContext
        public DepartmentContext()
            this.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.TrackAll;

        public DbSet Departments { get; set; }
        public DbSet Employees { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            optionsBuilder.UseSqlServer(@"server=obi-oberoi; database=Demo.Department; Trusted_Connection=True;");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
            modelBuilder.Entity().Property(p => p.depName).HasMaxLength(25);
            modelBuilder.Entity().Property(p => p.location).IsRequired();

As you can see not much has changed as you transition from EF 6.x to EF Core 1.2.

Posted in ORM | Comments Off on Fluent API in EF Core

EF6.x is feature rich. What about EF Core?

There’s no doubt, EF6.x has got everything it takes to be a productive developer from the ‘Get Go’. Apart from being a full-featured ORM coupled with support from a rich eco-system of relational database providers, it boasts over 25 million downloads as of this writing.

EF Core is brand new in comparison to its predecessor. One of the key features that one takes for granted in EF6.x is EF Designer. Unfortunately, this feature has sadly been stripped out from EF Core’s release. While this may come as shock to some die-hard EF6.x. developers, it’s worth noting that it’s not the end of the world.

Even though, the visual tooling is missing in EF Core, and most probably will never be added in future, there are a few third-party vendors that build some of these tools for a cost. You can find information on these companies in one of my blog posts here.


Posted in ORM | Comments Off on EF6.x is feature rich. What about EF Core?

Duplicate screen not working with the Projector. Here’s a Fix!!

If you have been gripped in the quagmire of frustration, desperation and restlessness while struggling to get your PC to work with your projector; trust me, you are not alone. I have been through this ordeal long enough and I have scar tissues to prove it!!!

If you are like me who presents at work or is active on the speaker’s circuit, you absolutely don’t want your presentation opportunity to be marred by restricting you to only use the ‘Extended’ mode just because your PC won’t allow you to change the setting to ‘Duplicate’ mode. Guess what, there’s HOPE!

In the steps below, I will outline, what you need in order to make your speaking experience as fun and delightful as it can be. I am not sure about other machines, but ever since I got my Lenovo W530, it has always been a challenge to get my ‘Duplicate’ mode to work for my PC and Projector. BTW, the projector that I am currently using is EPSON VS335W.

First things first, connect your PC to your Projector using VGA cable or HDMI. I personally like HDMI as the picture quality is far superior and it also support higher resolution. As a result, you also get more real estate.

  • When the cable is connected with the projector, restart your computer.
  • As it restarts, press F1 until it enters into the BIOS setup.
  • Once inside BIOS, navigate to Config => Display => Graphics Device and select ‘Discrete Graphics’ from the context menu (see image below)
  • Hit F10 to Save and Exit
  • This would restart your computer
  • When your computer boots up, go to NVIDIA Control Panel by Right-Clicking anywhere in the desktop and selecting NVIDIA Control Panel from the context menu.
  • When the NVIDIA Control Panel window opens, it will show a list of displays. You need at least two. See image below:
  • When you see the two displays listed, you are certain at this point that your Duplicate mode will work.
  • Next, press Windows-P keys together and select “Duplicate” item from the context menu like so:
  • This will enable the duplicate mode and allow you to view the window(s) on both your PC and Projector simultaneously
  • Voila, now you are all set!!!


Hope you found this trick helpful!


Posted in ORM | Comments Off on Duplicate screen not working with the Projector. Here’s a Fix!!

Change Tracking is a Real Deal in EF. Leverage It!!

These days modern automobiles are so sophisticated and mostly computerized, in that, we rarely need to see what’s under the hood.

When we work with Entity Framework, we take for granted that our LINQ queries will do the grunt work of translating LINQ queries to T-SQL or to the native language that a data provider understands, but, there’s more than meets the eye!

When you send a query to the database only to bring back say 10,000 records, you know well that you won’t be adding, updating or deleting records. In other words, all you’ll ever do is simply dump these records on a grid for the purposes of read-only. Why then, would you ever want to track your entities especially when you are going to be traversing through potentially thousands of entities for a LOB application. In this case, you are better off disabling your ChangeTracker. To do that, simply set the query to “AsNoTracking” and you are set. It is also worth noting that this particular scenario belongs to a State of Entity that is ‘Unchanged’. What it means is that the Entity exists in the database and it has not been modified on the client. Hence SaveChanges would ignore it.

In addition to ‘Unchanged’, there are three other ‘states’ of Entity which are the following:

  • Added: The entity does not exist in the database. SaveChanges should insert it.
  • Modified: The entity exists in the database and has been modified on the client. SaveChanges should send updates to the database.
  • Deleted: The entity exists in the database. SaveChanges should delete it.

Before we get into the nuts and bolts of Change Tracking, let’s first understand what is a Change Tracker? Well, to put it succinctly, change tracking is a process where Entity Framework keeps track of the changes you make to your objects.

So in the code snippet below, the code is marked as “AsNoTracking” which in essence turns off ChangeTracking on the query. As mentioned above, there is no practical use of a Change Tracker when all you are doing is retrieving records from the database. It is an overhead which should be avoided.

// Query some data
   var depts = db.Departments.Where(d => d.depID > 0)
         foreach (var item in depts)
              Console.WriteLine("{0} - {1} - {2}", item.depID, item.depName, item.location);

You can also set the entity state directly using the DbContext.Entry method. See the following:

db.Entry(dept).State = EntityState.Unchanged;


The other option is to simply turn off Change Tracking at the Context level like so:

public class DepartmentContext : DbContext
        public DepartmentContext()
            this.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

In the code snippet below, one of the existing entities is being updated using DBContext in a disconnected scenario. It’s doing the following:
– Getting an existing Department from the database
– Updating the department name from “Accounting” to “R&D”
– Pass the modified entity to the Entry method and mark it as modified
– Call SaveChanges() in order to persist changes to the database

private static void UpdateDepartmentDisconnected()
            using (var db = new DepartmentContext())
                var dept = db.Departments.Where(d => d.depName == "Accounting").FirstOrDefault();
                if (dept != null)
                    dept.depName = "R&D";

                //save the modified data from above to a new context defined below
                using (var ctx = new DepartmentContext())
                    ctx.Entry(dept).State = EntityState.Modified;

There are some excellent resources that you can take advantage of:
Check out Arthur Vickers blog here and Julie Lerman’s blog here.

Posted in ORM | Comments Off on Change Tracking is a Real Deal in EF. Leverage It!!

EF Core doesn’t support Stored Procedure mappings…Ok, So What!!!

While the above title is true to its core, however, you can still use stored procedures or for that matter raw SQL in EF Core 1.1. Here’s how:

Raw SQL:

private static void UseRawSql()
using (var db = new DepartmentContext())
var depts = db.Departments
            .FromSql("Select * from dbo.Departments").ToList();
foreach (var item in depts)
      Console.WriteLine("{0} - {1}", item.depID, item.depName);

Stored Procedure:

    private static void UseStoredProc(int deptID)
        using (var db = new DepartmentContext())
            var depts = db.Departments.FromSql("Execute GetDepartmentByID {0}", deptID).ToList();
            foreach (var item in depts)
                Console.WriteLine("{0} - {1}, {2}",item.depID, item.depName, item.location);

As you can see, the code is fairly straight-forward. All I am trying to highlight here is that you can leverage the “FromSQL” method to pass Raw SQL Queries or use Stored Procedure to bring back the result set, even without the support for stored procedure mappings in EF Core 1.1.

Mind you, EF Core is new. Going forward, with every release, EF Core will likely include a new set of feature(s), improvements et. al. This does not imply that everything that currently exists in EF 6.X, will automatically be added to EF Core!

EF team will attempt to add features that are crucial while features such as visual tooling, Entity SQL, Metadata Workspace API, EDMX Support etc. will a take a back seat. That being said, third party tools such LLBLGen Pro, devart are some of the tools that provide designer support with costs associated with it.

Posted in ORM | Comments Off on EF Core doesn’t support Stored Procedure mappings…Ok, So What!!!

Using Code First in EF Core to spin an instance of DB in SQL Server

If you have been coding using Entity Framework 6.x, majority of what I am about to show you won’t come as a surprise with the exception of a few nuances.

Make no mistake…you can easily leverage your existing skill-set to create your POCO classes and DB Context class just as you have been doing in the past. Let’s try it with EF Core 1.0.

Create a new console app project in VS 2015. Select a Console App project. Install SQL Server database provider from inside Package Manager console window and type the following and hit enter: Install-Package Microsoft.EntityFrameworkCore.SqlServer

This would install the package and resolve dependencies for the package. It could take up to a couple of minutes before the installation is complete.

Add some code to your POCO class like so:

Create a context class and declare the DbSet of types. The only thing that you are required to do is to override a method called OnConfiguring(). This method takes in DbContextOptionsBuilder as an argument. The OptionsBuilder has a Use method called “UseSqlServer“. This is an extension method which becomes available after SQL Server database provider is installed. If it were Oracle, EF Core would create an extension method UseOracle and so on.


Couple things to note here…Notice the red circle in the figure below. This is a new API that the EF team at Microsoft has introduced which is called Database.EnsureCreated(). This allows Entity Framework to create a database that matches the shape of your entity. In our case, the Department and Employee classes for which it would create two new tables in the Demo.Department Database when the application code is executed!EFCoreMain


Now run the application. This would take about a minute to create the database and the tables, would add a record and output it to the console.


That’s all there is to it!

Hope you enjoyed this short and succinct tutorial on how to create a database using Code First in EF Core without running migrations.


Posted in ORM | Comments Off on Using Code First in EF Core to spin an instance of DB in SQL Server

EF Core – What is it?

I have been meaning to write something on EF Core, a topic that is near and dear to my heart.

Microsoft has done phenomenal work in shaping this ORM what has turned out to be a real hot product, although the best is yet to come! In doing so, they have left no stone unturned in promoting EF Core in major conferences across the globe. This aggressive approach to this otherwise new technology has clearly resonated loud and clear amongst the developer community in particular and corporates in general. As a result, EF Core has already begun to gain traction.

Make no bones about it…EF Core is new and it’s still a version 1.0 product. Therefore, it wouldn’t be fair to compare it with its predecessor that has gone through numerous iterations for over eight years now.

So what exactly is EF Core (formerly EF7) and how is it different from Entity Framework?In Microsoft’s own words, Entity Framework (EF) Core is a lightweight and extensible version of the popular Entity Framework data access technology. EF Core is an object-relational mapper (ORM) that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write.

The most current release of Entity Framework i.e. 6.1.3 is by far the most stable product we have seen yet! It has gone through eight plus years of development, bug-fixes and constant improvement thus providing a rich eco-system of data providers. These database providers are predominantly relational. Having said that there should be no doubt that Entity Framework is mature which offers a rich set of features such as Creating Model using Spatial data types, Many-to-many relationships, Visualizing a model, Alternating inheritance mapping patterns to Lazy Loading, Saving Data and Database Schema Management just to name a few!!

EF 6.x to EF Core is “Port” not an “Upgrade”

What it means is that there is no upsize wizard that can magically update all your Entity Framework project(s) to EF Core. Furthermore, EF Core does not support the EDMX file format for models. In order to port your model, your best bet is to reverse-engineer your model from the database for your application.

While EF Core is built with a lot of the same API’s, their implementation is totally different. For example, some of the common top level API’s such as DbContext, DbSet are still the same which in my opinion is a wonderful thing. Having said that, these top level API’s which are also part of EF Core are highly optimized and are guaranteed to give a significant boost when performing CRUD operations against a data store.

EF Core is made up of core components which in essence are a collection of loosely coupled services. There are categories of services ranging from Context, Provider specific, Design-time and User services.

Database Providers

Another important aspect of EF Core is that it can target a slew of data stores irrespective of whether they are relational or non-relational. Unlike Entity Framework which can only target relational databases namely, SQL Server, Oracle etc. EF Core can target these, but also target non-relational ones such Redis, In-Memory (for Testing) etc.

Which One is Right for You

Don’t be afraid to use EF 6.x if you are building an enterprise app. EF 6.x is tried and tested technology with many years of features, bug fixes and stabilization. However, if you are building an application that is cross-platform or if it targets ASP.NET core, then EF Core is definitely the right choice. The diagram below illustrates the platforms and App Models that EF 6.x and EF Core support.


Posted in ORM | Comments Off on EF Core – What is it?