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

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
– Nerdroid, Commented Feb 15, 2021 at 3:41

grexter89 · Accepted Answer · 2023-09-27 21:41:07Z

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 Sep 27, 2023 at 21:41

grexter89

1,10110 silver badges24 bronze badges

answered Sep 9, 2016 at 13:04

Sampath

66.2k70 gold badges327 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Monojit Sarkar Over a year ago

can u plzz post a small but complete sample code for this scenario.

Monojit Sarkar Over a year ago

see my updated post. i am getting error when run the code. see my full code. thanks

Monojit Sarkar Over a year ago

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 ?

Sampath Over a year ago

Yes,you're right.No need to do that.I have removed it.can you tell me what is your DB view's name ?

Monojit Sarkar Over a year ago

my db view name is vwCustomer.

|

giokoguashvili · Accepted Answer · 2021-01-09 09:09:55Z

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

Collectives™ on Stack Overflow

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 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

my full code as follow

this way i am trying to load data.

UPDATE1

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related