How to create a view using EF code-first POCO

Question

That simple. I need to create a View using Code First. I found nothing about this on google nor SO. Is there a way to accomplish this?

I need that view to be created and queried using linq, so it's not a solution to create it using an script on Database creation, for example:

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

A work around is also acceptable. I need a way to query entities against non-constant/ non-entities values.

Answer 1

you must manually create the view, just like AnatoliiG stated. (Adding index to a 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 migration script that you can manually modify.

You can use your db context to execute your code in your migration 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 2

Just a heads up, in EF 6.1 (not sure if in earlier or not) there is now a Code First from Database option you can use and it will map to views as well.

I personally have mine in a separate junk project so I can just take out the code I want from it and not impact my project that 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)

It will create it as a Table mapping like Fred was talking about in his answer, but will do all the code for you which is nice. You'll probably want to change the indexes and ordering though.

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