An Introduction to Entity Framework and Dapper

In this blog, we are gonna see how to configure Entity Framework and Dapper in C# to read data from a local postgresql database. The aim is to get a very high level sense of how both of them work.

To start with, I have already created a local postgres database and a table called “students” inside the database. The table has columns (Id, Name, Address, Age).

Entity Framework

Entity Framework allows us to create objects of classes that model the underlying database tables and columns. It automatically creates a model of the underlying table and gives a DbContext which we can use to add or fetch data from the table. It uses a process called scaffolding to achieve this.

Packages needed

In order to use Entity Framework in our project, we need to install certain nuget packages. They are :

1. Microsoft.EntityFrameworkCore.Design
2. Npgsql.EntityFrameworkCore.PostgreSQL

Scaffolding

Scaffolding is used to create the model and DbContext automatically for us to add or retrieve data. The following command is used to achieve this :

dotnet ef dbcontext scaffold “Host=<hostname>;Port=<portnumber>;Username=<username>;Password=<password>;Database=<databasename>” Npgsql.EntityFrameworkCore.PostgreSQL -o <Folder to which you want the model and dbContext to be created> -t <name of the table to which we need to apply scaffolding>

Note : Text in <> are placeholders.

The string “Host=<hostname>;Port=<portnumber>;Username=<username>;Password=<password>;Database=<databasename>” is called the Connection String.

Once we are done with scaffolding we get the following model and dbcontext files.

Model :

public partial class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Address { get; set; }
}

DbContext :

public partial class dbContext : DbContext
    {
        public dbContext()
        {
        }

        public dbContext(DbContextOptions<dbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Student> Students { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseNpgsql(<connection string>);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "en_US.UTF-8");

            modelBuilder.Entity<Student>(entity =>
            {
                entity.ToTable("students");

                entity.Property(e => e.Id)
                    .ValueGeneratedNever()
                    .HasColumnName("id");

                entity.Property(e => e.Address)
                    .HasMaxLength(25)
                    .HasColumnName("address")
                    .IsFixedLength(true);

                entity.Property(e => e.Age).HasColumnName("age");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(20)
                    .HasColumnName("name");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }

We can then use it to fetch data.

var context = new dbContext();
var students = context.Students.ToList();

Entity framework can also be used to insert data into the table. This is how it’s done.

for (int i = 1; i <= 1000; i++)
{
    var student = new Student();
    student.Id = i;
    student.Name = "Abc";
    student.Address = "Paris";
    student.Age = 20;
    context.Students.Add(student);
    context.SaveChanges();
}

This way I have inserted 1000 records into the students table.

Entity framework took 1.31ms to fetch the data with tracking and 1.28ms with AsNoTracking(). Entity Framework has the advantage that it creates the model and dbContext for us.

When there is a change in the name of the column in the underlying table, Entity Framework throws a PostgresqlException during execution. We have to use scaffolding again to update the model to reflect the new column name.

Dapper

Dapper and EF helps in achieving the same result, but the main difference between them is that using Dapper, we have to define the Model of the underlying data, ourselves.

Packages needed :

1. Npgsql.EntityFrameworkCore.PostgreSQL
2. Dapper

There is no scaffolding here, rather we need to create the model class ourselves.

Model :

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Address { get; set; }
}

Once we have the model, it’s very simple to get the required data.

using (var connection = new NpgsqlConnection(<connection-string>))
{
    var students = connection.Query<Student>("select * from     students;").AsList();
}

Dapper took 0.62ms to fetch the data.

When there is a change in the name of the column in the underlying table, there won’t be any exception thrown, rather the value for that column will be empty while fetching data.

Summary

Entity Framework

• Uses scaffolding.
• Creates model and dbcontext automatically.
• Execution is slightly slow.
• Change in name of the column, throws an exception.

Dapper

• We have to create the model.
• Execution is faster.
• Change in name of the column, gives empty data for that column.

Conclusion

It is very clear that this kind of data to test the speed of Dapper and EF cannot be an accurate representation of their true potential. Dapper is faster in this case, but what framework to choose depends on a lot of other factors. Both Dapper and Entity Framework have their own advantages and disadvantages.

I hope this blog gave a high level introduction to a couple of frameworks that are very commonly used.

Happy learning! :)