How to retrieve the attribute of related object in linq, select projection

Question

I am trying to retrieve the Person name in my viewmodel while projection in the below code:

// GET api/Tickets
    public IQueryable Get()
    {
        var model = Uow.Tickets.GetAll().OrderByDescending(m => m.DateTimeTag)
            .Select(m => new TicketViewModel
             {
                 Id = m.Id,
                 TicketTitle = m.TicketTitle,
                 TicketBody = m.TicketBody,
                 DateTimeTag = m.DateTimeTag,
                 //AssignedTo = Uow.Persons.GetById(m.AssignedToPersonId).Name,
                 Status = m.Status.ToString(),
                 NoOfReplys = m.Replys.Count()
             });
        return model;
    }

But when I uncomment the AssignedTo line, it gives me the error:

InnerException: { Message: "An error has occurred.", ExceptionMessage: "LINQ to Entities does not recognize the method 'Ticketing.Model.Person GetById(Int32)' method, and this method cannot be translated into a store expression.", ExceptionType: "System.NotSupportedException", StackTrace: " at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) blah blah blah

The TicketViewModel class is:

public class TicketViewModel
{
    public int Id { get; set; }
    public string TicketTitle { get; set; }
    public string TicketBody { get; set; }
    public DateTime DateTimeTag { get; set; }       
    public string AssignedTo { get; set; }
    public string Status { get; set; }
    public int NoOfReplys { get; set; }
}

The actual Ticket class is:

public class Ticket
{
    public int Id { get; set; }
    public string TicketTitle { get; set; }
    public string TicketBody { get; set; }
    public DateTime DateTimeTag { get; set; }
    public int AssignedToPersonId { get; set; }
    public Status Status { get; set; }
    public virtual ICollection<Reply> Replys { get; set; }

}

My desired output is:

[
 {
  Id: 3,
  TicketTitle: "a problem",
  TicketBody: "problem descripted here.",
  DateTimeTag: "2012-04-21T00:00:00",
  AssignedTo: "Peter", <== ATTENTION!!!
  Status: "Open",
  NoOfReplys: 0
 }
]

Here, Peter is the name of the person who its id is in the ticket object. My goal is to show the name instead of personId. may be there is a better way, please help me do that. thanks

Answer 1

In this case I think that your property:

public int AssignedToPersonId { get; set; }

should be:

public Person AssignedToPerson { get; set; }

in your Ticket class. Mapping to the reference is generally better so that you can access properties like this using Linq. This way the line that is giving you trouble can be:

AssignedTo = AssignedToPerson.Name

The reason it isn't working right now is because Entity Framework has no idea how to convert your line:

Uow.Persons.GetById(m.AssignedToPersonId).Name

to a Query expression. By using a reference mentioned above you will instead create a Join between the two tables and get back the desired data in a single query.

The other and probably less attractive option is to store the Id in your View Model and then do a query for the name outside your Linq query. This will work because you have already retrieve items from the database. Untested example below:

public IQueryable Get()
{
    var model = Uow.Tickets.GetAll().OrderByDescending(m => m.DateTimeTag)
            .Select(m => new TicketViewModel
             {
                 Id = m.Id,
                 TicketTitle = m.TicketTitle,
                 TicketBody = m.TicketBody,
                 DateTimeTag = m.DateTimeTag,
                 AssignedToPersonId = m.AssignedToPersonId,
                 Status = m.Status.ToString(),
                 NoOfReplys = m.Replys.Count()
             }).ToList();
    model.ForEach(m => m.AssignedTo = Uow.Persons.GetById(m.AssignedToPersonId).Name);
    return model;
}

Note however that this second method is making an additional query to the database for each Ticket object returned in the first query.