1

How can I do this SQL query in LINQ:

select * from chat c
left outer join lead s on c.key = s.id
where (typeId = 5 AND c.key = @clientId) OR (c.typeId = 4 AND s.clientId = @clientId)

Or this SQL query -- Same, same

select * from chat c
where (typeId = 5 AND c.key = @clientId) OR (typeId = 4 AND c.key in (select id from lead where clientId = @clientId))

What I have:

var chatter = (from chat in linq.Chat
             join lead in linq.Lead
                on chat.key equals lead.Id.ToString() into clientLeads
                from cl in clientLeads.Where(l => l.clientId == clientId).DefaultIfEmpty()
            where (chat.typeId == 5 && chat.key == clientId.ToString()) ||
                (chat.typeId == 4 && chat.key == cl.Id.ToString())
                select chat).WithPath(prefetchPath).OrderByDescending(c => c.CreatedDate);

The above LINQ query doesn't yeild any results from the latter WHERE clause, what am I missing?

MartinHN
  • 19,542
  • 19
  • 89
  • 131

1 Answers1

1

I translated the second query to linq:

var leadIds = linq.Lead.Where(l => l.clientId == clientId.ToString()).Select(l => l.id);
var chatter = from chat in linq.Chat
              where (chat.typeId == 5 && chat.key == clientId.ToString()) ||
                    (chat.typeId == 4 && leadIds.Contains(chat.key));
david.s
  • 11,283
  • 6
  • 50
  • 82