LIKEs and ORs and stuff in Linq

Question

I'm trying to write a linq-to-sql query using || that behaves the same way as the OR in SQL when combined with a LIKE/Contains.

SQL:

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

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

A123456   John Davis
A312345   Bob Smith
A123456   Matt Jones

Linq: (provided 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 only give me the results on the left side of the ||:

A123456   John Davis
A123456   Matt Jones

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

Answer 1

Your query does look correct to me.

Have you tried looking at the Log to see what SQL is emitted?

Answer 2

I would definitely use StartsWith in this case, just to make the code more C#-like 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 that query doesn't work, could you post the SQL generated by it? Just set the context's log to write it to the console, or whatever's simplest. (I would write a console app just to test this problem - it'll be easier than running up a UI every time.)