If statements within a Linq where clause

Question

Struggling a bit today.

I have the following method that returns a list of products..lovely.

public static List<tblWeight> GetProductInfo(string memberid, string locationid, string basematerial, string source)
        {
             MyEntities getproductinfo = new MyEntities ();

            return (from p in getproductinfo .tblWeights
                        where    p.MemberId == memberid &&
                                 p.LocationId == locationid &&
                                 p.BaseMaterialName == basematerial &&
                                 p.WeightStatus == source
                       select p)
                       .ToList();

• Where basematerial & source are drop down lists.

How do I go about incorporating a few IF statements into the where clause?

For example, if the basematerial ddl is not touched but an item in the source ddl is selected, the result would return everything associated with basematerial but filtered by the selected source.

Does that even make sense?!

I'm not even sure I am taking the correct approach - please forgive my ignorance.

Solution 1

you can add them to your query on need:

var r =  (from p in getproductinfo .tblWeights 
                        where    p.MemberId == memberid && 
                                 p.LocationId == locationid && 
                                 p.WeightStatus == source 
                       select p) 

if (!String.IsNullOrEmpty(basematrial))
    r = r.Where(p => p.BaseMaterialName == basematerial);

return r.ToList();

Solution 2

Consider implementing these extension methods named WhereIf.

You pass it two parameters: a statement evaluated to a boolean, and a lambda function. If the bool statement evaluates to true, the lambda is added.

WhereIf on ExtensionMethod.net

Your query could look like:

return getproductinfo.tblWeights
            .Where(w=> w.MemberId == memberid &&
                     w.LocationId == locationid)
            .WhereIf(!string.IsNullOrEmpty(basematerial), w=>w.BaseMaterialName == basematerial)
            .WhereIf(!string.IsNullOrEmpty(source), w=>w.WeightStatus == source)                         
            .ToList();

Here they are, for both IEnumerable and IQueryable. This allows you to use .WhereIf() in LINQ To SQL, Entity Framework, Lists, Arrays, and anything else that implements those 2 interfaces.

public static IEnumerable<TSource> WhereIf<TSource>(this IEnumerable<TSource> source, bool condition, Func<TSource, bool> predicate)
{
    if (condition)
        return source.Where(predicate);
    else
        return source;
}

public static IEnumerable<TSource> WhereIf<TSource>(this IEnumerable<TSource> source, bool condition, Func<TSource, int, bool> predicate)
{
    if (condition)
        return source.Where(predicate);
    else
        return source;
}

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Func<TSource, bool> predicate)
{
    if (condition)
        return source.Where(predicate);
    else
        return source;
}

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Func<TSource, int, bool> predicate)
{
    if (condition)
        return source.Where(predicate);
    else
        return source;
}

Comments

[Ricardo Deano]

Hey there p..campbell - thanks for your input, it has given me plenty of food for thought. However, I implemented moi's solution so marked that as the answer. However, I am sure I will use your solution at some point so I'll bump the answer as much as I can.

[Ricardo Deano]

Hello Moi - thank you very much for this solution, it has helped me out a great deal and is simple to implement.

[Colin Pear]

For the IQueryable I had to add .AsQueryable() to the return: return source.Where(predicate).AsQueryable();