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

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:

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:

Create, Update and Delete

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

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

and the Delete method needs to be implemented similar:

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:
  • You can receive all by
  • Query with LINQ statement on the DbSet of the context like so:
  • Use simple filtering  with  LINQ on ef core like so
  • 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.

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.

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:

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:

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

Tags: ,

Leave a Reply