Query predicates composition in LINQ to DB

Question

LINQ to DB is a very interesting but light Object-Relational Mapping for C#. Here is my current issue with this nice tool.

Basically I need the equivalent of LINQ to Entities: Combining Predicates or a PredicateBuilder.

How can that be achieved in LINQ to DB?

Answer

I’ve managed to use Intersect to conditionally add more predicates in AND (similar way using Concat for OR) Quick example below:

private ObservableCollection<db_user> myusers = new ObservableCollection<db_user>();
void Refresh(ObservableCollection<db_user> myusers)
{
    myusers.Clear();
    using (var db = new db_userContext())
    {
        var query1 = from p in db.db_user
            where p.user_init.StartsWith("C") 
            select p;
        var query2 = from p in db.db_user
            where p.user_init.EndsWith("U") 
            select p;
        var query3 = from p in db.db_user
            where p.test
            select p;
        var queryA = query1;
        if (cb_U.IsChecked != null && (bool)cb_U.IsChecked) {
            queryA = queryA.Intersect(query2);
        }
        if (cb_Test.IsChecked != null && (bool)cb_Test.IsChecked) {
            queryA = queryA.Intersect(query3);
        }
        var SQL_A = GetInstanceField(queryA.GetType().BaseType,queryA,"_sqlText");
        Debug.WriteLine(SQL_A);
        List<db_user> ret_val = queryA.ToList();
        foreach (db_user  usr in ret_val) {
            myusers.Add(usr);
        }
    }
}

An in where clause can be also directly added and simply conditioned to the existence of at least one item, like in the next example with an array of int (int[] inWhere):

                DateTime upTo = new DateTime(2015,1,1);
                var query = from p in db.Products
                    where p.Id <=1000 && p.CreationDate < upTo 
                    && (inWhere.Length==0 || inWhere.Contains(p.order))
                    orderby p.Id ascending
                    select p;

One thought on “Query predicates composition in LINQ to DB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s