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?
In LINQ to SQL, we don't have a LIKE operator, but by using contains(), startswith(), and endswith() methods, we can implement LIKE operator functionality in LINQ to SQL. The following table shows more details regarding operators we used to achieve LINQ to SQL Like operators.
LINQ that stands for Language Integrated Query (pronounced as “link”) is a . NET language extension that supports data retrieval from different data sources like XML document, databases and collections. It was introduced in the . NET 3.5 framework.
Introduction to LINQ Include. LINQ include helps out to include the related entities which loaded from the database. It allows retrieving the similar entities to be read from database in a same query. LINQ Include() which point towards similar entities must read from the database to get in a single query.
LINQ offers common syntax for querying any type of data source; for example, you can query an XML document in the same way as you query a SQL database, an ADO.NET dataset, an in-memory collection, or any other remote or local data source that you have chosen to connect to and access by using LINQ.
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.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With