Linq to Entities Search Algorithm

I'm implementing a search that will take six possible (but not required) user inputs and then try to match them to some entities.

I think the issue I have come up against is that EF and SQL Server think of nulls as two very different things.

Idea: select an entity where columnA = (if columnA is null then columnA (or null) else searchTerm). The search terms are a mix of ints and strings.

Some code:

entities= (from c in context.Entities
           where c.ColumnA == (searchTermA ?? v.ColumnA)
           where c.ColumnB == (开发者_开发技巧searchTermB ?? v.ColumnB)
           select new
           {
               v.Property,
           }).ToList();

If all columns do not contain nulls, entities are returned. However, I do not get expected results if the column has nulls.

How can I work around this?

Richard

This is what i used to handle null values. It was the only way i could get the right results.

((searchTermA.HasValue) ? (c.ColumnA == searchTermA) : true)

Have the function accept a Predicate<T> and do the construction of the filter on the UI side.

See: http://msdn.microsoft.com/en-us/library/bfcke1bz.aspx

The easy way is this one:

var query = from c in context.Entities;

if  (searchTermA != null) {
    query = query.Where(c => c.ColumnA == searchTermA);
}

// Rest of your conditions defined in the same way.

var entities = query.Select(c => new { c.Property }).ToList();