# SQL Query Comparison
The initial problem which led us to look into this was the terrible performance in terms of DTU usage within one of our methods, FindAuftragInternal. I would like to share the knowledge that we gathered about querying data in different ways, using Entity Framework.

There are four major ways to load data in EF, which are Eager, Load, Explicit and Lazy. We gathered that only two of them are applicable for us, after trying to using and comparing them.

## Eager aka. Include()
This is the most common kind of retrieving data from a database. The problem we are facing here are our deeply nested object models. Generally speaking, you should not use more than 3 includes within one query. Unfortunately, this is not an option in case of retrieving our job and a lot of its nested objects. A way to get around this issue is to split the include queries into many smaller ones.
#### The bad way

// this will build one giant query and call all the data at once
Auftrag auftrag = Context.Auftrag
                    .Include(a => a.Auftragstyp)
                    .Include(a => a.Standort)
                    .Include(a => a.Checklist)
                    .Include(a => a.Zusatzinfos)
                    .Include(a => a.Auftragspositionen)
                    .Single(a => a.Id == id);

#### The good way

// this will build many small queries and call data individually
Auftrag auftrag = Context.Auftrag.Include(a => a.Auftragstyp).Single(a => a.Id == id);
auftrag = Context.Auftrag.Include(a => a.Standort).Single(a => a.Id == id);
auftrag = Context.Auftrag.Include(a => a.Checklist).Single(a => a.Id == id);
auftrag = Context.Auftrag.Include(a => a.Zusatzinfos).Single(a => a.Id == id);
auftrag = Context.Auftrag.Include(a => a.Auftragspositionen).Single(a => a.Id == id);

## Load();
This procedure will load all the requested data into the used context, which can be very quick and handy at first sight, but the problem lies in the fact that the performance relies on the size of the data stored in the requested tables. The bigger the database, the slower this way of retrieving data will become. We tried this on one of our production systems and had a query time of 24 seconds, trying to retrieve the same data for a job. Hence, this method is not applicable for us, unless we know that the data within the table is not increasing over time.
For example:

// preload all the data needed for the job
Context.Auftragstyp.Load();
Context.Standort.Load();
Context.Checklist.Load();
Context.Zusatzinfos.Load();
Context.Auftragspositionen.Load();

// retrieve the job with all its preloaded data
Auftrag auftrag = Context.Single(a => a.Id == id);

## Explicit
In case of explicit loading, we have full control over what is loaded, because we set the stage for the object beforehand. First, you will retrieve the object that you want to gather data for and only then do you individually load references or collections as needed.
For example:

// retrieve the data object
Auftrag auftrag = Context.Auftrag.Single(a => a.Id == id);

// explicitly load individual references or collections for it
Context.Entry(auftrag).Reference(a => a.Auftragstyp).Load();
Context.Entry(auftrag).Reference(a => a.Standort).Load();
Context.Entry(auftrag).Reference(a => a.Checklist).Load();
Context.Entry(auftrag).Collection(a => a.Zusatzinfos).Load();
Context.Entry(auftrag).Collection(a => a.Auftragspositionen).Load();

## Lazy
This way of loading the data straight up did not work, even though our models seem to be prepared for it properly. This kind of loading is not really useful though, because you have to explicitly point to a property of an object to load it.

For example:

// retrieve wanted data object
Auftrag auftrag = DbContext.Auftrag.Single(a => a.Id == id);

// this would lazy load the Zusatzinfos during runtime
var jobInfos = job.Zusatzinfos;