04 - EF Core (3)

Querying data

Entity Framework Core uses Language Integrated Query (LINQ) to query data from the database. LINQ allows you to use C# (or your .NET language of choice) to write strongly typed queries based on your derived context and entity classes. A representation of the LINQ query is passed to the database provider, to be translated in database-specific query language (for example, SQL for a relational database).

Query Basics

var persons = context.Persons.ToList();
var person = context.Persons .Single(p => p.PersonId == 1);
var persons = context.Persons.Where(b => b.FirstName.Contains(”Andres")) .ToList();

Linq lecture – upcoming….

Eager loading (joins):

Include and ThenInclude
Include always starts from the top of the graph
You can go several levels down with more ThenInclude’s

var persons = context.Persons
    .Include(p => p.Contacts)
        .ThenInclude(c => c.ContactType)
        .ThenInclude(ct => ct.Image)
    .Include(p => p.MetaData)
        .ThenInclude(m => m.Photo)
    .ToList();

Warning

If entity was already in memory, it will automatically be included as navigation property in new data.

Using single Include

You can also load multiple navigations using a single Include method.

var persons = context.Persons
    .Include(p => p.Contacts.ContactType.Image)
    .Include(p => p.MetaData.Photo)
    .ToList();

Filtered include

When applying Include to load related data, you can add certain enumerable operations to the included collection navigation, which allows for filtering and sorting of the results.

Supported operations are:

Where
OrderBy, OrderByDescending
ThenBy, ThenByDescending,
Skip, Take

Applied on the collection navigation in the lambda passed to the Include method

using (var context = new BloggingContext()) {
    var filteredBlogs = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.blogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5)
        )
        .ToList();
}

Explicit loading:

You can explicitly load a navigation property via the DbContext.Entry(...) API
context.Entry(person).Collection(p => p.Contacts).Load();
context.Entry(contact).Reference(c => c.ContactType).Load();

Querying related entities:

You can also get a LINQ query that represents the contents of a navigation property.
This allows you to do things such as running an aggregate operator over the related entities without loading them into memory.

var person = context.Persons
    .Single(p => p.PersonId == 1);
var skypeContacts = context.Entry(person)
    .Collection(b => b.Contacts)
    .Query()
    .Where(c => c.ContactType.Name == “Skype”)
    .ToList();

Lazy Loading

NO! NO! NO!

NO JUST NO

Image title

Install Microsoft.EntityFrameworkCore.Proxies package

.AddDbContext<BloggingContext>
    (b => b.UseLazyLoadingProxies()
           .UseSqlServer(myConnectionString));

EF Core will then enable lazy loading for any navigation property that can be overridden--that is, it must be virtual and on a class that can be inherited from (not sealed).

Tracking vs No-Tracking

Tracking behavior controls whether or not Entity Framework Core will keep information about an entity instance in its change tracker.

If an entity is tracked, any changes detected in the entity will be persisted to the database during SaveChanges().

Entity Framework Core will also fix-up navigation properties between entities that are obtained from a tracking query and entities that were previously loaded into the DbContext instance.

No tracking queries are useful when the results are used in a readonly scenario. They are quicker to execute because there is no need to setup change tracking information.

var persons = context.Persons.AsNoTracking().ToList()

Raw SQL queries

You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query.

var persons = context.Persons.FromSqlRaw("SELECT * FROM Persons").ToList();

var persons = context.Persons.FromSqlRaw("SELECT * FROM Persons WHERE FirstName LIKE ‘%{0}%’”, username).ToList();

var persons = context.Persons.FromSqlRaw($"SELECT * FROM Persons WHERE FirstName LIKE ‘%{username}%’").ToList();

Composing with LINQ

If the SQL query can be composed on in the database, then you can compose on top of the initial raw SQL query using LINQ operators. SQL queries that can be composed on begin with the SELECT keyword.

Change Tracking

As usual

Including related data

As usual

Async operations

Asynchronous operations avoid blocking a thread while the query is executed in the database.
This can be useful to avoid freezing the UI of a thick-client application.
Asynchronous operations can also increase throughput in a web application, where the thread can be freed up to service other requests while the database operation completes.
EF Core does not support multiple parallel operations being run on the same context instance. You should always wait for an operation to complete before beginning the next operation. This is typically done by using the await keyword on each asynchronous operation.

Global Query Filters

Global query filters are LINQ query predicates (a boolean expression typically passed to the LINQ Where query operator) applied to Entity Types in the metadata model (usually in OnModelCreating).

Such filters are automatically applied to any LINQ queries involving those Entity Types, including Entity Types referenced indirectly, such as through the use of Include or direct navigation property references.

Soft-delete
modelBuilder.Entity<Person>().HasQueryFilter(p => !p.IsDeleted);

Disabling filters
var persons = db.Persons.Include(p => p.Contacts).IgnoreQueryFilters().ToList();