Étiquettes

, , , ,

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 First approach.

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

A : Entity Framwork database first Model Creation

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

1

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

2

  • 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

3

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

4

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

1

  • By right clicking our Model , we have possibilities to explore our database

2

  • Expand our model.edmx file, and Model.tt so we’ill see lot of generated classes that represent our database tables.

3

  • Expand Model.Context.tt , we have a generated class that represent our context.

We will use this context to manage database objects

4

  • reference EntityFramwork.dll (or get it by nuget Package)
  • Compile our project

The code below shows how to use our Model,

2

1

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
— ————————————————–

Thank you,

Related articles