How to make a dynamic where clause: linq to sql

Question

I have the following linq query:

        Dim q = From definition In definitionList _
            Where definition.ValueDefName.Contains(criteria) _
            Select definition.ValueDefName, definition.ValueDefDesc, definition.DateCreated, _
            definition.StrategicInitiative, definition.ValueWikiURL, definition.ValueDefinitionStatusID, _
            definition.Aliaslist

I have already looked into this answer: Dynamic WHERE clause in LINQ

Unfortunately, it doesn't work for me using .net 4.0. When I attempt to pass the criteria string in it ask for a predicate. The end goal is to add any of these:

1. definition.ValueDefname.Contains(criteria)
2. definition.ValueDefDesc.Contains(criteria)
3. definition.Aliaslist.Contains(Criteria)
4. definition.StrategicInitiative.Contains(Criteria)

to be passed into the query depending on what checkboxes the user has selected. How can I create a dynamic where clause in linq to sql? Is there new syntax for passing in a where clause as a string?

Answer 1

For LINQ to SQL, the Dynamic LINQ approach would be the only option to pass a string as the Where clause. Alternatively, the Where clause takes an Expression, and you can dynamically build an expression using this approach: http://msdn.microsoft.com/en-us/library/bb882637.aspx

I did this using Entity Framework, I believe this works with LINQ to SQL too.

HTH.

Answer 2

Not 100% sure this answers your need, but we use lambda expression functions passed into our search methods:

In our repository class:

public override IEnumerable<KeyCode> FindAll(Expression<Func<KeyCode, bool>> filter)
{
    return GetAll().AsQueryable().Where(filter);
}

And in some other location

KeyCodeRepository keycodeRepository = new KeyCodeRepository(unitOfWork);
IEnumerable<KeyCode> keyCodes = keycodeRepository
    .FindAll(k => k.FlowStatus.Value == (int)FlowStatusType.Active 
                                    && k.AppSystem.Id == appSystemId);

The expression

k.FlowStatus.Value == (int)FlowStatusType.Active && k.AppSystem.Id == appSystemId

is passed in as an expression to the Where clause in the FindAll method. This allows us to dynamically determine our filters anytime we call FindAll();