CODEWITHSUNDEEP
c#asp.net-mvc-3sql-server-2008entity-framework-5
Peter Smith
Peter Smith

Reputation: 5550

Using an SQL View from an Entity Framework Code First version 5

I am developing a contact log in a website using VS 2010, MVC3 and EF 5 - the entities are created using code first. The data is stored in an SQL Server 2008 R2 set of databases. I want to display a summary of the contact log and have created 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

There are two entities in the Contact Log database (ContactLogEntries and ContactLogCategories) and a database first entity Control.dbo.EndUsers in another database. The contact log could contain a large number of records. I want to be able to display just the records for a specific case.

My question is in two parts:

  1. Can I use the SQL view directly to display a summary on a web page (perhaps by reading it into a class)
  2. Can I create a code first object equivalent to the SQL view.

Upvotes: 7

Views: 32398

Answers (2)

Peter Smith
Peter Smith

Reputation: 5550

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();
    }

It does all that's required so, although I'm interest in an answer to question 2 I have a working solution.

Upvotes: 9

Brandon
Brandon

Reputation: 868

You can just map the Entity directly to the view using TableAttribute (data annoations), or ToTable in your Fluent Mappings...

For example using data annotions:

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

Upvotes: 23

Related Questions