how to use views in code first entity framework

Question

How can I use the database view in entity framework code first,

Answer 1

If, like me, you are interested only in mapping entity coming from an other database (an erp in my case) to relate them to entities specific of your application, then you can use the views as you use a table (map the view in the same way!). Obviously, if you try to update that entities, you will get an exception if the view is not updatable. The procedure is the same as in the case of normal (based on a table) entities:

1. Create a POCO class for the view; for example FooView

2. Add the DbSet property in the DbContext class

3. Use a FooViewConfiguration file to set a different name for the view (using ToTable("Foo"); in the constructor) or to set particular properties

   public class FooViewConfiguration : EntityTypeConfiguration<FooView>      
   {
       public FooViewConfiguration()
       {
           this.HasKey(t => t.Id);
           this.ToTable("myView");
       }
   }
   

4. Add the FooViewConfiguration file to the modelBuilder, for example overriding the OnModelCreating method of the Context:

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       modelBuilder.Configurations.Add(new FooViewConfiguration ());
   }
   

Answer 2

This may be an update but to use views with EF Code first simply add [Table("NameOfView")] to the top of the class and all should work right without having to go through all the hoops everyone else is going through. Also you will have to report one of the columns as a [key] column. Here is my sample code below to implement it.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SomeProject.Data
{
    [Table("SomeView")]
    public class SomeView
    {
        [Key]
        public int NameID { get; set; }
        public string Name { get; set; }
    }
}

And here is what the context looks like

using System.Data.Entity;

namespace SomeProject.Data
{
    public class DatabaseContext : DbContext
    {
        public DbSet<SomeView> SomeViews { get; set; }
    }
}

Answer 3

If all you want is a bunch of de-normalized objects, then you might just created a public get-only IQueryable<TDenormolized> property in your DbContext class.

In the get you return a Linq result to project the de-normoalized values into your de-normalized objects. This might be better than writing a DB View because you are programming, you are not limited by only using select statements. Also it's compile time type safe.

Just be careful not trigger enumerations like ToList() calls, that will break the deferred query and you may end up with getting a million records back from the database and filter them on your application server.

I don't know if this is the right way, but I tried and it works for me.

Answer 4

I know this is an old question and there is many answers here, but I forced to an issue when I use this answer and an error occurred when I use update-database command in Package Manager Console:

There is already an object named '...' in the database.

and I use these steps to solve this issue:

1. run this command in Package Manager Console:Add-migration intial
2. Under the Migrations folder, you can find ..._intial.cs file, open it and comment or delete any command related to your class you want to map
3. now you can normally use update-database command for any other change to your models

hope it helps.