LINQ to Entity, using a SQL LIKE operator

Question

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

Answer 1

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.

Answer 2

SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName = 'xxx'

That means that you want the last name to be equal to 'xxx'. What you write in your above post is that the lastname should contain 'xxx'.

To get it to equal you should write:

if (!String.IsNullOrEmpty(sLastName))
   query = query.Where(ln => ln.LastName == sLastName);

Perhaps you should look at ignore case:

if (!String.IsNullOrEmpty(sLastName))
   query = query.Where(ln => ln.LastName.Equals(sLastName, StringComparison.InvariantCultureIgnoreCase));

Answer 3

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,

                });
    }