Creating Model Classes with the Entity Framework (C#). 12 minutes to read.In this articlebyIn this tutorial, you learn how to use ASP.NET MVC with the Microsoft Entity Framework.
You learn how to use the Entity Wizard to create an ADO.NET Entity Data Model. Over the course of this tutorial, we build a web application that illustrates how to select, insert, update, and delete database data by using the Entity Framework.The goal of this tutorial is to explain how you can create data access classes using the Microsoft Entity Framework when building an ASP.NET MVC application. This tutorial assumes no previous knowledge of the Microsoft Entity Framework. By the end of this tutorial, you'll understand how to use the Entity Framework to select, insert, update, and delete database records.The Microsoft Entity Framework is an Object Relational Mapping (O/RM) tool that enables you to generate a data access layer from a database automatically. The Entity Framework enables you to avoid the tedious work of building your data access classes by hand.In order to illustrate how you can use the Microsoft Entity Framework with ASP.NET MVC, we'll build a simple sample application.
We'll create a Movie Database application that enables you to display and edit movie database records.This tutorial assumes that you have Visual Studio 2008 or Visual Web Developer 2008 with Service Pack 1. You need Service Pack 1 in order to use the Entity Framework. You can download Visual Studio 2008 Service Pack 1 or Visual Web Developer with Service Pack 1 from the following address.
In many large-scale projects, software developers are often have to work with existing SQL Server databases with predefined tables and relationships. The problem can be that some predefined databases can have aspects that are awkward to deal with from the software side. As a software developer, my choice of database access tool is Microsoft’s (EF) so I am motivated to see how EF can handle this.Entity Framework 6 has a number of features to make it fairly straightforward to work with existing databases.
Creating an Entity Data Model. Here, we are going to create an Entity Data Model (EDM) for an existing database in database-first approach and understand the basic building blocks. Entity Framework uses EDM for all the database-related operations. Entity Data Model is a model that describes entities and the relationships between them.
In this article I’ll detail those steps that I needed to take on the EF side, in order to build a fully featured web application to work with the AdventureWorks database. I’ll actually use the database, which is a cut-down version of the larger AdventureWorks OLTP database. I am using Microsoft’s with the propriety package for the UI/presentation layer, which I cover inAt the end, I also mention some other techniques that I didn’t need for AdventureWorks, but I have needed on other databases. The aim is to show how you can use EF with pre-existing databases, including ones that need direct access to T-SQL commands and/or Stored Procedures. Creating the Entity Framework Classes from the existing databaseEntity Framework has a well-documented approach, called reverse engineering,.
This produces data classes with various Data Annotations to set some of the properties, such as string length and nullablity (see the example below built around the CustomerTable), plus a DbContext with an OnModelCreating method to set up the various relationships. This does a good job of building the classes.
Certainly it is very useful to have the Data Annotations because front-end systems like MVC use these for data validation during input. However I did have a couple of problems:. The default code generation template includes the `virtual` keyword on all of the relationships. This enabled, which I do not want.
(see section 1 below). The table SalesOrderDetail has two keys: one is the SalesOrderHeaderID and one is an identity, SalesOrderDetailID. EF failed on a create and I needed to fix this. (See section 3 below)I will now describe how I fixed these issues. 1: Removing lazy loading by altering the scaffolding of the EF classes/DbContext. As I said earlier the standard templates enable ‘lazy loading’.
I have been corrected in my understanding of lazy loading by some readers. The that ‘Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed’. The problem with this is it does not make for efficient SQL commands, as individual SQL SELECT commands are raised for each access to virtual relationships, which is not such as good idea for performance.For that reason I do not use Lazy Loading so I want to turn it off. However if this isn’t an issue for you then you can leave it in. Lazy Loading can make handling relationships easier for the software, although in my second article I will show you a method that specifically selects each data column it needs and therefore does not need Lazy LoadingNow you could hand-edit each generated class so as to remove the ‘virtual’, but what happens if, or rather when, the database changes? The problem is that you would then have to re-import the database and so lose all your edits, which you or your colleague might have forgotten about by then, and suddenly your whole web application slows down. No, the common rule with generated code is not to edit it.
In this case the answer is to change the code that is generated during the creation of the classes and DbContext. The generation of the EF classes and DbContext is done using some t4 templates, referred to as scaffolding. By default the reverse engineering of the database uses some internal scaffolding, but you can import the scaffolding and change it. There is a very clear explanation of how to using NuGet, so I’m not going to repeat it.Once you have installed the EntityFramework.CodeTemplates you will find two files called Content.cs.t4 and EntityType.cs.t4, which control how the DbContext and each entity class respectively are built. Even if you aren’t familiar with t4 (a great tool) then you can understand what it does – its a code generator and anything not surround by is standard text.
I found the word ‘virtual’ in the EntityType.cs.t4 and deleted it. I also removed the word ‘virtual’ from the Content.cs.t4 file on the declaration of the DbSet.You may want to alter the scaffolding more extensively, perhaps by adding a Key attribute on primary keys for some reason. All is possible, but you must dig into the.t4 code in more depth.One warning about using importing scaffolding – Visual Studio threw a nasty error message when I first tried to import using the EntityFramework.CodeTemplates scaffolding (see entry).
It took a bit of finding but it turns out if you have installed then they clash. If you have Entity Framework Power Tools installed then you need to disable it and restart Visual Studio before you can import/reverse engineer a database.
I hope that gets fixed as Entity Framework Power Tools is very useful. Cannot insert explicit value for identity column in table 'SalesOrderDetail' when IDENTITYINSERT is set to OFF.That confused me for a bit, as other two-key items had worked, such as CustomerAddress. I tried a few things but as it looked like an EF error I tried telling EF that the SaledOrderDetailID was an Identity key by using the attribute DatabaseGenerated(DatabaseGeneratedOption.Identity).That fixed it!The best solution would be to edited the scaffolding again to always add that attribute to identity keys. That needed a bit of work and the demo was two days away so in the meantime I added the needed attribute using the and a ‘buddy’ class.
This is a generally useful feature so I use this example to show you how to do this in the next section. Adding new DataAnnotations to EF Generated classesBeing able to add attributes to properties in already generated classes is a generally useful thing to do. I needed it to fix the key problem (see section 1 above), but you might want to add some DataAnnotations to help the UI/presentation layer such as marking properties with their datatype, e.g. DataType(DataType.Date). The process for doing this is given in the Example section of this the MetadataType attribute. I will show you my example of adding the missing Identity attribute.The process requires me to add a partial class in another file (see later for more on this) and then add the MetadataType(typeof(SalesOrderDetailMetaData)) attribute to the property SaledOrderDetailID in a new class, sometimes called a ‘buddy’ class.
The effect is to apply those attributes to the existing properties. That fixed my problem with EF creating new SalesOrderDetail properly and I was away. What happens when the database changes?Having sorted the scaffolding as discussed above then just repeat step 1, ‘Creating the Entity Framework Classes from the existing database’.
There are a few things you need to do before, during and after the re-import. You should remember/copy the name of the DbContext so you use the same name when you re-import. That way it will recompile properly without major name changes.
Because you are using the same name as the existing DbContext you must delete the previous DbContext otherwise the re-importing process will fails. If its easier you can delete all the generated files as they are replaced anyway.
That is why I suggest you put them in a separate directory with no other files added. When re-importing by default the process will add the connection string to your App.Config file again.
I suggest you un-tick that otherwise you end up with lots of connection strings (minor point, but can be confusing). If you use source control (I really recommend you do) then a quick compare of the files to check what has changed is worthwhile.Adding new properties or methods to the Entity classesIn my case I wanted to add some more properties or methods to the class? Clearly I can’t add properties that change the database – I would have to talk to the DBA to change the database definition and import the new database schema again. However in my case I wanted to add properties that accessed existing database properties to produce more useful output, or to have an, like HasSalesOrder.You can do this because the scaffolding produces ‘partial’ classes, which means I can have another file which adds to that class. To do this it must: have the same namespace as the generated classesThe class is declared as public partial.I recommend you put them in a different folder to the generated files.
That way they will not be overwritten by accident when you recreate the generated files (note: the namespace must be the original namespace, not that of the new folder). Below I give an example where I added to the customer class. Ignore for now the IModifiedEntity interface (dealt with later in this article) and Computed attribute, which I will cover in. Note that you almost certainly will want to add to the DbContext class (I did – see section 4 below). This is also defined as a partial class so you can use the same approach. Which leads me on to Dealing with properties best dealt with at the Data LayerIn the AdventureWorks database there are two properties called 'ModifiedDate' and ‘ rowguid‘. In the AdventureWorks Lite database these were not generated in the database.
Therefore the software needs to update ModifiedDate on create or update and set the rowguid on create.Many databases have properties like this and, if not handled by the database,they are best dealt with at Data/Infrastructure layer. With EF this can be done by providing a partial class and overriding the SaveChanges method to handle the specific issues your database needs. In the case of AdventureWorks I adding an IModifiedEntity interface to each partial class that has ModifiedDate and rowguid property.Then I added the code below to the AdventureWorksLt2012 DbContext to provide the functionality required by this database.
Using SQL Store ProceduresSome databases rely on (SPs) for insert, update and delete of rows in a table. AdventureWorksLT2012 did not, but if you need to that EF 6 has added a neat way of linking to stored procedures.
It’s not trivial, but you can find on how to get EF to use SPs for Insert, Update and Delete operations.Clearly if the database needs SPs for CUD (Create, Update and Delete) actions then you need to use them, and there are plenty of advantages in doing so. In the absence of stored procedures, it is easy from the software point of view to use EFs CUD actions and EFs CUD have some nice features. For instance, EF has an in-memory copy of the original values and uses this for working out what has changed. The benefit is that the EF updates are efficient – you update one property and only that cell in a row is updated.
The more subtle benefit is tracking changes and handling SQL security, i.e. If you use SQL column-level security (Grant/Deny) then if that property is unchanged we do not trigger a security breach. This is a bit of an esoteric feature, but I have used it and it works well.
Other things you could doThis is all I had to do to get EF to work with an existing database, but there are other things I have had to use in the past. Here is a quick run through of other items: Using Direct SQL commands. Sometimes it makes sense to bypass EF and use a SQL command, and EF has all the commands to allow you to do this.
The EF documentation has a page on this here which gives a reasonable overview, but I recommend Julia Lerman’s book ‘ which goes into this in more detail (note: this book is very useful but it covers an earlier version of EF so misses some of the latest commands like the use of SPs in Insert, Update and Delete).For certain types of reads SQL makes a lot of sense. For instance in my GenericSecurity library I need to read the current SQL security setup (see below). I think you will agree it makes a lot of sense to do this with a direct SQL read rather than defining multiple data classes just to build the command. Neither of these example had parameters, but if you did need any parameters then SqlQuery and SqlCommand methods can take parameters, which are checked to protect against a SQL injection attack. The documentation shows this.One warning on SqlCommands.
Once you have run a SqlCommand then EF’s view of the database, some of which is held in memory, is out of date. If you are going to close/dispose of the DbContext straight away then that isn’t a problem. However if the command is followed by other EF accesses, read or write, then you should use the EF ‘Reload’ command to get EF back in track. See my here for more on this.
SQL Transaction control. When using EF to do any database updates using the. SaveChanged function then all the changes are done in one transaction, i.e.
If one fails then none of the updates are committed. However if you are using raw SQL updates, or a combination of EF and SQL updates, you may well need these to be done in one transaction. Thankfully EF version 6 introduced commands to allow you to control transactions.I used these commands in my EF code to work with SQL security. I wanted to execute a set of SQL commands to set up SQL Security roles and grant/deny access, but if any one failed I wanted to roll back. The code to execute a sequence of sql commands and rollback if any single command fails is given below.
You can also use the same commands in a mixed SQL commands and EF commands. See this for an example of that. ConclusionThere were a few issues to sort out but all of them were fixable. Overall, getting EF to work with an existing database was fairly straightforward, once you know how. The problem I had with multiple keys (see section 1) was nasty, but now I, and you, know about it we can handle it in the future.I think the AdventureWorks Lite database is complex enough to be a challenge: with lots of relationships, composite primary keys, computed columns, nullable properties etc. Therefore getting EF to work with AdventureWorks is a good test of EFs capability to work with existing SQL databases.
While the AdventureWorks Lite database did not need any raw SQL queries or Stored Procedures other projects of mine have used these, and I have mentioned some of these features at the end of the article to complete the picture.In fact version 6 of EF added a significance amount of extra features and commands to make mixed EF/SQL access very possible. The more I dig into things the more goodies I find in EF 6. For instance EF 6 brought in, plus a number of other things. Have a good look around the – there is a lot there.So, no need to hold back on using Entity Framework on your next project that has to work with an existing SQL database. You can use it in a major role as I did, or now you have good connection sharing just use it for the simple CRUD cases that do not need heavy T-SQL methods.My carried on this theme by looking at the challenges of displaying and updating this data at the user interface end.
I talk about various methods to develop a good the user experience quickly while still keeping a reasonable database performance. Jon P Smith is a full-stack software developer and architect who focuses on Microsoft's web applications using Entity Framework (EF) ORM on the server-side, with various front-end JavaScript libraries.
Jon is especially interested in defining patterns and building libraries that improve the speed of development of web/database applications. As well as his articles on Simple-Talk Jon has a number of extra articles on own technical blog, and has produced a number of open-source. He is also the author of the book called, published by Manning. Lazy LoadingI too found the terminology here a bit confusing so I looked that up when I was writing the article. The link says that ‘Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed’.If you look at that document in that link you will see a definition of the three terms that EF uses, e.g. Eager, Lazy and Explicit loading.I have avoided Lazy Loading and it seems I didn’t quite understand it. However readers like you have put me right.
I still don’t want to use Lazy Loading because of performance issues so I use.Include statements if I want a relationship loaded (what they call Explicit loading).(edited ). Lazy Loading and other reverse engineer optionsLazy loading will not directly drag in things that you did not want. Lazy Loading and other reverse engineer optionsHi Cristian (name from you blog)Thank you for your comment as it showed a hole in my understanding. I had avoided Lazy Loading after some initial problems and therefore had not really looked into it properly. As you say it loads navigational properties ‘when they are read’ not when the main class is loaded. I have updated the article to reflect that and sorry for my error.As you say Lazy Loading isn’t that conducive to good SQL performance as it creates multiple SQL commands so I don’t want to use them.On the comment about the EF 6.1/reverse engineering I have to say the main reason I could write this article is because version 6.1 of EF has included a whole raft of features that make working with existing SQL databases so much easier.
I do however mention two other reverse engineering tools which work with pre-6.1 EF code, one of which you mention in your blog. However, for me, the ranges of new features I mention at the end of the article make EF 6.1 the preferred choice for working with databases that use SQL to the full.Edited:. RE: Great articleHi Anonymous.Thanks for your comment. As an active developer/architect I spend a lot of time trying to work things for documentation, so I know what works for me. I want to know ‘why’ as well as ‘how’ because often just knowing ‘how’ isn’t enough.My feeling is EF 6 has made a big difference to what you can do with existing databases, but you have to decide if it can work/help in your situation.A second follow on article coming out in a week’s time will look at the other part of the puzzle, i.e. Using EF in a real MVC5 web application.
I think the two together cover the use of EF from end-to-end. RE: T4 FilesHi RickyTad,Yes, as I explained in section 2: ‘Altering the code that Reverse Engineering produces’ you can download and then edit T4 files.
That section includes a link to an article from the Date Developer Team, but its mainly about how to download rather than how the T4 works.I wouldn’t say that the T4 code is very easy to follow and I haven’t found any documentation on it (I didn’t expect to). I think a bit of experimenting will be needed, but I expect you can achieve what you are looking to do. RE: T4 FilesHi Jon,Thank you for the reply.I will definitely have to make some 'try & error' experiments regarding the T4 files.The problem is that these T4 scripts are executed only when adding a new 'ADO.NET Entity Data Model' to the project and selecting 'Code First from Database', but the breakpoints set in the T4 files are not hit during code generation. And without Visual Studio debugging it is very difficult to understand what exactly happens in the T4 scripts.
Do you know any 'trick' to trigger execution and debug these T4 scripts in VisualStudio?. Auto-save Data base changes in Database first approachHi Ammarhassan48,From your question I assume you are using a ‘Model First’ approach, where you create the database using the EF Designer. Correct?If you are doing that then I don’t believe there is a way of changing the edmx files without running the designer or changing the xml file.However if you are changing your database a lot then you might like to think about changing to the Code First-Reverse Engineering approach I have listed in this article. See the process on the EF Documentation site:.