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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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,

                });
    }
kervin

Related Questions

LINQ query or GetObjectKey to get single Entity Framework entities?

The latter is better in my opinion since it makes what you want very clear. We have to get around the structure that Entity Framework imposes on us by utilizing EntityKey (and the ADO.NET team doesn't appear to comprehend this). By using the query language in the second example's manner, we are informing any other developers who may ever look at ou...
asp.net c# entity-framework linq
asked by Rob

In Linq to Entities / Entity SQL with type conversion, choose Max.

This operates......String[] numbers = new String[] { "2", "34", "5", "23", "5", "1" }; var max = numbers.Max(x => Int32.Parse(x));
entity-framework linq max select type-conversion
asked by user110714

Linq to Entities vs. Linq to SQL vs. Entity Framework

Entity Framework allows for the development of queries in one of two ways:...Entities with Linq ( ...http://msdn.microsoft.com/en-us/library/bb386964.aspx... )...SQL Entity ( ...http://msdn.microsoft.com/en-us/library/bb387145.aspx... )...L2E is a component of EF rather than a distinct technology....For comparison, have a look at this article: ...h...
ado.net entity-framework linq-to-entities linq-to-sql
asked by Yakimych

In linq to entity, the operator '==' cannot be used on operands of type 'System.Guid' and'string.'

That's because a Guid and a string cannot be compared....Therefore, you must first convert the Guid to a string. Normally, I'd advise...where C.CustomerId.ToString().Equals(customerProfileId) ...but ...ToString()... is absent from Linq to Entities....This question's response, ...Linq-To-Entity query having trouble obtaining the GUID string value...
.net c# entity-framework linq linq-to-entities
asked by ChrisF

The LINQ expression node type 'Invoke' is not supported in the entity framework's LINQ to Entities.

The AsExpandable() function created by Joe Albahari and included in LINQKIT may be used to remedy this issue. He is the same person behind...PredicateBuilder... thing you're utilizing, I see....If querying with Entity Framework, change the last line to this:...return objectContext.Products.AsExpandable().Where(predicate);...You may download the LIN...
c# entity-framework expression linq predicate
asked by Leniel Maccaferri

LINQ-To-Entity (LINQ to Entities does not support the given type member ".)

There you will find the response to your inquiry: ...Can I make a linq to entity query on fields that are not members of an entity?...You can only utilize "entity members," which are attributes that are declared within your data table, when employing those LinQ to entity queries....You must utilize attributes that are specified in your Data Model s...
c# entity-framework lambda linq-to-entities
asked by Kro

How can I build a LINQ IN clause query that also acts as a LIKE operator?

var brandids = brandid .Split(',').Select(n => int.Parse(n)).ToList(); var bodyStyleids = bodystyleid.Split(',').Select(n => int.Parse(n)).ToList(); var results = (from c in car where brandids.Contains(c.brandid) && bodyStyleids.Contains(c.bodystyleid) select c ).ToList();
entity-framework in-clause linq linq-to-entities sql-like
asked by sgmoore

Compare Dates using LINQ to Entities (Entity Framework)

Instead, use ...EntityFunctions.... ...CreateDateTime... technique, perhaps....So maybe like this:...return All().Where(o => EntityFunctions.CreateDateTime(o.expiry_date.Year, o.expiry_date.Month, o.expiry_date.Day, 17, 30, 0) >= DateTime.Now) ...Update:...When using EF6, substitute ...DbFunctions....
c# datetime entity-framework
asked by Euphoric

Logging the SQL generated by LINQ to SQL in Entity Framework in .net

In line with ...Logging in Entity Framework...The DbContext.Database.Log property can be set to a delegate for any method that takes a string.... Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following ...
entity-framework-6 logging sql sql-server
asked by Markus Safar

Offset/Fetch based paging (Implementation) in EntityFramework (Using LINQ) for SQL Server 2008

ZZZ_tmp
entity-framework entity-framework-6 pagination sql-server sql-server-2008
asked by DavidG

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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