1

Having an issue with projection and getting child objects to load. The following is simplified code to represent the logic I'm trying to implement, not the actual code.

public class TicketItem
{
    public int TicketItemId { get; set; }
    public string TicketReason { get; set; }
    public Station Station { get; set; }
    public TicketOwner TicketOwner { get; set; }
}

public class Station
{
    public int StationId { get; set; }
    public string Name { get; set; }
}

public class TicketOwner
{
    public int TicketOwnerId { get; set; }
    public Employee Employee { get; set; }
    public Organization Organization { get; set; }
}

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Organization
{
    public int OrganizationId { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

public class CommonReasons
{
    public int CommonReasonId { get; set; }
    public string Reason { get; set; }
}


public TicketItem GetById(int id)
{
    var query = from i in _dataContext.TicketItems
                                    .Include("Station")
                                    .Include("TicketOwner.Employee")
                                    .Include("TicketOwner.Organization")
                join r in _dataContext.CommonReasons on i.TicketReason equals r.CommonReasonId.ToString() into r1
                from r2 in r1.DefaultIfEmpty()
                where i.TicketItemId == id
                select new TicketItem {
                    TicketItemId = i.TicketItemId,
                    TicketReason = r2.Reason == null ? i.Reason : r2.Reason,
                    Station = i.Station,
                    TicketOwner = i.TicketOwner
                };
    return query
            .AsNoTracking()
            .FirstOrDefault();
}

Most the code is self-explanatory. The part that is indirectly causing the trouble would be the relationship between TicketItem.TicketReason property (a string) and the CommonReasons entity. From the user interface side, the end-user has an input field of "Reason", and they can select from "common" reasons or input an adhoc reason. They original developer chose to have the TicketReason property contain either the key ID from the CommonReasons table (if the user selected from drop-down) or the adhoc reason typed in.

So, to handle this logic in the linq query, the only way I have found is to do a left join between TicketItem.TicketReason and CommonReasons.CommonReasonId, then use projection to modify the TicketReason column returning either the common reason text or adhoc text. If there is a different way to do this that would get me around the trouble I'm having with projection/include, I'm all ears.

For the "reason" logic, this query works, returning the proper text. The trouble is that none of the "grand-child" objects are returning, i.e. TicketItem.TicketOwner.Employee, TicketItem.TicketOwner.Organization. How do I get those objects to return also?

Changing the structure of the tables would be an absolute last resort, just based on the amount of code that would have to change. There are other spots in the code that are using the above logic but don't need the child objects.

Any help would be appreciated. Hope I've explained enough.

0 Answers0