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 to 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?

MVPMIX 2016 Toronto Technology Conference


Due to popular demand, we are bringing MVP MIX 2016 to Toronto yet again!!

Seats are limited, so reserve your spot now. There will be two simultaneous tracks with sessions ranging from .NET, VS Code, ASP.NET Core, EF Core, JavaScript frameworks, Roslyn, Open Source, Cross-plat Apps with Xamarin and Azure, Document DB, Keno UI, Office 365, NuGet and more!!

We have a stellar line-up speakers who will enthrall you with their technical prowess and virtuosity.


MVPMIX2016Speakers1See you on October 17th at the Canadian Microsoft’s Toronto Headquarters.


Posted in News | Comments Off on MVPMIX 2016 Toronto Technology Conference

LINQ to SQL – Is it worth it?

I presume we’ve all used LINQ-To-SQL or perhaps still using it in some way shape or form; perhaps even supporting a legacy application that has a large footprint of LINQ-To-SQL.

In this short blurb, I’d like to compare the above with Entity Framework and how the two differ from one another. Now, it begs the question, should we be using one over the other? Why?

There is no question LINQ to SQL is a light-weight API and you can get up and running much more quickly than you would with Entity Framework. However, the main question is that are new features being added to LINQ to SQL? The answer is probably not! Even though, Microsoft continues to support LINQ to SQL in all its glory, their efforts are mainly restricted on developing the next version of Entity Framework, called EF7, the topic on which I have blogged earlier.

Although, I have used both technologies; I must say, while LINQ to SQL does provide out of the box solution in a rather quick succession particularly if you are using SQL Server 2000 or higher. However, I have found it to be flaky at times. This is especially true when adding new database objects onto the DBML designer, it sometimes does not generate the necessary classes and when it does, it does create an empty Return Type for a given database object e.g. a stored procedure. You can certainly fix it, but at the cost of your precious time!

My take is that if you are building a Line of Business application; consider investing some time becoming proficient in Entity Framework. Apart from a plethora of workflows it offers such as Code First, Model First and Database First, it’s versatile enough, loosely coupled, allows many to many relationships, inheritance, eager loading; just to name a few!!

Posted in ORM | Comments Off on LINQ to SQL – Is it worth it?