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
– Jorge

2013-06-03 05:33:21 +00:00
Commented Jun 3, 2013 at 5:33 — Jorge
– Jorge, Commented Jun 3, 2013 at 5:33

Cisco Cabe · Accepted Answer · 2013-06-03 08:29:45Z

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 3, 2013 at 8:29

Cisco Cabe

5845 silver badges10 bronze badges

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

3 Comments

Naomi Over a year ago

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.

jonas Over a year ago

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...

aj go Over a year ago

What about for keyless views? Some functions i searched over are not existing in this EF i use..

Peter · Accepted Answer · 2014-07-09 20:15:56Z

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 9, 2014 at 20:15

Peter

9,7127 gold badges66 silver badges109 bronze badges

2 Comments

John Over a year ago

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.

Mike Devenney Over a year ago

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> UserFolders { get; private set; } (assuming only public get since it would be a read only view of data?)

Collectives™ on Stack Overflow

Mapping SQL view in code-first approach

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related