CODEWITHSUNDEEP
c#linqentity-framework
user3362714
user3362714

Reputation: 159

Linq to Entites - Multiple columns search

I have an entity Car with 3 searchable properties:

Brand       Model            Code
----------- ---------------- -----------
Ferrari     F40              X7844ADFS
Porsche     911 Turbo        YSAD42313

And I have the following method:

public IEnumerable<Car> SearchCar(string search)
{
    IEnumerable<Car> result = Enumerable.Empty<Car>().AsEnumerable();

    if (!string.IsNullOrEmpty(search))
    {
        var preResult = from A in _context.Cars
                  select new { TextField = A.Brand + " " +  A.Model + A.Code,
                               car = A};
        result = preResult.Where(x => x.TextField.ToLower().Contains(searchValue.ToLower())).Select(v => v.Car);
    }
    return result;
}

Example of searches :

This method, however, doesn't work for the first one (Porsche Turbo). Is there anyway to do this so that it works in all cases?

Upvotes: 2

Views: 72

Answers (2)

Andy Nichols
Andy Nichols

Reputation: 3002

An alternative answer to IronMan84, which I think will be more efficient as it doesn't concatenate all the fields before searching on them. Creating the anonymous type with TextField and the car is unnecessary as you only return the car. Are you sure the object in the context is called Persons and not Cars?

public IEnumerable<Car> SearchCar(string search)
{
    if (string.IsNullOrEmpty(search)) return Enumerable.Empty<Car>().AsEnumerable();
    var terms = search.ToLower().Split(' ');
    return _context.Cars
        .Where(x => terms.All(t => 
            x.Brand.ToLower().Contains(t) 
            || x.Model.ToLower().Contains(t)
            || x.Code.ToLower().Contains(t)));
}

Also note that if you are connecting to a database with a case-insensitive collation then you can leave out ToLower() in all cases.

Upvotes: 1

Corey Adler
Corey Adler

Reputation: 16137

You would need to split the different words that are in the search variable, and then search on those individually. Something like this:

var splitSearch = search.Split(' ');
result = preResult.Where(x => splitSearch.All(s => x.TextField.ToLower().Contains(s)))
                  .Select(v => v.Car);

Upvotes: 5

Related Questions