Efcore: How do I map a Database View?

[gdoron]

I have an hierarchy table that I'm trying to map with EF 7.
I tried almost everything (except probably the right thing) to map the view I already create in the DB but couldn't make it to work.

This is my model:

public class Post
{
    public int Id { get; set; }
    public string Text { get; set; }
    public DateTime PublishDate { get; set; }
    public DateTime? LastChangedDate { get; set; }
    public String UserId { get; set; }
    public ApplicationUser User { get; set; }
    public int ForumId { get; set; }
    public virtual Forum Forum { get; set; }
    public int? ReplyToPostId { get; set; }
    public Post ReplyToPost { get; set; }
    public virtual List<Post> Replies { get; set; }
}

And this is the view I'm trying to map:

CREATE VIEW HierarchyPosts 
as
WITH    cte ( Id, ParentPostId, Depth ) 
              AS ( SELECT   Id,
                            ReplyToPostId,
                            0 as TheLevel
                   FROM     posts
                   where ReplyToPostId is null
                   UNION ALL 
                   SELECT   pn.Id, 
                            pn.ReplyToPostId,
                            p1.Depth +1
                   FROM     Posts pn
                    INNER JOIN cte AS p1 on p1.Id = pn.ReplyToPostId
                 )
select cte.Id as PostId,ParentPostId, Depth, ForumId, LastChangedDate, PublishDate, ReplyToPostId, Text, U.UserName, u.Id as UserId
from  cte 
INNER JOIN POSTS P ON CTE.ID = P.ID
INNER JOIN USERS u ON U.id = p.UserId

What do I need to write in the DbContext to map HierarchyPosts?

BTW How do I write custom SQL as migration script, for stuff that EF currently doesn't support such as views or for stuff that EF will never support such as partitioning.

BTW2, it would be very helpful if EF 7 would finally be able the handle recursive data natively without requiring views or SP .

Thanks

[gdoron]

@divega @rowanmiller , sorry for pinging you on Sunday, but it's quite urgent, I was asked to lecture in a meetup of several early adopting companies about ASP.NET 5 and EF 7, and it should take place this Wednesday...
Thanks!

[ErikEJ]

Mapping Views is not currently supported, and in the Backlog: https://github.com/aspnet/EntityFramework/issues/827
You can call custom SQL from MIgrations using the Sql method

[gdoron]

Thanks @ErikEJ.
I saw issue #827 already, but I didn't imagine there's absolutely no way of mapping views in EF 7 RC (specially that it was opened on Aug 14!).
How can real companies with complicated applications work with EF 7 when in my very simple demo application I created for my lecture there's already a need for views support.

I'm a bit shocked...

@rowanmiller is there any workaround available?

Thanks.

[rowanmiller]

You can tell EF it is a table. Obviously migrations won't create a view for you, but I'm guessing you are mapping to an existing database anyway. If not an existing database then just delete the CreateTable call in migrations and replace with a Sql(string) call to create the view.

Alternatively, you can just use a raw SQL command to select from a view (context.Blogs.FromSql("SELECT * FROM dbo.BlogView")).

[gdoron]

Thanks @rowanmiller, the trick with the "table" worked.
So it's not such a great of a deal if there's a relatively simple workaround.

[jlmelis]

Can you explain the trick?

[rowanmiller]

@jlmelis

You can tell EF it is a table. Obviously migrations won't create a view for you, but I'm guessing you are mapping to an existing database anyway. If not an existing database then just delete the CreateTable call in migrations and replace with a Sql(string) call to create the view.

Alternatively, you can just use a raw SQL command to select from a view ( context.Blogs.FromSql("SELECT * FROM dbo.BlogView") ).

[jlmelis]

@rowanmiller Sorry I should have been more clear. What do you mean by "tell EF it is a table"?

[rowanmiller]

@jlmelis just write the code the same as you would when you map to a table. Create an entity, and if the table name it expects by convention doesn't match the name of your view then use DataAnnotations or the Fluent API to specify the "table" name.

[gdoron]

Steps:

1. Create the POCO-class as you would had it been a table.
2. Add customization like changing the table name as @rowanmiller suggested.

If you are using migrations

1. Create a migration
2. Remove the create table migration script from the Up method and the drop table from the down.
3. Add the create/drop view in the Up/Down instead if you want to manage the view via migrations.

Note that it's a hack and very annoying when you change the View since you need to repeat all these steps on every change.😢

[audacity76]

Found out that you can ignore the view table in migrations by using the model builder. But you must only do that when migration is ongoing. (IsMigration Property is defined by myself)

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

[villecoder]

@audacity76 Any chance you can share your code or some guidance as to how you're handling IsMigration?

[audacity76]

@villecoder I added the IsMigration property as static to the DbContext. Default value is set to true. In my startup.cs File in the Configure method I set the property to false because the migration doesn't hit the method.

I'm sure there is a better way but I didn't found one. Maybe someone here? @rowanmiller

[rowanmiller]

@audacity76 that looks like a reasonable approach.

The other option is to let it scaffold the CreateTable call and then just delete it from the scaffolded migration. EF won't try and re-create it since it uses the model snapshot to diff against (rather than the actual schema). The downside is that it would try and migrate and changes you make to the view model, so you may have to delete future migration operations too.

[gdoron]

@rowanmiller the problem with the other approache is when deleting the snapshot file and all the migrations (a good approach IMHO for every major version) all those tricks will be lost.

[VladanStojanovic]

@audacity76 I think the inverted approach would be better - you set the default to false and configure it to true only for migrations. When you do Enable-Migrations, a Configuration.cs class is added to a Migrations folder and you can set your static property from there. If your migration configuration and entities are in the same project - even better - you can declare the IsMigration internal and keep it hidden from the rest of the world.

[paaland]

I'm involved in a large line-of-business application and we decided to give efcore a try. And daily we regret our choice. I understand the team's desire to create a database agnostic tool. But my guess is that 90% of ef core users are running against MS SQL Server and we want all those SQL server or relational database specific feature that the team does not want to implement.

I feel that ef core is ending up as being useless for all, since it won't commit to what it's supposed to be good at. If you are using relational data I feel that EF 6 and model-first is the only way to go if you want full control over your data. Code first sounds tempting, but how many hours do we need to spend on coaxing the framework to create the table we want it to.

[divega]

@paaland thanks for providing this feedback. Could you name specifically what SQL Server or general relational features you want that EF Core doesn't allow you to use? Is it just mapping to views or something else? (If the latter it might make sense to create a new issue. We don't often revisit already closed issues like this one) Also can you explain how EF6 model-first is giving you more control to create the database you want? I would really want to understand if there is anything we can do to reduce the friction and your feedback can help us prioritize future work.

[tombrown571]

@divega I can't speak for @paaland but I would like to see Views in EF Core, (and EF6 if any work is continuing there) ; also indices with include columns. The underlying problem is performance. Using materialised indexed views can be a great way to improve performance - if only EF could allow this using Code First. I would also like read-only tables (where you can only update in the seed method). We get around some of these issues in EF6 using Sql statements in Up/Down migrations - but it feels hackish - it would be nice to have built-in support for these features.

[divega]

@tombrown571 as I mentioned in my previous comment, we don't look often at already closed issues like this one. I would recommend you search for existing issues on the areas you care about and then +1 on them (I believe there are existing issues in our backlog for the majority of the things you mentioned).