Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ to Entity, using a SQL LIKE operator

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")

like image 544
SlackerCoder Avatar asked Mar 22 '10 15:03

SlackerCoder


People also ask

How LINQ queries converted into SQL queries?

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.

How do you like a query in LINQ?

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.

Which is correct about LINQ to Entities?

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.


2 Answers

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.

like image 155
John B Avatar answered Oct 22 '22 18:10

John B


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,

                });
    }
like image 45
kervin Avatar answered Oct 22 '22 19:10

kervin