Entity Framework 6 - Can Views be used properly in EF6?

Question

I have this application that i have to fix and maintain. It has a mobile app, a web app dashboard, some web services (2 layers) and some SQL Stored procedures to load some initial data (big picture). My problems is i have a policy number has 3 location address records. The dashboard is pulling back 9 records of the same location. The issue i believe is in the web service layer that is using EF6 and it's using an Inspection table and a PolicyInfo view. Code did this var iList = dbContext.Inspection.Include("PolicyInfo") and .Where() and a couple OrderBy() ands with a .ToList(). This should work fine if we had two tables with proper navigation properties for EF. however, the view "PolicyInfo" is not a table but a SQL Server view. the Coder that did this seemed to not know you can't include a table and a view. The applications are expecting this object with two layers Inspection with a PropertyInfo object. I need to change this to a join on three or more fields. I have 3 records in the Inspection table with different locations and my dashboard is pulling 9 records with the same address. I have multiple extra records coming from the Inspection table and when i comment back in the Include("PolicyInfo") view i still have 9 records. Initially i thought the include to the PolicyView was causing the issue. Does anyone have a clue as to how to solve this type of issue? I know i can do some type of join but then my data will be flat. I need to keep the object in tack to make the two app work as is.

Answer 1

Hi CS-Shooter,
To use view as an entity, first you will need to add database views to EDM. After adding views to your model then you can work with it the same way as normal entities except for Create, Update, and Delete operations.
Here are some documents you can refer to.
[Create Views]
[Pre-generated mapping views]
Best Regards,
Daniel Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Thanks for the links and feedback Daniel! Is there better source for how to pull in views from SQL Server to EF6? A good book on EF6? I also have to ask EF Power Tools Community Edition was this added to EF itself in Core? I thought i had read it was written by someone from Microsoft and integrated with EF at some point. TIA!!