Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to map exisiting sql server view with EF code first

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");
        }
    }
like image 865
Monojit Sarkar Avatar asked Sep 09 '16 12:09

Monojit Sarkar


1 Answers

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.

  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 ovveriding the OnModelCreating method of the Context:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new FooViewConfiguration ());
    }
    
  5. 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();
like image 153
Sampath Avatar answered Oct 13 '22 01:10

Sampath