Complex Linq expression with a stored procedure

Question

I am fairly new to MVC4 and I am working on a complex model: a model that contains a property of type IList along with properties of primitive types (strings and ints). The property of type IList should use a stored procedure and the primitive types uses a regular link query. Here is the code for the model:

public class EditUserModel
{
    public IList<UserTranscript> UserTranscripts { get; set; }

    public int? PersonID { get; set; }
    public string UserName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string StateCode { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Here is the code for the UserTranscript class:

public class UserTranscript
{        
    public decimal Score { get; set; }        
    public DateTime CompletionDate { get; set; }
    public string Status { get; set; }
}

Here is my method:

public EditUserModel GetUserRecord(int personid)
    {
        //using (var db = new TceoModel.TceoContext())
        //{

            MyContext db = new MyContext();

            var user = (from p in db.People
                        from pu in db.PersonUsernames.Where(f => f.PersonID == p.UPID).DefaultIfEmpty()
                        from pe in db.PersonEmails.Where(a => a.PersonID == p.UPID).DefaultIfEmpty()
                        from pa in db.Addresses.Where(c => c.PersonID == p.UPID).DefaultIfEmpty()                            
                        from lnr in db.Activities.Where(y => y.ActivityID == un.ActivityID).DefaultIfEmpty()
                        from tr in db.uspTranscripts(personid)
                        where p.UPID == personid

                        select new EditUserModel
                        {
                            PersonID = p.UPID,
                            UserName = pu.Username,
                            Email = pe.Email,
                            FirstName = p.FirstName,
                            MiddleName = p.MiddleName,
                            LastName = p.LastName,
                            Address = pa.Address1,
                            City = pa.City,
                            StateCode = sc.StateAbbr,
                            PostalCode = pa.Zip,
                            Phone = pp.PhoneNumber

                        }).AsEnumerable().Select(s => new UserTranscript() { 

                          **How to return a list of UserTranscripts using the stored procedure db.uspTranscripts(personid)**

                        });

My question is, how can I return the user list of transcripts on the second query using the db.uspTranscripts(personid) stored procedure?

Thanks.

Answer 1

I'm no expert with stored procedures in EF, but I've seen 2 ways to do this.

• For example take a peek at https://msdn.microsoft.com/en-us/library/bb399357(v=vs.110).aspx. They have their example stored procedure written as a function so you could then use it like

  // Usage
  .AsEnumerable().Select(s => db.uspTranscripts(s));
  
  // Stored Procedure
  [Function(Name="dbo.CustOrderTotal")] //probably dbo.uspTranscripts in your case
  [return: Parameter(DbType="Int")]
  public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
  {
      IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
      totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
      return ((int)(result.ReturnValue));
  }
  

• Or you might be able to do it like the last line of code in this guy's answer where you actually reach in and grab the stored procedure to use it https://stackoverflow.com/a/20973919/4875338

The first appears to be the best way to do it if you are willing to rewrite the stored procedure in c#. Good luck!

Answer 2

First of all, try to use below link.

https://www.linqpad.net

It helped me a lot.

Secondly, I think that List have to stay inside

new EditUserModel() { UserTranscripts = tr }