How to map exisiting sql server view with EF code first

Question

i am fairly new in EF and learning EF code first. i am looking for a knowledge to map exisiting sql server view with EF code first. i have map my view with POCO but getting the below error.

when i try to fetch data from view then got the below error thrown

Additional information: The model backing the 'TestDBContext' context has changed since the database was created. Consider using Code First Migrations to update the database

my full code as follow

public class TestDBContext : DbContext
    {
        public TestDBContext()
            : base("name=TestDBContext")
        {
        }

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

        public DbSet<vwCustomer> vwCustomer { get; set; }
    }

public class vwCustomerConfiguration : EntityTypeConfiguration<vwCustomer>
{
    public vwCustomerConfiguration()
    {
        this.HasKey(t => t.CustomerID);
        this.ToTable("vwCustomer");
    }
}

      public class vwCustomer
        {
            public int CustomerID { get; set; }
            public string FirstName { get; set; }

        }

this way i am trying to load data.

    using (var db = new TestDBContext())
    {
        var listMyViews = db.vwCustomer.ToList();
    }

guide me what i am missing in code for which error is throwing. thanks

UPDATE1

When i issue Add-Migration "My_vwCustomer" then i saw new migration code added as below one. it seems there is no migration is pending.

   public partial class My_vwCustomer : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.vwCustomers",
                c => new
                    {
                        CustomerID = c.Int(nullable: false, identity: true),
                        FirstName = c.String(),
                    })
                .PrimaryKey(t => t.CustomerID);

        }

        public override void Down()
        {
            DropTable("dbo.vwCustomers");
        }
    }

I solved migration generation problem. [See my answer](https://stackoverflow.com/a/62287959/5200896) — giokoguashvili, Jun 09 '20 at 17:01
To prevent migration from being added for your view you can use `.ToView()` instead of `.ToTable()` within the DbContext, change `this.ToTable("vwCustomer");` to `this.ToView("vwCustomer");` in your code — Nerdroid, Feb 15 '21 at 03:41

score 10 · Answer 1 · edited May 23 '17 at 10:29

10

OP's Feedback :

When i generate the view with ADO.Net Entity model wizard then everything works fine.

You can do it as shown below.

Note : I have picked the 1 to 4 from this post.

Create a POCO class for the view; for example FooView
Add the DbSet property in the DbContext class

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

}

Add the FooViewConfiguration file to the modelBuilder, for example ovveriding the OnModelCreating method of the Context:

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

According to the above configuration,now your table is this.ToTable("myView");.In other words myView.

Here is the EF query to retrieve all the data on the myView table.

var listMyViews = yourDbContext.myView.ToList()

Your projection may be like this :

var query = yourDbContext.myView
        .Select(v=> new
        {
            ID = v.ID,
            EmpName = v.EmpName,
            Salary = v.Salary 
        }).ToList();

edited May 23 '17 at 10:29

Community

1
1

answered Sep 09 '16 at 13:04

Sampath

63,341
64
307
441

can u plzz post a small but complete sample code for this scenario. – Monojit Sarkar Sep 09 '16 at 13:06
see my updated post. i am getting error when run the code. see my full code. thanks – Monojit Sarkar Sep 09 '16 at 13:28
why do i need to migrate because i have a view exist in db table. migrate create db object but what if the object already exist ? – Monojit Sarkar Sep 09 '16 at 13:39
Yes,you're right.No need to do that.I have removed it.can you tell me what is your DB view's name ? – Sampath Sep 09 '16 at 13:47
my db view name is vwCustomer. – Monojit Sarkar Sep 09 '16 at 13:52
OK then it should be mapped automatically.do you have any other pending migrations ? you can see it by just run `PM> Add-Migration "My_Tables"` command. – Sampath Sep 09 '16 at 13:55
which line i need to remove. this line is in my dbcontext class `public DbSet vwCustomer { get; set; }` – Monojit Sarkar Sep 09 '16 at 14:24
When i generate the view with ADO.Net Entity model wizard then everything works fine. just do not understand what was the problem. many thanks for your help. – Monojit Sarkar Sep 09 '16 at 15:11
Great.I have updated the above post with your feedback.if you need to add more please let me know. – Sampath Sep 09 '16 at 15:25

giokoguashvili · Answer 2 · 2021-01-09T09:09:55.517

Configure view as a table and use this custom generator to prevent migration generation for tables marked as views

public class SkipViewGenerator: CSharpMigrationCodeGenerator
{
    protected override void Generate(CreateTableOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(RenameTableOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(MoveTableOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(DropTableOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(AddColumnOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Table))
            base.Generate(operation, writer);
    }

    protected override void Generate(DropColumnOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Table))
            base.Generate(operation, writer);
    }

    protected override void Generate(DropPrimaryKeyOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Table))
            base.Generate(operation, writer);
    }

    protected override void Generate(AlterColumnOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Table))
            base.Generate(operation, writer);
    }

    protected override void Generate(AddPrimaryKeyOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Table))
            base.Generate(operation, writer);
    }

    protected override void Generate(AlterTableOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(CreateIndexOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    protected override void Generate(DropIndexOperation operation, IndentedTextWriter writer)
    {
        if (!IsView(operation.Name))
            base.Generate(operation, writer);
    }

    private bool IsView(string tableNameWithSchemaName)
    {
        var tableName = DatabaseName.Parse(tableNameWithSchemaName).Name;
        var schemaName = DatabaseName.Parse(tableNameWithSchemaName).Schema;
        return schemaName.Contains("View");
    }
}

Usage example

internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        CodeGenerator = new SkipViewGenerator();
    }

    protected override void Seed(MyDbContextcontext)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method
        //  to avoid creating duplicate seed data.
    }
}

DatabaseName.Parse implementatnion you can get from Github sources

How to map exisiting sql server view with EF code first

my full code as follow

this way i am trying to load data.

UPDATE1

2 Answers2

Linked