Beginning Entity Framework Core 2.0

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

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 (201) 348-4505, 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

Derek J. Rouleau

East Baldwin, Maine, USA

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.

v

About the Author ����������������������������������������������������������������������������������������������������� ix

About the Technical Reviewer ��������������������������������������������������������������������������������� xi

Acknowledgments������������������������������������������������������������������������������������������������� xiii

Introduction�������������������������������������������������������������������������������������������������������������xv

Table of Contents

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

vi

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

Table of Contents

vii

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

Table of Contents

viii

Change Tracking................................................................................................. 316

Summary................................................................................................................................... 321

Appendix A: Database Script for Chapter 6 ���������������������������������������������������������� 323

Database Diagrams ................................................................................................................... 329

Index��������������������������������������������������������������������������������������������������������������������� 333

Table of Contents

ix

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.

xi

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/.

xiii

Acknowledgments

I would like to thank Jonathan Gennick and Jill Balzano for having the faith in me and

for helping me bring this book to you. If it weren’t for them and all the great people at

Apress, this would not have been possible.

I also need to thank my Uncle “Doc” and Don Rahmlow of TC2 Consulting Services for

reminding me that we always need to keep trying to learn new things and for pushing me

to keep improving myself. As we get along in our careers, we tend to stick with what we

know, but in this industry you need to keep up with the ever-changing environment, which

isn’t always easy.

xv

Introduction

We decided to try a different approach at writing a technical book, and that is to convey

the information through examples, rather than long paragraphs of text you are only

going to forget later. The hope is that this will give you a good foundation to start your

Entity Framework Core 2.0 journey and that, when you complete the book and start

working on your own projects, you’ll have a good understanding of the material.

Even though this is a book on Entity Framework Core 2.0, we are going to cover some

C# topics that you might not be familiar with, so I hope you find those parts helpful.

In the second part of this book, we cover some aspects of ASP.NET MVC Core. If you

are interested in that topic, Apress has some very good books on the subject. I highly

recommend that you look into them. Although you will have a working web application

by the end, it will behoove you to do some more reading on the subject.

Who This Book Is For

This book is for someone who has never used Entity Framework Core and is looking

for a new way to access databases. Although we focus on SQL Server in this book,

according to Microsoft, it is possible to connect to MySQL, PostgreSQL, and MyCAT as

well. (However, there is no Oracle support for EF Core yet, but hopefully there will be in

the future.) If you have not programmed in any language before, you may have a little

bit of trouble, as we don’t explain the basics of C# or ASP.NET. You may not understand

or might miss some key concepts. If you are coming over from Visual Basic, you will

probably do fine, although you need to remember to add those pesky semicolons at the

end of your code lines.

Although we do use quite a bit of LINQ in this book, it is not required that you have

any background in LINQ prior to running any of the examples. It would be wise to do

some follow-up reading on LINQ after you complete this book, but you should be able to

piece together most of what you need to do after completing these examples. If you get

stuck, an Internet search will give you a better idea of what you are looking at so you can

make a better decision as to what to use.

xvi

Requirements

When writing this book, I did everything in Visual Studio 2017 Preview and SQL Server

2014. The free version of SQL Server and Visual Studio works with all the examples in

this book. Since this is a beginner book, I thought it best to use the tools that someone

just starting might be using. You can use any version of SQL Server 2008 or newer with

Entity Framework Core 2.0; however, you will run into issues when you try to do the

paging examples if you have an older version of SQL Server, so try to use at least version

2014 if at all possible.

Although you do create a web application in the second section of this book, it is not

required that you have a web server. You need one only if you want to deploy the project

or one like it. Otherwise, you can run and test everything through Visual Studio.

Notes About NuGet Packages

At the time of the writing of this book, some of the packages that we use were still only

available in preview1-final or preview2-final. If you find that you are getting errors with

your application, it may be due to a mixture of the preview and the release versions. It’s

better to either use all preview or all release versions with your application, as that seems

to be the safest. When you use the preview packages, sometimes you will get warnings

in which it’s trying to find the best match. Normally those warnings won’t cause you any

problems when you run your code and are more of an annoyance than anything.

Before We Begin

One final note before we begin. I generally try to live my programming life by two of

my favorite quotes. The first is “never remember anything you can look up” which I’m

pretty sure is paraphrased from Albert Einstein. Now I’m not saying that you shouldn’t

remember how to use an int without having to look it up online or in a book. But for

those things that you hardly ever do, don’t take the time to commit them to memory.

The second is “frustration begins where knowledge ends”. This quote I know for sure is

from Clinton Anderson. This is by far the hardest to implement. If you are getting frustrated

Introduction

xvii

with something, take a step back and try to figure out why. If you are honest with

yourself, it’s probably because you have run out of knowledge on the subject. Try to

figure out where the problem is and which part you are missing. Then learn about it and

try again. By doing this, you will likely have more success than just plugging away and

blindly trying different things.

Introduction

1

© Derek J. Rouleau 2018

D. J. Rouleau, Beginning Entity Framework Core 2.0, https://doi.org/10.1007/978-1-4842-3375-7_1

CHAPTER 1

Getting Started

We are going to jump right into an example, as I think that is the best way to learn

something. As we cover new topics, we explain them as we work on them. This is better

than a general overview at the start of the chapter or section, because that won’t mean

much to you while you are reading it. I personally dislike it when books show you the

wrong way of doing something and then show you how to do it “correctly” after you just

spent five minutes typing in the wrong way, so I’m not going to do that to you. However,

I do explain why we are doing something and explain what would be wrong. Since this is

a “getting started” type of book, all the examples work as written, although they may not

be the best way of getting it done. As you get more comfortable with this technology and

as your skills grow, you’ll come up with your own way of doing things. I’m just here to

help you started down the path to greatness.

For those of you who are like me and skipped the Introduction, you should be using

the latest build of Visual Studio 2017 and at least .NET Framework 4.6.1. At the time of

this writing, the latest build of Visual Studio was 15.3.3 with the .NET Framework build

4.7.02046. These build numbers can be found in the Visual Studio About window.

What Is .NET Core

Let’s take a quick moment to cover something that some of you may be wondering—

what is the difference between .NET and .NET Core? First off, .NET Core is cross

platform, so if you want to run an application on Windows, Linux, or Mac, .NET Core is

your tool. Due to its compact nature, .NET Core also gives better performance. The other

nice thing is that you can always start with .NET Core and, if you find you need more

features, you can switch to the full version of the .NET Framework. This is especially true

if you are writing a service.