Guide to Entity Framework in .NET | Unlock the Power of ORMs, LINQ Lambdas & Migrations | 5 Key Interview Questions
My first real world project was a windows desktop application. It had an on-premises database server (a computer stashed in the CFO’s storage closet) and was developed using the same methods I had learned in my programming classes from university. For data access I wrote SQL statements and for the schema, it was managed separately without source control.
Despite being a success for the company, it was painful to develop, and without a doubt, a nightmare for whoever inherited the code when I left! Before starting my second real world project, I knew I needed to learn about ORMs. If you are just now learning about ORMs then great, because knowing how to use them will take your skills to another level. This is more of a broad overview versus a step-by-step guide, with interview questions at the end.
ORMs in .NET
ORMs exist for all kinds of web development frameworks. In the world of .NET however, Entity Framework Core is the main one. Entity Framework has been around for years, since 2008 and it’s maintained by Microsoft. Another ORM worth mentioning for .NET is Dapper, but Entity Framework will be the de facto choice for many because it’s very good. ORMs deal with back-end persistence; you can use entity framework without MVC. The rest of your application design doesn’t depend on the ORM.
Benefits of using Entity Framework Core
If I had to summarize what ORMs do in one sentence it would be this: ORMs take care of all the tedious work related to interactions with a database. Here are a few of the major benefits using an ORM like Entity Framework Core can bring to the table:
LINQ instead of SQL Statements
The first massive benefit is that instead of writing raw SQL statements, you can use LINQ. LINQ (Language Integrated Query) is a .NET feature that uses lambda expressions to provide a standardized, SQL-like syntax for querying and transforming data across different sources like collections, databases, and XML.
The same lambda expressions you write to manipulate Lists / Enumerable / Collections are used to retrieve data in a database. Some benefits are that you don’t need to parameterize strings and you get intellisense and type-safety. Overall, LINQ is way easier to write than raw SQL. Under the hood, Entity Framework translates LINQ statements into raw SQL, no matter how complex with subqueries, unions, joins, etc. To illustrate, here is a simple example of how you could use a LINQ lambda to select the max value with Entity Framework Core
// Assuming 'context' is your DbContext and 'Products' is your DbSet
var maxValue = context.Products
.Max(e => e.ColumnName);And here is lambda select statement that would be translated by the ORM into raw SQL
SELECT MAX(ColumnName) FROM ProductsNow for a more complex example (taken from one of my projects) of a LINQ statement:
var query = _context.Policies
.Include(x => x.Seasons)
.Include(x => x.ItineraryProducts).ThenInclude(x => x.Product)
.Include(x => x.ItineraryProducts).ThenInclude(x => x.Product.Rooms);As raw SQL, yikes!
SELECT
p.*, -- Select all columns from the Policies table
s.*, -- Select all columns from the Seasons table
ip.*, -- Select all columns from the ItineraryProducts table
pr.*, -- Select all columns from the Products table
r.* -- Select all columns from the Rooms table
FROM Policies p
LEFT JOIN Seasons s ON p.Id = s.PolicyId
LEFT JOIN ItineraryProducts ip ON p.Id = ip.PolicyId
LEFT JOIN Products pr ON ip.ProductId = pr.Id
LEFT JOIN Rooms r ON pr.Id = r.ProductId
ORDER BY p.StartDate;Code First Design
The next benefit is that by using migrations and code-first design, your database schema will gain source control.
Code-first development didn’t make a lot of sense to me at first. I had always created a database separately; made the tables, designed the schema, etc. in a database tool like MySQL workbench or SSMS. The application had no knowledge of the database apart from a connection string. If a table name had changed in the database, a SQL statement referencing it in code would simply break. There was also no way to track schema changes. With the code, I had source control, but not with the database.
Code-first migrations solved all these problems. The critical thing to understand is you will use code to design the database, not a separate database tool.
Entity Framework makes use of a class called a database context (DbContext). Within this context class, are references to any entity you will track as a table in the database (DbSets). These entity classes are simple C# classes with properties that define the columns.
For example, creating a class called Products and adding that as a DbSet to the DbContext will result in a product table being created within the database. To create columns, you define them as properties in the class. Entity Framework will translate the C# types to databases types for you. A string property will generate an nvarchar column in SQL Server, for example. EF also gives you data annotations for even greater control over column attributes.
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
// Foreign key for relationship
public int SupplierId { get; set; }
// Navigation property back to Supplier
public Supplier Supplier { get; set; }
}Relationships between entity types are defined by following convention. There are conventions for all kinds of relationships: one-to-many, one-to- one, many-to- many. Here is how a typical one-to-many relationship might look in Entity Framework:
public class Supplier
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Phone { get; set; }
// Collection navigation property for one-to-many relationship
public List<Product> Products { get; set; }
}Any property named as an existing table with ID appended will be set up as a foreign key by convention. Adding ICollection navigation properties is optional, but including these will enable Entity Framework to retrieve related data whenever you build your LINQ queries. The data from related tables can be lazy loaded, or eager loaded for optimizing performance.
Despite Entity Framework’s convention based configuration being so easy to use, sometimes you might want a bit more control. For this reason, EF allows you to fully configure everything explicitly if you need to. Within the entity classes or the database context class, you can set explicit configurations using fluent expressions.
Since the complete map of the database is defined as code in your entity classes, EF can manipulate data in a database just as easily as if you were transforming data within a List or Enumerable object.
Migrations
Once we define all of the entity classes and reference them in the database context class we can create our first migration to actually apply the changes to a database. At this point, if we were to run the application, no database would exist nor would our application create one. Actually we would get several errors too because EF can detect if there is a difference in the database and your models.
A migration is like a snapshot of the evolutionary state of your schema. A migration is a class that is auto generated by Entity Framework design-time tools. Using the command line, we can use the command add-migration to do this. If you don’t define a specific output directory, EF will create a folder called Migrations in your project and keep the classes there. These migrations will be pending until they are actually applied using the update-database command.
The code generated in the migration classes contains SQL scripts. When applied with update-database, these commands script a schema that matches your entity classes design in the target database. If a database doesn’t exist, EF will create a new one for you using whatever name you defined in the default connection string. These migration classes contain two sections of code: up and down. The commands in the up method will run whenever you apply a migration; the commands in the down method will run if you want to revert to a previous migration state. Here is how our Product and Supplier migration might look (assuming you did one migration for both tables):
public partial class InitialCreate : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Suppliers",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(max)", nullable: true),
Address = table.Column<string>(type: "nvarchar(max)", nullable: true),
Phone = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Suppliers", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Products",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(max)", nullable: true),
Description = table.Column<string>(type: "nvarchar(max)", nullable: true),
SupplierId = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Products", x => x.Id);
table.ForeignKey(
name: "FK_Products_Suppliers_SupplierId",
column: x => x.SupplierId,
principalTable: "Suppliers",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Products_SupplierId",
table: "Products",
column: "SupplierId");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Products");
migrationBuilder.DropTable(
name: "Suppliers");
}
}Entity Framework tracks changes using a table in the database and a database context model snapshot class in the migrations folder. Each new migration will only generate code to produce the changes from one migration to the next. For example, let’s say you created an initial migration for an app that had one Product entity. Then later you add another entity for Supplier. At the point when you create a new migration for Supplier, this new one will only contain code related to the Supplier entity and any new relationships. When we run the update-database command, all pending migrations will be applied in chronological order.
Since these migration classes are stored with the application in source control, anyone who downloads your repo later on can recreate the database exactly as it is supposed to be currently. This is a game changer for anyone needing to work in teams, or just desiring to maintain a minimum level of sanity.
Migrations are also hugely important in multi-tenant applications. When creating new tenants on the fly with a separate database, these migrations are what make that possible. Multi-tenancy is a bit more of an advanced topic but it is essential if you want to build SaaS applications for the masses. If that is your goal, the Nano ASP.NET Boilerplate is an adaptable project template which can be used to generate all kinds of new projects, including multi-tenant SaaS applications.
ORMs and Data Persistence
This article has only covered the basics of what tools like Entity Framework can do. Knowing about code-first migrations and LINQ are the most essential, but there is so much more. You can execute code whenever save changes occurs, you can implement global query filters, even implement difference configurations per each request.
Data persistence is a huge topic and ORMs are only one part of that topic. When used in conjunction with patterns like generic repository and specification (Ardalis specification especially), you’ll begin to unlock the power within ASP.NET and start building truly scalable applications. The Nano boilerplate is a project template we developed which combines only the best of these patterns and practices. Use it on unlimited projects and generate well-architected solutions instantly. It’s always kept up to date, and comes with three UI projects.
I hope this article has served as a useful instruction to the world of ORMs and Entity Framework Core in particular. There is a ton of technical documentation and entity framework tutorials freely available from Microsoft and across other sources on the internet!
Entity Framework Interview Questions
1. What is Entity Framework, and what are its main advantages?
Purpose: To assess the candidate’s understanding of EF’s core purpose and benefits.
Expected Answer: Entity Framework is an Object-Relational Mapper (ORM) for .NET that simplifies database operations by enabling developers to work with database entities using C# objects. Advantages include:
- Reduced need for writing raw SQL.
- Strongly typed queries for compile-time safety.
- Built-in support for LINQ to query data.
- Database-First, Code-First, and Model-First development approaches.
2. Explain the differences between Code-First, Database-First, and Model-First approaches in EF.
Purpose: To test knowledge of EF’s different workflows and their use cases.
Expected Answer:
- Code-First: Define the database structure using C# classes. EF generates the database schema based on the model. Ideal for projects starting from scratch.
- Database-First: Start with an existing database, and EF generates the model and context from the schema. Suitable for legacy databases.
- Model-First: Define the database visually in a designer, and EF generates the schema and classes. Useful for projects requiring a visual approach.
3. What is the difference between DbSet and DbContext in Entity Framework?
Purpose: To check the candidate’s understanding of core EF components.
Expected Answer:
- DbContext: The main class responsible for interacting with the database. It manages the connection, tracks changes, and handles CRUD operations.
- DbSet: Represents a collection of entities of a specific type that EF tracks and interacts with in the database. It’s used to query and save instances of entities.
4. What are eager, lazy, and explicit loading in EF, and when would you use each?
Purpose: To assess understanding of data loading strategies and performance optimization.
Expected Answer:
- Eager Loading: Loads related entities with the main entity in a single query. Use when you know you’ll need related data.
- Lazy Loading: Loads related entities only when accessed. Good for minimizing initial load time but can lead to multiple queries (N+1 problem).
- Explicit Loading: Manually load related data using .Load(). Useful for precise control over data fetching.
5. How does EF handle concurrency, and how can you resolve concurrency conflicts?
Purpose: To evaluate knowledge of concurrency management in EF.
Expected Answer: EF handles concurrency using a ConcurrencyToken. When two users try to update the same data simultaneously, a DbUpdateConcurrencyException is thrown.
- To resolve conflicts, use Optimistic Concurrency Control with properties like a RowVersion column.
- Options to resolve conflicts include client wins, store wins, or custom merging logic.