Unexpected Behavior from a Linq to EF4 Contains Method

On a query of people, this statement should return a number of results that have "And" in their last name-

 var results = repository.GetQuery().Where(p => p.Names
                                    .Select(n=> n.LastName)
                                    .Contai开发者_如何学编程ns("And");

It returns no results. If we change it to-

var results = repository.GetQuery().Where(p => p.Names
                                   .Select(n=> n.LastName)
                                   .Contains("Anderson");

We get all people who has a last name of Anderson.

Obviously it is being translated to SQL as an Equals instead of a Like . Furthermore we modified it to be-

var results = repository.GetQuery().Where(p => p.Names
                                   .Select(n=> n.LastName)
                                   .FirstOrDefault()
                                   .Contains("And");

That returns all the people who have "And" anywhere in there last name, unfortunately it only checks the first lastname of the person.

var results = repository.GetQuery().Where(p => p.Names
                                   .Any(n=> n.LastName
                                   .Contains("And"));

Works properly, but we cannot use this the way we would like to.

---

It looks like you are trying to do this:

Select *
From Names
Where LastName like 'And%'

The .Contains() method only works with exact matches. You can use Linq to SQL and specify the exact SQL you are looking for, but that would probably be better with a stored procedure.

This looks like it would be of some assistance: LINQ to SQL and wildcard searches

---

Been working on this dynamic search problem so long...I could not see the forest through the trees.

This does not work because it is an IEnumerable.Contains method which only returns a perfect match.

var results = repository.GetQuery().Where(p => p.Names
                                    .Select(n=> n.LastName)
                                    .Contains("And");

This works because it is using a String.Contains method.

var results = repository.GetQuery().Where(p => p.Names
                                   .Any(n=> n.LastName
                                   .Contains("And"));

They are two differnent Contains methods. We go the last one working with our Dynamic Searches.