Tag Archives: entity framework

“Cool” New Courses for Entity Framework

donfrozen-rev-300x300We’ve joined forces with our Yeti instructor, Don Kiely, to create two new Entity Framework 6.1 courses for you. (I wonder how he can type when he’s that “Frozen?” I guess I’ll just have to “Let It Go.”)

No, Don doesn’t normally look this way in the winter. And no, he didn’t have a run in with Elsa. His friend Tracey Martinson was not too frozen to take this picture of Don after he went for a run when the temperature was -18°F (which is -27.8°C or 245 K). In between running, caring for his sled dogs, and never being asked “Do you Want to Build a Snowman?”, Don has created these exciting new Entity Framework courses:

Entity Framework 6.1: SQL Server Features – Now available
In this course you’ll learn about a few of Entity Framework’s “For the First Time in Forever” additions to support SQL Server features. You’ll start with a look at Entity Framework’s support for hierarchyID or, rather, its missing support. Then you’ll jump into one of the best new features in Entity Framework in a long time— enums—which you can use to protect the integrity of your data. Next you’ll explore Entity Framework’s support for spatial data, which covers location-aware applications and data. You’ll wrap up with a look at table-valued functions and their support in Entity Framework.

Entity Framework 6.1: Code-First Development – Coming 2/2/15
You will begin by learning how code-first works by default, which will probably work for most applications early in their development cycle. But when you’re ready to deploy the application to a production server, or need more flexibility (in a “Fixer Upper” kind of way), you’ll learn how Entity Framework creates a database. You’ll see how to create a code-first model and create a database from it, and see an application that makes use of it to maintain data in the database. You’ll also learn how to customize the database using data annotations, and the DBModelBuilder API which lets you write code instead of using data annotations. Lastly you’ll see how code migration is a newer feature of code first that goes beyond just deleting and recreating the database when the model changes.


Be sure to check out all of our Entity Framework courses including these two new additions. By the way, here is a picture of Don all thawed out. I doubt he thinks “Reindeer are Better than People,” because he may not have met one yet. Let’s hope “In Summer” up in Alaska, Don’s runs won’t have that frozen look.

My apologies to Disney for using Frozen songs as puns.

About the Author


Brian Ewoldt is the Project Manager for LearnNowOnline. Brian joined the team in 2008 after 13 years of working for the computer gaming industry as a producer/project manager. Brian is responsible for all production of courses published by LearnNowOnline. In his spare time, Brian enjoys being with his family, watching many forms of racing, racing online, and racing Go-Karts.


Entity Framework 6.1 Fundamentals

New from our instructor in the land of the midnight sun are new courses covering the fundamentals of Entity Framework 6.1. That instructor is Don Kiely…and between high adventure trips, skijoring, saving sled dogs, dodging moose, and running marathons, Don has found the time to work with us to create two excellent new courses.

According to Microsoft, Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

In our first new course, Entity Framework 6.1: Introduction, Don covers the basics from data access issues to the EF API and tools. In the second course, Entity Framework 6.1: Data Model, as the title suggests, Don digs in to the entity data model. These courses total over four hours of video training and are now available. Learn more

Entity Framework 6.1: Introduction

Watch trailer – Entity Framework 6.1: Introduction

Entity Framework 6.1: Data Model

Watch trailer – Entity Framework 6.1: Data Model







Watch for more EF courses from Don coming in the near future. In the meantime, I invite you to attend Don’s upcoming webinar on the Entity Framework Entity Data Model. He will be broadcasting live from Alaska beginning at 1pm CST on Wednesday, September 10th. (Don’t be surprised if you hear his dogs barking at moose in the background!) Register now

About the Author


Brian Ewoldt
 is the Project Manager for LearnNowOnline. Brian joined the team in 2008 after 13 years of working for the computer gaming industry as a producer/project manager. Brian is responsible for all production of courses published by LearnNowOnline. In his spare time, Brian enjoys being with his family, watching many forms of racing, racing online, and racing Go-Karts.

Domain Centric Modeling – Microsoft SQL Server 2012

When the first version of Entity Framework came out, it included support for database-first design, which would reverse engineer an existing database and build a model for you. You could build a model using the designer, but there was no tooling support for building a database from the model, so you had to perform that work yourself. The next version of Entity Framework, version 4, released with .NET 4 and Visual Studio 2010, added the necessary tooling support that made model-first design a viable option for using Entity Framework. Through these two releases, the database and model were the focus of early development work on a project. But if you follow domaincentered design, you probably found that Entity Framework really didn’t fit into the way you create applications.

All that changed with Version 4.1 of Entity Framework, released in the early part of 2011. This version added yet another option, code-first design. Codefirst design lets you create the domain model for your application by building Plain Old CLR Objects (POCOs), with no Entity Framework code whatsoever and without using any base classes. Then, through some clever features, you can create a context class that almost magically turns the objects into fully functional entity data objects. Even more amazingly, you don’t have to worry about creating the database at all. Assuming that you can live with the default conventions that you’ll learn about in this chapter, Entity Framework will create the database for you automatically when you first run the application.

The way that code-first works by default will probably work for most applications early in their development cycle. But when you’re ready to deploy the application to a production server, or need more flexibility in how Entity Framework creates a database, you can take advantage of data annotations on the model classes or use a fluent API to take control of the process. And even if you have an existing database that the application will use, you can still use code-first design and map the POCOs to the existing tables and fields.

The biggest benefit of code-first design is that it lets you focus on domaincentric modeling for application development, focusing on the data objects in your application rather than on the database or the Entity Data Model. In fact, your application won’t even have a model, and won’t have an .edmx file in the project, or any XML mapping code. Entity Framework takes care of inferring the model—including the conceptual, storage, and mapping models—at runtime. It really is a code-centric style of writing database applications.

Code-first design works so well and is implemented so nicely that we are confident that it will become the method of choice for new application development.

NOTE Code-first design is only available with Entity Framework 4.1 and later. This is an out-of-band release made available well after the release of Visual Studio 2010, .NET Framework 4, and Entity Framework 4. As a result you’ll need to download and install this release separately. See the course introduction for information.

Using Code-First

There are two different ways you can use code-first for development. The first, and what we expect will turn out to be the most common way, is to write data model .NET classes in your application first, then let code-first in Entity Framework create the database for you. This is the usage scenario that you will learn about in this chapter. Using code-first this way is the easiest, most agile way to jump-start database development, and lets you evolve and refactor the application in its early stages right through to production. Code-first can even update the database as you make changes to the model.

The other usage scenario is to again create the data model classes in your application, then map those data classes to an existing database. This will provide the information Entity Framework needs to provide data access between the application and the database, without the formality of an Entity Data Model. This might sound a little like database-first design, but the difference is that you create your own POCOs rather than having Entity Framework generate them from the Entity Data Model.

As you’ll learn later in the chapter, there are two ways in Entity Framework to map classes to objects in a database, no matter which usage scenario you use. One uses data annotations on the classes and properties of the data model classes, and the other uses the DbModelBuilder API to control the mapping in code.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

ObjectContext’s SavingChanges Event

ObjectContext’s SavingChanges event lets you validate or change data before Entity Framework sends it to the database. Entity Framework raises this event immediately before it creates the SQL INSERT, UPDATE, and DELETE statements that will persist the changes to the database.

One of the issues with using Entity Framework with SQL Server is that the range of DateTime values in .NET is different than those of the DateTime type in SQL Server. So in the case where you have a non-nullable DateTime field in a table, you have to assign a value to the corresponding property in an entity. This is exactly the case with the Modified property, associated with the ModifiedDate field in all of the tables of the AdventureWorksLT database. If you create a new instance of any entity and don’t explicitly set a valid value for the Modified property, .NET sets its minimum value, which doesn’t work for a SQL Server DateTime type. (It would, however, work with SQL Server 2008 and later’s DateTime2 type, but AdventureWorksLT doesn’t use this type for its date/time fields.) So you either need to explicitly set the Modified property to DateTime.Now when you create or update any entity, you’ll get a rather cryptic exception about some unnamed DateTime type being out of range.

NOTE: It is interesting that while the ModifiedDate fields in the database have a default value of GETDATE()—the T-SQL equivalent of DateTime. Now—there is nothing that updates the value when a record is changed. So you’ll need to supply a value for the Modified property when creating a new instance of an entity to avoid the datetime overflow problem, and when modifying an entity so that the field reflects the last time any data in the record changed.

This is a perfect use of the SavingChanges event. The one trick is that the view entities—CategoryList, ProductAndDescription, and ProductModelCatalogDescription—don’t have a Modified property. So the code has to be selective about which entities it applies to. But SavingChanges makes this easy through how it requires you to get access to the set of inserted, updated, or deleted entities. You have to access the entities with the ObjectContext’s GetObjectStateEntries method. The method takes one or more EntityState enumerations OR’d together, and returns a collection of entities with the selected state. You can select for Added, Deleted, Modified, and Unchanged states, but in this case you’re only interested in the Added and Modified states. There is no reason to update Modified for an entity you are deleting or that remains unchanged.

modify entities method

TIP: There is one other EntityState enumeration value, Detached. This state means that ObjectContext isn’t managing state for that entity. This value is not relevant in the SavingChanges event because there won’t be any work to do for detached entities.

The AWLT project in AWLT.sln has a Context.cs code file with a partial AWLTEntities class to customize the context object. The class implements the following AWLTEntities_SavingChanges method. The code uses the ObjectStateManager property of ObjectContext to get a reference to the ObjectStateManager for the context. It then uses the GetObjectStateEntries method, with the Added and Modified EntityState enumeration values, to populate the entities object with a collection of modified entities. Then the code loops through the collection, and uses reflection to update the value of the Modified property of each entity. The update code is wrapped in a try block and a do-nothing catch just in case an entity slips through that doesn’t have a Modified property.

You also have to wire up the SavingChanges event handler, and the OnContextCreated partial method is the perfect place to do that. The following code in the partial AWLTEntities class takes care of this task in the code.

The ModifyEntries method in Program.cs puts the SavingChanges event handler to use. It executes a query to retrieve all customers named Ann, and updates each one with a Jr. suffix. Then it saves the changes to the database, and turns around and resets the suffixes to null (which is what they all were originally). It then refreshes the collection of customers, just to make sure nothing was cached in memory, and writes out each customer, including the new value of the Modified property. Figure 1 shows the result of running the application.


Figure 1. The result of running the ModifyEntities method.


It’s Cleanup Time, Entity Data Model Style

As good and useful as a model generated using database-first design can be—it is highly dependent on the quality of the underlying database design—you’ll almost always need to do at least small tweaks to the resulting Entity Data Model. There are some things that Entity Framework just can’t handle intelligently, such as naming conflicts, vagaries in English pluralization, and various other issues that make the model usable but less than ideal for development work. Similarly, there are tweaks you can make that make the data entities easier to use, such as setting intelligent default values for properties when that makes sense. And many of these techniques apply to creating a model from scratch using model-first design, so you’re likely to put them to use no matter how the model comes into existence.

This article focuses on cleaning up an Entity Data Model once it’s created.

One of the very first things you should do after creating a model is to clean up the names of objects and properties in the model, making sure that they make sense for applications that use the model. Just because a name made sense to the database designer doesn’t mean that it will make sense to application developers. Make sure that the terminology is correct, consistent, and valid. Good names can make a huge difference to the productivity of application developers, particularly when they don’t constantly have to stop and think about which inconsistent name is used in the part of the database they are working with at the time.

One big reason you’ll have to rename things in the model is if the person who designed the database used outdated or misguided naming standards. For example, the figure below shows just one table from a very large database that we’ve encountered in our consulting work. In fairness, the original database was created back in the days of SQL Server 2000 before schemas were available as a kind of namespace container. So all the tables have a “tbl” prefix on the name, followed by the name of the functional area (“Subscript” here for the subscription area), and the table name (“Member”). Complicating things further, some of the tables have a three-character subscript on the name (“smi” in the figure), none of which any current employees of the company has any idea the meaning of.

Naming standards implemented in a production database.

The fields in all the tables follow a similar pattern, with a data type prefix, a functional area name (no one knows why the table in the above figure uses both “Member” and “Subscript” in various field names), and finally the field name. Unfortunately, the data type prefixes are unreliable because over the years it has been necessary to change data types here and there—which isn’t necessarily a breaking change—without changing the field name—which is a breaking change.

The nightmare of this database design has persisted for years because the company has a large, complex Web site built on it, as well as various support applications and tools. Making all the required changes to the applications and stored procedures would be a nightmare if any field names changed. As you can imagine, we had to spend a ton of time cleaning up the names in the Entity Data Model we created for the database. It was time well spent, however, because we were able to simplify the names, clean up inconsistencies, and change names that made no sense at all. All without changing the underlying database design.

Fortunately, all of the AdventureWorks databases use a much saner naming convention, so there isn’t nearly as much work to do. But there are still things to refine that will make the model much easier to use in an application. And with Entity Framework version 4 and later, the Entity Data Model Wizard has already made entity and entity set names singular and plural by default, which saves a lot of the work required in earlier versions. It isn’t perfect, but it is good enough by far and away most of the time.

You can fix database object misspellings in the model as well. Sometimes you can’t change the misspellings because of application dependencies, but you don’t have to perpetuate the problem in your new model and applications.


Updating Existing Entities


Querying data is a useful benefit of using Entity Framework, but you’ll often need to create, modify, and delete entity data as well. As with most of its features, Entity Framework provides rich support for changing data and has lots of options. This article discusses updating existing entities.

When you query the model and retrieve one or more entities then modify them, you can simply call SaveChanges to persist the changes. The following code retrieves all customers with the first name Ann and selects the first one, then writes the entity’s current state to the console window. Then the code changes a couple of property values, and again writes the state. Then it calls SaveChanges and writes the state yet again, then confirms that it updated the entity.

Figure 1 shows the results in the console window. Notice that immediately after materializing the Customer object, its state is Unchanged. This is as you’d expect, since no entity data has changed. After making changes but before saving them, the state is Modified. And after saving the changes, the state is once again Unchanged, because the context updated the entity’s original values to the new values to reflect the values now stored in the database.


Figure 1. The results of changing a single entity.

If you run SQL Profiler and examine the UPDATE statement that Entity Framework generated to persist the changes, you’ll find the following statement. Notice that Entity Framework is passing only the two updated fields, not the entire set of entity data. The context keeps track of which
properties changed, so is able to create more efficient SQL for the operation.

An interesting experiment is to update the FirstName property to Ann instead of Anne; in other words to set the value of the property to its current value. Change the code for that statement to the following and comment out the line that updates the ModifiedDate property. Then run the application again. And again. And once more. Notice that each time you get the same Unchanged/Modified/Unchanged states that you saw in Figure 1. Entity Framework sees that you assign a value to the property and changes the state to Modified without comparing the new value to the old one.

If you use SQL Profiler to look at the SQL statement generated with this new version of the code, you’ll find that it is the following statement every time. The UPDATE statement sets the FirstName field to ‘Ann’ even though that is already its value.

You can also update related data using the navigation properties of each entity, as long as those related objects are materialized in the query. Usually the easiest way to do that is to use the Include method for the related entities to make sure their data is available in memory for modification.

Entity Data Model in the Raw

Screen Shot 2012-09-06 at 10.33.11 AM


The Entity Data Model designer in Visual Studio displays a graphical view only of the conceptual model, which Entity Framework uses to provide entity data objects to an application. The designer exposes most, but not all, of the features of the conceptual model, and nothing at all about the storage and mapping models. Ultimately, the designer is just a pretty face on the underlying XML that actually defines the three models. This makes having at least a basic understanding of the structure of the XML in the .edmx file important for learning how Entity Framework works and how to make effective use of it. And as you attempt to use Entity Framework in more advanced ways, you’ll sometimes find that your only option is to manually change the XML to take advantage of features not supported by the designer.

You can view the XML in the .edmx file using any text editor, but usually it is easiest to work with the XML data in a Visual Studio XML code editor window. This has the advantage of color-coding the XML, performing checks of well-formedness, and providing IntelliSense if you modify the XML by hand.

The only problem is that you can’t have the file open both as XML and in the designer at the same time, since this could lead to file corruption if you were to modify and save the model both in the designer and XML editors. One way around this problem is to open the file as XML in another instance of Visual Studio. This makes it convenient to use both views of the model simultaneously, but you’ll want to be careful not to do anything to corrupt the .edmx file. To be safe, don’t save any changes in either instance of Visual Studio.

WARNING! Seriously, be very careful if you use this technique with two instances of Visual Studio. During normal application development using Entity Framework, you’ll do fine with the model open in a single instance of Visual Studio using either the designer or XML editor. It is really only when you are exploring Entity Framework that you’ll probably want to have both views open so that you can go back and forth.

The three models each have an XML language that determines the structure of the XML’s elements and attributes:

  • The conceptual model uses Conceptual Schema Definition Language (CSDL).
  • The storage model uses Store Schema Definition Language (SSDL).
  • The mapping model uses Mapping Specification Language (MSL).

Each of the three language specifications (CSDL, SSDL, and MSL) define the schema of the XML used for each model. Visual Studio includes XML schema files, which you can find in the following folders in a default installation of Visual Studio, to provide IntelliSense when you edit any of these model files and compiler errors if any of them have any structural problems.

  • C:Program Files (x86)Microsoft Visual Studio 10.0XmlSchemas in a 64-bit installation of Windows
  • C:Program FilesMicrosoft Visual Studio 10.0XmlSchemas in a 32-bit installation of Windows

The three files are:

  • System.Data.Resources.CSDLSchema_2.xsd for the conceptual model
  • System.Data.Resources.SSDLSchema_2.xsd for the storage model
  • System.Data.Resources.CSMSL_2.xsd for the mapping model

NOTE: You will probably have other versions of these XSD files, with either a 1 instead of a 2 in the file name or no number at all. These are the XML schema files for earlier versions of the Entity Framework.

Learn More!

The Entity Data Model Designer



Entity Framework’s Entity Data Model is the key link between the entity data objects in your application and the backend data store where data resides. It provides all the pieces that Entity Framework needs to provide your application with a conceptual model that the application uses, a storage model of the data store schema, and mappings between the two, along with support for relationships between the various entities in the model. Entity Framework uses the model to generate .NET entity classes and APIs that provide powerful data access features to an application.

At design time, you’ll most often work with the Entity Data Model in the graphical designer in Visual Studio, which provides a great way to see, understand, and modify the model. But all of the details of the model, including how it appears in the designer, are stored in a .edmx XML file.

The Entity Framework takes care of all the work of connecting to the data store, generating commands and executing them, providing entity data objects to the application, and processing changes to the data.

In this blog, you’ll learn about how the Entity Data Model Designer works, so that you can make effective use of it in applications.

Entity Data Model Designer

Your model will open in the Entity Data Model designer, shown in the figure below, once the Entity Data Model Wizard finishes its work. The wizard creates an entity for each table and view you selected in the database and association lines that reflect the table relationships defined in the database. Each entity has properties that reflect the corresponding table’s fields, and entities with associations that have navigation properties. Notice how all the entity names are singular, and the navigation properties are either singular or plural, reflecting whether they reference a single entity or a collection, which in turn reflects the multiplicity of the relationship. For example, a Customer can have multiple addresses so it has a CustomerAddresses navigation property to the CustomerAddress entity. But a customer address can have only a single associated customer, so the CustomerAddress entity has a Customer property. The model implemented this singular/plural naming scheme because you left the Pluralize or singularize generated object names option checked in the wizard.

Each entity has scalar properties—listed under the header Properties on each entity in the designer—that have values contained within the entity. The Customer entity includes CustomerID, Title, FirstName, and LastName scalar properties, and more, each of which will contain the current value of the corresponding field in the Customer table from the database when the object is materialized at runtime.

Entities that have associations with other entities also have one or more navigation properties. These properties are references to the related entities. For example, the Customer entity can have one or more addresses and sales orders, so the entity has CustomerAddresses and SalesOrderHeaders navigation properties. These properties let you navigate from a Customer to its addresses or orders without having to write a join in a LINQ expression.

The lines that connect related entities are associations, which represent the relationships between the tables in the database, and define the relationship between the associated entities. Each end of an association is described by its multiplicity, which is the number of entities that can be on that end. (Multiplicity is often referred to as cardinality in relational database theory, but Entity Framework more often uses the term multiplicity.) There are three options for the multiplicity of each end of an association, with the symbol used in the designer:

  • One: 1
  • Zero or one: 0..1
  • Many: *

WARNING! The location of the end points of an association line in the designer have no bearing on the fields involved in the relationship between the two entities. If you look back at the figure above, the association between the Customer and SalesOrderHeader entities has nothing to do with the Customer’s FirstName field, even though that end of the line is adjacent to FirstName.

You can then combine the multiplicity of the two ends of an association to describe the relationship, such as:

  • 1:* means “one to many.” For example, a single customer can have many orders.
  • *:* means “many to many.” An example of this relationship would be customers to addresses (although this is not the type of relationship in the AdventureWorksLT database). A customer can have many addresses (home, school, work, mailing), and each address can be used by multiple people, such as several family members living at the same home address.
  • 0..1:* means “zero or one to many.” An example of this relationship is the association between the ProductCategory and Product entities in AdventureWorksLT. A product category can have many products, and a product can optionally have a category assigned to it.

NOTE: The CustomerAddress entity, shown back in the figure above, is a many-to-many table in the AdventureWorksLT database. If a many-to-many table in the database contains only the foreign keys to the related tables, the Entity Data Model Wizard simply creates a many-to-many association between the two tables, and doesn’t include an entity for the many-to-many table. But because CustomerAddress includes other fields—AddressType, rowquid, and ModifiedDate—that describe the type of customer address, the model includes it as a separate entity so that you can access those additional properties in your application.


Top-Down Design or Bottom-up Design, That is the Question



When it comes to brand new data applications, there are two broad ways to approach building the application: design the object model first and the database later, or build the database first and the object model later. The first option is often referred to as top-down design, because you’re building application components first that sit on top of other layers including the database. The second option is bottom-up design because the underlying foundation of the application, the database, comes first. Top-down design places the business rules and architecture of the application as the highest value, and bottom-up design puts the emphasis on strong data organization. Neither methodology is necessarily better than the other: each one fits some development teams better than the other, and even some applications better than others. A project dominated by DBAs is likely to favor a bottom-up design methodology, while one dominated by developers is somewhat more likely to favor a top-down design, depending on how comfortable they are with data design. If you are building a new application that will use an existing production database, you’re inherently taking the bottom-up approach because the database already exists.
Entity Framework supports both methodologies, although not at the same time in an application. It provides three ways to build an application, one that supports bottom-first design and two that support top-first design.

  • Database-first design, available with the initial release of Entity Framework. This is the technique you used when you created the AdventureWorksLibrary project earlier in this chapter, based on objects in the existing AdventureWorksLT database. The Entity Data Model Wizard obtained the database schema and created the entity data objects for the tables you selected. The existing database schema shapes the entity objects using this technique, although you can modify the resulting objects extensively.
  • Model-first design, introduced in version 4 of Entity Framework. If the database doesn’t yet exist, you can create an Entity Data Model using the designer in Visual Studio from scratch, or you can write the XML for the .edmx file directly, if you’re so inclined. When you’re ready, you can have Entity Framework create a new database that corresponds to the model you’ve designed, shaping the data and storage to the model. If you later make changes to the model, the designer in Visual Studio supports recreating the database with the changes.
  • Code-first design, sometimes called code-only design. This is a new option released with version 4.1 of the Entity Framework. Here you write Plain Old CLR Objects (POCOs), which are regular .NET class objects, and Entity Framework will create the data store from these objects. Code-first works with an implied model at runtime, so there is no need for an .edmx model file. This technique is the purest form of top-down design, because you design your POCOs with no foresight about how the data will be persisted in a data store.