In this article we learn how to use EntityFramework by examples, creating parts of an application that stores Books, Customers and Orders. We will also learn how to configure the database and how to query, add or remove data. This guide is for beginners so you don’t have to know anything beforehand about EntityFramework.
Contents
- What is EntityFramework
- Building the model
- Create the DbContext
- Configure the database
- Setting The Table and Schema Name
- Include and exclude properties
- Setting a Primary Key
- Configuring the Column Name
- Configuring whether a column is required or not
- Configuring default value for a property
- Configuring the Type of the column
- Database Initialization
- Write CRUD operations
- Create and display Orders. Include, ThenInclude keywords
What is EntityFramework
EntityFramework is a popular ORM used in .NET ecosystem for database interaction. It gives us with capabilities of write, update and retrieve data for a variate of databases like Postgres, SQL Server, MySQL and more.
One important gain we have for using an ORM like EntityFramework is that we decouple the database from the rest of the application. This means we don’t have to pay attention for database details, focusing more on the application development itself. Furthermore, we don’t need to write SQL inside our application in order to interact with the database, EntityFramework gives as a powerful high level API for all database use cases we will need.
Building the model
First, it is necessary to have a model which basically is a set of C# classes that represent our domain objects. A Domain Object is a representation of an actual (real world) object like a Book or a Customer or can be a non physical entity like an Order. In our case we will keep it simple and have a class that represents a Book, another that represents a Customer and one last for an Order. Basically, a Customer can order a Book that has stock information (how many books are available in our store).
In a real life application our domain model will evolve. A lot of properties maybe added or removed and so with entities. In order to keep our database in sync with our model we use migrations which are not in the scope of this article.
Below are our main classes:
public class Book
{
public int Id { get; private set; }
public string Name { get; private set; }
public int Stock { get; private set; }
public Book(string name, int stock) {
Name = name;
Stock = stock;
}
public void SetStock(int stock) {
Stock = stock;
}
public void SetOutOfStock() {
Stock = 0;
}
}
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string Email { get; set; }
public virtual List<Order> Orders { get; set; }
public Customer(string firstName, string email)
{
FirstName = firstName;
Email = email;
Orders = new List();
}
}
public class Order
{
public int Id { get; set; }
public Customer Customer { get; set; }
public Book Book { get; set; }
public DateTime Placed { get; set; }
public bool Completed { get; set; }
public Order(Customer customer, Book book)
{
Customer = customer;
Book = book;
Placed = DateTime.Now;
}
private Order(){ }
public void SetCompleted() => Completed = true;
}Create the DbContext
DbContext is a central object in EntityFramework. Without it we cannot interact with the database. You can think of it as the database gateway. In our case we should have one for our BookStore application so we will create a BookStoreDbContext that extends from DbContext. Then, we will specify all the entities we want EntityFramework to include in the resulting database. Also all the configurations will be added there.
Furthermore, DbContext has some useful methods that can be overridden and are related with the database configuration pipeline like the OnModelCreating method.
public class BookStoreDbContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Order> Orders { get; set; }
public DbSet<Book> Books { get; set; }
public BookStoreDbContext(DbContextOptions<BookStoreDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>(ConfigureCustomerMap);
modelBuilder.Entity<Order>(ConfigureOrderMap);
modelBuilder.Entity<Book>(ConfigureBookMap);
}
private void ConfigureCustomerMap(EntityTypeBuilder builder)
{
builder.ToTable("Customers");
builder.HasKey(x => x.Id);
}
private void ConfigureOrderMap(EntityTypeBuilder builder)
{
builder.ToTable("Orders");
builder.HasKey(x => x.Id);
}
private void ConfigureBookMap(EntityTypeBuilder builder)
{
builder.ToTable("Books");
builder.HasKey(x => x.Id);
}
}There are a few interesting points inside the BookStoreDbContext implementation. First we set all the entities that we want to be included in the resulting database as tables using the EntityFramework’s DbSet. DbSet represents an entity (domain object) and can be used for creating, deleting or updating data in the corresponding database table.
Then we override the OnModelCreating method and using the EntityTypeBuilder object from EntityFramework we make further configurations for our entities.
Configure the database
Next, we will create configurations that tells the EntityFramework which objects should be stored in the database and how. We can control in a detailed level how each property will be translated and stored into relational tables, for example which data type will they have. Hopefully, we don’t need to specify all those things explicitly because EntityFramework has many conventions out of the box and does a good job deciding many things like the column data type by looking into the C# class itself. For example if we create an integer property:
public int Stock { get; set; }this will be translated into a column of type integer (also this depends on the underlying database engine).
We can change/override these default configurations using mainly two approaches:
- Data annotations
- FluentAPI configuration
Data annotations is the easiest but ‘dirty’ way of configuring how a property will be stored. Its not considered as clean code because database details of how properties should be stored are infiltrated into our domain model which should be unaware from those details. Nevertheless in some cases this approach is fine to follow and implement.
Another way of configuring the translation of domain objects into database tables is using FluentAPI. With this way we don’t pollute our domain model with database mapping details but instead we use the build in EntityTypeBuilder of EntityFramework in order to create our specific configurations.
Below, we present the most used configurations and how we can implement them for each of the available techniques.
Setting The Table and Schema Name
Its very common to control the resulting names that tables will have in the database. For example we want the class Book to have the resulting name ‘Books’ as a table. In addition in the following examples we also set the schema name (if the underlying database does not support ‘schema’, the setting will not have any effects).
Using Data Anotations
[Table("Books", Schema = "store")]
public class Book
{
public int Id { get; set; }
}Using FluentApi
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.ToTable("Books", "store");
}Include and exclude properties
In many cases we might need some properties in a domain object but we don’t want them to be persisted, so we need a way to instruct EntityFramework to exclude them. In the following example we exclude the LoadedFromDatabase property from mapping into a table column.
Using Data Anotations
public class Book
{
public int Id { get; set; }
public int Reads { get; set; }
[NotMapped]
public DateTime LoadedFromDatabase { get; set; }
}Using FluentApi
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.Ignore(c => c.LoadedFromDatabase);
}Setting a Primary Key
EntityFramework has a very convenient way to choose the primary key from the class itself. If the property has the name Id then its set automatically as the primary key of the resulting table. But if we want to explicitly set or override this behavior we can do the following:
Using Data Annotations
public class Book
{
[Key]
public int Id { get; set; }
}Using FluentApi
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.HasKey(x => x.Id);
}Configuring the Column Name
We can configure the resulting column name of a property very easily as the following examples demonstrate renaming the BookName property of our domain model to a column named Name.
Using Data Annotations
public class Book
{
public int Id { get; set; }
[Column("Name")]
public string BookName { get; set; }
}Using FluentApi
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.Property(x => x.BookName).HasColumnName("Name");
}If we don’t use any configuration, EntityFramework will apply as column name the name of the property in the class, in this case ‘BookName’.
Configuring whether a column is required or not
When we want the property to be nullable or not, EntityFramework has the following way of configuring it:
Using Data Annotations
public class Book
{
public int Id { get; set; }
[Required]
public string Name { get; set; }
}Using FluentApi
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.Property(x => x.Name).IsRequired(required: true);
}Notice that if we try to save an entity with a required property and don’t set a value for that property then EntityFramework will raise an exception.
Configuring default value for a property
Depending on the type of the property we might want to set a default value for a property. This way if the property is required and we don’t set explicitly a value in the code then EntityFramework will provide the default value for it.
Using Data Annotations
public class Book
{
public int Id { get; set; }
[DefaultValue(0)]
public int Stock { get; set; }
}Using FluentAPI
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.Property(x => x.Stock).HasDefaultValue(0);
}We can also configure a default value using only C# property initialization
public class Book
{
public int Id { get; set; }
public int Stock { get; set; } = 0;
}Configuring the Type of the column
A more complex configuration is to control the resulting type the column will have. In many cases the way EntityFramework determines the type is good enough but some times we have to step in and provide a more explicit type or something different (if its possible). We may choose also some constrains of the data type like how much length will have a varchar column type.
So for example if we want the property Name to be a varchar of length 50 we can do this as following:
Using Data Annotations
public class Book
{
public int Id { get; set; }
[Column(TypeName = "varchar(50)")]
public string Name { get; set; }
}Using FluentAPI
private void ConfigureBookMap(EntityTypeBuilder<Book> builder)
{
builder.Property(x => x.Name).HasMaxLength(50).IsUnicode(false);
}Notice the IsUnicode method. This will tell EntityFramework to use VARCHAR instead of NVCHAR (if possible).
Database Creation, Initialization
One of the first pieces of code in any application is to check the state of the database, create it if necessary or update its state to the latest, reflecting our domain entity models. Updating the database state according to our domain models is done with migrations but for simplicity in the following examples we will automatically recreate the database.
In our console application we will initialize our database inside the main method. The basic configuration specifies the type of the underlying database our application will use (SQL Server, MySQL, Postgres, SQLite), and the connection string to the database. In this example we will use SQLite that doesn’t require a server or something else to be installed. Also the connection string value is stored in the App.config of the project. For more about Console Project configuration you can check this article.
static async Task Main(string[] args)
{
IServiceCollection services = new ServiceCollection();
string connString = ConfigurationManager.AppSettings["BookStoreConnectionString"];
services.AddDbContext<BookStoreDbContext>(options =>
options.UseSqlite(connString));
var provider = services.BuildServiceProvider();
using (var serviceScope = provider.GetService<IServiceScopeFactory>().CreateScope())
{
var context = serviceScope.ServiceProvider.GetRequiredService<BookStoreDbContext>();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreated();
}
}There are some interesting pieces of code in the above sample. First, we inject the BookStoreDbContext in the application using the AddDbContext method. Inside it we also configure that the underlying database provider will be SQLite using the UseSqlite method with the connection string. Below is the App.config displaying the value of the connection string.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="BookStoreConnectionString" value="Data Source=Application.db;Cache=Shared" />
</appSettings>
</configuration>Then, the context.Database.EnsureCreated() ensures that the database is created, it doesn’t make any other change if its there even if our domain model classes have changed. Because of that we should delete the database up front (in this article we don’t care about data loss) in order to recreate it in the correct state.
Create CRUD (Create Read Update Delete) Operations
Now its time to create some objects and save them into the database. We will use the BookStoreDbContext in order to access the DbSets for Books and Customers:
using (var serviceScope = provider.GetService().CreateScope())
{
var context = serviceScope.ServiceProvider.GetRequiredService();
context.Books.Add(new Book("Design Patterns", 5));
context.Books.Add(new Book("Clean Code", 4));
context.Customers.Add(new Customer("Jim", "jim@softwareparticles.com"));
context.Customers.Add(new Customer("Nik", "nik@softwareparticles.com"));
await context.SaveChangesAsync();
}If we don’t call the SaveChangesAsync() method after the changes we made, then the new objects will not be stored in the database.
Create and display Orders. Include, ThenInclude keywords
After adding some Books and Customers we can build a simple logic in order to create and retrieve orders in a similar way.
using (var serviceScope = provider.GetService().CreateScope())
{
var context = serviceScope.ServiceProvider.GetRequiredService();
var jim = await context.Customers
.FirstOrDefaultAsync(x => x.Email == "jim@softwareparticles.com");
var designPatterns = await context.Books
.FirstOrDefaultAsync(x => x.Name == "Design Patterns");
var cleanCode = await context.Books
.FirstOrDefaultAsync(x => x.Name == "Clean Code");
context.Orders.Add(new Order(jim, designPatterns));
context.Orders.Add(new Order(jim, cleanCode));
await context.SaveChangesAsync();
}Finally we can query a customer’s orders and display the book names he ordered.
using (var serviceScope = provider.GetService().CreateScope())
{
var context = serviceScope.ServiceProvider.GetRequiredService();
var jim = await context.Customers
.Include(x=>x.Orders)
.ThenInclude(y=>y.Book)
.FirstOrDefaultAsync(x => x.Email == "jim@softwareparticles.com");
Console.WriteLine($"Customer: {jim.FirstName} has ordered the following books:" +
$" {string.Join(',', jim.Orders.Select(x => x.Book.Name))}.");
}An interesting feature EntityFramework has is the Include and ThenInclude functions. These allows us to join two different tables and load those data in our entity, in this example we want the Customer to have also the Orders and in one more level those Orders to have the Books. So for the 2nd level join we use the ThenInclude function.
The final result in Console will be like this:
Customer: Jim has ordered the following books: Design Patterns,Clean Code.
