How to construct a proper WHERE clause with RavenDb

Go To StackoverFlow.com

1

I have experimented with 2 forms of the call, this one

 products = DocumentSession.Query<Product>()
      .Statistics(out stats)
      .Where(p => p.INFO2.StartsWith(term1))
      .Where(p => p.INFO2.StartsWith(term2))
      .Where(p => p.INFO2.StartsWith(term3)) 
      .OrderByField(columnToSortBy, columnToSortByAsc)
      .Skip(pageIndex * pageSize)
      .Take(pageSize)
      .ToList()
      ;

and this way

  products = DocumentSession.Query<Product>()
      .Statistics(out stats)
      .Where(p => p.INFO2.StartsWith(term1) & p.INFO2.StartsWith(term2) & p.INFO2.StartsWith(term3))
      .OrderByField(columnToSortBy, columnToSortByAsc)
      .Skip(pageIndex * pageSize)
      .Take(pageSize)
      .ToList()
      ;

The first one returns records that are more in-line with my expectations, while the seconds seems to return ALL documents of type Product. What are the differences between the 2 from a LINQ expression point of view, and have I overlooked anything that might negate what I am trying to accomplish, which is a 3 term query and each term being AND'd together.

EDIT: revised code per Russ.

  string t1 = terms[0];
  string t2 = terms[1];
  string t3 = terms[2];

  products = DocumentSession.Query<Product>()
      .Statistics(out stats)
      .Where(p => p.INFO2.StartsWith(t1) && p.INFO2.StartsWith(t2) && p.INFO2.StartsWith(t3)) 
      .OrderByField(columnToSortBy, columnToSortByAsc)
      .Skip(pageIndex * pageSize)
      .Take(pageSize)
      .ToList()
      ;

EDIT 2: This is where you smash your face down on the keyboard, or any other solid object for that matter... Gotta get back to the basic here with standard C# And and Or

Thank you, Stephen

2012-04-04 00:28
by Stephen Patten
No worries; I don't think there's a single C# developer on Stack overflow who hasn't done this at least once! I know I have : - Russ Clarke 2012-04-04 00:49


2

In the second block you're doing an & instead of an && so instead of being an AND comparison, it's trying to do a bitwise operation.

Edit: in the 2nd case, you can change this:

.Where(p => p.INFO2.StartsWith(terms[0]) & p.INFO2.StartsWith(terms[1]) & p.INFO2.StartsWith(terms[2]))

to this:

.Where(p => p.INFO2.StartsWith(terms[0]) && p.INFO2.StartsWith(terms[1]) && p.INFO2.StartsWith(terms[2]))

Which makes it proper AND clause.

2nd edit: If this is meant to be an AND operation, then you don't need 3 terms - you need a single term, otherwise you'll be comparing against 3 instances of the same string.

terms[0] = "test";
terms[1] = "test";
terms[2] = "test";

.Where(p => p.INFO2.StartsWith(terms[0]) && p.INFO2.StartsWith(terms[1]) && p.INFO2.StartsWith(terms[2]))

Is the same as

string term = "test";

.Where(p => p.INFO2.StartsWith(term) && p.INFO2.StartsWith(term) && p.INFO2.StartsWith(term))

Just mentioning this as this may make your code harder to maintain in the future.

2012-04-04 00:33
by Russ Clarke
Thanks Russ. What I'm looking for is AND not OR - Stephen Patten 2012-04-04 00:39
Ok - you didn't mention that in your original question, I've edited my main answer based on this - Russ Clarke 2012-04-04 00:42
I changed the last sentence of the question to emphasize the And - Stephen Patten 2012-04-04 00:50