EF core querying basics

In this post we will be looking at all the basic patterns you can use to query the database with ef core.
The post includes the following:

• Crud Operations
• Querying (get data from the database)
• Explicit, Eager and Lazy Loading

CRUD Operations

For the following examples and instructions we assume that ef core is setup in your application (else see here) and you are using a DbContext similar to this one:

public class TodoItemContext : DbContext
    {
        public DbSet<TodoEntity> TodoItems { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=todoitem.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder) 
        { }
    }

We are using a sqlite database for simplicites sake. You could also use postgres, mariadb, sql server express or any other database for the given examples (see my examples with docker for help)

We work with a pretty stupid Repository for now, just to demonstrate how ef core queries work and we are going to complete the methods included in the repository:

public class CrudRepository
{
    private readonly TodoItemcontext _context;
    public CrudRepository(TodoItemContext context)
    {
        _context = context;
    }

    public Task AddAsync(TodoItem item)
    { 
      return Task.CompletedTask;
    }

    public Task UpdateAsync(TodoItem item)
    {
      return Task.CompletedTask;
    }

    public Task DeleteAsync(TodoItem item)
    {
      return Task.CompletedTask;
    }
   // omitted query methods for now

}

Create, Update and Delete

To create an entity you simply use the instance of your DbContext and invoke the following method on it

public async Task AddAsync(TodoItem item)
{
   await _context.AddAsync(item);
   await _context.SaveChangesAsync();
}

As we can see we simply call the AddAsync method on the DbContext and are good to go.
The same is true for the Update and Delete methods.

Always remember that you need to call to call SaveAsync after the operation.

For an update operation this is not always necessary but still best practice. Sometimes you do not track the entities returned from a query, which results in non tracked entities (Tracking means that the loaded entities from the database are monitored for change by ef core behind the scenes).

The update method looks as follows

public async Task UpdateAsync(TodoItem item)
{
   await _context.UpdateAsync(item);
   await _context.SaveChangesAsync();
}

and the Delete method needs to be implemented similar:

public async Task DeleteAsync(TodoItem item) 
{
   await _context.DeleteAsync(item); 
   await _context.SaveChangesAsync(); 
}

This is all pretty straight forward.
For a real world repository we should probably do more than just delegating the method calls, but as I said before we simply want to get the idea of how ef core works against the database.

But an essential skill of an ORM is querying the database for data. And this is what we will look at next: Read operation (SELECTs)

Querying

To receive data from the database with ef core you can utilize different approaches.

• If you have access to the Id of the entity (remember an Id field is required on entities for ef to work), then you can use:

  TodoItem item = _context.FindAsync(id); //id is known
  

• You can receive all by

  var all =  await _context.Entities.ToListAsync();

• Query with LINQ statement on the DbSet of the context like so:

  var all = context.Entities.Where(x => true);
  // any expression that returns queryable

• Use simple filtering  with  LINQ on ef core like so

  var first = await _context.Entities.FirstOrDefaultAsync();
  var single= await _context.Entities.SingleOrDefaultAsync();

• Use the QueryTypes to query database views, stored procedures and invoke raw sql queries (not in this post though)
• Or you can execute FromSql and invoke stored procedures or raw sql directly with ef core.

  var all =  await _context.FromSql("SElECT * from TodoItems");

The LINQ statement, the Entities.ToList  and the FindAsync are the ones we will focus on with this post.
If you have some experience with LINQ (which you most likely have as a dotnet developer) you should be familiar with the syntax and the grouping, ordering, projection and other methods from LINQ.
Because of this we will not look further into it, except for the ef core specifics.

To optimize ef core queries we can add the AsNoTracking chained call to a LINQ-Query on the database, which will deactivate the change tracker of ef core.

The change tracker is activated by default.
If you only need to do read operations on your entities, then prefer AsNoTracking, because ef core does not need to construct the whole tree of dependent entities and manage it in memory in this case.

var allNotTracked = await _context.Entities.AsNoTracking().ToListAsync();

As we learned about ef core it allows for the so called NavigationProperties to get access to the related entiteis (linked by foreign key relationships).
But how do we query for those effectively and efficiently?

Explicit, Eager and Lazy loading

The naive approach would be to load all entities and their dependent entities in some form of loop similar to this one:

// first load all entities you need to have
var entities = _context.MyEntities.ToListAsync() // needs to be tracked!

// then loop through each and load the properties explicitly
foreach (var entity in entities)
{
    _context.Entry(entity).Reference(b => b.ToLoadProperty).LoadAsync();
    //or
    // _context.Entry(entitiy).Collection(b => b.CollectionPropertyToLoad).LoadAsync();
}

Here we show how to load a single (Reference) or a Collection property (Collection). I added some fictional properties, as our simple app does not make use of such relations.

But this is extremely inefficient!!!
This is because you do not use the database as it is meant to be for this kind of relations (Join anyone?).

This is knwon as the N+1 problem of ORM´s.

It means for each entity you have (or N items) you load, you need to loop through those N entities and +1 for each related entity you want to load (or even worse if you load a collection).

So how do we solve this issue?

We will see in the next section that there are possible solutions to this problem:

Solutions to the N+1 problem

1. Eager Loading, or load everything all the time with include
2. Lazy Loading, or load on demand

1 load everything when needed

In ef core the preferred way to get around this issue are the Include and ThenInclude ExtensionMethods on an ef query result.

To use the example of before you can use them like this:

var entiteis = await _context.Entry
                             .Where(x => x.IsActive)
                             .Include(x => x.PropertyToLoad())
                                 .ThenInclude(p => p.PropertyOnProperty())
                             .ToListAsync();

This then uses joins implicitly to populate the entity object graph.

With the basics in mind we can now look at some patterns that make querying easier.

This we will be doing in the next post on querying:
Query patterns in  ef core.

2 is not implemented currently in ef core but is in development