Showing posts with label Entity Framework. Show all posts
Showing posts with label Entity Framework. Show all posts

August 20, 2014

I build an application using Visual Studio and Sql Server and built the model using Entity Framework but when I deployed the application, I keep getting an error. To give bit more information, I set up the database using Sql Server 2012 and applied the compatibility level to Sql Server 2005 as the database server only had Sql Server 2005. Ya, I know it's an old server but it's not a priority to the business to update that server.

So, after running the application against the production database, I realised that the error is happening from the database server and not from the web server. Basically, it's failing to a add a record when the table has a datetime column and it's failing with the message "The version of SQL Server in use does not support datatype 'datetime2'". I was a bit confused as I thought I have applied the compatibility settings correctly.

To further test, I reloaded the data model from the Sql Server 2005 database but the error was still there.

Now, to fix the issue, I opened the edmx file in an xml editor and within <edmx:StorageModels> element there is "ProviderManifestToken" attribute in "Schema" element. The value for "ProviderManifestToken" was set to 2008. Bingo. I changed that value to 2005 and recompiled the application which fixed the issue.

This is probably a bug in Entity Framework as it not picking up the right database and instead applying a default lowest value.

To apply the compatibility level, right click the database name in SQL Server Management Studio, and click on Properties, navigate to Options and then set compatibility level.

image

Models in an MVC application are just classes and therefore it is easy to add data annotations in the class itself. When MVC generates the views, the different field names and validation messages and datatypes are automatically generated. For example, the following can be a data model in an application.

The above LoginModel is copied from the auto generated template in an MVC application. Note that the data annotations like "Required", "DataType" are applied. However, if the model is generated from Entity Framework, these data annotations will not appear and how would we add the necessary data annotations.

So, to add annotations to an Entity model, we can take advantage of the System.ComponentModel.DataAnnotations.MetadataTypeAttribute class that allows to specify the metadata class to use with the data model class. So, we need to create a new class specify the metadata attribute which will link the data model class to another class which will contain all the data annotations for the necessary fields.

Here is an example of how this would work.

[MetadataType(typeof(AccountMetaData))]
public partial class Account
{
    //empty class just to apply class level attribute
    //imagine this as data annotations for Account
    //is in AccountMetaData class
}

public class AccountMetaData
{
    //same property name & type as in the Entity model
    [Display(Name = "Date of Birth of date")]
    public DateTime DateOfBirth;

    [Required]
    [MinLength(6, ErrorMessage = "Too short")]
    public string AnnountName;
}

I am working on an older product and updating it to use the latest version of .NET and Entity Framework. Now that I have created the Entity Framework model using database first approach, I am finding issues like "EntityType 'EntityName' has no key defined". Basically, what this means is that the table in the database does not have a primary key defined. To fix the issue, the database needs to be updated and a primary key needs to be defined.

July 10, 2014

While retrieving data from a Sql Server database using Entity Framework, I have sometimes received the error "There is already an open DataReader associated with this Command which must be closed first.". Entity Framework handles database connections properly, so what exactly is this issue?

Ater diving into it, I figured that the MultipleActiveResultSets attribute is set to false in the connection string which should be turned to true as there are multiple data retrieval commands running against a single database connection and maybe a select statement is running while another select statement is already executing.

Changing the MultipleActiveResultSets attribute to true fixed the issue. Here is a sample connection string.

  1. <add name="DemoEntities" connectionString="metadata=res://*/DemoLogic.DemoModel.DemoModel.csdl|res://*/DemoLogic.DemoModel.DemoModel.ssdl|res://*/DemoLogic.DemoModel.DemoModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=Demo;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient"/>

July 7, 2014

I am using Entity Framework to insert data to a database and I am getting the exception below.

System.Data.Entity.Validation.DbEntityValidationException: Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at MethodName(Int32 id, ...) in c:\Users\project\filename.cs:line 33}
  

June 5, 2014

I have this table that has a reference to itself and to use entity framework and query the table in an asp.net application. Creating associations to is not usual but can help in certain scenarios, for example, if you have a table containing Employee details and you want to manage Manager relation in the same table. In the post, I will use the above example and have EmployeeId and ManagerId in the same table and set the proper associations.

May 29, 2014

Tables in a database can many to many relationships. For example, an Order can contain multiple Items and an Item can be in multiple Orders. In this scenario, the tables are linked to each other using a link table that contains the OrderId and ItemId. Here is how the table structure looks like.

Table Structure
  1. USE [Employee]
  2. GO
  3.  
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE TABLE [dbo].[Item](
  9.     [ItemId] [int] IDENTITY(1,1) NOT NULL,
  10.     [ItemName] [nvarchar](50) NOT NULL,
  11. CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
  12. (
  13.     [ItemId] ASC
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18. /****** Object:  Table [dbo].[Order]    Script Date: 29/05/2014 7:04:15 PM ******/
  19. SET ANSI_NULLS ON
  20. GO
  21. SET QUOTED_IDENTIFIER ON
  22. GO
  23. CREATE TABLE [dbo].[Order](
  24.     [OrderId] [int] IDENTITY(1,1) NOT NULL,
  25.     [OrderName] [nvarchar](50) NOT NULL,
  26. CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
  27. (
  28.     [OrderId] ASC
  29. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  30. ) ON [PRIMARY]
  31.  
  32. GO
  33. /****** Object:  Table [dbo].[OrderItem]    Script Date: 29/05/2014 7:04:15 PM ******/
  34. SET ANSI_NULLS ON
  35. GO
  36. SET QUOTED_IDENTIFIER ON
  37. GO
  38. CREATE TABLE [dbo].[OrderItem](
  39.     [OrderId] [int] NOT NULL,
  40.     [ItemId] [int] NOT NULL
  41. ) ON [PRIMARY]
  42.  
  43. GO
  44.   ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADDCONSTRAINT [FK_OrderItem_Item] FOREIGN KEY([ItemId])
  45. REFERENCES [dbo].[Item] ([ItemId])
  46. GO
  47. ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Item]
  48. GO
  49.   ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADDCONSTRAINT [FK_OrderItem_Order] FOREIGN KEY([OrderId])
  50. REFERENCES [dbo].[Order] ([OrderId])
  51. GO
  52. ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Order]
  53. GO

 

To import these tables in Entity Framework model, I will create a new edmx file and drag and drop the tables into the designer view. You will notice that the link table does not appear in the designer view and the link is denoted by a line with multiple (*) sign at both ends.

image

Now the model can be queried in the usual way as we would query any other entity framework model. Here is an example on how to get Items by OrderId and to add Items and Orders in the database.

Query Model
  1. using System.Collections.Generic;
  2. using System.Linq;
  3.  
  4. namespace WebApplication3.Helper
  5. {
  6.     public class ItemOrderBll
  7.     {
  8.         private List< Model.Item> GetItemsByOrderId(int orderId)
  9.         {
  10.             using(Model.EmployeeEntities en = new Model.EmployeeEntities())
  11.             {
  12.                 return (from c in en.Items
  13.                         from d in en.Orders
  14.                         where d.OrderId == orderId
  15.                         select c).ToList();
  16.             }
  17.         }
  18.  
  19.         private void AddStuff()
  20.         {
  21.             using (Model.EmployeeEntities en = new Model.EmployeeEntities())
  22.             {
  23.                 Model.Item item1 = new Model.Item() { ItemName = "Item 1" };
  24.                 Model.Item item2 = new Model.Item() { ItemName = "Item 2" };
  25.  
  26.                 Model.Order order1 = new Model.Order() { OrderName = "Order 1" };
  27.                 Model.Order order2 = new Model.Order() { OrderName = "Order 2" };
  28.  
  29.                 order1.Items.Add(item1);
  30.                 item2.Orders.Add(order2);
  31.  
  32.                 en.SaveChanges();
  33.             }
  34.         }
  35.     }
  36. }

March 6, 2014

I am using Entity Framework Code First approach but I am receiving the error “The model backing the ‘ModelContext' context has changed since the database was created. Consider using Code First Migrations to update the database”. What does this mean?

Basically, the above error can occur when we have enabled and added a migration and updated a database and after that we have changed the model for the application. If the model has changed, Migrations process will need to be re-run before we can run the application. Read on Enable Migrations on how to use the Migrations in Code First Approach. At this stage, add a new Migration and update the database and then re-run adding the new update.

While using Code First Migrations, we can also add data to the database by using the Seed method in the Configuration.cs file. The Seed method contains the data context as the parameter and would initialise the data sets and use the SaveChanges method to save all the changes back to the database,

Here is the code for Seed method.

Configuration.cs
  1. protected override void Seed(MvcEntity.Models.EmployeeModelContext context)
  2. {
  3.     //  This method will be called after migrating to the latest version.
  4.  
  5.     //  You can use the DbSet<T>.AddOrUpdate() helper extension method
  6.     //  to avoid creating duplicate seed data.       
  7.  
  8.     var departments = new List<Department>
  9.     {
  10.         new Department{ DepartmentName="HR", StartDate=new DateTime(2010,4,1)},
  11.         new Department{ DepartmentName="IT", StartDate=new DateTime(2010,1,1)},
  12.         new Department{ DepartmentName="Sales", StartDate=new DateTime(2010,10,1)}
  13.     };
  14.  
  15.     var employees = new List<Employee>
  16.     {
  17.         new Employee{EmailAddress="john@hotmail.com", FirstName="John", LastName="Smith", DateJoined=new DateTime(2014,1,1)
  18.             , DepartmentId=departments.Single(d => d.DepartmentName == "HR").DepartmentId, IsActive=true},
  19.         new Employee{EmailAddress="ken@hotmail.com", FirstName="Ken", LastName="Marsh", DateJoined=new DateTime(2014,1,1)
  20.             , DepartmentId=departments.Single(d => d.DepartmentName == "IT").DepartmentId, IsActive=true},
  21.         new Employee{EmailAddress="shaun@hotmail.com", FirstName="Shaun", LastName="Lin", DateJoined=new DateTime(2014,1,1)
  22.             , DepartmentId=departments.Single(d => d.DepartmentName == "Sales").DepartmentId, IsActive=true},
  23.     };
  24.  
  25.     departments.ForEach(c => context.Departments.AddOrUpdate(d => d.DepartmentName, c));
  26.     employees.ForEach(c => context.Employees.AddOrUpdate(e => e.LastName, c));
  27.  
  28.     context.SaveChanges();
  29. }

 

In the method, I created 2 Lists of Employee and Department objects. Important to note how I set the DepartmentId on the Employee. The DepartmentId is not created as yet but I am querying to get the ids. Also, towards the end, I am calling the AddOrUpdate method that determines if the object has changed by checking for the specified property. For Department, I am checking for DepartmentName and for Employee, I am checking for LastName.

It’s important to note that when the migration is run first time, all data specified in the Seed method will be added. On subsequent migrations, only updated value will be updated or new values will be inserted.

The Code First approach allows for a feature called Migrations that allows the developer to keep a history of the changes to the model and allows the changes to be applied to the database. To enable migrations, few commands need to be applied from the  Package Manager Console.

To open Package Manager Console, go to Tools > NuGet Package Manager > Package Manager Console. Run the command “Enable-Migrations”. Alternatively, I can run “Enable-Migrations –EnableAutomaticMigrations”. The Automatic Migrations makes it easy as we do not need a code file for each change.  A folder called “Migrations” is created in the solution and it contains a Configuration.cs file and a datetime_MigrationName.cs file.  The Configuration class lets us configure how Migrations work for the context. We can leave the default for now. The other file contains the changes that will be deployed to the database.

image

The default implementation for the Configuration class is below. It’s important to note there is a Seed method that allows data to be entered in the database.

Configuration.cs
  1. namespace MvcEntity.Migrations
  2. {
  3.     using System;
  4.     using System.Data.Entity;
  5.     using System.Data.Entity.Migrations;
  6.     using System.Linq;
  7.  
  8.     internal sealed class Configuration : DbMigrationsConfiguration<MvcEntity.Models.EmployeeModelContext>
  9.     {
  10.         public Configuration()
  11.         {
  12.             AutomaticMigrationsEnabled = true;
  13.         }
  14.  
  15.         protected override void Seed(MvcEntity.Models.EmployeeModelContext context)
  16.         {
  17.             //  This method will be called after migrating to the latest version.
  18.  
  19.             //  You can use the DbSet<T>.AddOrUpdate() helper extension method
  20.             //  to avoid creating duplicate seed data. E.g.
  21.             //
  22.             //    context.People.AddOrUpdate(
  23.             //      p => p.FullName,
  24.             //      new Person { FullName = "Andrew Peters" },
  25.             //      new Person { FullName = "Brice Lambson" },
  26.             //      new Person { FullName = "Rowan Miller" }
  27.             //    );
  28.             //
  29.         }
  30.     }
  31. }

Once migrations is enabled, run the command “Add-Migration”. This will prompt for a name for the migration. Enter a name and hit Enter. This will scaffold the migration. For further changes to the model before deploying to the database, run the command “Add-Migration MigrationName”.

Finally run the command “Update-Database” and this will apply the changes. Here is a screen shot of how this would appear.

image

March 3, 2014

I will build from the previous post on code first approach and in this one use MVC framework to add data in the database. To do so, I will start by adding a new Controller to the project. Here are the steps to add a new Controller.

  • In Solution Explorer, right click on Controllers and click Add > Controller
  • Set Controller name as EmployeeController
  • Set scaffolding option to – MVC controller with read/write actions and views, using Entity Framework
  • Set Model class as Employee
  • Set DataContext class as EmployeeModelContext

Here is a screen shot of the above

image

image

Similarly, add the Department Controller. Both Employee and Department Controllers and associated Views are now added to the application. The application can now be run and data can be added from the Views.

image

Code First Approach

Previously,  showed on how to use the Model First approach in Entity Framework, In this post, I will show how to use the Code First approach to create the data model. Code First model helps developers generate models when access to database is not possible – for example, the database team has not created the database yet. In this scenario, the developers could use the Code First approach and create a model using the entity classes. I will use the same data model in this post and generate entities.  

Code-First approach allows an entity container to be created around domain classes (POCO) available in code. This entity container behave just like Database- or Model-First approach and supports CRUD operations on a database.

To generate the entities and to test it, I will use an empty MVC application. After the empty application is created, I will add the two classes – Department and Employee – in the Models folder.

Here is the source code for the two classes.

  1. namespace MvcEntity.Models
  2. {
  3.     public class Department
  4.     {
  5.         public int DepartmentId { get; set;}
  6.         public string DepartmentName { get; set; }
  7.         public DateTime StartDate { get; set; }
  8.         public DateTime EndDate { get; set; }
  9.         public List<Employee> Employees { get; set; }
  10.     }
  11. }

 

  1. namespace MvcEntity.Models
  2. {
  3.     public class Employee
  4.     {
  5.         public int EmployeeId { get; set; }
  6.         public string FirstName { get; set; }
  7.         public string LastName { get; set; }
  8.         public string EmailAddress { get; set; }
  9.         public DateTime DateJoined { get; set; }
  10.         public double Salary { get; set; }
  11.         public bool IsActive { get; set; }
  12.         public int DepartmentId { get; set; }
  13.     }
  14. }

 

Note that I have a property DepartmentId in Department class and EmployeeId in Employee class which automatically will be mapped as primary keys.

Also note that the DepartmentId in Employee class allows the formation of foreign key relationship.

Next, I will create a EmployeeModelContext class that inherits System.Data.Entity.DbContext class and add Employee and Department classes as DBSet instances.

Data Context
  1. namespace MvcEntity.Models
  2. {
  3.     public class EmployeeModelContext : DbContext
  4.     {
  5.         public EmployeeModelContext()
  6.             : base("name = EmployeeModelContext")
  7.         {
  8.         }
  9.         public DbSet<Department> Departments { get; set; }
  10.         public DbSet<Employee> Employees { get; set; }
  11.     }
  12. }

Finally, the web.config file needs to be updated to contain a data connection. The connection name needs to be the same as the DataContext name.

<connectionStrings>
    <add name = "EmployeeModelContext" connectionString = "Data Source =
(localdb)\v11.0; Initial Catalog = EmployeeModelContext-201402262103602;
Integrated Security = True; MultipleActiveResultSets = True;
AttachDbFilename = |DataDirectory|EmployeeModelContext-201402262103602.mdf"
    providerName = "System.Data.SqlClient" />
  </connectionStrings>

At this stage,, the entities have been mapped to a Sql Server LocalDB database.  In the next posts, I will show how to use this database and how to connect to it via Entity Framework.

February 27, 2014

To continue on from the previous post, in this post I will show how to create a relationship between two entities in the model. In the last post, I had an Employee entity created. In this one, I will create a new entity Department, update the Employee entity to have an association with Department.

Here is how the model looks like currently.

 image

Now, the Department entity is added in the same way Employee was added previously. The model looks like below now.

image

Now, to add the association, right click on Employee entity and choose Add New > Association.

image

An Add Association prompt like below will appear with most of the options correctly set.

image

Notice that Multiplicity has been incorrectly set in this scenario. I will invert the relationships so 1 department can have multiple employees. So , this will look like below.

image

Notice that there is an option to “Add Foreign key to the Employee Entity”. I have selected it clicked OK. The model is now updated but the foreign key is named as “DepartmentDepartmentId” like below. This happened as I renamed the original primary key from “Id” to “DepartmentId”.

image

I have renamed it to DepartmentId by clicking on it and editing it through the properties. My model is now ready and I clicked Validate like but it failed with the messages below.

image

This is because the new table does not exist yet. So, I will right click and choose generate database from model which will correct this issue as shown in the previous post.

That’s it, the association is now created and the model is good to go.

I usually create a Sql Server database using Sql Server Management Studio and use asp.net or mvc application to create a database connected application. Using Entity Framework’s model first approach, I can also visually create the model and then create a database using it. in this post, I will show how to create a data model using entity framework and then create a database using it.

I will use an empty asp.net application and add an entity data model to it. To do so, add a new file and choose Data > ADO.NET Entity Data Model .

image

I have renamed the model to "EmployeeModel and clicked OK. In the next screen, I chose Empty Model and clicked Finish.

image

Clicking Finish creates the EmployeeModel.edmx and EmployeeModel.Context.tt files in the solution explorer.

image

The designer should be opened by default and I will write click and Add a New  Entity.

image

In the next prompt, fill in the Entity name, and choose a unique id property which I have set as EmployeeId.

image

Clicking OK will generate the entity as below.

image

Next, I will add a few properties to this entity like FirstName, LastName, EmailAddress, DateJoined, IsActive and Salary.

To add the properties, right click on the Entity and choose Add New > Scalar Property

image

After adding the properties, set the properties of individual fields. Like FirstName, LastName, EmailAddress will be string, isActive will be Boolean, DateJoined will be DateTime and Salary will be double. Other properties like Nullable and default value can also be set. I have set 0 for the default value for Salary. Notice that the string fields have additional properties like Fixed Length and Max Length. I have set 50 to be the Max Length for all string properties.

For now, let’s assume that the model is ready. In future posts, (I will add more to it.).  Right click on the designer and click on Validate.

image

Notice that Validation will fail with one warning – Entity type ‘Employee’ is not mapped.

image

This is because the model is not mapped to any database. To create the database, right click on the model and choose “Generate database from model”. A prompt will open to choose a connection or to create a new one. In this situation, I have created a new connection to an Employee database previously created using Sql Server Management Studio. The script is generated and when I click on Finish, the database will be generated.

Generated sql script
  1.  
  2. -- --------------------------------------------------
  3. -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
  4. -- --------------------------------------------------
  5. -- Date Created: 02/27/2014 15:33:14
  6. -- Generated from EDMX file: C:\Users\...\Documents\Visual Studio 2012\Projects\FormsTestApp\EntityPrac\EmployeeModel.edmx
  7. -- --------------------------------------------------
  8.  
  9. SET QUOTED_IDENTIFIER OFF;
  10. GO
  11. USE [Employee];
  12. GO
  13. IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
  14. GO
  15.  
  16. -- --------------------------------------------------
  17. -- Dropping existing FOREIGN KEY constraints
  18. -- --------------------------------------------------
  19.  
  20.  
  21. -- --------------------------------------------------
  22. -- Dropping existing tables
  23. -- --------------------------------------------------
  24.  
  25.  
  26. -- --------------------------------------------------
  27. -- Creating all tables
  28. -- --------------------------------------------------
  29.  
  30. -- Creating table 'Employees'
  31. CREATE TABLE [dbo].[Employees] (
  32.     [EmployeeId] int IDENTITY(1,1) NOT NULL,
  33.     [FirstName] nvarchar(50)  NOT NULL,
  34.     [LastName] nvarchar(50)  NOT NULL,
  35.     [EmailAddress] nvarchar(50)  NOT NULL,
  36.     [DateJoined] datetime  NOT NULL,
  37.     [Salary] float  NOT NULL,
  38.     [IsActive] bit  NOT NULL
  39. );
  40. GO
  41.  
  42. -- --------------------------------------------------
  43. -- Creating all PRIMARY KEY constraints
  44. -- --------------------------------------------------
  45.  
  46. -- Creating primary key on [EmployeeId] in table 'Employees'
  47. ALTER TABLE [dbo].[Employees]
  48. ADD CONSTRAINT [PK_Employees]
  49.     PRIMARY KEY CLUSTERED ([EmployeeId] ASC);
  50. GO
  51.  
  52. -- --------------------------------------------------
  53. -- Creating all FOREIGN KEY constraints
  54. -- --------------------------------------------------
  55.  
  56. -- --------------------------------------------------
  57. -- Script has ended
  58. -- --------------------------------------------------

 

The generated sql file is also saved in the project. This will create the store schema definition language (SSDL) and the mapping specification language (MSL) that is based on the model. Note that the database tables are not really added. The generated script will need to be run from with Sql Server Management Studio to create the tables to the database.

November 27, 2013

When a new instance of the object context class is created, it should be created using the using statement. It's because, in .NET, when a variable goes out of scope - the garbage collector retrieves its memory at its own time. The ObjectContext class holds resources including database connections which should be released when done. To achieve this, the using statement should be used. When the using code block is completed, the Dispose() method is called that closes the database connection and releases memory from all other unused objects from the using code block.

Do not use
  1. PersonEntities context = new PersonEntities();
  2. foreach (var person in context.People)
  3. {
  4.     System.Console.WriteLine("{0} {1}, Address: {2}",
  5.     person.FirstName, person.LastName, person.Address);
  6. }

 

Use following
  1. using (var context = new PersonEntities())
  2. {
  3.     foreach (var person in context.People)
  4.     {
  5.         System.Console.WriteLine("{0} {1}, Address: {2}",
  6.         person.FirstName, person.LastName, person.Address);
  7.     }
  8. }

Reference: Shahed Kazi at AspNetify.com