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.

Solution 1

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?

Solution 2

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

Solution 3

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

Solution 4

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)

View more solutions

Comments

[Chuck Norris]

create a view: create an sql view, just like you code an entity and it's translated as a Table on SQL.

[Stafford Williams]

Shouldn't the CREATE only should be in Up() and the DROP only be in the Down()?

[Fred]

hi there... Attempted to correct the code I wrote in this post, (removed ctx.Database.ExecuteSqlCommand(script); from the "Down" method) but was rejected by 3 users.

[Monojit Sarkar]

how to map my existing sql server to EF code first ? looking for good example. thanks

[Jim Kiely]

I did this and I get an error "Unable to update database to match the current model because there are pending changes and automatic migration is disabled." How do you resolve this?

[Kind Contributor]

This was an unimaginative answer. Workarounds were probably possible back in 2012. Make sure you look at the other answers.

[Max]

A point doesn't work Cannot implicitly convert type 'Syste.Data.Entity.Infrastructure.DbRawSqlQuery .. to ...<model> you must change IQueryable to IEnumerable

[Kind Contributor]

Tack, asqueryable to the end after sqlquery function

[Ortund]

Hi! Please revise your answer as it doesn't actually answer the OP's question but instead just indicates that what the OP wants to do can't be done. If you need help, check out how to answer in the help section.

[Rudey]

Instead of creating a context and calling ctx.Database.ExecuteSqlCommand, you can just use the Sql(...) function inherited from DbMigration.

[Rudey]

Won't the changes ignored by -IgnoreChanges show up in the next added migration?

[Rudey]

I don't think this belongs in the seed method. Creating a view should be done in an EF migration.

[Rudey]

No, don't chain AsQueryable after SqlQuery. That won't provide you with a dynamic query object. When using SqlQuery as suggested, all records will be selected and loaded into memory, even though the code filters them with a Where or uses FirstOrDefault... See this answer.

[Sebas]

Solution number 2 tested and working here. I have used stackoverflow.com/a/31102959/1291428 as an inspiration for setting the isMigration flag.

[Natalie Perret]

@Todd B cannot compile, where did you manage to get "this" in a static class without being in the method signature? =]

[Kind Contributor]

@Ethouarn fixed

[uonchiu]

Maybe missings steps, but I had to add Dbset of the new view class to the DbContext before the "Add-Migration" step. Also, after editing the empty migration script to implement the add and drop of the view, I had to drop the view manually and the run "Update-Database" to make sure I didn't make a mistake in the script and to complete the migration/database-update pair.

[Heretic Monkey]

This answer is the same as this one from four years previous.

[Barry]

SqlGenerator w/ Table Annotations anyone? strange answer.

[Richard Barraclough]

What is "the seed method"?