Query for documents where a string property is null

[Alexander Klaus]

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)]

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 Mobile:  +972-52-548-6969 Sales:  sa...@ravendb.net Skype:  ayenderahien Support:  sup...@ravendb.net

[Alexander Klaus]

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)]

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]

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)

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.

[Oren Eini (Ayende Rahien)]

.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.