Querying and Manipulating Data Using LINQ

This chapter is about Language INtegrated Query (LINQ) expressions. LINQ is a set of language extensions that add the ability to work with sequences of data and then filter, sort, and project them into different outputs.

This chapter will cover the following topics:

• Writing LINQ expressions
• LINQ in practice
• Sorting and more
• Using LINQ with EF Core
• Joining, grouping, and lookups
• Aggregating and paging sequences

Join our book community on Discord

https://packt.link/EarlyAccess

This chapter is about reading from and writing to relational data stores, such as SQLite and SQL Server, by using the object-to-data store mapping technology named Entity Framework Core (EF Core).This chapter will cover the following topics:

• Understanding modern databases
• Setting up EF Core in a .NET project
• Defining EF Core models
• Querying EF Core models
• Loading and tracking patterns with EF Core
• Modifying data with EF Core

Understanding modern databases

Two of the most common places to store data are in a Relational Database Management System (RDBMS), such as SQL Server, PostgreSQL, MySQL, and SQLite, or in a NoSQL database such as Azure Cosmos DB, Redis, MongoDB, and Apache Cassandra.Relational databases were invented in the 1970s. They are queried with Structured Query Language (SQL). At the time, data storage costs were high, so they reduced data duplication as much as possible. Data is stored in tabular structures with rows and columns that are tricky to refactor once in production. They can be difficult and expensive to scale.NoSQL databases do not just mean “no SQL”; they can also mean “not only SQL.” They were invented in the 2000s, after the Internet and the web had become popular, and adopted many of the learnings from that era of software. They are designed for massive scalability and high performance and to make programming easier by providing maximum flexibility and...

10 Working with Data Using Entity Framework Core

Join our book community on Discord

https://packt.link/EarlyAccess

This chapter is about reading from and writing to relational data stores, such as SQLite and SQL Server, by using the object-to-data store mapping technology named Entity Framework Core (EF Core).This chapter will cover the following topics:

• Understanding modern databases
• Setting up EF Core in a .NET project
• Defining EF Core models
• Querying EF Core models
• Loading and tracking patterns with EF Core
• Modifying data with EF Core

Understanding modern databases

Two of the most common places to store data are in a Relational Database Management System (RDBMS), such as SQL Server, PostgreSQL, MySQL, and SQLite, or in a NoSQL database such as Azure Cosmos DB, Redis, MongoDB, and Apache Cassandra.Relational databases were invented in the 1970s. They are queried with Structured Query Language (SQL). At the time, data storage costs were high, so they reduced data duplication as...

Defining EF Core models

EF Core uses a combination of conventions, annotation attributes, and Fluent API statements to build an entity model at runtime, which enables any actions performed on the classes to later be automatically translated into actions performed on the actual database. An entity class represents the structure of a table, and an instance of the class represents a row in that table.First, we will review the three ways to define a model, with code examples, and then we will create some classes that implement those techniques.

Using EF Core conventions to define the model

The code we will write will use the following conventions:

• The name of a table is assumed to match the name of a DbSet<T> property in the DbContext class, for example, Products.
• The names of the columns are assumed to match the names of properties in the entity model class, for example, ProductId.
• The string .NET type is assumed to be a nvarchar type in the database.
• The int .NET type is assumed...

Querying EF Core models

Now that we have a model that maps to the Northwind database and two of its tables, we can write some simple LINQ queries to fetch data. You will learn much more about writing LINQ queries in Chapter 11, Querying and Manipulating Data Using LINQ.For now, just write the code and view the results:

1. In the WorkingWithEFCore project, add a new class file named Program.Helpers.cs.
2. In Program.Helpers.cs, add a partial Program class with a SectionTitle method, as shown in the following code:

partial class Program
{
  private static void ConfigureConsole(string culture = "en-US",
    bool useComputerCulture = false)
  {
    // To enable Unicode characters like Euro symbol in the console.
    OutputEncoding = System.Text.Encoding.UTF8;
    if (!useComputerCulture)
    {
      CultureInfo.CurrentCulture = CultureInfo.GetCultureInfo(culture);
    }
    WriteLine($"CurrentCulture: {CultureInfo.CurrentCulture.DisplayName}");
  }
  private static void WriteLineInColor...

Loading and tracking patterns with EF Core

There are three loading patterns that are commonly used with EF Core:

• Eager loading: Load data early.
• Lazy loading: Load data automatically just before it is needed.
• Explicit loading: Load data manually.

In this section, we're going to introduce each of them.

Eager loading entities using the Include extension method

In the QueryingCategories method, the code currently uses the Categories property to loop through each category, outputting the category name and the number of products in that category.This works because when we wrote the query, we enabled eager loading by calling the Include method for the related products.Let's see what happens if we do not call Include:

1. In Program.Queries.cs, in the QueryingCategories method, modify the query to comment out the Include method call, as shown highlighted in the following code:

IQueryable<Category>? categories = db.Categories;
  //.Include(c => c.Products);

1. In Program.cs...

Modifying data with EF Core

Inserting, updating, and deleting entities using EF Core is an easy task to accomplish. This is often referred to as CRUD, an acronym that includes the following operations:

• C for Create
• R for Retrieve (or Read)
• U for Update
• D for Delete

By default, DbContext maintains change tracking automatically, so the local entities can have multiple changes tracked, including adding new entities, modifying existing entities, and removing entities.When you are ready to send those changes to the underlying database, call the SaveChanges method. The number of entities successfully changed will be returned.

Inserting entities

Let's start by looking at how to add a new row to a table:

1. In the WorkingWithEFCore project, add a new class file named Program.Modifications.cs.
2. In Program.Modifications.cs, create a partial Program class with a method named ListProducts that outputs the ID, name, cost, stock, and discontinued properties of each product, sorted with the costliest...

Practicing and exploring

Test your knowledge and understanding by answering some questions, getting some hands-on practice, and exploring this chapter's topics with deeper research.

Exercise 10.1 – Test your knowledge

Answer the following questions:

1. What type would you use for the property that represents a table, for example, the Products property of a database context?
2. What type would you use for the property that represents a one-to-many relationship, for example, the Products property of a Category entity?
3. What is the EF Core convention for primary keys?
4. When might you use an annotation attribute in an entity class?
5. Why might you choose the Fluent API in preference to annotation attributes?
6. What does a transaction isolation level of Serializable mean?
7. What does the DbContext.SaveChanges() method return?
8. What is the difference between eager loading and explicit loading?
9. How should you define an EF Core entity class to match the following table?

CREATE TABLE Employees...