how to use views in code first entity framework [closed]

Question

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

Kakalina

None of the answers below explain how to create a view using EF migrations. See this answer for a similar question. – Byng

Here is a thread with exactly same question. -#13594345 – Araucaria

Try my solution. It prevents migration generation for tables marked as views – Montenegro

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 ());
   }
   

Commotion

+1 for not assuming that "Code First" == auto database generation – Nevernever

Even if it's auto-gen, you can script the views in your IDatabaseInitializer and so long as your using an "class ViewName : Entity" that matches up like this answer is suggesting, you'll be fine. – Bathe

@DaveJellison would you care to elaborate, or provide a link on adding a view as part of an IDatabaseInitializer – Holothurian

@Ashkan There is no step by step solution in this answer because the answer is: you can use views in the same way you are using tables, provided that you don't need to update the data. I assumed that Sagar knows how to use tables with EF because this is not the object of the question. – Commotion

You mean just create a class like other entities for the view, for example named FooView and add a DbSet<FooView> property in my DbContext class, and entity framework knows to map it to the view (let's say we have a view named dbo.Foo) ?? – Pity

Yes, and you can use a FooViewConfiguration file to set a different name for the view (using ToTable("Foo"); in the constructor) or to set particular properties. All the same as if you were working with a table and not a view. – Commotion

Here is your step by step solution :) – Pity

what happens if you do modelBuilder.Entity<ViewClass>().ToTable("VIEW_NAME"); ? – Populous

So what if you do not want to map a view to your EF model but you really need let say a materialized view for performance reasons? – Gilbertine

Is it just me, or everyone is getting empty table created by the migration? Is there a way to avoid that? – Belostok

Just making sure here, is this solution required us to create View on the SQL database beforehand externally? Is it possible to define view in the code and have it populate in the database through Add-Migration/Update-Database command? – Mangrove

Where do I specify the foreign key on my table to the view? So I have a "Post" table that I need to join to "UserView". The Post table has a userID but in order for me to map Post table to the User View there needs to be a relationship and this answer does not tell me how to specify that relationship – Mountford

Name of view in SQL cannot match POCO name, or migrations will delete your view, and replace it with a blank table. At least, I'm pretty sure that's what happened the first time I tried this. – Donate

msdn.microsoft.com/en-us/magazine/dn519921.aspx from Julie Lerman shows how to create a view in CF migrations – Linguistics

A few things. 1. This answer fails to mention you have to create the view manually using SQL, this can be done using a migration. 2. You don't have to configure the view name if the class name matches the view name. 3. You can use DataAnnotations like so: [Table("myView")], this is arguably simpler than using creating a EntityTypeConfiguration. – Byng

Kremena Lalova just comment CreateTable statement in migration configuration in method named Up – Choler

My view doesnt have ID column. It did not work for my issue – Cashmere

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; }
    }
}

Selfassured

This is the same as the accepted answer, except this uses DataAnnotations while the accepted answer uses the EF Fluid API. – Byng

Actually no it isn't. I tried, without success, on the accepted answer and it didn't work well for me. But then I'm using Migrations so this may have impacted things. I found I had to do my migrations first THEN add my view class since it already existed in the database. We'd handle it exactly the same way if we already had existing tables in the database. Since a view is a "virtual table" the table syntax in the Entity Framework still works. – Barrettbarrette

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.

Satisfy

One of the reasons I'd like to use views is that the SQL generated by EF is not always 'nice' - we have some inheritance hierarchies in our model (found out about the pitfalls too late...) and using views allows us to manually create the SQL. Just a counterpoint as to why a view would be preferable – Donny

Other reason not to do this might be the usage of recursive common table expressions, which are not available in LINQ. But otherwise this is a good advice for simpler scenarios. – Janinajanine

Using a property instead of a view is not an option if you want to make use of the benefits of an indexed view. – Byng

"you are not limited by only using select statements". What do you mean by this? Anything you can do with LINQ can be done using SELECT statements, the same can't be said for the other way around. – Byng

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.

Word

Thanks! This really helped! As an extra, instead of just removing code generated with EF Migrations, you can instead add there migrationBuilder.Sql("CREATE OR REPLACE VIEW ...); So that colleagues can also use it to upgrade their database. – Sourdine

This is just an alternatif, is there any method to exclude class from migration proses? Just like NotMapped in DataAnnotations for method. – Guarnerius

In your OnModelCreating add if (IsMigration) modelBuilder.Ignore<ViewEntityName>(); Source: c-sharpcorner.com/article/… – Abed