Beginning Entity Framework Core 2.0: Database Access from .NET - Work directly with data through domain-specific objects and methods

345 Pages·2018·8.85 MB·english

by  Derek J. Rouleau

Beginning
Entity Framework
Core 2.0
Database Access from .NET
—
Work directly with data through
domain-specific objects and methods
—
Derek J. Rouleau
Beginning Entity
Framework Core 2.0
Database Access from .NET
Derek J. Rouleau
Beginning Entity Framework Core 2.0
Derek J. Rouleau
East Baldwin, Maine, USA
ISBN-13 (pbk): 978-1-4842-3374-0 ISBN-13 (electronic): 978-1-4842-3375-7
https://doi.org/10.1007/978-1-4842-3375-7
Library of Congress Control Number: 2018935922
Copyright © 2018 by Derek J. Rouleau
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the
material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation,
broadcasting, reproduction on microfilms or in any other physical way, and transmission or information
storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now
known or hereafter developed.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with
every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an
editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the
trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not
identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the
material contained herein.
Managing Director, Apress Media LLC: Welmoed Spahr
Acquisitions Editor: Jonathan Gennick
Development Editor: Laura Berendson
Coordinating Editor: Jill Balzano
Cover designed by eStudioCalamar
Cover image designed by Freepik (www.freepik.com)
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-ny@springer-
sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member
(owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a
Delaware corporation.
For information on translations, please e-mail [email protected], or visit http://www.apress.com/
rights-permissions.
Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and
licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales
web page at http://www.apress.com/bulk-sales.
Any source code or other supplementary material referenced by the author in this book is available to
readers on GitHub via the book's product page, located at www.apress.com/9781484233740. For more
detailed information, please visit http://www.apress.com/source-code.
Printed on acid-free paper
To my best friend Chris and his father Paul Champagne.
If it wasn’t for you two, I may have never gotten
interested in computer science.
Table of Contents
About the Author �����������������������������������������������������������������������������������������������������ix
About the Technical Reviewer ���������������������������������������������������������������������������������xi
Acknowledgments �������������������������������������������������������������������������������������������������xiii
Introduction �������������������������������������������������������������������������������������������������������������xv
Chapter 1: Getting Started ����������������������������������������������������������������������������������������1
What Is .NET Core .........................................................................................................................1
Setting Up Your Application ...........................................................................................................2
Creating the Database and Tables (Entities) .................................................................................3
Creating the Database ...........................................................................................................19
Adding Data to a Table ................................................................................................................21
Retrieving Data from a Database and Displaying It ....................................................................31
Deleting Data from a Database ...................................................................................................33
Deleting a Single Record .......................................................................................................33
Deleting Multiple Records .....................................................................................................40
Updating Data in a Table .............................................................................................................42
Summary.....................................................................................................................................45
Chapter 2: Working with Multiple Tables ����������������������������������������������������������������49
Adding Data to a Table That Has Foreign Keys............................................................................50
Retrieving Data from Multiple Tables ..........................................................................................75
Updating Tables ...........................................................................................................................81
Updating Multiple Records ....................................................................................................91
Summary.....................................................................................................................................95
v
Table of ConTenTs
Chapter 3: Stepping Beyond the Basics ������������������������������������������������������������������97
Specifying Fields .........................................................................................................................98
Joins .........................................................................................................................................101
Left Outer Join .....................................................................................................................102
Right Outer Join ...................................................................................................................104
Cross Join ............................................................................................................................109
Inner Join: Method Syntax ...................................................................................................110
Grouping....................................................................................................................................112
Query Syntax .......................................................................................................................112
Method Syntax .....................................................................................................................114
Logical Operators ......................................................................................................................115
Logical OR ...........................................................................................................................119
Paging and Filtering Results .....................................................................................................121
Concurrency ..............................................................................................................................125
Summary...................................................................................................................................130
Chapter 4: Data Validation and POCOs ������������������������������������������������������������������133
Validation Using DataAnnotations .............................................................................................134
Using Recursion...................................................................................................................143
POCOs .......................................................................................................................................153
Helper Classes .....................................................................................................................156
Validating Using a Class ......................................................................................................168
Summary...................................................................................................................................177
Chapter 5: Stored Procedures, Table Design, and Modifications ��������������������������179
Stored Procedures ....................................................................................................................179
Calling a Stored Procedure with One Parameter .................................................................185
Inserting Into Multiple Tables ..............................................................................................189
Table and Entity Design .............................................................................................................192
Making Changes to the Table ....................................................................................................194
Summary...................................................................................................................................201
vi
Table of ConTenTs
Chapter 6: ASP�NET MVC and EF Core 2�0 �������������������������������������������������������������203
Setting Up the Application ........................................................................................................204
Database-First Development ....................................................................................................207
Creating Controllers and Views ...........................................................................................209
Page Navigation ...................................................................................................................214
The Home Page and Adding an Image.................................................................................216
Error Handling .....................................................................................................................218
Displaying Events by Location .............................................................................................224
Sorting Results ....................................................................................................................228
ASP.NET MVC Basics .................................................................................................................230
Strongly Typed Views ...........................................................................................................232
Why Use MVC? .....................................................................................................................234
Summary...................................................................................................................................235
Chapter 7: Finishing Our ASP�NET MVC �NET Core 2�0 Project ������������������������������237
Data Validation ..........................................................................................................................237
Using a Regular Expression to Validate a Property ..............................................................241
Validating the Rest of Our Models .......................................................................................243
Adding Column Sorting to Results ............................................................................................252
Adding Search Capabilities .......................................................................................................256
Adding Pagination: Version 1 ....................................................................................................262
Adding Paging: Version 2 ..........................................................................................................270
Grouping Results .......................................................................................................................275
Summary...................................................................................................................................287
Chapter 8: Wrap Up and Where to Go Next �����������������������������������������������������������289
Not Just C# Anymore ................................................................................................................289
Cross Platform ..........................................................................................................................295
Unit Testing ...............................................................................................................................297
More on LINQ ............................................................................................................................312
SQL Server Profiler ..............................................................................................................315
vii
Table of ConTenTs
Change Tracking........................................................................................................................316
Summary...................................................................................................................................321
Appendix A: Database Script for Chapter 6 ����������������������������������������������������������323
Database Diagrams ...................................................................................................................329
Index ���������������������������������������������������������������������������������������������������������������������333
viii
About the Author
Derek J. Rouleau is a Computer and Information Systems Manager for a small company
in the greater Portland, Maine area. He has been working with computers since the mid
1990s thanks to a friend’s father, who got him interested in hardware and the workings
of the operating system. Derek started professionally programming while working for
an educational publishing and software company in the Quality Assurance department.
He has been using Visual Basic since VB 6 and C# since 2010. Although he now manages
the department where he works, Derek still makes time each week to write code and
research new and exciting technologies. When he’s not working, he is involved with Off
Track Standardbreds, and he competes in Dressage.
ix
About the Technical Reviewer
Doug Holland is a Technical Evangelist at Microsoft. Before joining Microsoft in 2010,
he was awarded the Microsoft MVP (C#) and Intel Black Belt Developer awards. He has
presented sessions at the Microsoft BUILD conference and frequently speaks at other
events about Microsoft technologies, from cloud computing to mixed reality. He holds a
Master’s Degree in Software Engineering from Oxford University, as well as certifications
from Microsoft and Unity Technologies. You can follow him on Twitter @dougholland or
connect with him on LinkedIn at https://www.linkedin.com/in/dougholland/.
xi