LINQ to Entity using the LIKE SQL operator

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

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

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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.

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

Popular Answer

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,

                });
    }
kervin

Related Questions

LINQ query or GetObjectKey to get single Entity Framework entities?

I prefer the latter because it is explicitly clear what it is you want. By using EntityKey (and this is something that the ADO.NET team doesn't seem to understand), we have to work around the structure imposed on us by Entity Framework. By using the query language in the way you did in the second example, we're telling all of the rest of the deve...
asp.net c# entity-framework linq
asked by Rob

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

This works......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

There are two ways of writing queries with Entity Framework:...Linq to Entities ( ...http://msdn.microsoft.com/en-us/library/bb386964.aspx... )...Entity SQL ( ...http://msdn.microsoft.com/en-us/library/bb387145.aspx... )...L2E is not a separate technology, but rather a part of EF....Have a look at this article for a comparison: ...http://thedatafar...
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 you can't equate a Guid and a string....So you need to convert the Guid to string first. Normally I'd suggest:...where C.CustomerId.ToString().Equals(customerProfileId) ...but ...ToString()... doesn't exist in Linq to Entities....The answer to this question - ...Problem getting GUID string value in Linq-To-Entity query... - will prob...
.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.

This problem can be solved using the AsExpandable() method present in LINQKIT by Joe Albahari. He's the same creator of ...PredicateBuilder... that I see you're using....If querying with Entity Framework, change the last line to this:...return objectContext.Products.AsExpandable().Where(predicate);...You can grab LINQKIT DLL ...here... or install i...
c# entity-framework expression linq predicate
asked by Leniel Maccaferri

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

The answer to your question is given there : ...Question : Can I create a linq to entity query on non entity member fields...When using thoses LinQ to entity queries, you can only use 'entity members' that are properties that are defined inside your data table....Since your queries are wrapped ("translated") into SQL commands, you must use properti...
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)

Use ...EntityFunctions... instead. Maybe ...CreateDateTime... method....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, use ...DbFunctions... instead.
c# datetime entity-framework
asked by Euphoric

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

According to ...Entity Framework Logging...:...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

This is possible with ...Entity Framework 6.1.2... and above so you should be OK to use it in your project. The standard Skip and Take methods can't be captured in the same way as others. There are now two additional overload of the Skip/Take methods that take lambdas, so instead of this:...var results = context.MyTable .Skip(10) .Take(5); ...
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