The Key attribute can be applied to a property in an entity class to make it a key property and the corresponding column to a PrimaryKey column in the database. The default convention creates a primary key column for a property whose name is Id or Entity Class NameId. Sep 13, 2016 That’s it. You can use sequence for any of your columns. EF Core also supports HiLo Pattern to generate keys and HiLo pattern also makes use of sequence. Read how to use HiLo to generate keys with Entity Framework Core. Thank you for reading. Keep visiting this blog and share this in your network.
-->A key serves as a unique identifier for each entity instance. Most entities in EF have a single key, which maps to the concept of a primary key in relational databases (for entities without keys, see Keyless entities). Entities can have additional keys beyond the primary key (see Alternate Keys for more information). See the answer from yildizm85 to this question: entity framework not working on table without identity column 'Entity Framework requires a Primary Key to generate a model from the database. If there is no Primary Key on a table it will simply select the non-nullable columns as a concatenated primary key and the Entity will be read/only.' In Entity Framework Core, this has not been implemented yet. We must create a joining entity class for a joining table. The joining entity for the above Student and Course entities should include a foreign key property and a reference navigation property for each entity. The steps for configuring many-to-many relationships would the following.
Value generation patterns
There are three value generation patterns that can be used for properties:
- No value generation
- Value generated on add
- Value generated on add or update
No value generation
No value generation means that you will always supply a valid value to be saved to the database. This valid value must be assigned to new entities before they are added to the context.
Value generated on add
Value generated on add means that a value is generated for new entities.
Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context. This temporary value will then be replaced by the database generated value during
SaveChanges()
.If you add an entity to the context that has a value assigned to the property, then EF will attempt to insert that value rather than generating a new one. A property is considered to have a value assigned if it is not assigned the CLR default value (
null
for string
, 0
for int
, Guid.Empty
for Guid
, etc.). For more information, see Explicit values for generated properties.Warning
How the value is generated for added entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, but others may require you to manually setup how the value is generated.
For example, when using SQL Server, values will be automatically generated for
GUID
properties (using the SQL Server sequential GUID algorithm). However, if you specify that a DateTime
property is generated on add, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE()
, see Default Values.Value generated on add or update
Value generated on add or update means that a new value is generated every time the record is saved (insert or update).
Like
value generated on add
, if you specify a value for the property on a newly added instance of an entity, that value will be inserted rather than a value being generated. It is also possible to set an explicit value when updating. For more information, see Explicit values for generated properties.Warning
How the value is generated for added and updated entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, while others will require you to manually setup how the value is generated.
For example, when using SQL Server,
byte[]
properties that are set as generated on add or update and marked as concurrency tokens, will be setup with the rowversion
data type - so that values will be generated in the database. However, if you specify that a DateTime
property is generated on add or update, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE()
(see Default Values) to generate values for new rows. You could then use a database trigger to generate values during updates (such as the following example trigger).Value generated on add
By convention, non-composite primary keys of type short, int, long, or Guid are set up to have values generated for inserted entities, if a value isn't provided by the application. Your database provider typically takes care of the necessary configuration; for example, a numeric primary key in SQL Server is automatically set up to be an IDENTITY column.
You can configure any property to have its value generated for inserted entities as follows:
Warning
This just lets EF know that values are generated for added entities, it does not guarantee that EF will setup the actual mechanism to generate values. See Value generated on add section for more details.
Default values
On relational databases, a column can be configured with a default value; if a row is inserted without a value for that column, the default value will be used.
You can configure a default value on a property:
You can also specify a SQL fragment that is used to calculate the default value:
Specifying a default value will implicitly configure the property as value generated on add.
Value generated on add or update
Warning
This just lets EF know that values are generated for added or updated entities, it does not guarantee that EF will setup the actual mechanism to generate values. See Value generated on add or update section for more details.
Computed columns
On some relational databases, a column can be configured to have its value computed in the database, typically with an expression referring to other columns:
Note
In some cases the column's value is computed every time it is fetched (sometimes called virtual columns), and in others it is computed on every update of the row and stored (sometimes called stored or persisted columns). This varies across database providers.
No value generation
Disabling value generation on a property is typically necessary if a convention configures it for value generation. For example, if you have a primary key of type int, it will be implicitly set configured as value generated on add; you can disable this via the following:
-->by Microsoft
In 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:
Note
There is no essential connection between ASP.NET MVC and the Microsoft Entity Framework. There are several alternatives to the Entity Framework that you can use with ASP.NET MVC. For example, you can build your MVC Model classes using other O/RM tools such as Microsoft LINQ to SQL, NHibernate, or SubSonic.
Creating the Movie Sample Database
The Movie Database application uses a database table named Movies that contains the following columns:
Column Name | Data Type | Allow Nulls? | Is Primary Key? |
---|---|---|---|
Id | int | False | True |
Title | nvarchar(100) | False | False |
Director | nvarchar(100) | False | False |
You can add this table to an ASP.NET MVC project by following these steps:
- Right-click the App_Data folder in the Solution Explorer window and select the menu option Add, New Item.
- From the Add New Item dialog box, select SQL Server Database, give the database the name MoviesDB.mdf, and click the Add button.
- Double-click the MoviesDB.mdf file to open the Server Explorer/Database Explorer window.
- Expand the MoviesDB.mdf database connection, right-click the Tables folder, and select the menu option Add New Table.
- In the Table Designer, add the Id, Title, and Director columns.
- Click the Save button (it has the icon of the floppy) to save the new table with the name Movies.
After you create the Movies database table, you should add some sample data to the table. Right-click the Movies table and select the menu option Show Table Data. You can enter fake movie data into the grid that appears.
Creating the ADO.NET Entity Data Model
In order to use the Entity Framework, you need to create an Entity Data Model. You can take advantage of the Visual Studio Entity Data Model Wizard to generate an Entity Data Model from a database automatically.
Follow these steps:
Entity Framework Composite Primary Key
- Right-click the Models folder in the Solution Explorer window and select the menu option Add, New Item.
- In the Add New Item dialog, select the Data category (see Figure 1).
- Select the ADO.NET Entity Data Model template, give the Entity Data Model the name MoviesDBModel.edmx, and click the Add button. Clicking the Add button launches the Data Model Wizard.
- In the Choose Model Contents step, choose the Generate from a database option and click the Next button (see Figure 2).
- In the Choose Your Data Connection step, select the MoviesDB.mdf database connection, enter the entities connection settings name MoviesDBEntities, and click the Next button (see Figure 3).
- In the Choose Your Database Objects step, select the Movie database table and click the Finish button (see Figure 4).
After you complete these steps, the ADO.NET Entity Data Model Designer (Entity Designer) opens.
Figure 1 – Creating a new Entity Data Model
Figure 2 – Choose Model Contents Step
Figure 3 – Choose Your Data Connection
Figure 4 – Choose Your Database Objects
Modifying the ADO.NET Entity Data Model
After you create an Entity Data Model, you can modify the model by taking advantage of the Entity Designer (see Figure 5). You can open the Entity Designer at any time by double-clicking the MoviesDBModel.edmx file contained in the Models folder within the Solution Explorer window.
Figure 5 – The ADO.NET Entity Data Model Designer
For example, you can use the Entity Designer to change the names of the classes that the Entity Model Data Wizard generates. The Wizard created a new data access class named Movies. In other words, the Wizard gave the class the very same name as the database table. Because we will use this class to represent a particular Movie instance, we should rename the class from Movies to Movie.
If you want to rename an entity class, you can double-click on the class name in the Entity Designer and enter a new name (see Figure 6). Alternatively, you can change the name of an entity in the Properties window after selecting an entity in the Entity Designer.
Figure 6 – Changing an entity name
Remember to save your Entity Data Model after making a modification by clicking the Save button (the icon of the floppy disk). Behind the scenes, the Entity Designer generates a set of C# classes. You can view these classes by opening the MoviesDBModel.Designer.cs file from the Solution Explorer window.
Don't modify the code in the Designer.cs file since your changes will be overwritten the next time you use the Entity Designer. If you want to extend the functionality of the entity classes defined in the Designer.cs file then you can create partial classes in separate files.
Selecting Database Records with the Entity Framework
Let's start building our Movie Database application by creating a page that displays a list of movie records. The Home controller in Listing 1 exposes an action named Index(). The Index() action returns all of the movie records from the Movie database table by taking advantage of the Entity Framework.
Listing 1 – ControllersHomeController.cs
Notice that the controller in Listing 1 includes a constructor. The constructor initializes a class-level field named _db. The _db field represents the database entities generated by the Microsoft Entity Framework. The _db field is an instance of the MoviesDBEntities class that was generated by the Entity Designer.
In order to use theMoviesDBEntities class in the Home controller, you must import the MovieEntityApp.Models namespace (MVCProjectName.Models).
The _db field is used within the Index() action to retrieve the records from the Movies database table. The expression _db.MovieSet represents all of the records from the Movies database table. The ToList() method is used to convert the set of movies into a generic collection of Movie objects (List<Movie>).
The movie records are retrieved with the help of LINQ to Entities. The Index() action in Listing 1 uses LINQ method syntax to retrieve the set of database records. If you prefer, you can use LINQ query syntax instead. The following two statements do the very same thing:
Use whichever LINQ syntax – method syntax or query syntax – that you find most intuitive. There is no difference in performance between the two approaches – the only difference is style.
The view in Listing 2 is used to display the movie records.
Listing 2 – ViewsHomeIndex.aspx
The view in Listing 2 contains a foreach loop that iterates through each movie record and displays the values of the movie record's Title and Director properties. Notice that an Edit and Delete link is displayed next to each record. Furthermore, an Add Movie link appears at the bottom of the view (see Figure 7).
Figure 7 – The Index view
The Index view is a typed view. The Index view includes a <%@ Page %> directive with an Inherits attribute that casts the Model property to a strongly typed generic List collection of Movie objects (List<Movie).
Inserting Database Records with the Entity Framework
You can use the Entity Framework to make it easy to insert new records into a database table. Listing 3 contains two new actions added to the Home controller class that you can use to insert new records into the Movie database table.
Listing 3 – ControllersHomeController.cs (Add methods)
The first Add() action simply returns a view. The view contains a form for adding a new movie database record (see Figure 8). When you submit the form, the second Add() action is invoked.
Notice that the second Add() action is decorated with the AcceptVerbs attribute. This action can be invoked only when performing an HTTP POST operation. In other words, this action can only be invoked when posting an HTML form.
The second Add() action creates a new instance of the Entity Framework Movie class with the help of the ASP.NET MVC TryUpdateModel() method. The TryUpdateModel() method takes the fields in the FormCollection passed to the Add() method and assigns the values of these HTML form fields to the Movie class.
When using the Entity Framework, you must supply a 'white list' of properties when using the TryUpdateModel or UpdateModel methods to update the properties of an entity class.
Next, the Add() action performs some simple form validation. The action verifies that both the Title and Director properties have values. If there is a validation error, then a validation error message is added to ModelState.
If there are no validation errors then a new movie record is added to the Movies database table with the help of the Entity Framework. The new record is added to the database with the following two lines of code:
The first line of code adds the new Movie entity to the set of movies being tracked by the Entity Framework. The second line of code saves whatever changes have been made to the Movies being tracked back to the underlying database.
Figure 8 – The Add view
Updating Database Records with the Entity Framework
You can follow almost the same approach to edit a database record with the Entity Framework as the approach that we just followed to insert a new database record. Listing 4 contains two new controller actions named Edit(). The first Edit() action returns an HTML form for editing a movie record. The second Edit() action attempts to update the database.
Entity Framework Generate Primary Key In Mysql
Listing 4 – ControllersHomeController.cs (Edit methods)
The second Edit() action starts by retrieving the Movie record from the database that matches the Id of the movie being edited. The following LINQ to Entities statement grabs the first database record that matches a particular Id:
Next, the TryUpdateModel() method is used to assign the values of the HTML form fields to the properties of the movie entity. Notice that a white list is supplied to specify the exact properties to update.
Next, some simple validation is performed to verify that both the Movie Title and Director properties have values. If either property is missing a value, then a validation error message is added to ModelState and ModelState.IsValid returns the value false.
Finally, if there are no validation errors, then the underlying Movies database table is updated with any changes by calling the SaveChanges() method.
When editing database records, you need to pass the Id of the record being edited to the controller action that performs the database update. Otherwise, the controller action will not know which record to update in the underlying database. The Edit view, contained in Listing 5, includes a hidden form field that represents the Id of the database record being edited.
Entity Framework Generate Primary Key Example
Listing 5 – ViewsHomeEdit.aspx
Deleting Database Records with the Entity Framework
The final database operation, which we need to tackle in this tutorial, is deleting database records. You can use the controller action in Listing 6 to delete a particular database record.
Listing 6 -- ControllersHomeController.cs (Delete action)
The Delete() action first retrieves the Movie entity that matches the Id passed to the action. Next, the movie is deleted from the database by calling the DeleteObject() method followed by the SaveChanges() method. Finally, the user is redirected back to the Index view.
Summary
The purpose of this tutorial was to demonstrate how you can build database-driven web applications by taking advantage of ASP.NET MVC and the Microsoft Entity Framework. You learned how to build an application that enables you to select, insert, update, and delete database records.
First, we discussed how you can use the Entity Data Model Wizard to generate an Entity Data Model from within Visual Studio. Next, you learn how to use LINQ to Entities to retrieve a set of database records from a database table. Finally, we used the Entity Framework to insert, update, and delete database records.