How to create a view using EF code-first POCO

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

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

A lot of good insights from the official issues thread of EF7:

1) Don't 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 have the migration process ignore certain dbsets

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

(All the above are untested)

How to create a view using EF code-first POCO

Related

Recent Posts