Create SQL View With Entity Framework 6 Code First

Question

I am new to Entity Framework 6 Code First and am trying to perform what I thought would be a simple task. I want to create a SQL View and then have an Entity in my database context that I can use to query the view.

I have tried articles such as this but the key difference in my case is that the SQL View is not an existing view coming from another existing database.

I examined the proposition made in this article but it seems like overkill to me that I would need to create some extension methods to do something as simple as create a view/entity combo and use it in my database context.

Am I missing something? I know it would be much easier if I weren't using Code First but please keep in mind it's Code First and I am trying to create a view, not reuse one from an existing database.

Answer 1

Colin and Kevin, Thank you for the link to your answer on the other post and your concise answer. I have used several resources to finally create a queryable entity based on a new SQL view. Just in case anyone else is new to EF 6.0 Code First and is just getting their feet wet, I do have a few steps that will hopefully benefit others in the future.

1. It may seem obvious to more seasoned Entity Framework developers, but in order to execute the 'Migration' approach you need to disable automatic migrations and actually dive into the guts of the Code First Migrations inner workings. Since automatic migrations is turned on out of the box, I had already created a fairly complex database with seed scripts all relying on automatic migrations and rebuilding the database on every run of my application. This post helped me wipe my migrations history and get to square 1 with automatic migrations turned off (I went with the web.config approach in case you were wondering)
2. After I had cleared my migrations information, I deleted the mdf from within solution explorer. That guaranteed that I wouldn't run into any problems when running Update-Database (further down the list of steps).
3. In the Package Manger console, I then executed Add-Migration Initial to generate an "Initial" migration. The result of this was the editable Up and Down methods as described in Colin's answer. I then followed the steps in Colin's answer by commenting out the table create statement (Entity Framework tries to create a table but we really want to create a view and map it to the Entity) and inserting my own view create sql statement at the end of the Up method. It's important to put the create statement after the creation of any tables that it may depend on. I also performed my Seed activities in the Configuration.Seed method instead of in my Context's Seed method. I see how this would be important if you were dealing with multiple migrations. Finally, as Colin suggested I added the table mapping to my context's OnModelCreating event.
4. The final step in this was to actually apply the migration to the database. In order to do that, in the Package Manager console you execute the Update-Database command. That statement will rebuild the database with the "Initial" migration you created and edited in earlier steps.

It still surprises me that I need to do all of this custom work to create a view and map it to an entity with Code First, but at the end of the day it was helpful in getting me started on migrations as you can only rely on the "automatic migrations" for so long anyways.

Answer 2

You can manually add the sql to create the view to a migration then consume it as per your first link.

Answer 3

The answer in the link provided by Colin does the job.

In case there are lots of views to be created, it can be a good idea to save the view queries in separate files and add them in a resource (.resx) file instead of hard-coding the sql queries in the Migration Up() method.

For e.g.

public override void Up()
{
    Sql("ResourceFileName.ResourceName");
}

instead of hard coding like

{
        Sql("EXEC ('CREATE View [dbo].[ClientStatistics] AS --etc");
}