For full documentation please visit: http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application
Repository Pattern with C# and Entity Framework
X.PagedList This is fork of Troy’s project PagedList (https://github.com/troygoode/PagedList). The main different is that X.PagedList is portable assembly. It means, that you can use it not only in Web projects, but in Winforms, Window Phone, Silverlight and etc. projects. https://github.com/kpi-ua/X.PagedList#xpagedlist-
Implement paging in asp net mvc
sample ILogger interface
You write method overrides that are automatically called when query is about to be executed. In these methods you can examine or log the query that is being sent to the database, and you can change the query before it’s sent to the database or return something to Entity Framework yourself without even passing the query to the database.
These lines of code are what causes your interceptor code to be run when Entity Framework sends queries to the database. Notice that because you created separate interceptor classes for transient error simulation and logging, you can independently enable and disable them.
Another option is to put this code in the DbConfiguration class that you created earlier to configure the execution policy.
Wherever you put this code, be careful not to execute DbInterception.Add for the same interceptor more than once, or you’ll get additional interceptor instances. For example, if you add the logging interceptor twice, you’ll see two logs for every SQL query.
Entity Framework Code First Approach
Entity Framework Code First End To End
If you deploy a database by running migrations automatically and you are deploying to a web site that runs on multiple servers, you could get multiple servers trying to run migrations at the same time. Migrations are atomic, so if two servers try to run the same migration, one will succeed and the other will fail (assuming the operations can’t be done twice). In that scenario if you want to avoid those issues, you can call migrations manually and set up your own code so that it only happens once.
By using data annotation attributes, you can make one code change that will fix the display format in every view that shows the data. DataType attributes do not provide any validation.
You can also use attributes to control how your classes and properties are mapped to the database.
It means we can use DataAnnotations for both client-side formatting/validation and for database formatting, validation and mapping.
The DatabaseGenerated attribute with the None parameter on the CourseID property specifies that primary key values are provided by the user rather than generated by the database.
Column mapping is generally not required, because the Entity Framework usually chooses the appropriate SQL Server data type based on the CLR type that you define for the property. The CLR decimal type maps to a SQL Server decimal type. But in this case you know that the column will be holding currency amounts, and the money data type is more appropriate for that.
By convention, the Entity Framework enables cascade delete for non-nullable foreign keys and for many-to-many relationships. This can result in circular cascade delete rules, which will cause an exception when you try to add a migration.
Example on foreign key constraint and delete cascade
Many to many relationship in entity framework code first
You can use the fluent API to specify most of the formatting, validation, and mapping rules that you can do by using attributes.
Entity Framework Relationships Fluent API
First look at the Fluent API
Lazy loading in LINQ to SQL
Eager loading in LINQ to SQL
Difference between eager loading and lazy loading
Eager loading often offers the best performance, because a single query sent to the database is typically more efficient than separate queries for each entity retrieved.
On the other hand, in some scenarios lazy loading is more efficient. Eager loading might cause a very complex join to be generated, which SQL Server can’t process efficiently.
Lazy loading might perform better because eager loading would retrieve more data than you need. If performance is critical, it’s best to test performance both ways in order to make the best choice.
One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects.
Here are some other ways to disable lazy loading:
- For specific navigation properties, omit the
virtualkeyword when you declare the property.
- For all navigation properties, set
false, put the following code in the constructor of your context class:
The code specifies eager loading for the Instructor.OfficeAssignment and the Instructor.Courses navigation property.
Eager loading is better than lazy loading only if the page is displayed more often with a course selected than without.
Inserting Updating and Deleting entities in Entity Framework
Normally the scaffolder doesn’t scaffold a primary key because the key value is generated by the database and can’t be changed and isn’t a meaningful value to be displayed to users. For Course entities the scaffolder does include an text box for the
CourseID field because it understands that the
DatabaseGeneratedOption.None attribute means the user should be able enter the primary key value.
Stored Procedures in Entity Framework
Using stored procedures with entity framework code first approach
ASP.NET MVC5 Async Queries
A web server has a limited number of threads available, and in high load situations all of the available threads might be in use. When that happens, the server can’t process new requests until the threads are freed up. With synchronous code, many threads may be tied up while they aren’t actually doing any work because they’re waiting for I/O to complete. With asynchronous code, when a process is waiting for I/O to complete, its thread is freed up for the server to use for processing other requests. As a result, asynchronous code enables server resources to be use more efficiently, and the server is enabled to handle more traffic without delays.
The following highlights show what was added to the synchronous code for the
Index method to make it asynchronous:
Some things to be aware of when you are using asynchronous programming with the Entity Framework:
- The async code is not thread safe. In in other words, don’t try to do multiple operations in parallel using the same context instance. For example do not update bank account balance of an user in parallel. Because context is not thread safe, you may be updating bank account with different value than you are intending.
- If you want to take advantage of the performance benefits of async code, make sure that any library packages that you’re using (such as for paging), also use async if they call any Entity Framework methods that cause queries to be sent to the database.
Use stored procedures for inserting, updating, and deleting
This code instructs Entity Framework to use stored procedures for insert, update, and delete operations on the Department entity.
More details for implementation visit: https://msdn.microsoft.com/en-us/data/dn468673 or check videos above.
Concurrency Check in Entity Framework
A concurrency conflict occurs when one user displays an entity’s data in order to edit it, and then another user updates the same entity’s data before the first user’s change is written to the database. If you don’t enable the detection of such conflicts, whoever updates the database last overwrites the other user’s changes. In many applications, this risk is acceptable: if there are few users, or few updates, or if isn’t really critical if some changes are overwritten, the cost of programming for concurrency might outweigh the benefit. In that case, you don’t have to configure the application to handle concurrency conflicts.
If your application does need to prevent accidental data loss in concurrency scenarios, one way to do that is to use database locks. This is called pessimistic concurrency. For example, before you read a row from a database, you request a lock for read-only or for update access. If you lock a row for update access, no other users are allowed to lock the row either for read-only or update access, because they would get a copy of data that’s in the process of being changed. If you lock a row for read-only access, others can also lock it for read-only access but not for update.
Difference between optimistic and pessimistic concurrency control
Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases. For these reasons, not all database management systems support pessimistic concurrency. The Entity Framework provides no built-in support for it.
The alternative to pessimistic concurrency is optimistic concurrency. Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. For example,
John runs the Departments Edit page, changes the Budget amount for the English department from $350,000.00 to $0.00.
Before John clicks Save, Jane runs the same page and changes the Start Date field from 9/1/2007 to 8/8/2013.
John clicks Save first and sees his change when the browser returns to the Index page, then Jane clicks Save. What happens next is determined by how you handle concurrency conflicts. Some of the options include the following:
- You can keep track of which property a user has modified and update only the corresponding columns in the database. In the example scenario, no data would be lost, because different properties were updated by the two users. The next time someone browses the English department, they’ll see both John’s and Jane’s changes — a start date of 8/8/2013 and a budget of Zero dollars.
You can let Jane’s change overwrite John’s change. The next time someone browses the English department, they’ll see 8/8/2013 and the restored $350,000.00 value. This is called a Client Wins or Last in Wins scenario. (All values from the client take precedence over what’s in the data store.) As noted in the introduction to this section, if you don’t do any coding for concurrency handling, this will happen automatically.
- You can prevent Jane’s change from being updated in the database. Typically, you would display an error message, show her the current state of the data, and allow her to reapply her changes if she still wants to make them. This is called a Store Wins scenario. (The data-store values take precedence over the values submitted by the client.)
You can resolve conflicts by handling OptimisticConcurrencyException exceptions that the Entity Framework throws. In order to know when to throw these exceptions, the Entity Framework must be able to detect conflicts.
The rowversion value is a sequential number that’s incremented each time the row is updated. In an Update or Delete command, the Where clause includes the original value of the tracking column (the original row version) . If the row being updated has been changed by another user, the value in the rowversion column is different than the original value, so the Update or Delete statement can’t find the row to update because of the Where clause.
The attribute is called Timestamp because previous versions of SQL Server used a SQL timestamp data type before the SQL rowversion replaced it. The .Net type for rowversion is a byte array.
Using ROWVERSION or TIMESTAMP to detect concurrency conflicts