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.