LINQ to Entity using the LIKE SQL operator

asp.net-mvc entity-framework linq-to-entities

Question

I need to be able to develop a "fuzzy" type search since I have a LINQ to ENTITY query that gets data from a database. In order to search by last name IF the search criteria are included, I must add a where clause (Textbox, CAN be blank —- in which case it pulls EVERYTHING).

What I have thus far is as follows:

    var query = from mem in context.Member
                orderby mem.LastName, mem.FirstName
                select new
                {
                    FirstName = mem.FirstName,
                    LastName = mem.LastName,

                };

This will extract all information from the Entity object's Member table.

I'll add this to the logic after that:

sLastName = formCollection["FuzzyLastName"].ToString();

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

The issue is that nothing is found when the search button is hit (0 results). The query I ran on the SQL Server in anticipation of this situation yields 6 results.

The inquiry I anticipate is this:

SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName LIKE '%xxx%'

(once the text xxx is input)

Anyone see a problem with this?

EDIT: The SELECT query was fixed. I intended it to say something like "percent xxx percent" (NOT "xxx").

1
12
3/22/2010 6:27:23 PM

Accepted Answer

I believe you should use theContains() similar to this function of the string parameter:

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 believe you might useStartsWith() and EndsWith() This would be identical to the SQL expressions "xxx percent" and "percent xxx"

15
3/22/2010 4:00:49 PM

Popular Answer

Only after you've included your "Where" clause should you add your "select new" to the query.

As you did with the where clause, insert your choose clause using object call syntax.

Untested, so pardon any minor faults, but the basic idea 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,

                });
    }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow