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; }
}
Accepted Answer
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...
Popular Answer
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());
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
