edited: 08\/12\/2014<\/p>\n
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\u00a0 to deal with from the software side. As a software developer, my choice of database access tool is Microsoft’s Entity Framework<\/a> (EF) so I am motivated to see how EF can handle this.<\/p>\n Entity Framework 6 has a number of features to make it fairly straightforward to work with existing databases. In this article I’ll detail those steps that I needed to take on the EF side, in order\u00a0 to build a fully featured web application to work with the AdventureWorks database. I’ll actually use the AdventureWorksLT2012 <\/a>database, which is a cut-down version of the larger AdventureWorks OLTP database. I am using Microsoft’s ASP.NET MVC5 (MVC)<\/a> with the propriety Kendo UI<\/a> package for the UI\/presentation layer, which I cover in the next article.<\/a><\/p>\n At 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.<\/p>\n Entity Framework has a well-documented approach, called reverse engineering, to create the EF Entity Classes and 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:<\/p>\n I will now describe how I fixed these issues.<\/p>\n As I said earlier the standard templates enable ‘lazy loading’. I have been corrected in my understanding of lazy loading by some readers. The documentation states<\/a> 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.<\/p>\n 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 Loading<\/p>\n Now 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 Note: You can turn off lazy loading via the EF Configuration class too, but I prefer to remove the virtual keyword as it ensures that lazy loading is definitely off.<\/p>\n<\/div>\n<\/div>\n The generation of the EF classes and Once you have installed the 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.<\/p>\n One warning about using importing scaffolding – Visual Studio threw a nasty error message when \u00a0I first tried to import using the Note: There are two other methods to reverse engineer an existing database:<\/p>\n <\/i>EntityFramework Reverse POCO Code First Generator<\/a> by Simon Hughes. This is Visual Studio extension recommended by the EF Guru, Julia Lerman, in one of her MSDN magazine articles<\/a>. I haven’t tried it, but if Julia recommends it then it must be good.<\/p>\n Entity Framework Power Tools Beta 4<\/a> can also reverse engineer a database. Its quicker, only two clicks, but its less controllable. I don’t suggest you use this.<\/i><\/p>\n<\/div>\n<\/div>\n The standard definition for the You can see it marks the first as not database-generated, but it does not mark the second as an Identity key. This caused problems when I tried to create a new That confused me for a bit, as other two-key items had worked, such as 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 MetadataType attribute<\/a> 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.<\/p>\n<\/div>\n Being 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 The process requires me to add a partial class in another file (see later for more on this) and then add the 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.<\/p>\n Having sorted\u00a0 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.<\/p>\n In 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 intention revealing name<\/a>, like 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 classes<\/p>\n \u00a0The class is declared as 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\u00a0 to the customer class. Ignore for now the Note that you almost certainly will want to add to the In the AdventureWorks database there are two properties called 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 Then I added the code below to the The Some databases rely on SQL Stored Procedures<\/a> (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 good information here<\/a> on how to get EF to use SPs for Insert, Update and Delete operations.<\/p>\n 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\u00a0 from the software point of view\u00a0 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.<\/p>\n This 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:<\/p>\n 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\u00a0 ‘Programming Entity Framework: DbContext’<\/a> 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).<\/p>\n 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.<\/p>\n For SQL commands such as create, update and delete is less obvious, but I have used it in some cases. For these you use the SqlCommand method, see example from Microsoft below:<\/p>\n Neither of these example had parameters, but if you did need any parameters then One warning on When using EF to do any database updates using the . 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:<\/p>\n You can also use the same commands in a mixed SQL commands and EF commands. See this EF documentation<\/a> for an example of that.<\/p>\n<\/div>\n There 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.<\/p>\n 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.<\/p>\n 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\u00a0Retry logic for Azure<\/a>,\u00a0Handling transaction commit failures<\/a>,\u00a0SQL transaction control<\/a>,\u00a0improved sharing connections between SQL and EF<\/a>, plus a number of other things. Have a good look around the\u00a0EF documentation<\/a>\u00a0– there is a lot there.<\/p>\n 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.<\/p>\n My second article <\/a>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<\/p>\n I have now set up a live web site<\/a> \u00a0running the web application that prompted this article. It is fairly basic in appearance as I was concentrating on the features, not the style, but you might find it interesting. I also have made the source code available on GitHub<\/a> in case you might like to see how this application was built. <\/p>\n My open-source GenericServices library is now available on NuGet<\/a> with documentation on the GenericServices’ GitHub Wiki<\/a>.<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":" Pre-existing SQL databases, particularly if complex, can often pose problems for software developers who are creating a new application. The data may be in a format that makes it hard to access or update, and may include functions and procedures. Jon Smith looks at what tools there are in Microsoft's Entity Framework’s Data Access technology that can deal with these requirements.…<\/p>\n","protected":false},"author":101092,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4229,4168,4150,95509],"coauthors":[6823],"class_list":["post-1912","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-net-framework","tag-database","tag-sql","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101092"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1912"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1912\/revisions"}],"predecessor-version":[{"id":73782,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1912\/revisions\/73782"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1912"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Creating the Entity Framework Classes from the existing database<\/h2>\n
DbContext<\/code> from an existing database<\/a>. 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<\/code> with an OnModelCreating<\/code> method to set up the various relationships.<\/p>\nnamespace DataLayer.GeneratedEf\r\n{\r\n\u00a0\u00a0\u00a0 [Table(\"SalesLT.Customer\")]\r\n\u00a0\u00a0\u00a0 public partial class Customer\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public Customer()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CustomerAddresses = new HashSet<CustomerAddress>();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SalesOrderHeaders = new HashSet<SalesOrderHeader>();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public int CustomerID { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public bool NameStyle { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [StringLength(8)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string Title { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Required]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [StringLength(50)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string FirstName { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [StringLength(50)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string MiddleName { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/more properties left out to shorten the class...\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Now the relationships\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public virtual ICollection<CustomerAddress> CustomerAddresses { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public virtual ICollection<SalesOrderHeader> SalesOrderHeaders { get; set; }\r\n}\r\n\u00a0\r\n<\/pre>\n\n
SalesOrderDetail<\/code> has two keys: one is the SalesOrderHeaderID<\/code> and one is an identity, SalesOrderDetailID<\/code>. EF failed on a create and I needed to fix this. (See section 3 below)<\/li>\n<\/ol>\n1: Removing lazy loading by altering the scaffolding of the EF classes\/DbContext<\/h3>\n
DbContext<\/code>.<\/p>\n2: Altering the code that Reverse Engineering produces<\/h3>\n
DbContext <\/code>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 import the scaffolding<\/a> using NuGet, so I’m not going to repeat it.<\/p>\nEntityFramework.CodeTemplates<\/code> you will find two files called Content.cs.t4<\/code> and EntityType.cs.t4<\/code>, which control how the DbContext<\/code> 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<\/code> file on the declaration of the DbSet<>.<\/code><\/p>\n EntityFramework.CodeTemplates<\/code> scaffolding (see stackoverflow<\/a> entry). It took a bit of finding but it turns out if you have Entity Framework Power Tools Beta 4<\/a> 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.<\/p>\n3: Fixing a problem with how the two keys are defined in the SalesOrderDetail table<\/h3>\n
SalesOrderDetail<\/code> table key parts are as follows<\/p>\n[Table(\"SalesLT.SalesOrderDetail\")]\r\npublic partial class SalesOrderDetail\r\n{\r\n\u00a0\u00a0\u00a0 [Key]\r\n\u00a0\u00a0\u00a0 [Column(Order = 0)]\r\n\u00a0\u00a0\u00a0 [DatabaseGenerated(DatabaseGeneratedOption.None)]\r\n\u00a0\u00a0\u00a0 public int SalesOrderID { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 [Key]\r\n\u00a0\u00a0\u00a0 [Column(Order = 1)]\r\n\u00a0\u00a0\u00a0 public int SalesOrderDetailID { get; set; }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 \/\/other properties left out for clarity...\r\n}\r\n<\/pre>\n SalesOrderDetail<\/code> so that I could add a line item to an order. I got the SQL error:<\/p>\nCannot insert explicit value for identity column in table 'SalesOrderDetail' when IDENTITY_INSERT is set to OFF.<\/pre>\n
CustomerAddress<\/code>. I tried a few things but as it looked like an EF error I tried telling EF that the SaledOrderDetailID <\/code>was an Identity key by using the attribute …
\n [DatabaseGenerated(DatabaseGeneratedOption.Identity<\/code>)].
\n That fixed it!<\/p>\nAdding new DataAnnotations to EF Generated classes<\/h2>\n
DataAnnotations <\/code>to help the UI\/presentation layer such as marking properties with their datatype, e.g. [DataType(DataType.Date<\/code>)]. The process for doing this is given in the Example section of this link to<\/a> the MetadataType<\/code> attribute. I will show you my example of adding the missing Identity attribute.<\/p>\n[MetadataType(typeof(SalesOrderDetailMetaData))]<\/code> attribute to the property SaledOrderDetailID <\/code>in a new class, sometimes called a ‘buddy’ class . See below:<\/p>\n[MetadataType(typeof(SalesOrderDetailMetaData))]\r\npublic partial class SalesOrderDetail : IModifiedEntity\r\n{\r\n}\r\n\u00a0\r\npublic class SalesOrderDetailMetaData\r\n{\r\n\u00a0\u00a0\u00a0 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]\r\n\u00a0\u00a0\u00a0 public int SalesOrderDetailID { get; set; }\r\n}\r\n\u00a0\r\n<\/pre>\nWhat happens when the database changes?<\/h2>\n
\n
DbContext<\/code> so you use the same name when you re-import. That way it will recompile properly without major name changes.<\/li>\n DbContext<\/code> you must delete the previous DbContext<\/code> 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.<\/li>\nApp.Config<\/code> file again. I suggest you un-tick that otherwise you end up with lots of connection strings (minor point, but can be confusing).<\/li>\nAdding new properties or methods to the Entity classes<\/h2>\n
HasSalesOrder<\/code>.<\/p>\npublic partial <same class name>.<\/code><\/p>\n IModifiedEntity<\/code> interface (dealt with later in this article) and [Computed]<\/code> attribute, which I will cover in the next article.<\/a><\/p>\npublic partial class Customer : IModifiedEntity\r\n{\r\n\u00a0\u00a0\u00a0 [Computed]\r\n\u00a0\u00a0\u00a0 public string FullName { get { return Title + \" \" + FirstName + \" \" + LastName + \" \" + Suffix; } }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 \/\/\/ <summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ This is true if any sales orders. We use this to decide if a 'Customer' has actually bought anything\r\n\u00a0\u00a0\u00a0 \/\/\/ <\/summary>\r\n\u00a0\u00a0\u00a0 [Computed]\r\n\u00a0\u00a0\u00a0 public bool HasSalesOrders { get { return SalesOrderHeaders.Any(); } }\r\n}\r\n<\/pre>\n DbContext<\/code> 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…<\/p>\nDealing with properties best dealt with at the Data Layer<\/h2>\n
'ModifiedDate'<\/code> and ‘rowguid<\/code>‘. In the AdventureWorks Lite database these were not generated in the database. Therefore the software needs to update ModifiedDate<\/code> on create or update and set the rowguid on create.<\/p>\nSaveChanges()<\/code> method to handle the specific issues your database needs. In the case of AdventureWorks I adding an IModifiedEntity<\/code> interface to each partial class that has ModifiedDate<\/code> and rowguid <\/code>property.<\/p>\nAdventureWorksLt2012 DbContext<\/code> to provide the functionality required by this database.<\/p>\npublic partial class AdventureWorksLt2012 : IGenericServicesDbContext\r\n{\r\n\u00a0\u00a0\u00a0 \/\/\/ <summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ This has been overridden to handle ModifiedDate and rowguid\r\n\u00a0\u00a0\u00a0 \/\/\/ <\/summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ <returns><\/returns>\r\n\u00a0\u00a0\u00a0 public override int SaveChanges()\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HandleChangeTracking();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return base.SaveChanges();\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 \/\/\/ <summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ This handles going through all the entities that have\r\n\u00a0\u00a0\u00a0 \/\/\/ changed and seeing if we need to do anything.\r\n\u00a0\u00a0\u00a0 \/\/\/ <\/summary>\r\n\u00a0\u00a0\u00a0 private void HandleChangeTracking()\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var entity in ChangeTracker.Entries()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Where(e => e.State == EntityState.Added\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 || e.State == EntityState.Modified))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UpdateTrackedEntity(entity);\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 \/\/\/ <summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ Looks at everything that has changed and\r\n\u00a0\u00a0\u00a0 \/\/\/ applies any further action if required.\r\n\u00a0\u00a0\u00a0 \/\/\/ <\/summary>\r\n\u00a0\u00a0\u00a0 \/\/\/ <param id=\"entityEntry\"\"><\/param>\r\n\u00a0\u00a0\u00a0 \/\/\/ <returns><\/returns>\r\n\u00a0\u00a0\u00a0 private static void UpdateTrackedEntity(DbEntityEntry entityEntry)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var trackUpdateClass = entityEntry.Entity as IModifiedEntity;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (trackUpdateClass == null) return;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trackUpdateClass.ModifiedDate = DateTime.UtcNow;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (entityEntry.State == EntityState.Added)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trackUpdateClass.rowguid = Guid.NewGuid();\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n IModifiedEntity<\/code> interface is really simple:<\/p>\n\/\/This interface is added to all the database entities\r\n\/\/that have a modified date and rowGuid. Save Changes uses this\r\n\/\/ to find entities that need the date updating, or a new rowguid added\r\npublic interface IModifiedEntity\r\n{\r\n\u00a0\u00a0\u00a0 DateTime ModifiedDate { get; set; }\r\n\u00a0\u00a0\u00a0 Guid rowguid { get; set; }\r\n}\r\n\u00a0\r\n<\/pre>\nUsing SQL Store Procedures<\/h2>\n
Other things you could do<\/h2>\n
Using Direct SQL commands<\/h3>\n
var allUsers = db.Database.SqlQuery<SqlUserAndRoleRow>(\r\n@\"select mp.name as UserName, rp.name as RoleName, mp.type as UserType\r\nfrom sys.database_role_members drm\r\njoin sys.database_principals rp on (drm.role_principal_id = rp.principal_id)\r\njoin sys.database_principals mp on (drm.member_principal_id = mp.principal_id)\r\nORDER BY UserName\");\r\n<\/pre>\n
using (var context = new BloggingContext()) \r\n{ \r\n\u00a0\u00a0\u00a0 context.Database.SqlCommand( \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"UPDATE dbo.Blogs SET Name = 'Another Name' WHERE BlogId = 1\"); \r\n}\r\n\u00a0\r\n<\/pre>\nSqlQuery<\/code> and SqlCommand<\/code> methods can take parameters, which are checked to protect against a SQL injection attack. The Database.SqlQuery Method<\/a> documentation shows this.<\/p>\nSqlCommand<\/code>s. Once you have run a SqlCommand <\/code>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<\/code> 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 stackoverflow answer<\/a> here for more on this.<\/p>\n<\/div>\n\u00a0SQL Transaction control<\/h3>\n
SaveChanged()<\/code> 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.<\/p>\nusing (var dbContextTransaction = db.Database.BeginTransaction())\r\n{\r\n\u00a0\u00a0\u00a0 try\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var text in sqlCommands)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.Database.ExecuteSqlCommand(text);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbContextTransaction.Commit();\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 catch (Exception ex)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbContextTransaction.Rollback();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/report the error in some way\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n\u00a0\r\n<\/pre>\nConclusion<\/h2>\n