Contents
Understanding Entity Framework Core ........................................................................ 3
About This Book ........................................................................................................ 4
What Do You Need to Know? ......................................................................................4
What Software Do You Need? .................................................................................... 4
What If You Don’t Want to Use Windows? .................................................................. 4
What Is the Structure of This Book? ........................................................................... 4
Part 1: Introducing Entity Framework Core 2 .............................................................. 5
Part 2: Entity Framework Core 2 in Detail ....................................................................5
Part 3: Advanced Entity Framework Core 2 ................................................................. 5
Where Can You Get the Example Code? ..................................................................... 5
Where Can You Get Corrections for This Book? ........................................................... 5
How Can You Contact Me? .........................................................................................5
Summary ................................................................................................................. 5
Getting Ready .......................................................................................................... 7
Installing .NET Core ..................................................................................................8
Installing Visual Studio 2017 ......................................................................................8
Creating the Project ................................................................................................ 10
Setting the Scene ................................................................................................... 10
Creating the Project ................................................................................................ 11
Creating the Data Model and Context Classes ........................................................... 14
Creating the Controller and Views ............................................................................ 15
Configuring Entity Framework Core .......................................................................... 19
Configuring the Connection String ............................................................................ 20
Configuring the Startup Class ................................................................................... 21
Preparing the Database ........................................................................................... 22
Testing the Application ............................................................................................ 22
Summary ............................................................................................................... 24
Preparing for This Chapter ..................................................................................... 26
Exploring the Database .......................................................................................... 27
Examining the Database Tables .............................................................................. 29
Examining the Database Content .............................................................................31
Understanding SQL ................................................................................................ 33
Querying Data ........................................................................................................33
Storing and Updating Data ..................................................................................... 42
Deleting Data .........................................................................................................44
Joining Data .......................................................................................................... 45
Preparing the Database ...........................................................................................45
Performing a Join ....................................................................................................47
Summary ............................................................................................................... 47
Creating the Project ................................................................................................ 49
Configuring the MVC Framework ...............................................................................51
Adding the Model .....................................................................................................52
Adding a Repository .................................................................................................52
Adding a Controller and View ....................................................................................54
Adding the Finishing Touches ....................................................................................56
Running the Example Application ............................................................................. 58
Summary ............................................................................................................... 59
■■Chapter 5: SportsStore: Storing Data .......................................................... 61
Preparing for this Chapter ....................................................................................... 61
Configuring Entity Framework Core .......................................................................... 62
Configuring Entity Framework Core Logging Messages .............................................. 62
Preparing the Data Model ....................................................................................... 63
Defining a Primary Key Property ...............................................................................63
Creating the Database Context Class ....................................................................... 63
Updating the Repository Implementation ..................................................................64
Preparing the Database .......................................................................................... 65
Configuring the Connection String ............................................................................65
Configuring the Database Provider and Context Class ............................................... 66
Creating the Database .............................................................................................68
Running the Application .......................................................................................... 69
Avoiding the Query Pitfalls ...................................................................................... 70
Understanding the IEnumerable<T> Pitfall ...............................................................71
Understanding the Duplicate Query Pitfall .................................................................73
Common Problems and Solutions ............................................................................ 78
Problems Creating or Accessing the Database .......................................................... 78
Problems Querying Data ..........................................................................................80
Problems Storing Data .............................................................................................81
Summary .............................................................................................................. 82
Preparing for This Chapter ..................................................................................... 83
Modifying Objects ................................................................................................. 85
Updating the Repository .........................................................................................85
Updating the Controller and Creating a View ........................................................... 86
Updating Only Changed Properties ......................................................................... 91
Performing Bulk Updates ....................................................................................... 92
Deleting Data ....................................................................................................... 99
Common Problems and Solutions ......................................................................... 103
Objects Are Not Updated or Deleted .................................................................... 103
The “Reference Not Set to an Instance of an Object” Exception ............................. 103
The “Instance of Entity Type Cannot be Tracked” Exception .................................. 104
The “Property Has a Temporary Value” Exception ................................................. 104
Updates Result in Zero Values ............................................................................. 104
Summary ........................................................................................................... 104
■■Chapter 7: SportsStore: Expanding the Model ........................................ 105
Preparing for This Chapter ....................................................................................105
Creating a Data Model Relationship .......................................................................107
Adding a Data Model Class ...................................................................................108
Creating the Relationship .....................................................................................108
Updating the Context and Creating the Repository .................................................109
Creating and Applying a Migration ........................................................................111
Creating a Controller and View .............................................................................111
Populating the Database with Categories ..............................................................115
Using a Data Relationship ....................................................................................116
Working with Related Data ..................................................................................116
Selecting a Category for a Product .......................................................................118
Creating and Editing Products with Categories ......................................................120
Adding Support for Orders ..................................................................................122
Creating the Data Model Classes .........................................................................122
Creating the Repository and Preparing the Database ............................................123
Creating Controllers and Views ...........................................................................125
Storing Order Data ............................................................................................131
Common Problems and Solutions ........................................................................133
The “ALTER TABLE conflicted with the FOREIGN KEY” Exception ...........................133
The “UPDATE Conflicted with the FOREIGN KEY” Exception ...................................134
The “The Property Expression ‘x => x.<name>’ is Not Valid” Exception .................134
The “Type of Navigation Property <name> Does Not ICollection<OrderLine>”........134
The “The Property <name> is Not a Navigation Property of Entity Type <name>”..134
The “Invalid Object Name <name>” Exception ................................................... 134
Objects Are Deleted Instead of Being Updated .................................................... 135
The Class Name for Related Data Is Displayed in a View ...................................... 135
Summary ......................................................................................................... 135
Preparing for This Chapter ................................................................................ 137
Creating a Seed Data Controller and View ...........................................................137
Scaling Up Data Presentation .............................................................................142
Adding Support for Pagination ............................................................................142
Adding Search and Ordering Support ..................................................................148
Applying the Data Presentation Features to Categories ....................................... 152
Indexing the Database ..................................................................................... 155
Creating and Applying Indexes ..........................................................................157
Common Problems and Solutions .......................................................................159
Queries for Pages Are Too Slow ........................................................................ 159
Applying the Index Migration Times Out .............................................................159
Creating an Index Does Not Improve Performance ............................................. 160
Summary ........................................................................................................ 160
Preparing for This Chapter ................................................................................ 161
Removing the Timing Statements .......................................................................161
Adding a View Import ....................................................................................... 162
Modifying the Data Model ..................................................................................162
Adding Product Seed Data .................................................................................163
Preparing the Database .....................................................................................166
Displaying Products to the Customer ..................................................................167
Preparing the Data Model ..................................................................................167
Creating the Store Controller, Views, and Layout ............................................... 169
Testing the Store Display ..................................................................................173
Adding the Shopping Cart ................................................................................ 174
Enabling Persistent Session Data .......................................................................174
Creating the Cart Model Class ...........................................................................177
Creating the Controller and Views .....................................................................178
Testing the Ordering Process ............................................................................183
Common Problems and Solutions ......................................................................183
Clicking a Page Button Manages the Wrong Data Type ...................................... 183
Clicking a Page Button Has No Effect .................................................................184
The “Cannot Insert Explicit Value for Identity Column” Exception ....................... 184
Session Objects Are Null ..................................................................................184
Session Objects Are Lost or Inconsistently Available ......................................... 184
Summary ....................................................................................................... 184
Preparing for This Chapter .............................................................................. 185
Creating a Web Service ................................................................................... 186
Creating the Repository ....................................................................................186
Creating the API Controller ...............................................................................188
Testing the Web Service ...................................................................................190
Projecting a Result to Exclude Null Navigation Properties ................................... 190
Including Related Data in a Web Service Response ........................................... 192
Querying for Multiple Objects ...........................................................................195
Completing the Web Service ............................................................................ 198
Updating the Controller ....................................................................................200
Common Problems and Solutions ..................................................................... 202
Null Property Values When Storing or Updating Objects .................................... 202
Slow Web Service Requests .............................................................................202
The “Cannot Insert Explicit Value for Identity Column” Exception ...................... 202
Summary ...................................................................................................... 202
Creating the ASP.NET Core MVC Project ........................................................... 206
Creating the Data Model Class ..........................................................................207
Configuring Services and Middleware ................................................................208
Adding a Controller and View ...........................................................................209
Adding the Bootstrap CSS Framework ............................................................. 210
Configuring the HTTP Port ...............................................................................211
Running the Example Application .....................................................................212
Adding and Configuring Entity Framework Core ............................................... 213
Adding the NuGet Package ..............................................................................213
Creating the Database Context Class ............................................................... 214
Preparing the Entity Class ...............................................................................214
Updating the Controller ...................................................................................215
Configuring the Database Provider ..................................................................216
Configuring Entity Framework Core Logging ....................................................219
Implementing the Repository Pattern ............................................................. 223
Defining the Repository Interface and Implementation Class ........................... 224
Avoiding the IEnumerable vs IQueryable Pitfall ................................................226
Hiding the Data Operations ............................................................................230
Completing the Example MVC Application ....................................................... 232
Completing the Repository ..............................................................................233
Adding the Action Methods .............................................................................234
Updating and Adding Views ............................................................................235
Summary ......................................................................................................237
Preparing for This Chapter .............................................................................240
Starting the Example Application ....................................................................241
Reading Data ................................................................................................242
Reading an Object by Key ..............................................................................242
Querying All Objects ......................................................................................246
Querying for Specific Objects .........................................................................247
Storing New Data ......................................................................................... 252
Understanding Key Assignment ......................................................................253
Updating Data ............................................................................................. 254
Updating a Complete Object ..........................................................................254
Querying the Existing Data Before Updating ................................................... 256
Updating in a Single Database Operation .........................................................258
Deleting Data ............................................................................................... 262
Summary ..................................................................................................... 264
Preparing for This Chapter ............................................................................. 266
Understanding Migrations ............................................................................... 267
Working with the Initial Migration ....................................................................267
Examining the Migration SQL ...........................................................................271
Applying the Migration .....................................................................................272
Seeding the Database and Running the Application ........................................... 273
Creating Additional Migrations ......................................................................... 274
Adding Another Property to the Data Model ...................................................... 276
Managing Migrations ....................................................................................... 277
Listing Migrations .............................................................................................278
Applying All Migrations .....................................................................................278
Updating to a Specific Migration ........................................................................279
Removing a Migration .......................................................................................280
Resetting the Database .....................................................................................281
Working with Multiple Databases ....................................................................... 281
Extending the Data Model ................................................................................ 282
Configuring the Application ................................................................................283
Creating and Applying Migrations .......................................................................285
Managing Migrations Programmatically .............................................................. 286
Creating the Migration Manager Class .................................................................286
Creating the Migrations Controller and View ....................................................... 289
Configuring the Application ................................................................................291
Running the Migrations Manager ........................................................................291
Seeding Databases Programmatically ................................................................. 292
Creating a Seeding Tool .....................................................................................294
Seeding During Startup ......................................................................................296
Summary ......................................................................................................... 298
■■Chapter 14: Creating Data Relationships .............................................. 299
Preparing for This Chapter ................................................................................ 300
Creating a Relationship ..................................................................................... 300
Adding a Navigation Property .............................................................................301
Creating the Migration .......................................................................................302
Querying and Displaying the Related Data ......................................................... 304
Updating the View to Display Related Data ........................................................ 308
Preparing the Database .....................................................................................310
Creating and Updating Related Data ................................................................. 312
Creating a New Supplier When Creating a New Product ..................................... 314
Updating a Supplier when Updating a Product ....................................................316
Deleting Related Data ...................................................................................... 318
Creating a Required Relationship ...................................................................... 321
Creating a Foreign Key Property ........................................................................321
Dropping the Database and Preparing the Seed Data ........................................ 323
Updating and Seeding the Database ................................................................ 324
Understanding the Required Relationship Delete Operation ............................... 325
Querying for Multiple Relationships .................................................................. 327
Updating and Seeding the Database ................................................................ 328
Querying the Chain of Navigation Properties .................................................... 329
Summary ....................................................................................................... 331
■■Chapter 15: Working with Relationships Part 1 ................................. 333
Preparing for This Chapter .............................................................................. 333
Directly Accessing Related Data ....................................................................... 334
Promoting Related Data ...................................................................................335
Accessing Related Data Using a Type Parameter ............................................... 338
Completing a Data Relationship ....................................................................... 343
Querying Related Data in a One-to-Many Relationship ...................................... 344
Working with Related Data in a One-to-Many Relationship ................................ 353
Updating Related Objects ................................................................................355
Creating New Related Objects .........................................................................358
Changing Relationships ...................................................................................362
Summary .......................................................................................................369
■■Chapter 16: Working with Relationship Part 2 .................................. 371
Preparing for This Chapter ............................................................................. 371
Completing a One-to-One Relationship ........................................................... 372
Defining the Navigation Property ....................................................................372
Selecting the Dependent Entity Class ..............................................................373
Creating and Applying the Migration ...............................................................374
Working with One-to-One Relationships ......................................................... 375
Querying Related Data in a One-to-One Relationship ...................................... 375
Creating and Updating Related Objects ...........................................................377
Changing One-to-One Relationships ...............................................................380
Defining Many-to-Many Relationships ............................................................ 389
Creating the Junction Class ............................................................................390
Completing the Many-to-Many Relationship .................................................... 391
Preparing the Application ..............................................................................392
Querying for Many-to-Many Data ...................................................................394
Managing Many-to-Many Relationships ...........................................................397
Summary .....................................................................................................402
Preparing for This Chapter ............................................................................404
Understanding the Example Existing Database ...............................................404
Connecting to the Database Server ...............................................................405
Creating the Database .................................................................................405
Creating the ASP.NET Core MVC Project ........................................................411
Testing the Example Application ...................................................................415
Scaffolding an Existing Database ..................................................................416
Performing the Scaffold Process ...................................................................416
Using a Scaffolded Data Model in the ASP.NET Core MVC ............................. 419
Responding to Database Changes ............................................................... 423
Modifying the Database ..............................................................................424
Updating the Data Model ............................................................................425
Updating the Context Class ........................................................................ 426
Updating the Controllers and Views .............................................................426
Adding Persistent Data Model Features ....................................................... 428
Summary .................................................................................................. 430
Preparing for This Chapter ......................................................................... 432
Creating a Manual Data Model ................................................................... 432
Creating the Context and Entity Classes .......................................................433
Creating the Controller and View .................................................................434
Understanding the Basic Data Model Conventions ........................................ 436
Overriding the Data Model Conventions .......................................................437
Modeling Relationships ...............................................................................443
Completing the Data Model .........................................................................449
Using the Manually Created Data Model ...................................................... 451
Querying Data in a Manually Created Data Model ........................................ 452
Updating Data in a Manually Created Data Model ........................................ 455
Summary ................................................................................................. 461
Preparing for This Chapter ........................................................................ 466
Creating the Data Model ............................................................................467
Creating the Controller and Views ..............................................................468
Configuring the Application ........................................................................471
Creating the Database and Testing the Application ..................................... 474
Managing Key Generation ......................................................................... 475
Understanding the Identity Strategy ...........................................................475
Understanding the Hi-Lo Key Strategy ........................................................476
Working with Natural Keys ....................................................................... 479
Ensuring Unique Values for Natural Keys ....................................................480
Creating an Alternate Key ..........................................................................482
Using Natural Keys as Primary Keys .......................................................... 487
Creating Composite Keys ......................................................................... 490
Summary ................................................................................................ 495
■■Chapter 20: Queries ...................................................................... 497
Preparing for This Chapter ....................................................................... 498
Managing Change Tracking for Query Results ............................................ 500
Removing Individual Objects from Change Traczing ................................... 502
Changing the Default Change Tracking Behavior ........................................ 503
Using a Query Filter ................................................................................. 505
Overriding a Query Filter .......................................................................... 509
Querying Using Search Patterns ................................................................ 512
Making Asynchronous Queries ...................................................................515
Explicitly Compiling Queries .......................................................................518
Avoiding the Client Evaluation Pitfall .......................................................... 520
Throwing a Client Evaluation Exception ......................................................523
Summary .................................................................................................525
Preparing for This Chapter ....................................................................... 528
Specifying SQL Data Types ...................................................................... 531
Specifying a Maximum Length .................................................................. 533
Updating the Database .............................................................................534
Validating or Formatting Data Values ........................................................ 536
Avoiding the Backing Field Selective Update Pitfall ..................................... 539
Hiding Data Values from the MVC Application ............................................ 543
Accessing Shadow Property Values ............................................................544
Including Shadow Properties in Queries .....................................................545
Setting Default Values ............................................................................. 546
Displaying the Default Value .....................................................................548
Detecting Concurrent Updates .................................................................. 551
Using Concurrency Tokens ........................................................................552
Using a Row Version to Detect Concurrent Updates ................................... 557
Summary ................................................................................................ 562
Preparing for This Chapter ......................................................................... 563
Understanding Delete Constraints ............................................................... 568
Configuring the Delete Behavior ..................................................................570
Using the Cascade Delete Behavior ............................................................. 571
Setting Foreign Keys to Null ....................................................................... 573
Relying on the Database Server to Change Foreign Keys .............................. 574
Relying on Entity Framework Core to Update Foreign Keys ........................... 576
Taking Control of the Delete Operation ....................................................... 581
Re-creating the Cascade Behavior ...............................................................581
Re-creating the Set Null Behavior ................................................................582
Reinstating the Soft-Delete Feature ............................................................ 583
Summary ................................................................................................. 587
Preparing for This Chapter ........................................................................ 590
Using SQL Directly .................................................................................... 594
Querying Using SQL ...................................................................................594
Calling Stored Procedures or Other Operations ........................................... 607
Using Server-Generated Values .................................................................. 611
Using Database Server–Generated Default Values ....................................... 611
Incorporating Sequential Values .................................................................616
Computing Values in the Database .............................................................619
Modeling Automatically Generated Values ...................................................623
Summary ................................................................................................ 628
■■Chapter 24: Using Transactions ............................................................ 629
Preparing for This Chapter ....................................................................... 630
Understanding the Default Behavior ......................................................... 633
Performing Independent Changes .............................................................634
Disabling Automatic Transactions ..............................................................636
Using Explicit Transactions ...................................................................... 638
Including Other Operations in a Transaction ............................................. 640
Changing the Transaction Isolation Level ...................................................642
Summary ............................................................................................... 645
Index .................................................................................................... 647
The first part of this book is designed to help you understand broadly the foundational ideas of Entity Framework Core 2 development and to experience in practice what the framework is like to use in an ASP.NET Core MVC project.
Entity Framework Core in Context
Entity Framework Core—also known as EF Core—is an object-relational mapping (ORM) package produced by Microsoft that allows .NET Core applications to store data in relational databases.
Understanding Entity Framework Core
Entity Framework Core has one key task: storing.NET objects in a database and retrieving them again later. Put another way, Entity Framework Core acts as the bridge between an ASP.NET Core MVC application and a database.
Storing .NET objects in a database can be a surprisingly complex process. Databases don’t exist in isolation. They are created and managed by database servers, which are specialized applications focused solely on the storage and management of data. Database servers provide the persistent storage for most applications, and the most popular database servers have been under development for decades, resulting in high-performance and robust software that is packed with features. Database servers support a core set of common features, but they differentiate themselves with custom additions, and getting the best results from a database server means taking advantage of them.
There are different types of database server available; the kind that Entity Framework Core works with is called a relational database server, also known as a relational database management system (RDBMS).
A relational database server manages relational databases, where data is stored as rows in tables, not unlike a spreadsheet. Relational database servers typically accept commands expressed in the Structured Query Language (SQL), which allows data operations—such as storing or deleting data—to be expressed. There is a SQL standard, but database servers use slightly different dialects, especially when it comes to accessing the nonstandard features.
To store .NET objects in a database, Entity Framework Core has to be able to translate objects into a form that can be stored in a database table and formulate a SQL command that the database server can process. To deal with the differences between database servers, Entity Framework Core relies on a database provider, which is responsible for communicating with the database and formulating the SQL commands.
To retrieve .NET objects from the database, Entity Framework Core has to be able to reverse the process. It has to be able to create a SQL query that will ask the database server for the data values that represent the object and use them to populate the properties of a .NET object. To make this process as natural as possible, Entity Framework Core supports LINQ for querying the database, which makes working with collections of objects stored in a database similar to working with collections of objects in memory.
About This Book
In this book, I explain how to use Entity Framework Core in ASP.NET Core MVC applications. I show you the different ways that Entity Framework Core can be added to a project and the pitfalls that await the unwary.
Entity Framework Core is a powerful, tool but it is important to pay attention to the details; otherwise, you will end up with an application that doesn’t perform well or doesn’t behave as expected.
What Do You Need to Know?
To get the most from this book, you should already be familiar with ASP.NET Core MVC development. You will struggle if you don’t already know how controllers and actions work and how Razor views behave. If you are not familiar with ASP.NET Core MVC, then consult https://docs.microsoft.com/en-us/aspnet/core or my book Pro ASP.NET Core MVC 2, published by Apress.
What Software Do You Need?
To follow the examples in this book, you will need a PC running Windows, the .NET Core SDK, and the latest version of Visual Studio. With the exception of Windows, all the tools that I use in this book are available without charge, and I explain how to set up your development environment in Chapter 2.
What If You Don’t Want to Use Windows?
I have relied on Windows and Visual Studio throughout this book because that is what most readers use, and being able to use LocalDB—the Windows-only developer version of SQL Server—makes the examples more predictable and reliable. With a little effort, you can run all of the examples in this book on any platform that .NET Core supports, although you will need to install the full SQL Server product (or use Docker containers).
What Is the Structure of This Book?
This book is split into three parts, each of which covers a set of related topics.
