Chapter 10: Working with Data Using Entity Framework Core

Question 1

What are the most common places to store data?

Answer 1

Rational Database Management System (RDBMS) - SQL Server, PostgreSQL, MySQL, and SQLite
& NoSql - Azure
Cosmos DB, Redis, MongoDB, and Apache Cassandra

Question 2

What are two approaches to working with EF Core

Answer 2

Database First
Code First

Question 3

How to create SQLite database with cli?

Answer 3

sqlite3 Northwind.db -init Northwind4SQLite.sql

Question 4

How to tell Visual Studio 2022 to copy the database file to the directory that it runs the code in so that it
can find the file, but only if the database file is newer or is missing?

Answer 4

<ItemGroup>
<None>
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
</None>
</ItemGroup
</ItemGroup>

Question 5

Ef core sql server database provider

Answer 5

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SQLite
Microsoft.EntityFrameworkCore.InMemory
Microsoft.EntityFrameworkCore.Cosmos

Question 6

Ef core Mysql database provider

Answer 6

MySQL.EntityFrameworkCore

Question 7

Ef core Oracle DB 11.2 database provider

Answer 7

Oracle.EntityFrameworkCore

Question 8

Ef core PostgreSQL database provider

Answer 8

Npgsql.EntityFrameworkCore.PostgreSQL

Question 9

How to defining the Northwind database context class?

Answer 9

using Microsoft.EntityFrameworkCore;
public class Northwind : DbContext
{
protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
string path = Path.Combine(Environment.CurrentDirectory, “Northwind.db”);
string connection = $”Filename={path}”;

    optionsBuilder.UseSqlite(connection);
} }

Question 10

What is the EF Core conventions to define the model?

Answer 10

• The name of a table is assumed to match the name of a DbSet<T> property in the DbContext
  class, for example, Products.</T>
• 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 to be an int type in the database.
• The primary key is assumed to be a property that is named Id or ID, or when the entity model
  class is named Product, then the property can be named ProductId or ProductID. If this
  property is an integer type or the Guid type, then it is also assumed to be an IDENTITY column
  (a column type that automatically assigns a value when inserting)

Question 11

What is the EF Core annotation attributes to define the model?

Answer 11

[Required]
[StringLength(50)]
[RegularExpression(expression)]
[Column(TypeName = “money”, Name = “UnitPrice”)]

example:
[Required]
[StringLength(40)]
public string ProductName { get; set; }

Question 12

How to using the EF Core Fluent API to define the model?

Answer 12

public class Northwind : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.Property(product => product.ProductName)
.IsRequired()
.HasMaxLength(40);</Product>

Question 13

How to seeding data in EF Core with the Fluent API

Answer 13

modelBuilder.Entity<Product>()
.HasData(new Product
{
ProductId = 1,
ProductName = "Chai",
UnitPrice = 8.99M
});</Product>

Question 14

What is the lazy loading feature

Answer 14

The two properties that relate the two entities, Category.Products and Product.Category, are both
marked as virtual

In Product Model
public virtual Category Category { get; set; } = null!;

in Category Model
public virtual ICollection<Product> Products { get; set; }</Product>

Question 15

How to adding tables to the Northwind database context class?

Answer 15

public class Northwind : DbContext
{
// these properties map to tables in the database
public DbSet<Category>? Categories { get; set; }
public DbSet<Product>? Products { get; set; }</Product></Category>

Question 16

What is the dotnet-ef tool?

Answer 16

The .NET CLI tool named dotnet can be extended with capabilities useful for working with EF Core

Question 17

What is the meaning of scaffolding models using an existing database?

Answer 17

scaffolding create all the models & context from existing database
actually this is the reverse engineering templates tool

Question 18

What is the scaffolding command for creating models from existing database?

Answer 18

dotnet ef dbcontext scaffold “Filename=Northwind.db” Microsoft.
EntityFrameworkCore.Sqlite –table Categories –table Products –outputdir AutoGenModels –namespace WorkingWithEFCore.AutoGen –dataannotations –context Northwind

Question 19

How to query Categories table with ef core

Answer 19

IQueryable<Category>? categories;
categories = db.Categories?.Include(c => c.Products);
if ((categories is null) || (!categories.Any()))
{
Fail("No categories found.");
return;
}</Category>

foreach (Category c in categories)
{
WriteLine($”{c.CategoryName} has {c.Products.Count} products.”);
}

Question 20

How to filtering included entities?

Answer 20

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

Question 21

How to sorting products

Answer 21

IQueryable<Product>? products = db.Products?
.Where(product => product.Cost > price)
.OrderByDescending(product => product.Cost);</Product>

Question 22

How to getting the generated SQL

Answer 22

categories.ToQueryString()

Question 23

How to logging EF Core

Answer 23

protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.LogTo(WriteLine)
.EnableSensitiveDataLogging();

Question 24

How to filtering logs by provider-specific values

Answer 24

optionsBuilder.LogTo(WriteLine, // Console
new[] { RelationalEventId.CommandExecuting })
.EnableSensitiveDataLogging();

Question 25

How to logging with query tags

Answer 25

IQueryable<Product>? products = db.Products?
.TagWith("Products filtered by price and sorted.")
.Where(product => product.Cost > price)
.OrderByDescending(product => product.Cost);</Product>

Question 26

How to pattern matching with Like

Answer 26

IQueryable<Product>? products = db.Products?
.Where(p => EF.Functions.Like(p.ProductName, $"%{input}%"));</Product>

Question 27

How to generating a random number in queries

Answer 27

Product? p = db.Products?.FirstOrDefault(
p => p.ProductId == (int)(EF.Functions.Random() * rowCount));

Question 28

How to defining global filters

Answer 28

modelBuilder.Entity<Product>()
.HasQueryFilter(p => !p.Discontinued)</Product>

Question 29

How many loading patterns have with EF Core

Answer 29

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

Question 30

How to statically import the System.Console class for all C# files?

Answer 30

<ItemGroup>
<Using></Using>
</ItemGroup>

Question 31

Eager loading entities using the Include extension method

Answer 31

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

Question 32

Enabling lazy loading

Answer 32

<PackageReference></PackageReference>

optionsBuilder.UseLazyLoadingProxies();

Question 33

Explicit loading entities using the Load method

Answer 33

using Microsoft.EntityFrameworkCore.ChangeTracking;
CollectionEntry<Category, Product> products =
db.Entry(c).Collection(c2 => c2.Products);
if (!products.IsLoaded) products.Load();