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

Question

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.

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

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

Solution 2

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

Comments

[myhrmans]

Nothing says SQL Injection like string concatenation!

[Peter Smith]

No SQL Injection here. One - this is an illustrative example; two, CaseID is not returned from the website. Can I have my plus one back please?

[myhrmans]

Peter - I can understand that this code is illustrative in nature, but, there's no way that a reader could know whether or not CaseID comes from something that is either posted to a form (e.g. and edit, or comes in from a query string). I'll put your plus back if you parameterize the string. It will make your code a little longer, but, it will be a solid example for the community. It will also allow SQL Server to create an execution plan for this query for subsequent reuse.

[Peter Smith]

@SwampyFox OK, finally got round to adding a parameter to the query. (Can I have my plus back please?)

[Monojit Sarkar]

if i have multiple parameter then how could i pass ? can i pass like ("SELECT * FROM dbo.ContactLogSummaries WHERE MaternalCaseID = @CaseID ORDER BY ContactLogEntryID DESC", new SqlParameter("CaseID", caseID), new SqlParameter("TestID", TestID)).ToList(); can i pass param separated by comma?

[Peter Smith]

@MonojitSarkar You should ask this as a separate question. Looking quickly at your example, I think what you need is alist of parameters rather than using comma separated items.

[Laurence]

@MonojitSarkar You should add parameters in the way Entity Framework does: @p0, @p1 etc. You can use the params overload: dbContext.Database.SqlQuery<ContactLogSummary>("SELECT * FROM dbo.ContactLogSummaries WHERE MaternalCaseID = @p0 AND TestID = @p1 ORDER BY ContactLogEntryID DESC", caseId, testId)