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: #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
3. Create a migration
4. Remove the create table migration script from the Up method and the drop table from the down.
5. 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