LINQ to SQL complex query

Question

I have the following object

public Cell {
    public string CategoryId {get; set;}
    public DateTime FromTime {get; set}
    public DateTime ToTime {get; set}
}

I have a DB table that is called Item that looks like this:

Item

-------------------------
CategoryId    LastUpdated

Now in the code I have a list of Cell List<Cell> ToBeFetchedFromDB that contains more than one Cell, suppose the list contains Foo and Bar, I want to dynamically build a query like this BY INTERATING THROUGH THE COLLECTION ToBeFetchedFromDB WITHIN MY LINQ TO SQL QUERY instead of statically constructing the query:

from x in Item
where x.CategoryId == Foo.CategoryId && Foo.FromTime < x.LastUpdated < Foo.ToTime
      || x.CategoryId == Bar.CategoryId && Bar.FromTime < x.LastUpdated < Bar.ToTime
select x

I have been trying but can't figure it out :(

Thanks guys!

As @Keith said, I want to dynamically iterate through the List(), not just list them out statically. — Tu Hoang
– Tu Hoang, Commented Aug 13, 2013 at 15:12
And I don't understand the downvotes ... It's a legitimate question ... — Tu Hoang
– Tu Hoang, Commented Aug 13, 2013 at 15:19
It's not clear at all what you're trying to do, and you also seem to be asking people to just hand you the code. I've read your question three times and I still don't know what you're asking. — Justin Morgan
– Justin Morgan, Commented Aug 13, 2013 at 15:22

Servy · Accepted Answer · 2013-08-13 15:17:16Z

4

Using PredicateBuilder you can go through each item in your list using a foreach and add a new option for your filter:

var filter = PredicateBuilder.False<Item>();

foreach (var cell in ToBeFetchedFromDB)
{
    filter = PredicateBuilder.Or(filter, item => 
        item.CategoryId == cell.CategoryId &&
        cell.FromTime < item.LastUpdated &&
        item.LastUpdated < cell.ToTime);
}

var query = Item.Where(filter);

A copy of PredicateBuilder from the link:

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
                (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                            Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
                (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

answered Aug 13, 2013 at 15:17

Servy

204k27 gold badges347 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tu Hoang Over a year ago

Thank you for your answer! Just wondering, shouldn't the predicate be true?

Servy Over a year ago

@Noobie Nope. Anything ORed with true is true. If you stared with true you'd never end up with false. If you were ANDing each of the expressions together then you'd start with True.

Tu Hoang Over a year ago

You sir are awesome! I owe you dinner!

Collectives™ on Stack Overflow

LINQ to SQL complex query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related