Mr.Bacon

Query 3+ values against one index

4 posts in this topic

I've constructed a Perst database that contains several gigs worth of data, all keyed off of date. Occasionally, I have the need to query data across multiple, non-sequential dates using LINQ. Is it currently possible to do this in a way in which the index isn't ignored?

So far, I've attempted the following:

- Using multiple OR statements -

PerstDBTable.Where(a => a.date == date1 || a.date == date2 || a.date == date3); //this only works up to two dates, with 3+ dates the index is ignored and a sequential scan is used.

- Using a list and Contains -

List<DateTime> dates = new List<DateTime>(); //populate dates here

PerstDBTable.Where(a => dates.contains(a.Date)); //This query throws an exception within Perst

 

Please let me know your thoughts. Thank you in advance!

 

 

 

Share this post


Link to post
Share on other sites

Apply the following patch to database.cs, rebuild Perst, and it should fix the problem:


Index: src/Database.cs
===================================================================
--- src/Database.cs    (revision 15853)
+++ src/Database.cs    (working copy)
@@ -1827,23 +1827,23 @@
                 }
                 if (expr.NodeType == ExpressionType.OrElse)
                 {
-                    if (left.NodeType == ExpressionType.Equal && IsLiteral(((BinaryExpression)left).Right, out value))
+                    if (right.NodeType == ExpressionType.Equal && IsLiteral(((BinaryExpression)right).Right, out value))
                     {
-                        Expression baseExpr = ((BinaryExpression)left).Left;
+                        Expression baseExpr = ((BinaryExpression)right).Left;
                         alternatives = new ArrayList();
-                        while (right is BinaryExpression)
+                        while (left is BinaryExpression)
                         {
                             Expression cmp;
-                            if (right.NodeType ==  ExpressionType.OrElse)
+                            if (left.NodeType ==  ExpressionType.OrElse)
                             {
-                                BinaryExpression or = (BinaryExpression)right;
-                                right = or.Right;
-                                cmp = or.Left;
+                                BinaryExpression or = (BinaryExpression)left;
+                                left = or.Left;
+                                cmp = or.Right;
                             }
                             else
                             {
-                                cmp = right;
-                                right = null;
+                                cmp = left;
+                                left = null;
                             }
                             if (cmp.NodeType != ExpressionType.Equal
                                 || !EqualExpressions(baseExpr, ((BinaryExpression)cmp).Left)
@@ -1853,11 +1853,11 @@
                             }
                             alternatives.Add(value);
                         }
-                        if (right != null)
+                        if (left != null)
                         {
                             return null;
                         }  
-                        expr = left;
+                        expr = right;
                         left = ((BinaryExpression)expr).Left;
                         right = ((BinaryExpression)expr).Right;
                     }



 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now