While developing several C# .NET projects throughout my career, one major concern has always been how to manage relationships with the database. In some cases, I use Entity Framework; in others, Dapper. Which one is better suited for the project—and when should I use it? What is ORM? ORM (Object-Relational Mapping) is a technique used to map code to database tables. It creates a “link” between application classes and database tables. This technique allows you to manipulate database data using only application code. This manipulation can be done directly from the backend, for example. However, to manipulate data efficiently, you need to choose a framework that facilitates interaction between the application and the database, such as Entity Framework or Dapper. Practical example of ORM Imagine you’re working on a project that requires you to establish a relationship between a store’s products and their category in C#.NET. Within this one-to-many relationship, the ORM manages the relationships between classes, making the code cleaner and easier to maintain in the future. public class Category { public int Id { get; set; } public string Name { get ; set; } public List<Product> Products { get; set; } = new(); } public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } public int CategoryId { get; set; } public Category? Category { get; set; } } public class Category { public int Id { get; set; } public string Name { get ; set; } public List<Product> Products { get; set; } = new(); } public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } public int CategoryId { get; set; } public Category? Category { get; set; } } Entity Framework and Dapper in Practice Entity Framework (EF) According to the Entity Framework documentation, object-oriented programming can be challenging when interacting with data storage systems, but the framework makes it easy to associate objects with data, modify entity data, and bring models to life. This data querying is done using a LINQ (Language-Integrated Query) query language in Entity. There are two approaches currently most commonly used with Entity: Code First and Database First. To use Database First, Entity Framework can generate class mapping code from an existing database. With Code First, the model classes are written first, and Entity Framework generates the database using migrations. Code First Database First Here's a command to generate and update the structure with migration: dotnet ef migrations add InitialTables dotnet ef database update dotnet ef migrations add InitialTables dotnet ef database update Imagine you’ve been asked to work on building a C# .Net application using an annotation API that requires saving data to a database. An example using Entity Framework: using Microsoft.EntityFrameworkCore; using System; using System.Linq; // Model public class Note { public int Id { get; set; } public string Title { get; set; } = string.Empty; public string Description { get; set; } = string.Empty; } // Context public class AppDbContext : DbContext { public DbSet<Note> Notes { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options) { options.UseSqlite("Data Source=notesApp.db"); } } class Program { static void Main() { using var db = new AppDbContext(); db.Database.EnsureCreated(); // Add notes db.Produtos.Add(new Produto { Title = "Study C#", Description = "Entity framework vs dapper"}); db.SaveChanges(); var notes = db.Notes.ToList(); foreach (var note in notes) { Console.WriteLine($"{note.Id} - {note.Title} - R${note.Description}"); } } } using Microsoft.EntityFrameworkCore; using System; using System.Linq; // Model public class Note { public int Id { get; set; } public string Title { get; set; } = string.Empty; public string Description { get; set; } = string.Empty; } // Context public class AppDbContext : DbContext { public DbSet<Note> Notes { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options) { options.UseSqlite("Data Source=notesApp.db"); } } class Program { static void Main() { using var db = new AppDbContext(); db.Database.EnsureCreated(); // Add notes db.Produtos.Add(new Produto { Title = "Study C#", Description = "Entity framework vs dapper"}); db.SaveChanges(); var notes = db.Notes.ToList(); foreach (var note in notes) { Console.WriteLine($"{note.Id} - {note.Title} - R${note.Description}"); } } } The Note class is an entity that represents the table we have in our database; the mapping occurs in the AppDbContext class. The Entity Framework’s role here would be to interpret the SQL query language and execute the SQL commands. Dapper According to the Dapper documentation, the mapper functionality in Dapper provides extension methods on database connections that simplify sending T-SQL statements for execution or querying the database. For example, Dapper makes it easy to map between your .NET objects and SQL statement parameters using Execute calls, or to consume the results of your SQL queries in .NET objects using Query calls in Dapper. using System; using System.Collections.Generic; using System.Data.SQLite; using Dapper; using System.Linq; public class Note { public int Id { get; set; } public string Title { get; set; } = string.Empty; public string description { get; set; } = string.Empty; } class Program { static void Main() { using var connection = new SQLiteConnection("Data Source=app.db"); connection.Execute(@" CREATE TABLE IF NOT EXISTS Note ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, Description TEXT NOT NULL ); "); connection.Execute("INSERT INTO Note (Title, Description) VALUES (@Title, @Description)", new Note { Title = "Study C#", Description = "Entity framework" }); var notes = connection.Query<Note>("SELECT * FROM Note").ToList(); foreach (var note in notes) { Console.WriteLine($"{note.Id} - {note.Title} - R${note.Description}"); } } } using System; using System.Collections.Generic; using System.Data.SQLite; using Dapper; using System.Linq; public class Note { public int Id { get; set; } public string Title { get; set; } = string.Empty; public string description { get; set; } = string.Empty; } class Program { static void Main() { using var connection = new SQLiteConnection("Data Source=app.db"); connection.Execute(@" CREATE TABLE IF NOT EXISTS Note ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, Description TEXT NOT NULL ); "); connection.Execute("INSERT INTO Note (Title, Description) VALUES (@Title, @Description)", new Note { Title = "Study C#", Description = "Entity framework" }); var notes = connection.Query<Note>("SELECT * FROM Note").ToList(); foreach (var note in notes) { Console.WriteLine($"{note.Id} - {note.Title} - R${note.Description}"); } } } When to use Dapper and Entity Framework? When the application requires maximum performance, faster performance, and complete control over the SQL queries, Dapper may be the most recommended. For example, if you need to develop a financial report that will require multiple queries with aggregations and join data from multiple tables (unions), you need faster accuracy. Or when the SQL is more complex than LINQ, Dapper may be more advantageous. The Entity Framework can be used when productivity and ease of maintenance are required, but slower performance is required, but it has some advantages, such as in an application that needs to map a basic CRUD with the ability to generate SQL internally. It is a complete ORM. References “Overview of Entity Framework.” Microsoft Learn, Microsoft, https://learn.microsoft.com/pt-br/dotnet/framework/data/adonet/ef/overview https://learn.microsoft.com/pt-br/dotnet/framework/data/adonet/ef/overview
