Introduction to entity framework Code first

Microsoft’s ADO.NET Entity Framework (EF) simplifies data access by allowing you to avoid working directly with the database in your code. Instead you can retrieve data by writing queries against strongly typed classes letting the Entity Framework handle the database interaction on your behalf. EF can also persist changes back to the database for you. In addition to this benefit, you will also benefit from the EF’s comprehension of relationships. This means you will not be required to write extra code to specify joins between entities when expressing queries or simply working with your objects in memory.

EF provides you with three ways to define the model of your entities. Using the database first workflow, you can begin with a legacy database to create a model. With the model first workflow, you  can design a model in a designer. Or you can simply define classes and let EF work with those—referred to as code first.

In tis tutorial, we are going to introduce Entity Framework using the Database Code First approach:

Then we start from an existing database, which lets us create the model by a simple drag and drop.

A : Entity Framwork  Code first  Using an existing Database

We are going to use aspnetdb (the default membeship database)

Here is database script :

– ————————————————–
– Creating all tables
– ————————————————–
– Creating table ‘aspnet_Applications’
CREATE TABLE [dbo].[aspnet_Applications] (
[ApplicationName] nvarchar(256) NOT NULL,
[LoweredApplicationName] nvarchar(256) NOT NULL,
[ApplicationId] uniqueidentifier NOT NULL,
[Description] nvarchar(256) NULL
);
GO
– Creating table ‘aspnet_Membership’
CREATE TABLE [dbo].[aspnet_Membership] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[Password] nvarchar(128) NOT NULL,
[PasswordFormat] int NOT NULL,
[PasswordSalt] nvarchar(128) NOT NULL,
[MobilePIN] nvarchar(16) NULL,
[Email] nvarchar(256) NULL,
[LoweredEmail] nvarchar(256) NULL,
[PasswordQuestion] nvarchar(256) NULL,
[PasswordAnswer] nvarchar(128) NULL,
[IsApproved] bit NOT NULL,
[IsLockedOut] bit NOT NULL,
[CreateDate] datetime NOT NULL,
[LastLoginDate] datetime NOT NULL,
[LastPasswordChangedDate] datetime NOT NULL,
[LastLockoutDate] datetime NOT NULL,
[FailedPasswordAttemptCount] int NOT NULL,
[FailedPasswordAttemptWindowStart] datetime NOT NULL,
[FailedPasswordAnswerAttemptCount] int NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] datetime NOT NULL,
[Comment] nvarchar(max) NULL
);
GO
– Creating table ‘aspnet_Paths’
CREATE TABLE [dbo].[aspnet_Paths] (
[ApplicationId] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NOT NULL,
[Path] nvarchar(256) NOT NULL,
[LoweredPath] nvarchar(256) NOT NULL
);
GO
– Creating table ‘aspnet_PersonalizationAllUsers’
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers] (
[PathId] uniqueidentifier NOT NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_PersonalizationPerUser’
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser] (
[Id] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NULL,
[UserId] uniqueidentifier NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_Profile’
CREATE TABLE [dbo].[aspnet_Profile] (
[UserId] uniqueidentifier NOT NULL,
[PropertyNames] nvarchar(max) NOT NULL,
[PropertyValuesString] nvarchar(max) NOT NULL,
[PropertyValuesBinary] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_Roles’
CREATE TABLE [dbo].[aspnet_Roles] (
[ApplicationId] uniqueidentifier NOT NULL,
[RoleId] uniqueidentifier NOT NULL,
[RoleName] nvarchar(256) NOT NULL,
[LoweredRoleName] nvarchar(256) NOT NULL,
[Description] nvarchar(256) NULL
);
GO
– Creating table ‘aspnet_SchemaVersions’
CREATE TABLE [dbo].[aspnet_SchemaVersions] (
[Feature] nvarchar(128) NOT NULL,
[CompatibleSchemaVersion] nvarchar(128) NOT NULL,
[IsCurrentVersion] bit NOT NULL
);
GO
– Creating table ‘aspnet_Users’
CREATE TABLE [dbo].[aspnet_Users] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[UserName] nvarchar(256) NOT NULL,
[LoweredUserName] nvarchar(256) NOT NULL,
[MobileAlias] nvarchar(16) NULL,
[IsAnonymous] bit NOT NULL,
[LastActivityDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_WebEvent_Events’
CREATE TABLE [dbo].[aspnet_WebEvent_Events] (
[EventId] char(32) NOT NULL,
[EventTimeUtc] datetime NOT NULL,
[EventTime] datetime NOT NULL,
[EventType] nvarchar(256) NOT NULL,
[EventSequence] decimal(19,0) NOT NULL,
[EventOccurrence] decimal(19,0) NOT NULL,
[EventCode] int NOT NULL,
[EventDetailCode] int NOT NULL,
[Message] nvarchar(1024) NULL,
[ApplicationPath] nvarchar(256) NULL,
[ApplicationVirtualPath] nvarchar(256) NULL,
[MachineName] nvarchar(256) NOT NULL,
[RequestUrl] nvarchar(1024) NULL,
[ExceptionType] nvarchar(256) NULL,
[Details] nvarchar(max) NULL
);
GO
– Creating table ‘aspnet_UsersInRoles’
CREATE TABLE [dbo].[aspnet_UsersInRoles] (
[aspnet_Roles_RoleId] uniqueidentifier NOT NULL,
[aspnet_Users_UserId] uniqueidentifier NOT NULL
);
GO
– ————————————————–
– Creating all PRIMARY KEY constraints
– ————————————————–
– Creating primary key on [ApplicationId] in table ‘aspnet_Applications’
ALTER TABLE [dbo].[aspnet_Applications]
ADD CONSTRAINT [PK_aspnet_Applications]
PRIMARY KEY CLUSTERED ([ApplicationId] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [PK_aspnet_Membership]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [PathId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [PK_aspnet_Paths]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO
– Creating primary key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [PK_aspnet_PersonalizationAllUsers]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO
– Creating primary key on [Id] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [PK_aspnet_PersonalizationPerUser]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [PK_aspnet_Profile]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [RoleId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [PK_aspnet_Roles]
PRIMARY KEY CLUSTERED ([RoleId] ASC);
GO
– Creating primary key on [Feature], [CompatibleSchemaVersion] in table ‘aspnet_SchemaVersions’
ALTER TABLE [dbo].[aspnet_SchemaVersions]
ADD CONSTRAINT [PK_aspnet_SchemaVersions]
PRIMARY KEY CLUSTERED ([Feature], [CompatibleSchemaVersion] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [PK_aspnet_Users]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [EventId] in table ‘aspnet_WebEvent_Events’
ALTER TABLE [dbo].[aspnet_WebEvent_Events]
ADD CONSTRAINT [PK_aspnet_WebEvent_Events]
PRIMARY KEY CLUSTERED ([EventId] ASC);
GO
– Creating primary key on [aspnet_Roles_RoleId], [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [PK_aspnet_UsersInRoles]
PRIMARY KEY NONCLUSTERED ([aspnet_Roles_RoleId], [aspnet_Users_UserId] ASC);
GO
– ————————————————–
– Creating all FOREIGN KEY constraints
– ————————————————–
– Creating foreign key on [ApplicationId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__Appli__145C0A3F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Me__Appli__145C0A3F’
CREATE INDEX [IX_FK__aspnet_Me__Appli__145C0A3F]
ON [dbo].[aspnet_Membership]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [FK__aspnet_Pa__Appli__45F365D3]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pa__Appli__45F365D3′
CREATE INDEX [IX_FK__aspnet_Pa__Appli__45F365D3]
ON [dbo].[aspnet_Paths]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [FK__aspnet_Ro__Appli__32E0915F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Ro__Appli__32E0915F’
CREATE INDEX [IX_FK__aspnet_Ro__Appli__32E0915F]
ON [dbo].[aspnet_Roles]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [FK__aspnet_Us__Appli__0425A276]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Us__Appli__0425A276′
CREATE INDEX [IX_FK__aspnet_Us__Appli__0425A276]
ON [dbo].[aspnet_Users]
([ApplicationId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__UserI__15502E78]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4BAC3F29]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [PathId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4F7CD00D]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__PathI__4F7CD00D’
CREATE INDEX [IX_FK__aspnet_Pe__PathI__4F7CD00D]
ON [dbo].[aspnet_PersonalizationPerUser]
([PathId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__UserI__5070F446]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__UserI__5070F446′
CREATE INDEX [IX_FK__aspnet_Pe__UserI__5070F446]
ON [dbo].[aspnet_PersonalizationPerUser]
([UserId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [FK__aspnet_Pr__UserI__29572725]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [aspnet_Roles_RoleId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Roles]
FOREIGN KEY ([aspnet_Roles_RoleId])
REFERENCES [dbo].[aspnet_Roles]
([RoleId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Users]
FOREIGN KEY ([aspnet_Users_UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK_aspnet_UsersInRoles_aspnet_Users’
CREATE INDEX [IX_FK_aspnet_UsersInRoles_aspnet_Users]
ON [dbo].[aspnet_UsersInRoles]
([aspnet_Users_UserId]);
GO
– ————————————————–
– Script has ended
– ————————————————–

• Now , create a Class Library project
• Add an ADO.NET Entity Data Model (.edmx  file )  Item

• Choose Generate from database option because will generate our model from an existing database

• Here, Will connect to SQL Server or another database Server, So Microsoft SQL SERVER as Data Source, if we use another database Server click change.
• Type or select our Server Name (SQLEXPRESS) in our case
• Select our authentication Mode
• Select ASPNETDB Database (in our case, we are going to use this database for test),  If you do not have the ASPNETDB Database you can download it at the end tutorial

• Check Table Objet, we no need Stored Procedures and views

• Click finish to create our model.  The generated model will look like

• Expand our model.edmx file, and  remove Model.Context.tt, Model.Designer.cs, Model.edmx.diagram and Model.tt

• Right Click Model.ebmx file and click  Add Code Generation Item

• Choose EF 5.x DbContext Fluent Generator ( or install it by nuget Packages if does not exists)

• Now expand your Model. edmx file, then you ll see that Model.Entiites.tt (contains our Database object generated as class files) and Model.Mapping.tt (contains mappings of our objects)

• Here, we are going to move our generated items to new folders or project so as to remove the model.edmx file and become independent of it)

1. So Create a folder CodeFirstModel and move the content of Model.Entities.tt on it
2. Create a folder CodeFirstMapping and move the content of Model.Mappings.tt on it
3. Create a folder CodeFirstContext and move the file of Model.Context.cs on it

• now, we can delete our model.edmx file

• Finally, we can clean our connectionString by removing all metadata

Now, we can start testing our work

B : Entity Framwork  Code first  Without  an existing Database

In this case, we don’t  have an existing database (the goals is to enable a more code-centric option which we call “code first development”).

• So, lets  create a class named Product (the sample is based on Nothwind Sample database)

• Create a Vendor Class as follow

• Create a ProductVendor class as follow. Note that a Product can have many Vendors and a vendor can sell many Products.

• Our next step is to define Mapping between classes so as to define our database objects.

Note that it is possible to do Code First without mapping relationships between classes . In this case our databases objects will be created as the names of our classes and database fields as the name of our class properties.

• So lets mapp relationships between classes as follow :

for more information about Configuring Relationships between classes, please take a look at Configuring Relationships with the Fluent API

• Now define our DbContext like this :

• The line  below create the database if does not exist:

Database.SetInitializer<OurContext>(new CreateDatabaseIfNotExists<OurContext> ());

• Now update our web.config file so as to set our Database name (in our case , we are setting the name as CommerceDatabase).
• To test our sample, we can only execute the code below.
• The result is that a databse whose name is CommerceDatabase  is created on our server .SQLEXPRESS.
• 

C : Entity Framwork  Code first  Using UnitOfWork pattern

Plese see  Section A of our totorial ASP.NET MVC Custom Membership Password Hashing based on SALT key using SHA-3 Algorithm

[contact-form][contact-field label=’Nom’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]