Entity Framework Code First - T-SQL Views

Question

I'm creating a SQL View on my Configuration Seed

protected override void Seed(QuiverContext context)
{
    context.Database.ExecuteSqlCommand(DatabaseScripts.Views.MyView);
}

Now I want to add a DBSet to my DbContext that represents my View. I read that one can do this using then the Entity like a regular table.

So I tried but it requires me to add a migration which I did, but then the update-database command fails when creating the view since a table is created first.

You should also understand that you can't just treat a View like a table... there are a lot of issues with Views, particularly since they may not be updatable, and EF can't tell what the primary key might be so it treats all non-nullable fields as primary keys. In general, I find using views with EF to be unworkable, but some people find them acceptable. — Erik Funkenbusch
– Erik Funkenbusch, Commented Apr 14, 2015 at 15:26

Richard · Accepted Answer · 2015-04-14 15:23:05Z

1

It looks like you're trying to create a view in your Seed method. This isn't the way to create a view (remember the seed method runs every time ANY migration runs).

The better way to be would be to add a migration. This will create a code file containing CreateTable lines, which will make your table. Just remove these lines, and replace them with a call to create your view.

You can execute custom Sql inside a migration using the Sql command, for example...

Sql("CREATE VIEW myView.....");

If you want to make things a bit more robust, you can create an extension for migrations which allows you to call CreateView.

answered Apr 14, 2015 at 15:23

Richard

30.7k12 gold badges79 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hugo Hilário Over a year ago

Well I still can use the seed, I just need to make sure my migration Up doesn't do anything. Having this logic on seed has some benefits since all my scripts will be together there (sp's, views, triggers) and every-time a change needs to be done to these scripts I don't need to pollute my migrations. But still I understand the advantages of having it on the migration, thank you so much.

Richard Over a year ago

I tend to use the Sql method for all of the things you mention - the only reason I would use Seed is if you actually need to use EF, rather than adding things using SQL. Putting code in the migrations means you don't have to worry about checking whether they should run.

Collectives™ on Stack Overflow

Entity Framework Code First - T-SQL Views

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related