| About the Author |
|
xix | |
| About the Technical Reviewer |
|
xxi | |
| Acknowledgments |
|
xxiii | |
| Introduction |
|
xxv | |
|
|
|
1 | (146) |
|
Chapter 1 Introduction to Entity Framework |
|
|
3 | (22) |
|
One, two, three versions? Oh my! |
|
|
3 | (2) |
|
|
|
3 | (2) |
|
A brief note about ADO.Net |
|
|
5 | (1) |
|
Entity Framework makes its debut |
|
|
5 | (1) |
|
Entity Framework and LINQ |
|
|
5 | (1) |
|
A new direction and a new visionary leader |
|
|
6 | (2) |
|
Microsoft goes all in for all developers |
|
|
6 | (1) |
|
A new vision requires a new path |
|
|
6 | (1) |
|
|
|
7 | (1) |
|
|
|
8 | (1) |
|
Activity 0101 Getting started with Entity Framework |
|
|
8 | (14) |
|
Create a new project and add the EF packages |
|
|
9 | (10) |
|
|
|
19 | (1) |
|
Activity supplemental information |
|
|
20 | (2) |
|
|
|
22 | (3) |
|
|
|
23 | (1) |
|
|
|
23 | (2) |
|
Chapter 2 Working with an Existing Database |
|
|
25 | (52) |
|
Reverse engineering or database first |
|
|
25 | (2) |
|
Why would we approach Entity Framework in this manner? |
|
|
25 | (1) |
|
Database-first or reverse-engineered solutions |
|
|
26 | (1) |
|
Keeping everything in sync |
|
|
26 | (1) |
|
Interacting with the existing database |
|
|
27 | (1) |
|
Working with a preexisting database activities |
|
|
27 | (49) |
|
Download the backup file for the latest version of AdventureWorks |
|
|
27 | (8) |
|
Activity 0201 Creating a reverse-engineered database in Entity Framework Core |
|
|
35 | (21) |
|
Activity 0202 Creating a database-first project in Entity Framework 6 |
|
|
56 | (20) |
|
Final thoughts for this chapter |
|
|
76 | (1) |
|
Overall things we learned |
|
|
76 | (1) |
|
|
|
76 | (1) |
|
Chapter 3 Entity Framework: Code First |
|
|
77 | (70) |
|
Code first doesn't always mean code first |
|
|
77 | (3) |
|
When not to use the code-first approach |
|
|
77 | (1) |
|
When to use the code-first approach |
|
|
78 | (1) |
|
Code first in an existing project |
|
|
79 | (1) |
|
Code first in a new project against a mature database |
|
|
79 | (1) |
|
Code first in a new project with a new database |
|
|
79 | (1) |
|
The benefits of a well-executed code-first development effort |
|
|
80 | (3) |
|
Ability to get up and running quickly |
|
|
80 | (1) |
|
A complete record of database changes in source control |
|
|
80 | (1) |
|
Agility when needing to revert to a previous state |
|
|
81 | (1) |
|
Shifting from declarative to imperative database programming |
|
|
82 | (1) |
|
It's time to see code-first database programming in action |
|
|
83 | (1) |
|
Activity 0301 Creating a new code-first implementation against an existing database project in EFCore |
|
|
84 | (13) |
|
Use the starter files, or your project from
Chapter 2 |
|
|
84 | (13) |
|
Activity 0302 Creating a new code-first project in EFCore |
|
|
97 | (26) |
|
|
|
98 | (25) |
|
Activity 0303 Creating a code-first project in EF6 |
|
|
123 | (22) |
|
|
|
124 | (1) |
|
Using an existing project to implement an EF6 code-first approach |
|
|
124 | (21) |
|
Final thoughts for this chapter |
|
|
145 | (1) |
|
Final thoughts on section 1 |
|
|
146 | (1) |
|
Part II Building the Data Solution |
|
|
147 | (302) |
|
Chapter 4 Models and the Data Context |
|
|
149 | (40) |
|
What is the database context, and why do we need it? |
|
|
149 | (4) |
|
DBContext vs. ObjectContext |
|
|
150 | (1) |
|
|
|
150 | (1) |
|
Constructing a new DBContext |
|
|
151 | (2) |
|
Critical properties and methods available when working with the DBContext |
|
|
153 | (5) |
|
Important properties on the DbContextOptions Builder object |
|
|
153 | (1) |
|
Important properties on the DBContextOptions object |
|
|
154 | (1) |
|
Important properties on the DBContext object |
|
|
154 | (1) |
|
Methods available on the DBContext |
|
|
155 | (1) |
|
Methods and extensions on the DBSet<T> object |
|
|
156 | (1) |
|
Methods and extensions for the DBContextOptions Builder object |
|
|
157 | (1) |
|
|
|
158 | (2) |
|
Two immediate benefits of code-first models |
|
|
159 | (1) |
|
Building a database entity model |
|
|
159 | (1) |
|
A final thought about models |
|
|
159 | (1) |
|
Activity 0401 Modifying the Item |
|
|
160 | (13) |
|
Practical application for your daily routine |
|
|
160 | (1) |
|
Building out the solution |
|
|
160 | (13) |
|
Activity 0402 Using the ChangeTracker to inject some automated auditing |
|
|
173 | (15) |
|
|
|
173 | (1) |
|
Common critical underlying objects |
|
|
173 | (1) |
|
The ChangeTracker is the lifeblood of our interaction with the Entity Framework |
|
|
174 | (1) |
|
Implementing automated auditing on our entities |
|
|
174 | (14) |
|
Final thoughts for this chapter |
|
|
188 | (1) |
|
Chapter 5 Constraints, Keys, and Relationships |
|
|
189 | (56) |
|
Constraining our data to enhance our solutions |
|
|
189 | (5) |
|
|
|
190 | (2) |
|
|
|
192 | (1) |
|
|
|
192 | (1) |
|
|
|
193 | (1) |
|
Using keys in database tables for unique and relational results |
|
|
194 | (1) |
|
Working with relational data |
|
|
195 | (8) |
|
First-, second-, and third-normal form |
|
|
195 | (1) |
|
|
|
195 | (1) |
|
|
|
196 | (2) |
|
|
|
198 | (2) |
|
|
|
200 | (1) |
|
|
|
200 | (1) |
|
One-to-many relationships |
|
|
201 | (1) |
|
Many-to-many relationships |
|
|
202 | (1) |
|
Some final thoughts about relationships and normalization |
|
|
203 | (1) |
|
Activities for this chapter |
|
|
203 | (1) |
|
Activity 0501 Add length, range, and other constraints to the Item model |
|
|
204 | (15) |
|
|
|
204 | (1) |
|
Affecting the length of columns |
|
|
204 | (5) |
|
Creating a range on numeric fields |
|
|
209 | (5) |
|
Ensuring a field is a Key, making fields required, and setting default values on a column |
|
|
214 | (4) |
|
Key takeaways from activity 0501 |
|
|
218 | (1) |
|
Activity 0502 Setting up relationships |
|
|
219 | (11) |
|
Creating a one-to-many relationship |
|
|
219 | (5) |
|
Creating a one-to-one relationship |
|
|
224 | (5) |
|
Key takeaways from activity 0502 |
|
|
229 | (1) |
|
Activity 0503 Using a non-clustered unique index |
|
|
230 | (13) |
|
Soft delete or hard delete, either way, just make sure it works |
|
|
230 | (7) |
|
Adding a unique, non-clustered index to the ItemGenre table to make sure that the joins are unique |
|
|
237 | (2) |
|
|
|
239 | (4) |
|
Final thoughts for this chapter |
|
|
243 | (2) |
|
Chapter 6 Data Access (Create, Read, Update, Delete) |
|
|
245 | (26) |
|
|
|
245 | (1) |
|
|
|
245 | (1) |
|
|
|
245 | (2) |
|
Leverage the DbSet<T> objects |
|
|
246 | (1) |
|
|
|
246 | (1) |
|
Activity 0601 Quick CRUD with scaffolded controllers |
|
|
247 | (22) |
|
|
|
248 | (5) |
|
Step 2 Build the application, update the database, run the web application |
|
|
253 | (2) |
|
Step 3 Review the database |
|
|
255 | (2) |
|
Step 4 Create a model, then a migration |
|
|
257 | (3) |
|
Step 5 Scaffold the controller |
|
|
260 | (4) |
|
Step 6 Review the controller -- Read |
|
|
264 | (1) |
|
Step 7 Review the controller -- Create |
|
|
265 | (1) |
|
Step 8 Review the controller -- Update |
|
|
266 | (1) |
|
Step 9 Review the Controller -- Delete |
|
|
267 | (1) |
|
Step 10 Set a couple of categories, then run the application |
|
|
267 | (2) |
|
Key takeaways from activity 0602 |
|
|
269 | (1) |
|
|
|
269 | (2) |
|
Chapter 7 Stored Procedures, Views, and Functions |
|
|
271 | (56) |
|
Understanding stored procedures, views, and functions |
|
|
271 | (2) |
|
|
|
272 | (1) |
|
|
|
272 | (1) |
|
|
|
273 | (1) |
|
Setting up the database to run scripts efficiently |
|
|
273 | (4) |
|
|
|
274 | (3) |
|
|
|
277 | (1) |
|
|
|
277 | (2) |
|
Working with the database objects |
|
|
279 | (1) |
|
|
|
279 | (1) |
|
Activity 0701 Working with stored procedures |
|
|
279 | (20) |
|
|
|
279 | (1) |
|
Step 2 Create a new migration for a simple stored procedure |
|
|
280 | (3) |
|
Step 3 Create the MigrationBuilder extension |
|
|
283 | (7) |
|
Step 4 Execute and use the results from the stored procedure |
|
|
290 | (4) |
|
Step 5 Use the Fluent API to map out a result set entity for the stored procedure |
|
|
294 | (3) |
|
Step 6 Use parameters to avoid SQL Injection attacks |
|
|
297 | (2) |
|
|
|
299 | (1) |
|
Activity 0702 Working with functions and seed data |
|
|
299 | (22) |
|
|
|
300 | (1) |
|
Step 2 Script out a new scalar-valued function |
|
|
300 | (2) |
|
Step 3 Add a new migration and update the database |
|
|
302 | (1) |
|
Step 4 Get the result set from the function into a mapped entity with no defined key |
|
|
302 | (1) |
|
Step 5 Make the program changes to execute the function and get the results |
|
|
303 | (1) |
|
Step 7 Create a new table-valued function |
|
|
304 | (3) |
|
Step 8 Seeding data with the Fluent API |
|
|
307 | (1) |
|
Step 9 Rolling our own custom migrator |
|
|
308 | (7) |
|
Step 10 Create an Items builder |
|
|
315 | (5) |
|
|
|
320 | (1) |
|
Activity 0703 Working with views |
|
|
321 | (4) |
|
|
|
321 | (1) |
|
Step 2 Add the view as a script |
|
|
321 | (1) |
|
Step 3 Add the view DTO and set the view in the InventoryDbContext |
|
|
322 | (1) |
|
Step 4 Update the Fluent API for the view |
|
|
322 | (1) |
|
Step 5 Create the migration |
|
|
323 | (1) |
|
Step 6 Make the call and get the data from the new view |
|
|
324 | (1) |
|
|
|
325 | (1) |
|
|
|
325 | (2) |
|
Chapter 8 Sorting, Filtering, and Paging |
|
|
327 | (26) |
|
|
|
327 | (1) |
|
|
|
327 | (1) |
|
Use a profiler or another tool |
|
|
328 | (1) |
|
|
|
328 | (3) |
|
Issue #1 Pre-fetching results, then iterating to filter |
|
|
328 | (1) |
|
Issue #2 Not disconnecting your data |
|
|
329 | (1) |
|
Issue #3 Enumerable vs. IQueryable |
|
|
330 | (1) |
|
|
|
331 | (1) |
|
Activity 0801 Sorting, paging, and filtering |
|
|
332 | (19) |
|
Step 1 Get the starter files for setup |
|
|
332 | (2) |
|
Step 2 Comparing two queries |
|
|
334 | (2) |
|
Step 3 Perform a server analysis on the code we just wrote |
|
|
336 | (7) |
|
Step 4 Filtering our results |
|
|
343 | (4) |
|
Step 5 Paging the filtered results |
|
|
347 | (2) |
|
Step 6 Disconnecting the result sets |
|
|
349 | (1) |
|
|
|
350 | (1) |
|
Final thoughts for this chapter |
|
|
351 | (2) |
|
Chapter 9 LINQ for Queries and Projections |
|
|
353 | (46) |
|
|
|
353 | (2) |
|
LINQ vs. stored procedures |
|
|
353 | (1) |
|
Complex data and the code-first approach |
|
|
354 | (1) |
|
DTOs, view models, or domain models |
|
|
355 | (2) |
|
Decoupling your business or view logic from the database |
|
|
355 | (1) |
|
Sometimes, a pre-defined object is overkill |
|
|
356 | (1) |
|
One tool to rule them all |
|
|
357 | (1) |
|
|
|
357 | (1) |
|
Chapter 9 Activities: Using LINQ, decoupled DTO classes, projections, anonymous types, and AutoMapper |
|
|
358 | (1) |
|
Activity 0901 Working with LINQ in complex queries |
|
|
358 | (23) |
|
|
|
359 | (3) |
|
Step 2 Start getting more useful results, and find some limitations |
|
|
362 | (2) |
|
Step 3 Use navigation properties to get results |
|
|
364 | (3) |
|
Step 4 Use projections to get more efficient queries |
|
|
367 | (3) |
|
Step 5 Getting deep relational data with filters and sorting |
|
|
370 | (8) |
|
Step 6 Finish the query by projecting to a DTO instead of an anonymous class |
|
|
378 | (3) |
|
Final thoughts on activity 0901 |
|
|
381 | (1) |
|
Activity 0902 Setting up Autolvlapper |
|
|
381 | (8) |
|
|
|
381 | (1) |
|
|
|
382 | (1) |
|
Step 3 Create the Inventory Mapper Profile |
|
|
383 | (1) |
|
Step 4 Create the DTO objects |
|
|
384 | (1) |
|
Step 5 Modify the main program to set up AutoMapper and configure the mappings |
|
|
385 | (2) |
|
Step 6 Leverage AutoMapper |
|
|
387 | (1) |
|
Final thoughts on activity 0902 |
|
|
388 | (1) |
|
Activity 0903 Working with AutoMapper in system |
|
|
389 | (9) |
|
|
|
389 | (1) |
|
Step 2 Perform a more advanced query |
|
|
389 | (2) |
|
Step 3 Update the DTO so that it maps to the correct type |
|
|
391 | (2) |
|
Step 4 Using AutoMapper to project results to a type |
|
|
393 | (1) |
|
Step 5 Handling the times when the fields don't line up exactly |
|
|
394 | (4) |
|
Final thoughts on activity 0903 |
|
|
398 | (1) |
|
Final thoughts for this chapter |
|
|
398 | (1) |
|
Chapter 10 Encryption of Data |
|
|
399 | (50) |
|
Keeping your system's data secure |
|
|
399 | (4) |
|
|
|
399 | (1) |
|
Encryption in the past vs. encryption today |
|
|
399 | (1) |
|
|
|
400 | (1) |
|
|
|
400 | (1) |
|
ASP.Net built-in authentication |
|
|
400 | (1) |
|
|
|
401 | (2) |
|
Protecting sensitive user information |
|
|
403 | (2) |
|
|
|
403 | (1) |
|
|
|
404 | (1) |
|
Chapter 10 Activities: Using Always Encrypted with EFCore and using TDE with EFCore |
|
|
405 | (1) |
|
Activity 1001 Using Always Encrypted in an EFCore solution |
|
|
405 | (17) |
|
|
|
405 | (2) |
|
Step 2 Enable Always Encrypted |
|
|
407 | (6) |
|
|
|
413 | (1) |
|
Step 4 Review the data in SSMS |
|
|
414 | (2) |
|
Step 5 Run the application |
|
|
416 | (3) |
|
Step 6 Fix the Method to Get the Items for Listing using LINQ |
|
|
419 | (1) |
|
Step 7 Turn on other method calls |
|
|
420 | (1) |
|
Final thoughts on activity 1001 |
|
|
421 | (1) |
|
Activity 1002 Using transparent data encryption |
|
|
422 | (26) |
|
A quick review of TDE vs. AlwaysEncrypted |
|
|
422 | (1) |
|
How TDE can be a better choice for your solutions |
|
|
422 | (1) |
|
|
|
423 | (1) |
|
Step 2 Discuss the TDE migration strategy, including backup |
|
|
424 | (2) |
|
Step 3 Begin the migration strategy |
|
|
426 | (4) |
|
Step 4 Run a script to back up the data for the target columns |
|
|
430 | (2) |
|
Step 5 Create a new script to generate the database keys |
|
|
432 | (3) |
|
Step 6 Drop the constraints and indexes on the target columns |
|
|
435 | (3) |
|
Step 7 Change the data type for target columns to varbinary(max) byte |
|
|
438 | (7) |
|
Step 8 Encrypt the backup data into the new columns |
|
|
445 | (2) |
|
Step 9 Delete the backup columns |
|
|
447 | (1) |
|
Final thoughts on activity 1002 |
|
|
447 | (1) |
|
Final thoughts for this chapter |
|
|
448 | (1) |
|
Part III Enhancing the Data Solution |
|
|
449 | (124) |
|
Chapter 11 Repository and Unit of Work Patterns |
|
|
451 | (50) |
|
|
|
451 | (2) |
|
The sources of information are plentiful |
|
|
451 | (1) |
|
The repository pattern abstracts the database plumbing code from the implementation |
|
|
452 | (1) |
|
Entity Framework's built-in repository |
|
|
452 | (1) |
|
|
|
453 | (1) |
|
|
|
453 | (1) |
|
Combining the repository and the unit of work |
|
|
453 | (1) |
|
|
|
453 | (1) |
|
|
|
454 | (1) |
|
|
|
455 | (1) |
|
|
|
455 | (1) |
|
Logical separation of concerns |
|
|
456 | (1) |
|
Final benefits of separating our concerns |
|
|
456 | (1) |
|
|
|
456 | (1) |
|
Activity 1101 Layering our solution |
|
|
457 | (16) |
|
|
|
457 | (1) |
|
|
|
457 | (1) |
|
Step 2 Adding the database layer project |
|
|
458 | (4) |
|
Step 3 Add the business layer project |
|
|
462 | (1) |
|
Step 4 Add AutoMapper to the two-layer projects |
|
|
462 | (1) |
|
Step 5 Create database operations in the database layer |
|
|
462 | (2) |
|
Step 6 Implement the database operations |
|
|
464 | (3) |
|
Step 7 Create operations in the service layer |
|
|
467 | (2) |
|
Step 8 Implement the service layer operations |
|
|
469 | (1) |
|
Step 9 Rework the console program |
|
|
470 | (3) |
|
Final thoughts on activity 1101 -- layering our solution |
|
|
473 | (1) |
|
Activity 1102 Rolling our own UoW |
|
|
473 | (27) |
|
Transactions are easy and effective |
|
|
473 | (1) |
|
Use the using statement for transaction life cycles |
|
|
474 | (1) |
|
|
|
474 | (1) |
|
Step 2 Modify the database interface and project |
|
|
474 | (6) |
|
Step 3 Modify the ItemsService interface and implementation in the InventoryBusinessLayer project |
|
|
480 | (4) |
|
Step 4 Build the insert logic |
|
|
484 | (5) |
|
Step 5 Build the update logic |
|
|
489 | (4) |
|
Step 6 Build the delete logic |
|
|
493 | (4) |
|
Step 7 Update the transaction scope |
|
|
497 | (2) |
|
Final thoughts on activity 1102 |
|
|
499 | (1) |
|
Final thoughts for this chapter |
|
|
500 | (1) |
|
Chapter 12 Unit Testing, Integration Testing, and Mocking |
|
|
501 | (38) |
|
Testing your code is a must-have, not a nice-to-have |
|
|
501 | (1) |
|
The code needs to be changed |
|
|
501 | (1) |
|
The database is the lifeblood of the application |
|
|
502 | (1) |
|
Testing saves your sanity and protects the system |
|
|
502 | (1) |
|
Two different approaches leading to the ability to test changes |
|
|
502 | (2) |
|
|
|
502 | (1) |
|
|
|
503 | (1) |
|
|
|
503 | (1) |
|
Chapter 12 Activities: Unit and Integration Testing |
|
|
504 | (1) |
|
Activity 1201 Unit testing with mocking |
|
|
504 | (22) |
|
|
|
505 | (21) |
|
Activity 1202 Integration testing with the Net in-memory database |
|
|
526 | (11) |
|
Using an in-memory database solution |
|
|
526 | (11) |
|
Final thoughts for this chapter |
|
|
537 | (2) |
|
|
|
537 | (1) |
|
|
|
537 | (1) |
|
|
|
538 | (1) |
|
Dependencies and injection to decouple layers |
|
|
538 | (1) |
|
Chapter 13 Alternatives to Entity Framework: Dapper |
|
|
539 | (34) |
|
|
|
539 | (1) |
|
Entity Framework is likely sufficient, if you use it correctly |
|
|
540 | (1) |
|
Benefits of using a lightweight ORM |
|
|
540 | (1) |
|
|
|
541 | (1) |
|
|
|
541 | (1) |
|
|
|
541 | (1) |
|
Implementing a hybrid solution |
|
|
542 | (1) |
|
Activity 1301 Implementing a hybrid solution with Dapper |
|
|
542 | (28) |
|
Providing a read-only data layer alternative |
|
|
543 | (27) |
|
Final thoughts for this chapter |
|
|
570 | (3) |
|
Dapper with Slapper Automapper |
|
|
570 | (1) |
|
Cached queries and direct access |
|
|
570 | (1) |
|
Multiple table joins, flat, and relational data |
|
|
570 | (1) |
|
Interface segregation and inversion of control |
|
|
571 | (1) |
|
We are positioned well for success |
|
|
571 | (2) |
|
Part IV Recipes for Success |
|
|
573 | (60) |
|
Chapter 14 Asynchronous Data Operations and Multiple Database Contexts |
|
|
575 | (50) |
|
|
|
575 | (3) |
|
Multithreaded programming |
|
|
575 | (1) |
|
Async, await, and the TaskParallelLibrary |
|
|
576 | (1) |
|
Responsive solutions for the end user |
|
|
576 | (1) |
|
Asynchronous database operations |
|
|
577 | (1) |
|
Basic asynchronous syntax |
|
|
577 | (1) |
|
Multiple database contexts |
|
|
578 | (2) |
|
|
|
578 | (1) |
|
|
|
579 | (1) |
|
Multiple contexts require a bit more work |
|
|
579 | (1) |
|
|
|
580 | (1) |
|
Activity 1401 Asynchronous database operations |
|
|
580 | (32) |
|
Leveraging async and await |
|
|
580 | (32) |
|
Activity 1402 Multiple database contexts |
|
|
612 | (11) |
|
|
|
612 | (11) |
|
Final thoughts for this chapter |
|
|
623 | (2) |
|
Chapter 15 Net 5 and Entity Framework |
|
|
625 | (8) |
|
One framework to rule them all |
|
|
625 | (1) |
|
A combination of the best parts of everything |
|
|
625 | (1) |
|
|
|
626 | (1) |
|
|
|
626 | (1) |
|
Core is going away, right? |
|
|
627 | (1) |
|
|
|
627 | (3) |
|
Many-to-many navigation properties |
|
|
627 | (1) |
|
Table-per-type (TFT) inheritance mapping |
|
|
628 | (1) |
|
|
|
628 | (1) |
|
Rationalize ToTable, ToQuery, ToView, FromSql |
|
|
629 | (1) |
|
Migrations and deployment experience |
|
|
629 | (1) |
|
EFCore platforms experience |
|
|
630 | (1) |
|
|
|
630 | (1) |
|
Final thoughts for this chapter |
|
|
630 | (1) |
|
|
|
631 | (2) |
|
Appendix A Troubleshooting |
|
|
633 | (6) |
|
|
|
633 | (2) |
|
Objects exist/objects don't exist |
|
|
633 | (1) |
|
|
|
634 | (1) |
|
Manual insert to the database |
|
|
634 | (1) |
|
|
|
634 | (1) |
|
|
|
635 | (1) |
|
General starter pack creation |
|
|
635 | (1) |
|
What you should do every time |
|
|
636 | (1) |
|
|
|
636 | (3) |
|
|
|
637 | (1) |
|
Use a diff tool like GitHub, VSCode, or WinMerge |
|
|
637 | (2) |
| Index |
|
639 | |
Lisainfo e-raamatute kohta