Entity Framework 4 search on combined fields

Question

How do I search on two combined fields. The search should happen on the SQL end if possible.

So say I have a customer table with first name and last name. I would like users to be able to search on both of those columns using a single search box.

My query currently looks like this:

var query = DbContext.Customers
    .Where(c => c.FirstName.Contains(search) || c.LastName.Contains(search));

but it should be something like

var query = DbContext.Customers
     .Where(c => c.FullName.Contains(search));

Answer 1

It is not possible unless you have FullName column also mapped. The way around this problem can be String.Concat which is allowed in Linq-to-entities:

var query = DbContext.Customers
                     .Where(p => String.Concat(p.FirstName, " ", p.LastName)
                                       .Contains(search));

Answer 2

You could use a computed column in the database and map that e.g.

alter table Customer add FullName AS FirstName + ' ' + LastName

(Not pretty I know)