Query for documents where a string property is null

218 views
Skip to first unread message

Alexander Klaus

unread,
Apr 10, 2019, 1:41:37 AM4/10/19
to RavenDB - 2nd generation document database
Given a persistent model 

public class Entity
{
public string Number { get; set; }
}

When how I can query the collection of Entity where Number is NULL with using IRavenQueryable ?

A straightforward attempts to query like below fail:
Query<Entity>.Where(e => e.Number == null)
or
Query<Entity>.Where(e => e.Number != null)

No luck in Raven Studio on querying
from 'Entities' where Number != null

However, when I build an index on Entities, when having a field like below works:
IsNumberNull = e.Number == null

Is there a dual standard on handling queries vs indexes?

Oren Eini (Ayende Rahien)

unread,
Apr 10, 2019, 4:38:03 AM4/10/19
to ravendb
All of those should work.
What does the _document_ looks like on your database? Not the class, the actual JSON?

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
To post to this group, send email to rav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/c4b0f80e-57b3-4137-9d3c-92b6a7677d1a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


--
Oren Eini
CEO   /   Hibernating Rhinos LTD
Skype:  ayenderahien
Support:  sup...@ravendb.net

Alexander Klaus

unread,
Apr 10, 2019, 6:40:16 AM4/10/19
to RavenDB - 2nd generation document database
Check out a test database I created at http://live-test.ravendb.net/studio/index.html#databases/documents?&database=NullDemo

The collection Entities has 4  records:
  • 2 records, where property Number has a value.
  • 1 record, where Number is NULL.
  • 1 record, where property Number is absent.
I also created a simple index for the collection.

Could you please explain results of the following requests:
  1. from index 'Entities/ForList' where Number != null
    returns 3 records: the two where  Number has a value and 1 where  Number is absent.
  1. from 'Entities' where Number != null
  1. returns 2 records - only the two where  Number has a value
  2. from index 'Entities/ForList' where Number == null
    from 'Entities' where Number == null
    Both queries return 1 record, there Number is NULL.
My expectations are
  1. The first two queries return the same result - 2 records, where  Number has a value.
  2. The last group of queries (Number == null), returns 2 records, where  Number is NULL or absent
Are my expectations reasonable?


On Wednesday, April 10, 2019 at 6:38:03 PM UTC+10, Oren Eini wrote:
All of those should work.
What does the _document_ looks like on your database? Not the class, the actual JSON?

On Wed, Apr 10, 2019 at 8:41 AM Alexander Klaus <alex....@gmail.com> wrote:
Given a persistent model 

public class Entity
{
public string Number { get; set; }
}

When how I can query the collection of Entity where Number is NULL with using IRavenQueryable ?

A straightforward attempts to query like below fail:
Query<Entity>.Where(e => e.Number == null)
or
Query<Entity>.Where(e => e.Number != null)

No luck in Raven Studio on querying
from 'Entities' where Number != null

However, when I build an index on Entities, when having a field like below works:
IsNumberNull = e.Number == null

Is there a dual standard on handling queries vs indexes?

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rav...@googlegroups.com.

To post to this group, send email to rav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/c4b0f80e-57b3-4137-9d3c-92b6a7677d1a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Oren Eini (Ayende Rahien)

unread,
Apr 10, 2019, 7:28:35 AM4/10/19
to ravendb
A field being null and a field being absent are two different things.
We have the exists method that you can use for detecting this.

2 results for: from Entities where Number != null and exists(Number)
1 result for: from Entities where Number = null
2 results for: from Entities where Number = null or  not exists(Number)
1 result for: from Entities where true and not exists(Number)

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

To post to this group, send email to rav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/826e209f-70bb-4b24-97cf-30f0dbe8110b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Alexander Klaus

unread,
Apr 10, 2019, 7:46:24 AM4/10/19
to RavenDB - 2nd generation document database
Thank you. 
I have follow up questions:
  1. How to use the exists condition with IRavenQueryable?
    it seems to be absent in LINQ and I can't find an appropriate method in RavenQueryableExtensions.

  2. Why similar Where condition in the index behaves differently?
    I've modified the index on that demo database by adding a new filtering parameter: HasNumber = e.Number != null.
    Now, 
       from index 'Entities/ForList' where HasNumber = true
    returns 2 records, where the Number has a value
    and
       from index 'Entities/ForList' where HasNumber = false
    returns 2 records, where the Number is NULL or absent.

On Wednesday, April 10, 2019 at 9:28:35 PM UTC+10, Oren Eini wrote:
A field being null and a field being absent are two different things.
We have the exists method that you can use for detecting this.

2 results for: from Entities where Number != null and exists(Number)
1 result for: from Entities where Number = null
2 results for: from Entities where Number = null or  not exists(Number)
1 result for: from Entities where true and not exists(Number)

Oren Eini (Ayende Rahien)

unread,
Apr 10, 2019, 7:56:31 AM4/10/19
to ravendb
.Any() on a collection will use exists()
Otherwise, use the DocumentQuery and call WhereExists
You can also customize the linq query as well, of course, to do just that.

In the index, you are testing against null, which can be true/false, and no intermediate state


To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

To post to this group, send email to rav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/72784077-f709-4690-ab92-0b392d68610a%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages