I have a LINQ to ENTITY query that pulls from a table, but I need to be able to create a "fuzzy" type search. So I need to add a where clause that searches by lastname IF they add the criteria in the search box (Textbox, CAN be blank --- in which case it pulls EVERYTHING).
Here is what I have so far:
var query = from mem in context.Member
orderby mem.LastName, mem.FirstName
select new
{
FirstName = mem.FirstName,
LastName = mem.LastName,
};
That will pull everything out of the Member table that is in the Entity object.
Then I have an addition to the logic:
sLastName = formCollection["FuzzyLastName"].ToString();
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName.Contains(sLastName));
The problem is when the search button is pressed, nothing is returned (0 results). I have run the query against the SQL Server that I expect to happen here and it returns 6 results.
This is the query I expect:
SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName LIKE '%xxx%'
(when xxx is entered into the textbox)
Anyone see anything wrong with this?
EDIT: Fixed the SELECT query. I meant for it to read LIKE '%xxx%' (NOT = 'xxx")
LINQ to SQL translates the queries you write into equivalent SQL queries and sends them to the server for processing. More specifically, your application uses the LINQ to SQL API to request query execution. The LINQ to SQL provider then transforms the query into SQL text and delegates execution to the ADO provider.
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.
LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context.
I think you want to use the Contains()
function of the string parameter like this:
var query = from mem in context.Member
where mem.LastName.Contains("xxx")
orderby mem.LastName, mem.FirstName
select new
{
FirstName = mem.FirstName,
LastName = mem.LastName,
};
I think you can also use StartsWith()
and EndsWith()
which would be equivalent to the SQL 'xxx%' and '%xxx" respectively.
Add your "select new" to the query only after you append your "Where" clause.
Hence append your select clause using object call syntax as you did with the where clause.
Untested, please excuse small errors, but the general concept would be....
using( someContent sc = new someContent())
{
var query = sc.Member.OrderBy( i => i.LastName)
.ThenBy( i => i.FirstName);
sLastName = formCollection["FuzzyLastName"].ToString();
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName.Contains(sLastName));
query = query.Select( i => new
{
FirstName = i.FirstName,
LastName = i.LastName,
});
}
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