Call sql view from WCF web service in EF 4.1

Question

I working to code first technique with EF4.1, WCF webservice, and Sql azure. To improve the performance i wanted to create the View to fetch data at server side. but according to this we can not use sql View with EF code first. Because the database and models are defined. it has the data. to re create and dump data for the only creating view is time taking process.

so i just created the View explicitly at sql server. now I want to call that view from my wfc web service. Now i want to access that view as dataset in WCF. need guidlines. and right approach

Answer 1

Could you define a table in codefirst with the same columns, then create a custom initializer that drops the table from the database and creates it again as a view?

Then you should be able to query it just like normal.

Edit Update to show working example

public class User
{
  public int Id { get; set; }
  public string Email { get; set; }
}

public class UserView
{
  public int Id { get; set; }
  public string Email {get; set;}       
}

public class TestContext : DbContext
{
  static TestContext()
  { 
    Database.SetInitializer<TestContext>(new DatabaseInitializer());
  }

  public DbSet<User> Users { get; set; }
  public DbSet<UserView> UserView { get; set; }
}


class DatabaseInitializer : DropCreateDatabaseIfModelChanges<TestContext>
{
  protected override void Seed(TestContext context)
  {
    base.InitializeDatabase(context);
    context.Database.ExecuteSqlCommand("drop table UserViews");
    context.Database.ExecuteSqlCommand(@"CREATE VIEW [dbo].[UserViews] AS SELECT * 
      from [dbo].[Users]
      GO");
    context.Users.Add(new User() { Email = "test@test.com" });
  }     
}

...

using (TestContext context = new TestContext())
{
  context.UserView.ToList(); //contains all the users
}