Mapping SQL view in code-first approach

Question

I have a SQL view:

WITH DirectReports (ID,ParentFolderID, ParentFolderName,FolderID,FolderName,OwnerOCID,OwnerArName,OwnerEnName,FolderType,LEVEL)
                            AS
                            (
SELECT        e.Id AS ID,cast(cast(0 AS binary) AS uniqueidentifier) AS ParentFolderID, cast('MainFolder - ' + MainFolders.enName AS nvarchar(250)) AS ParentFolderName, 
                         e.Id AS FolderID, e.Name AS FolderName, WorkSpaces.Owner_Id AS OwnerOCID, OrgCharts.arName AS OwnerArName, OrgCharts.enName AS OwnerEnName, 
                         MainFolders.Type AS FolderType, 0 AS LEVEL
FROM            WorkSpaceFolders AS e INNER JOIN
                         MainFolders ON MainFolders.RootFolder_Id = e.Id INNER JOIN
                         WorkSpaces ON WorkSpaces.Id = MainFolders.WorkSpace_Id INNER JOIN
                         OrgCharts ON OrgCharts.Id = WorkSpaces.Owner_Id
WHERE        e.Root = 1 AND e.Parent_Id IS NULL
UNION ALL
SELECT        e.Id AS ID,e.Parent_Id AS ParentFolderID, d .FolderName AS ParentFolderName, e.Id AS FolderID, e.Name AS ChildFolderName, d .OwnerOCID, d .OwnerArName, 
                         d .OwnerEnName, d .FolderType, LEVEL + 1
FROM            WorkSpaceFolders AS e INNER JOIN
                         DirectReports AS d ON e.Parent_Id = d .FolderID)
    SELECT        *
     FROM            DirectReports

and I'm using code first migrations to my database - how can I map a view to the following entity?

public class UserFolders
{
    public Guid ID { get; set; }
    public Guid ParentFolderID { get; set; }
    public string ParentFolderName { get; set; }
    public Guid FolderID { get; set; }
    public string FolderName { get; set; }
    public Guid OwnerOCID { get; set; }
    public string OwnerArName { get; set; }
    public string OwnerEnName { get; set; }
    public int FolderType { get; set; }
    public int LEVEL { get; set; }
}

I think that's only possible using database-first approach – Jorge Jun 03 '13 at 05:33 — Jorge, Jun 03 '13 at 05:33

score 30 · Accepted Answer · answered Jun 03 '13 at 08:29

30

A view can be mapped as a table. It should be something like:

public class UserFoldersMap : EntityTypeConfiguration<UserFolders>
{
    public UserFoldersMap()
    {
        this.ToTable("view_name");

        this.HasKey(t => t.Id);                        
    }
}

I hope help you...

answered Jun 03 '13 at 08:29

Cisco Cabe

584
5
10

I've tried similar approach. It doesn't seem to work for me. We're using mapped views and they do not seem to be generated correctly for the view. – Naomi Jan 16 '15 at 04:02
1

I needed to `Add-Migration` with this new change, and delete the `CreateTable()` method from the `Up()` function. -- Found this out after A LOT of searching... – jonas Jan 11 '17 at 08:10
What about for keyless views? Some functions i searched over are not existing in this EF i use.. – aj go Jan 24 '21 at 11:47

score 1 · Answer 2 · answered Jul 09 '14 at 20:15

1

I found that if my mapping didn't perfectly match my view then it would error trying to generate the "table", since it thought it already existed or didn't match the definition in my code.

I used the Reverse Engineer Code First feature in EF Power Tools and copied the model and model Map files it generated for my view. These worked without issue.

Then, the final step, I added the Map file during your OnModelCreation method in your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new UserFoldersMap());
}

answered Jul 09 '14 at 20:15

Peter

9,643
6
61
108

First time I've heard of EF Power Tools , gave it a try but it has an error saying something about my context does not contain valid characters. – John May 07 '15 at 12:14
Two questions: 1) When you map an entity in this way, how do you access it? 2) Do you need to create it in the Context class? As in: public DbSet UserFolders { get; private set; } (assuming only public get since it would be a read only view of data?) – Mike Devenney Nov 16 '16 at 20:59

Mapping SQL view in code-first approach

2 Answers2

Linked