Using SQL View from Entity Base Code First Version 5

Question

I am developing a contact log on a website using VS 2010, MVC3 and EF 5 - entities are created using code in the first place. Data is stored in a SQL Server 2008 R2 database set. I want to show a summary of the contact log and create a view.

CREATE VIEW dbo.ContactLogSummaries AS SELECT CLE.ContactLogEntryID, CLE.CaseID, 'Test' AS ContactName, EU.UserName As OfficeUser, CLE.DateAndTimeOfContact, CLC.Category, CLE.ContactDetails FROM ContactLogEntries AS CLE JOIN ContactLogCategories AS CLC ON CLE.ContactLogCategoryID = CLC.ContactLogCategoryID JOIN Control.dbo.EndUsers AS EU ON CLE.UserID = EU.EnduserID 

The contact log database ( ContactLogEntries and ContactLogCategories ) has two objects and the first entity of the Control.dbo.EndUsers database in another database. The contact history can contain a large number of entries. I want to be able to display only entries for a specific case.

My question has two parts:

• Is it possible to directly use the SQL view to display a summary on a web page (perhaps by reading it in a class)
• Is it possible to create the first code object equivalent to the SQL representation.

Answer 1

Found a simple solution to question 1:

 public class ContactLogSummary { public int ContactLogEntryID { get; set; } public int MaternalCaseID { get; set; } public String ContactName { get; set; } public String OfficeUser { get; set; } public DateTime DateAndTimeOfContact { get; set; } public String Category { get; set; } public String ContactDetails { get; set; } public static List<ContactLogSummary> LoadContactListSummary (int caseID, String connectionString); { MyDataContext dbContext = new MyDataContext(connectionString); return dbContext.Database.SqlQuery<ContactLogSummary> ("SELECT * FROM dbo.ContactLogSummaries WHERE MaternalCaseID = @CaseID ORDER BY ContactLogEntryID DESC", new SqlParameter("CaseID", caseID)).ToList(); } 

He does whatever it takes, although I'm interested in answering question 2. I have a working solution.

Answer 2

You can simply map Entity directly to the view using TableAttribute or ToTable in your Fluent Mappings ...

For example, using data annotations:

 using System; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; public namespace whatever.mynamespace [Table("dbo.ContactLogSummaries")] //<-- this is your view public class ContactLogSummary { ... } }