Left join in linq to entities null error

0
votes

I have sql query like below 

 select * from Attachments a
    left join UserTickets ut
    on
    ut.Id=a.UserTicketId
    left join Tickets t
    on
    t.Id=ut.TicketId
    where a.ItemKey='abcd'

its returning correct result in sql server, but when i am trying in linq to entities in my mvc project like below 

var attachmentDetails = (from a in AttachmentsService.FindAllAttachments().Attachments
                                 join ut in UserTicketsService.FindAllUserTickets().UserTickets on a.UserTicketId equals ut.Id into aut
                                 from ut in aut.DefaultIfEmpty()
                                 join t in TicketsService.FindAllTickets().Tickets on ut.TicketId equals t.Id into utt
                                 from t in utt.DefaultIfEmpty()
                                 where a.ItemKey.ToUpper() == userName.ToUpper() 
                                 select new UserTicketsViewModel
                                 {
                                     AttachmentId = a.Id,
                                     FilePath = a.FileName,
                                     TicketName=t!=null?t.TicketName:"",
                                     FileName = Path.GetFileName(a.FileName),
                                     UserId = UserId,
                                 }).ToList();

its give me exception at "ut.ticketId".inner excetion show "null".please suggest what i am doing wrong

2
c#linqleft-join
i don't know this is allowed in renaming but can your try to rename from t in utt.DefaultIfEmpty to from tt in utt.DefaultIfEmpty and also rename in the select the following: t!=null?t.TicketName:"" to tt!=null?tt.TicketName:"" ..hope this will help you – Monah
Hey Hadi, thanks for the reply...but i already tried this won't work... – Mukesh Salaria

2 Answers

2
votes

please try below query.

var attachmentDetails = (from a in AttachmentsService.FindAllAttachments().Attachments
                             join ut in UserTicketsService.FindAllUserTickets().UserTickets on a.UserTicketId==null ? 0 : a.UserTicketId equals ut.Id into aut
                             from ut in aut.DefaultIfEmpty()
                             join t in TicketsService.FindAllTickets().Tickets on ut.TicketId==null ? 0 : ut.TicketId equals t.Id into utt
                             from t in utt.DefaultIfEmpty()
                             where a.ItemKey.ToUpper() == userName.ToUpper() 
                             select new UserTicketsViewModel
                             {
                                 AttachmentId = a.Id,
                                 FilePath = a.FileName,
                                 TicketName=t!=null?t.TicketName:"",
                                 FileName = Path.GetFileName(a.FileName),
                                 UserId = UserId,
                             }).ToList();
0
votes

I think the issue is that LINQ is trying to translate everything to SQL (including the Path.GetFileName method). Try running this query as:

var attachmentDetails = (from a in AttachmentsService.FindAllAttachments().Attachments
                             join ut in UserTicketsService.FindAllUserTickets().UserTickets on a.UserTicketId equals ut.Id into aut
                             from ut in aut.DefaultIfEmpty()
                             join t in TicketsService.FindAllTickets().Tickets on ut.TicketId equals t.Id into utt
                             from t in utt.DefaultIfEmpty()
                             where a.ItemKey.ToUpper() == userName.ToUpper() 
                             select new UserTicketsViewModel
                             {
                                 AttachmentId = a.Id,
                                 FilePath = a.FileName,
                                 TicketName=t!=null?t.TicketName:"",
                                 FileName = a.FileName,
                                 UserId = UserId,
                             }).ToList();
foreach (var item in attachmentDetails)
    item.FileName = Path.GetFileName(item.FileName);