Entity Framework Core is the .Net Core version of Entity Framework. Which is an ORM (Object Relational Mapper) tool for .Net Framework to work with databases.
Entity Framework Core has the following features, which differentiates it from the classic Entity Framework:
- It is cross-platform (by the virtue of being built for .Net Core)
- Extensible
- And lightweight compared to classic Entity Framework
The high-level topics, that I am going to cover in this blog are:
- Using Entity Framework to Create, Update and Delete data in SQL Server database
- Use Stored Procedure and in-line queries with Entity Framework Core
- Transactional operation with Entity Framework Core
Preparing Project with Entity Framework Core
Firstly, I will create a new .Net Core Console Application project. For that, I will open Visual Studio 2017. Once opened, I will click File -> New -> Project. Once the project dialog opens up, I will select Console App (.NET Core). I will name the project as EntityFrameworkDemo.
If you are new to .Net Core project, and want to know more about it, you can start with my blog Creating your first .Net Core Console Application.
Adding Entity Framework Core Nuget Package
Secondly, I will add the Entity Framework Core nuget package to the project. For that, I will right-click on the project and click Manage Nuget Package. This will open the Nuget Package Manager window.
I will Search for Microsoft.EntityFramework.Core in the Nuget package manager window. From the search result, I will select Microsoft.EntityFrameworkCore.SqlServer, since I will be working with SQL Server as a database for this example. This will also install Microsoft.EntityFramework.Core, since it is a dependency for Microsoft.EntityFrameworkCore.SqlServer nuget package.
NOTE: Entity Frameworks also have other design-time tools, but I will not be using those for this example. I want to keep this simple and first step towards using Entity Framework Core. I personally will never use any of the design tools.
Database
For the database, I will use my existing TimeManagement database, which has the table Employee.
Model in Entity Framework Core
One of the core concepts of the Entity Framework is the Model. The data is always accessed through a model. The Model consists of the data entity class and a database context. Database context establishes a database session to access and update the data in the database.
First of all, I will create a data entity class to represent my database table. I will name the entity class as Employee. And it will have the properties, Id, FirstName, LastName, Address, HomePhone and CellPhone.
internal class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string HomePhone { get; set; }
public string CellPhone { get; set; }
}
Once the data entity class is created, subsequently I will create a database context to access and modify my data entity.
using Microsoft.EntityFrameworkCore;
namespace EntityFrameworkDemo
{
internal class EmployeeContext : DbContext
{
public EmployeeContext(string connectionString)
{
ConnectionString = connectionString;
}
public DbSet<Employee> Employees { get; set; }
public string ConnectionString { get; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConnectionString);
}
}
}
Here I created a database context to access Employee. We can create multiple contexts per database, to logically isolate each domain/sub-domains of data. Or we can create a single database context for the entire database. I prefer database context per data domain/sub-domain depending on size. This helps with isolation and easier separation of databases (if needed) in the future.
Employee Provider
Once the Employee database context is ready, I will create a EmployeeProvider class, which will provide the Employee for a specific id.
namespace EntityFrameworkDemo
{
internal interface IEmployeeProvider
{
Employee Get(int id);
}
}
using System.Linq;
namespace EntityFrameworkDemo
{
internal class EmployeeProvider : IEmployeeProvider
{
private readonly EmployeeContext employeeContext;
public EmployeeProvider(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public Employee Get(int id)
{
return employeeContext.Employees.Where(e => e.Id == id).FirstOrDefault();
}
}
}
Because the name of the database table is Employee, hence I will add an attribute [Table("Employee")] to the Employee class. This will tell the Entity Framework Core that event tough my access property is Employees, the table name to look for is Employee.
Similarly, since my column name in SQL table are all in snake case, hence, the other thing I will do, is to provide mapping attribute for all the properties of my Employee class.
Below is the updated Employee class.
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityFrameworkDemo
{
[Table("Employee")]
internal class Employee
{
public int Id { get; set; }
[Column("first_name")]
public string FirstName { get; set; }
[Column("last_name")]
public string LastName { get; set; }
public string Address { get; set; }
[Column("home_phone")]
public string HomePhone { get; set; }
[Column("cell_phone")]
public string CellPhone { get; set; }
}
}
Running the code with Entity Framework Core
Now that most of the Entity Framework Core plumbing is done, let’s add code to the Program.cs class to check and see if we can access data from the database.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
var provider = new EmployeeProvider(context);
Console.WriteLine(provider.Get(2).FirstName);
}
}
}
Finally, I will run the program, which will print the expected output.
Insert Data
For inserting data using Entity Framework, I will create a new interface IEmployeeCreator and a new class EmployeeCreator for managing the insert functionality.
namespace EntityFrameworkDemo
{
internal interface IEmployeeCreator
{
Employee Create(string firstName, string lastName, string address, string homePhone, string cellPhone);
}
}
namespace EntityFrameworkDemo
{
internal class EmployeeCreator : IEmployeeCreator
{
private readonly EmployeeContext employeeContext;
public EmployeeCreator(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public Employee Create(string firstName, string lastName, string address, string homePhone, string cellPhone)
{
var employee = employeeContext.Add(new Employee { FirstName = firstName, LastName = lastName, Address = address, HomePhone = homePhone, CellPhone = cellPhone });
employeeContext.SaveChanges();
return employee.Entity;
}
}
}
The implementation of the class EmployeeCreator is straightforward. It takes all the attributes of an employee as an input parameter. Then calls Add on the EmployeeContext to create a new employee record. Finally, it calls SaveChanges on EmployeeContext, which saves the changes to the database. In the end, returning the instance of the Employee back to the caller.
Finally, I will update the implementation of Program class to add a method to create a new employee and write the name and address of the returned Employee object from the EmployeeCreator class.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
CreateEmployee(context);
}
static void ProvideEmployee(EmployeeContext context)
{
var provider = new EmployeeProvider(context);
Console.WriteLine(provider.Get(2).FirstName);
Console.ReadLine();
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Address: {employee.Address}");
Console.ReadLine();
}
}
}
Update Data
To implement the update feature, I will create a new interface IEmployeeEditor and a new class EmployeeEditor.
namespace EntityFrameworkDemo
{
internal interface IEmployeeEditor
{
void Edit(Employee employee);
}
}
namespace EntityFrameworkDemo
{
internal class EmployeeEditor : IEmployeeEditor
{
private readonly EmployeeContext employeeContext;
public EmployeeEditor(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public void Edit(Employee employee)
{
employeeContext.Update(employee);
employeeContext.SaveChanges();
}
}
}
The implementation of EmployeeEditor is simple. It takes an Employee object and calls Update on the EmployeeContext. After that, it calls the SaveChanges to save the changes to the database.
Next, to test this out, I will change the Program class to implement an update method.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
UpdateEmployee(context, 2008);
}
static void ProvideEmployee(EmployeeContext context)
{
var provider = new EmployeeProvider(context);
Console.WriteLine(provider.Get(2).FirstName);
Console.ReadLine();
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Address: {employee.Address}");
Console.ReadLine();
}
static void UpdateEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var editor = new EmployeeEditor(context);
employee.Address = "Gondor";
editor.Edit(employee);
Console.ReadLine();
}
}
}
In the UpdateEmployee method above, I got an Employee object for a specific employee id using EmployeeProvider class. And then, updated the Address property for the Employee and called the Edit function of the EmployeeEditor class.
Finally, when I run the program. And we can see the updated record in the SQL Server Management studio.
Delete Data
First of all, I will create an interface IEmployeeRemover and class EmployeeRemover for the implementation for deleting an Employee record from the database.
namespace EntityFrameworkDemo
{
internal interface IEmployeeRemover
{
void Remove(Employee employee);
}
}
namespace EntityFrameworkDemo
{
internal class EmployeeRemover : IEmployeeRemover
{
private readonly EmployeeContext employeeContext;
public EmployeeRemover(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public void Remove(Employee employee)
{
employeeContext.Remove(employee);
employeeContext.SaveChanges();
}
}
}
The implementation of EmployeeRemover class is similar to the implementation of EmployeeEditor class.
Next, I will update the Program class to add a new method to delete the Employee.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
DeleteEmployee(context, 2008);
}
static void ProvideEmployee(EmployeeContext context)
{
var provider = new EmployeeProvider(context);
Console.WriteLine(provider.Get(2).FirstName);
Console.ReadLine();
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Addredd: {employee.Address}");
Console.ReadLine();
}
static void UpdateEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var editor = new EmployeeEditor(context);
employee.Address = "Gondor";
editor.Edit(employee);
Console.ReadLine();
}
static void DeleteEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var remover = new EmployeeRemover(context);
remover.Remove(employee);
}
}
}
Finally, I will execute the program and check the outcome in the SQL Server. And we can see the record with id 2008 is removed from the database.
Using Custom SQL Query
Next, I will create a new class, EmployeeProviderThroughSQLQuery. The class EmployeeProviderThroughSQLQuery will implement the interface IEmployeeProvider.
Inside the class, I will use SQL query to retrieve the Employee information. To use the custom SQL query I will use the FromSql extension method. And for passing the parameters to the SQL query, I will use an instance of SqlParameter class.
using Microsoft.EntityFrameworkCore;
using System.Data.SqlClient;
using System.Linq;
namespace EntityFrameworkDemo
{
internal class EmployeeProviderThroughSQLQuery : IEmployeeProvider
{
private readonly EmployeeContext employeeContext;
public EmployeeProviderThroughSQLQuery(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public Employee Get(int id)
{
var employees = employeeContext.Employees.FromSql("SELECT * FROM Employee WHERE ID = @ID", new SqlParameter("@ID", id));
return employees.FirstOrDefault();
}
}
}
Finally, I will update the Program class to add a new method to call Get method from EmployeeProviderThroughSQLQuery class and print the First Name and Last Name in the console.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
ProvideEmployeeWithSql(context, 1002);
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Addredd: {employee.Address}");
Console.ReadLine();
}
static void UpdateEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var editor = new EmployeeEditor(context);
employee.Address = "Gondor";
editor.Edit(employee);
Console.ReadLine();
}
static void DeleteEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var remover = new EmployeeRemover(context);
remover.Remove(employee);
}
static void ProvideEmployeeWithSql(EmployeeContext context, int id)
{
var provider = new EmployeeProviderThroughSQLQuery(context);
var employee = provider.Get(id);
Console.WriteLine($"{employee.FirstName} {employee.LastName}");
Console.ReadLine();
}
}
}
Then, I will run the application to see the response in Console.
Using a Stored Procedure
To implement accessing data through Stored Procedure, I will first create a class EmployeeProviderThroughSP. Next, I will implement the interface IEmployeeProvider. To use Stored Procedure to access data, I will use the same extension FromSql as I have used when executing custom SQL code.
using Microsoft.EntityFrameworkCore;
using System.Linq;
namespace EntityFrameworkDemo
{
internal class EmployeeProviderThroughSP : IEmployeeProvider
{
private readonly EmployeeContext employeeContext;
public EmployeeProviderThroughSP(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public Employee Get(int id)
{
var employees = employeeContext.Employees.FromSql($"Execute dbo.GetEmployeeById {id}");
return employees.FirstOrDefault();
}
}
}
One thing to notice here, I am not using SqlParameter anymore. Instead, I am just using an inline string.
Finally, I will update the Program class to print the output from this class.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
ProvideEmployeeWithSP(context, 1002);
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Addredd: {employee.Address}");
Console.ReadLine();
}
static void UpdateEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var editor = new EmployeeEditor(context);
employee.Address = "Gondor";
editor.Edit(employee);
Console.ReadLine();
}
static void DeleteEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var remover = new EmployeeRemover(context);
remover.Remove(employee);
}
static void ProvideEmployeeWithSql(EmployeeContext context, int id)
{
var provider = new EmployeeProviderThroughSQLQuery(context);
var employee = provider.Get(id);
Console.WriteLine($"{employee.FirstName} {employee.LastName}");
Console.ReadLine();
}
static void ProvideEmployeeWithSP(EmployeeContext context, int id)
{
var provider = new EmployeeProviderThroughSP(context);
var employee = provider.Get(id);
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.Address}");
Console.ReadLine();
}
}
}
Once I run the program, I will see the desired output in the console.
Using Transactions
Finally, I will create a class, which will perform a transactional write.
using System;
namespace EntityFrameworkDemo
{
internal class MultipleEmployeeCreator
{
private readonly EmployeeContext employeeContext;
public MultipleEmployeeCreator(EmployeeContext employeeContext)
{
this.employeeContext = employeeContext;
}
public void Create(Employee[] employees)
{
using (var transaction = employeeContext.Database.BeginTransaction())
{
try
{
foreach (var employee in employees)
{
employeeContext.Add(employee);
employeeContext.SaveChanges();
}
transaction.Commit();
}
catch(Exception)
{
transaction.Rollback();
Console.WriteLine(exc);
// Log etc.
}
}
}
}
}
The transaction implementation is the same as plain ADO.Net. I created a transaction around the saves, and commit in case of no exception. In case of an exception, just rolling it back.
I will update Program class, to add implementation to call MultipleEmployeeCreator and create a couple of new employees.
using System;
namespace EntityFrameworkDemo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
CreateMultipleEmployee(context, 1002);
}
static void CreateEmployee(EmployeeContext context)
{
var creator = new EmployeeCreator(context);
var employee = creator.Create("Samwise", "Gamgee", "Shire", "123-123-1234", "123-123-1234");
Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Addredd: {employee.Address}");
Console.ReadLine();
}
static void UpdateEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var editor = new EmployeeEditor(context);
employee.Address = "Gondor";
editor.Edit(employee);
Console.ReadLine();
}
static void DeleteEmployee(EmployeeContext context, int id)
{
var provider = new EmployeeProvider(context);
var employee = provider.Get(id);
var remover = new EmployeeRemover(context);
remover.Remove(employee);
}
static void ProvideEmployeeWithSql(EmployeeContext context, int id)
{
var provider = new EmployeeProviderThroughSQLQuery(context);
var employee = provider.Get(id);
Console.WriteLine($"{employee.FirstName} {employee.LastName}");
Console.ReadLine();
}
static void ProvideEmployeeWithSP(EmployeeContext context, int id)
{
var provider = new EmployeeProviderThroughSP(context);
var employee = provider.Get(id);
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.Address}");
Console.ReadLine();
}
static void CreateMultipleEmployee(EmployeeContext context)
{
var creator = new MultipleEmployeeCreator(context);
creator.Create(new Employee[] {
new Employee{ FirstName= "Samwise", LastName = "Gamgee", Address= "Shire", HomePhone= "123-123-1234", CellPhone= "123-123-1234" },
new Employee{ FirstName= "Frodo", LastName = "Baggins", Address= "Shire", HomePhone= "123-123-1234", CellPhone= "123-123-1234" }
});
Console.ReadLine();
}
}
}
Finally, I will run the application and verify the database. And I will see two new records inserted since there was no exception.
Conclusion
As you can see, implementing ORM functionality with Entity Framework Core is extremely simple. I am pleasantly surprised by how far Entity Framework has come and the simplicity it has achieved.
Most importantly, it is very easy to use either custom SQL or Stored Procedures with Entity Framework Core apart from using the default behavior. The transactional code is also very familiar as in ADO.Net.
I have uploaded the example code in GitHub. https://github.com/choudhurynirjhar/EntityFrameworkCoreDemo
Some resources: Entity Framework Core Official Documentation
YouTube video is available here: https://youtu.be/VVWi9ZdAS9M