How to create a view using the POCO EF code

Question

It's simple. I need to create a View using Code First. I did not find anything about this in google and SO. Is there any way to do this?

I need the view to be created and queried using linq, so this is not a solution to create it using a script to create the database, for example:

var results = from c in db.Customer join v in db.MyView on c.Id equals v.Id select c; 

Work around is also possible. I need a way to query entities regarding constant / non-entity values.

Answer 1

You cannot create views using EF Code First. If you want to create a view then create a sql script in Seed . But you still can’t match the entity with this view, with the exception of the hack model, creating and dropping a table with the same name as your view.

Some useful links:

• How to match object objects with a view with EF 4 code first?
• How to determine database view using Entity Framework 4 Code-First?

Answer 2

you must manually create the view, just like Anatoly said. ( Adding an index to the table ).

You add the name of the view as an attribute to your class

 [Table("UserDTO")] public class UserDTO { /* Class code here */ } 

You can create an empty migration by specifying the -IgnoreChanges attribute at the end

 Add-Migration MigrationName -IgnoreChanges 

This gives you an empty script migration that you can change manually.

You can use your db context to execute code in your wrapper script

 public partial class editUserDTO : DbMigration { public override void Up() { string script = @" CREATE VIEW dbo.UserDTO AS SELECT p.PersonId AS UserId, p.FirstName, p.LastName, u.UserName FROM dbo.Users u INNER JOIN dbo.People p ON u.PersonId = p.PersonId"; BloggingContext ctx = new BloggingContext(); ctx.Database.ExecuteSqlCommand(script); } public override void Down() { BloggingContext ctx = new BloggingContext(); ctx.Database.ExecuteSqlCommand("DROP VIEW dbo.UserDTO"); } } 

Answer 3

Just in mind, in EF 6.1 (not sure if it’s earlier or not) there is now a Code First from Database option that you can use, and it will also be displayed on views.

I personally have my own separate project, so I can just extract the code that I want from him and not affect my project, which actually uses the database. To use it Add a New file to your project -> Data -> ADO.NET Entity Data Model

Then select the Code First from Database option and select your views (and other tables if you want)

He will create it in the form of a table comparison, which Fred talked about in his answer, but will do all the code for you, which is good. You probably want to change the indexes and order.

Then just call Sql(@"YOUR VIEW CREATE SQL HERE") on Up and add Sql(@"DROP STATEMENT HERE") on Down

Answer 4

Many good ideas from the official releases of EF7 :

1) You do not have a DbSet and instead have a property or extension method

A) Property

 class YourContext { public IQueryable<YourView> YourView { get { return this.Database.SqlQuery<YourView>("select * from dbo.YourView"); } } } 

B) Extension Method

 static class YourContextExtensions { public static IQueryable<YourView>(this YourContext context) { return context.Database.SqlQuery<YourView>("select * from dbo.YourView"); } 

2) Apparently you can make the migration process ignore some dbsets

 protected override void OnModelCreating(ModelBuilder modelBuilder) { if (IsMigration) modelBuilder.Ignore<YourViewTable>(); ... } 

(All of the above have not been verified)

Answer 5

You cannot create a view from EF code. You must first add the script to the "seed" script to pre-populate the view.