Mapping Database Views to Ef 5.0 Code First W/Migrations

Mapping Database Views to EF 5.0 Code First w/Migrations

You have specified that the ClientStatisticsView entity should be mapped to a table named "ClientStatistics". So entity framework will generate a migration containing an instruction to create that table. But you have independently created that view in the database so to prevent the error you are getting you should remove the CreateTable instruction from the Up migration.

I think a better way to do it is to create the view in the migration by running sql like this:

public override void Up()
{
    Sql("EXEC ('CREATE View [dbo].[ClientStatistics] AS --etc"
}

public override void Down()
{

    Sql(@"IF  EXISTS (SELECT
                        *
                    FROM sys.views
                    WHERE object_id = OBJECT_ID(N'dbo.ClientStatistics'))
                    DROP VIEW dbo.ClientStatistics)")
}

That way your views and tables are specified in one place and you can safely migrate up and down

Reference

http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/

Entity Framework Code First with an existing database view

There is no configuration which satisfies both requirements, and I don't see how Database First (or Code Second) would help, since the problem is with migration, not mapping.

The requirement #2 can easily be satisfied by mapping the view as table (either conventionally, [Table] data annotation or ToTable fluent API). For EF CRUD operations it really doesn't matter if the actual db object with that name is a table or view, as soon as it supports the corresponding SQL commands.

The requirement #1 cannot be satisfied by the above solution, but can easily be workaround. Since EF Core requires pre created code migrations (no automatic migrations like EF6), you can manually edit the migrations which contains CreateTable in Up / DropTable in Down and simply remove them.

Actually there is also a solution based on custom MigrationSqlGenerator, but it's more complicated and doesn't worth the effort just for a single view.

how to use views in code first entity framework

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

How to use SQL Server Views with EF Code First Migrations

Not entirely sure this is best practice, and someone else might have a better solution, but I got around this by wrapping the entire view creation script in an Execute, thus:

var sql = "CREATE VIEW [dbo].[TheViewName] AS ......."
sql = sql.Replace("'", "''"); // sanitize the sql string
sql = string.Format("EXECUTE sp_executesql N'{0}'", sql);
Sql(sql);

How to create a view using EF code-first POCO

You cannot create views with EF Code First approach. If you want to create view then execute creation sql script in Seed method. But you'll still not be able to map entity to this view, except hacking model by creating and droping table with same name as your view will have.

Some helpful links:

• How map objects to a view with EF 4 code first?
• How do I define a database view using Entity Framework 4 Code-First?

Mapping SQL view in code-first approach

A view can be mapped as a table. It should be something like:

public class UserFoldersMap : EntityTypeConfiguration<UserFolders>
{
    public UserFoldersMap()
    {
        this.ToTable("view_name");

        this.HasKey(t => t.Id);                        
    }
}

I hope help you...

---

Related Topics