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

Answer 3

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)

Answer 4

You cannot create views with EF Code First approach. If you want to create view then execute creation sql script in Seed method. But you'll still not be able to map entity to this view, except hacking model by creating and droping table with same name as your view will have.

Some helpful links:

• How map objects to a view with EF 4 code first?
• How do I define a database view using Entity Framework 4 Code-First?

Answer 5

You cannot create a view from EF Code First ,you should add the script in the 'seed' script to pre populate the view .