102

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

Improve this question
3
  • 3
    None of the answers below explain how to create a view using EF migrations. See this answer for a similar question. Commented Aug 2, 2017 at 8:28
  • Here is a thread with exactly same question. -stackoverflow.com/questions/13593845/… Commented Jan 22, 2020 at 14:39
  • Try my solution. It prevents migration generation for tables marked as views Commented Jun 9, 2020 at 17:05

4 Answers 4

Reset to default
105

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 ());
}
Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

+1 for not assuming that "Code First" == auto database generation
@DaveJellison would you care to elaborate, or provide a link on adding a view as part of an IDatabaseInitializer
Is it just me, or everyone is getting empty table created by the migration? Is there a way to avoid that?
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?
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.
|
37

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; }
    }
}
Improve this answer

2 Comments

This is the same as the accepted answer, except this uses DataAnnotations while the accepted answer uses the EF Fluid API.
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.
14

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.

Improve this answer

4 Comments

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
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.
Using a property instead of a view is not an option if you want to make use of the benefits of an indexed view.
"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.
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.

Improve this answer

3 Comments

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.
This is just an alternatif, is there any method to exclude class from migration proses? Just like NotMapped in DataAnnotations for method.
In your OnModelCreating add if (IsMigration) modelBuilder.Ignore<ViewEntityName>(); Source: c-sharpcorner.com/article/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.