0

I'm trying to search for matching people via a query like using entity framework:

var ret = from s in Lab_SubSpaces
        join w1 in Workers on s.Alt_Contact equals w1.WWID
        where SqlMethods.Like(w1.Full_Name, search) || SqlMethods.Like(w1.IDSID, search)
        select s.Lab_Space_Id;

My problem now is that I have 5 more columns which I have to search against. So I think that means I need 6 more LEFT joins there as well. I really have no concept of how to do that as the joins keep expanding out and I get lost on the left joins with linq.

This is the SQL call that I need to end up replicating:

SELECT DISTINCT Lab_Space_Id
FROM Lab_SubSpace
LEFT JOIN Worker AS w1 ON Alt_Contact = w1.WWID
LEFT JOIN Worker AS w2 ON Capital_Contact = w2.WWID
LEFT JOIN Worker AS w3 ON PPHW_Contact = w3.WWID
LEFT JOIN Worker AS w4 ON IT_Contact = w4.WWID
LEFT JOIN Worker AS w5 ON Main_Contact = w5.WWID
LEFT JOIN Worker AS w6 ON Safety_Contact = w6.WWID
WHERE w1.Full_Name LIKE @search OR w1.IDSID LIKE @search OR
      w2.Full_Name LIKE @search OR w2.IDSID LIKE @search OR
      w3.Full_Name LIKE @search OR w3.IDSID LIKE @search OR
      w4.Full_Name LIKE @search OR w4.IDSID LIKE @search OR
      w5.Full_Name LIKE @search OR w5.IDSID LIKE @search OR
      w6.Full_Name LIKE @search OR w6.IDSID LIKE @search
Gargoyle
  • 9,590
  • 16
  • 80
  • 145
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help? Also, you aren't doing a left join in your LINQ sample anyway. – NetMage Jul 13 '18 at 20:05
  • The class `Lab_SubSpace` should have 6 navigation properties referring to `Worker`. You don't need these join statements. – Gert Arnold Jul 13 '18 at 20:32
  • If the DBA had created relationships you'd be correct. Unfortunately, they didn't. – Gargoyle Jul 13 '18 at 20:36
  • You can still configure relationships in the EF model if there are no foreign keys. – Gert Arnold Jul 13 '18 at 20:52

1 Answers1

0

OK, I figured this out. As Gert pointed out if relationships were setup this wouldn't be necessary, but I'm posting still for people just doing Linq-to-SQL that stumble across this.

var byContactName = from s in context.Lab_SubSpace.AsNoTracking()
    from w1 in context.Worker.Where(w => w.WWID == s.Alt_Contact).DefaultIfEmpty()
    from w2 in context.Worker.Where(w => w.WWID == s.Main_Contact).DefaultIfEmpty()
    from w3 in context.Worker.Where(w => w.WWID == s.IT_Contact).DefaultIfEmpty()
    from w4 in context.Worker.Where(w => w.WWID == s.PPHW_Contact).DefaultIfEmpty()
    from w5 in context.Worker.Where(w => w.WWID == s.Capital_Contact).DefaultIfEmpty()
    from w6 in context.Worker.Where(w => w.WWID == s.Technical_Contact).DefaultIfEmpty()
    where w1.Full_Name.ToLower().Contains(text) || w1.IDSID.ToLower().Contains(text) ||
        w2.Full_Name.ToLower().Contains(text) || w2.IDSID.ToLower().Contains(text) ||
        w3.Full_Name.ToLower().Contains(text) || w3.IDSID.ToLower().Contains(text) ||
        w4.Full_Name.ToLower().Contains(text) || w4.IDSID.ToLower().Contains(text) ||
        w5.Full_Name.ToLower().Contains(text) || w5.IDSID.ToLower().Contains(text) ||
        w6.Full_Name.ToLower().Contains(text) || w6.IDSID.ToLower().Contains(text)
    select s.Lab_Space_Id;
Gargoyle
  • 9,590
  • 16
  • 80
  • 145