LIKE and ORs and more in Linq

Question

I am trying to write a linq-to-sql query using || which behaves the same as OR in SQL, combined with LIKE / Contains.

SQL

SELECT * FROM Users WHERE GroupNumber = 'A123456' OR (FirstName LIKE 'Bob%' AND LastName LIKE 'Smith%') 

This will result in everyone having a name like "Bob Smith," as well as everyone with a GroupNumber of A123456. In my database, the sql example gives me three results (desired result):

  A123456 John Davis
 A312345 Bob Smith
 A123456 Matt Jones 

Linq : (assuming PNum = A123456, first = "Bob", last = "Smith")

 var users = from a in dc.Users where a.PolicyNumber == PNum || (SqlMethods.Like(a.FirstName, first + "%") && SqlMethods.Like(a.LastName, last + "%")) orderby a.PolicyNumber, a.FirstName select a; 

This will give me only the results on the left side of ||:

  A123456 John Davis
 A123456 Matt Jones 

I also tried a.Contains () and a.StartsWith (), but with each version I get the same two results. When I delete Any Like / Contain / StartsWith, I get the desired result, but I need a wildcard. How to get all three results in a Linq query?

Answer 1

I would definitely use StartsWith in this case to make the code more like C # when reading - but this should work:

 var users = from a in dc.Users where a.PolicyNumber == PNum || (a.FirstName.StartsWith(first) && a.LastName.StartsWith(last)) orderby a.PolicyNumber, a.FirstName select a; 

If this query does not work, can you publish the SQL generated by it? Just install the context log to write it to the console or whatever. (I would write a console application to test this problem - it will be easier than starting the user interface every time.)

Answer 2

Your request looks correct for me.

Have you tried looking through the log to see what SQL emits?