Entity Framework Code First Succinctly Ricardo Peres Copyright © 2014 by Syncfusion Inc. 2501 Aerial Center Parkway Suite 200 Morrisville, NC 27560 USA Contents Introduction ............................................................................................................................................... 12 Chapter 1 Setting Up ................................................................................................................................ 13 Before We Start ..................................................................................................................................... 13 Getting Entity Framework Code First from NuGet ................................................................................. 13 Getting Entity Framework Code First from CodePlex ............................................................................ 13 Configuring the Database ...................................................................................................................... 14 SQL Server .......................................................................................................................................... 14 SQL Server Compact Edition ............................................................................................................... 15 SQL Server 2012 Express LocalDB .................................................................................................... 16 Chapter 2 Domain Model ......................................................................................................................... 17 Scenario ................................................................................................................................................. 17 Core Concepts ....................................................................................................................................... 18 Contexts ............................................................................................................................................... 18 Entities ................................................................................................................................................. 19 Complex Types .................................................................................................................................... 21 Scalar Properties ................................................................................................................................. 22 Identity Properties ................................................................................................................................ 24 References ........................................................................................................................................... 24 Collections ........................................................................................................................................... 25 Mapping by Attributes ............................................................................................................................ 27 Overview .............................................................................................................................................. 27 Schema ................................................................................................................................................ 27 Primary Keys ........................................................................................................................................ 29 Navigation Properties .......................................................................................................................... 30 Computed Columns ............................................................................................................................. 30 Complex Types .................................................................................................................................... 31 External Metadata ................................................................................................................................ 32 Limitations ............................................................................................................................................ 33 Mapping by Code ................................................................................................................................... 33 Overview .............................................................................................................................................. 33 Schema ................................................................................................................................................ 33 Primary Keys ........................................................................................................................................ 34 Navigation Properties .......................................................................................................................... 34 Computed Columns ............................................................................................................................. 35 Complex Types .................................................................................................................................... 35 Identifier Strategies ................................................................................................................................ 36 Overview .............................................................................................................................................. 36 Identity ................................................................................................................................................. 36 Manual ................................................................................................................................................. 36 Inheritance Strategies ............................................................................................................................ 38 Conventions ........................................................................................................................................... 43 Obtaining the Model Definition ............................................................................................................... 44 Generating Code Automatically ............................................................................................................. 46 Chapter 3 Database .................................................................................................................................. 53 Configuring the Connection String ......................................................................................................... 53 Generating the Database ....................................................................................................................... 53 Explicit Creation ................................................................................................................................... 53 Database Initializers............................................................................................................................. 54 Generating Scripts ............................................................................................................................... 58 Migrations............................................................................................................................................... 59 Automatic Migrations ........................................................................................................................... 59 Versioned Migrations ........................................................................................................................... 62 Chapter 4 Getting Data from the Database ............................................................................................ 65 Overview ................................................................................................................................................ 65 By Id ....................................................................................................................................................... 65 LINQ ....................................................................................................................................................... 65 Entity SQL .............................................................................................................................................. 69 SQL ........................................................................................................................................................ 72 Lazy, Explicit, and Eager Loading ......................................................................................................... 74 Lazy Loading ........................................................................................................................................ 74 Explicit Loading .................................................................................................................................... 77 Eager Loading ...................................................................................................................................... 78 Local Data .............................................................................................................................................. 82 Chapter 5 Writing Data to the Database ................................................................................................ 84 Saving, Updating, and Deleting Entities ................................................................................................ 84 Saving Entities ..................................................................................................................................... 84 Updating Entities .................................................................................................................................. 85 Upserting Entities ................................................................................................................................. 85 Deleting Entities ................................................................................................................................... 85 Inspecting the Tracked Entities ............................................................................................................ 86 Cascading Deletes ................................................................................................................................. 87 Refreshing Entities ................................................................................................................................. 89 Concurrency Control .............................................................................................................................. 89 Overview .............................................................................................................................................. 89 First One Wins ..................................................................................................................................... 90 Last One Wins ..................................................................................................................................... 90 Applying Optimistic Concurrency ......................................................................................................... 91 Detached Entities ................................................................................................................................... 92 Validation ............................................................................................................................................... 93 Validation Attributes ............................................................................................................................. 94 Implementing Self Validation ............................................................................................................... 97 Wrapping Up ........................................................................................................................................ 97 Transactions .......................................................................................................................................... 98 Chapter 6 Spatial Data Types ............................................................................................................... 100 Overview .............................................................................................................................................. 100 Chapter 7 Handling Events ................................................................................................................... 102 Saving and Loading Events ................................................................................................................. 102 Chapter 8 Extending Entity Framework ............................................................................................... 107 Calling Database Functions ................................................................................................................. 107 Implementing LINQ Extension Methods .............................................................................................. 108 Chapter 9 Exposing Data to the World ................................................................................................ 110 Overview .............................................................................................................................................. 110 WCF Data Services ............................................................................................................................. 110 ASP.NET Web API .............................................................................................................................. 112 ASP.NET Dynamic Data ...................................................................................................................... 114 Chapter 10 Tracing and Profiling ......................................................................................................... 118 Getting the SQL for a Query ................................................................................................................ 118 MiniProfiler ........................................................................................................................................... 119 SQL Server Profiler .............................................................................................................................. 122 Chapter 11 Performance Optimizations ............................................................................................... 123 Filter Entities in the Database .............................................................................................................. 123 Do Not Track Entities Not Meant for Change ...................................................................................... 123 Disable Automatic Detection of Changes ............................................................................................ 124 Use Lazy, Explicit, or Eager Loading Where Appropriate ................................................................... 124 Use Projections .................................................................................................................................... 125 Disabling Validations Upon Saving ...................................................................................................... 125 Working with Disconnected Entities ..................................................................................................... 126 Do Not Use IDENTITY for Batch Inserts .............................................................................................. 126 Use SQL Where Appropriate ............................................................................................................... 127 Chapter 12 Common Pitfalls ................................................................................................................. 128 Overview .............................................................................................................................................. 128 Changes Are Not Sent to the Database Unless SaveChanges Is Called ........................................... 128 LINQ Queries over Unmapped Properties ........................................................................................... 128 Null Navigation Properties ................................................................................................................... 128 Validation Does Not Load References ................................................................................................. 128 Concrete Table Inheritance and Identity Keys ..................................................................................... 129 Cannot Return Complex Types from SQL Queries ............................................................................. 129 Cannot Have Non Nullable Columns in Single Table Inheritance ....................................................... 129 Deleting Detached Entities with Required References Doesn’t Work ................................................. 129 Attempting Lazy Loading of Navigation Properties in Detached Entities ............................................ 129 SELECT N + 1 ..................................................................................................................................... 129 Appendix A Working with Other Databases ........................................................................................ 130 Appendix B Additional References ...................................................................................................... 131 Introduction Object/Relational mappers (ORMs) exist to bridge a gap between object-oriented programming (OOP) and relational databases. At the expense of being less specific, ORMs abstract away database-specific technicalities and hide from you, the OOP developer, those scary SQL queries. Entity Framework Code First is the latest edition of Microsoft’s flagship data access technology. It sits on the “classic” Entity Framework, which has existed since 2009. Entity Framework already offered two development models:  Database first, which generated code from an existing database.  Model first, which defined a conceptual model from which both the database and the code were generated. Code First picks up where “classic” left off: starting by code and generating the database from it, which is known as a domain-driven design (DDD) approach. It also offers a much simpler and streamlined API, which has gained a great deal of well-deserved attention. Since Entity Framework was first included in Visual Studio 2008 and the .NET Framework 3.5 SP1, and certainly object/relational mapping existed long before that, then why is there all this hype around Entity Framework Code First (EFCF)? Well, it seems that EFCF is the new cool kid on the block for a number of reasons:  Easy to set up: you just pop up NuGet’s package manager and you’re done.  Simple to use: there are no XML schemas to master, no base classes to inherit from, no arcane interfaces to implement, and it has a clean, tidy API. You just focus on the actual domain model and its characteristics, and forget about the persistence details, which is pretty much what domain driven design (DDD) is about.  It sits on an API for database access that you can expect to see more support and improvement for by Microsoft.  Because it is not tied to the regular .NET framework releases, new versions come out much more often.  Microsoft hit the bull’s eye when it decided to release EFCF’s source code and to start accepting community requests and even pull requests: bugs are fixed more quickly, you can influence the features the product will have, and you have the chance to try out the latest improved functionality. For those coming from “classic” Entity Framework, this means that you have to code your own entities by hand. There is no fancy designer here. This actually gives you more control over how things are generated, and it is not a bad thing. You can make your own decision. Stick with me and let’s start exploring Entity Framework Code First. 12 Chapter 1 Setting Up Before We Start Before you start using EFCF, you need to have its assemblies deployed locally. The distribution model followed by Microsoft and a number of other companies does not depend on old school Windows installers, but instead relies on new technologies such as NuGet and Git. We’ll try to make sense of each of these options in a moment, but before we get to that, make sure you have Visual Studio 2012 installed (any edition including Visual Web Developer Express will work), as well as SQL Server 2008 (any edition including Express) or higher. On SQL Server, create a new database called Succinctly. Getting Entity Framework Code First from NuGet NuGet is to .NET package management what Entity Framework is to data access. In a nutshell, it allows Visual Studio projects to have dependencies on software packages—assemblies, source code files, PowerShell scripts, etc.—stored in remote repositories. EFCF comes in its own assembly, which is deployed out-of-band between regular .NET releases. In order to install it to an existing project, first run the Package Manager Console from the Tools > Library Package Manager and enter the following command. This is by far the preferred option for deploying Entity Framework Code First. Tip: This will only work with an existing project, not on an empty solution. Getting Entity Framework Code First from CodePlex The second option, for advanced users, is to clone the Entity Framework Code First repository on CodePlex, build the binaries yourself, and manually add a reference to the generated assembly. First things first, let’s start by cloning the Git repository using your preferred Git client. git clone https://git01.codeplex.com/entityframework.git 13 13 Next, build everything from the command line using the following two commands. build /t:RestorePackages /t:EnableSkipStrongNames build You can also fire up Visual Studio 2012 and open the EntityFramework.sln solution file. This way, you can do your own experimentations with the source code, compile the debug version of the assembly, run the unit tests, etc. Configuring the Database Entity Framework is database-agnostic, but the standard version only includes providers for Microsoft technologies. This means only SQL Server 2005+, SQL Server Compact Edition, and SQL Server Express LocalDB are supported. The examples in this book will work on any of these editions. Make sure you have one of them installed and you have the appropriate administrative permissions. Entity Framework decides on what connection to use by executing the following algorithm.  If a connection string is passed in the DbContext’s constructor, then it will try to use that connection string with the default connection factory.  If the parameter-less constructor is used, it will look for a connection string in the configuration file, where its name is the same as the context’s class.  If no connection string is passed and no connection string with an appropriate name is found in the connection string, it will try to connect to a SQL Server instance named SQLEXPRESS, and a database with the same name as the context class, including namespace. A connection factory is an implementation of IDbConnectionFactory that sits in a well-known location: Database.DefaultConnectionFactory. This instance can be explicitly set, and should be if a specific database engine requires it. This can be done either by code or by setting a value in the configuration file. <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework" / > </entityFramework> SQL Server For connecting to the SQL Server, no special action is required. The default Database.DefaultConnectionFactory is already an instance of SqlConnectionFactory. 14