RavenDb4: Indexing/Querying on Dictionary fields. Dynamic fields is the only option?

[Alexander Klaus]

Given an entity with a dictionary, like below:

public class Person

{

   public string Title { get; set; }

   public Dictionary<string, string> CustomFields { get; set; }

}

where the key of the CustomFields is a reference to a CustomField entity and the value of the CustomFields is serialised value of that field (can be any type, e.g. text, numeric, date, etc.).

Question: how to build queries in C# against the Person collection filtering by Title and CustomFields (e.g. where the dictionary key is '1-A' and the value starts from 'Paddington')?

------------------------------------------------------------------------------------------------------

My consideration #1

It's understood, that one way of querying a dictionary is to build an index with Dynamic fields. However, it seems to imposes a restriction of writing queries only in RQL directly or using DocumentQuery. There is no way of using IRavenQueryable, because it will require using strongly type objects in the WHERE condition. Or am I mistaken? 

Is it possible to use indexes with Dynamic fields in the usual Query(IRavenQueryable type)?

My consideration #2

Perhaps, there is a different way of querying against dictionaries. Consider the following index:

public class Persons_ForList : AbstractIndexCreationTask<Person>

{

   public class Persons_ForList()
   {
      Map = persons => 
         from p in persons

              from customFields in p.CustomFields

         select new
      {

p.Title,

CustomFields = new { Key = customFields.Key, Value = customFields.Value }

 }

    }

}

That index would create terms like 

{"key":"1-a","value":"Paddington"}

which, perhaps, would be easy to query from the JavaScript perspective.

Unfortunately, I didn't find a way to do so except,

from index 'Persons/ForList' 

where CustomFields = '{"key":"1-a","value":"Paddington"}'

which is very clunky.

I'd like to have something like

from index 'Persons/ForList' 

where CustomFields.Key="1-a" and CustomFields.Value="Paddington"

Would you consider adding extensions, so the query from above would work?

[Oren Eini (Ayende Rahien)]

Easier:

public Dictionary<string, object> CustomFields { get; set; }

Then query using:

.Query<Person>().Where(x => x.Title == "Super" && x.CustomFields["Age"].Equals(5) ).ToList()

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alexander Klaus]

Hi Oren,

While your example works. it's not a solution for us due to:

• It generates a new auto index disregarding an already existing index, which includes these fields (the index also includes a bunch of other fields).
• We need to query against an index, because the WHERE condition may need data resolved from other collections and we also need Search functionality.
  

Is there another solution, which may tick these to checkboxes from the above?

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

[Arkadiusz Palinski]

Regarding Consideration #1 in your first email - Is it possible to use indexes with Dynamic fields in the usual Query(IRavenQueryable type)?

Yes, it is possible. The documentation article you linked exactly does it (https://ravendb.net/docs/article-page/4.0/Csharp/indexes/using-dynamic-fields)

Note that Products_ByAttribute index has Result class which is used to build the query (.Query<Products_ByAttribute.Result, Products_ByAttribute>()). The trick is the usage of .OfType<Product>() in order to get to the original entity type on the client side.

[Alexander Klaus]

Hi Arkadiusz,

Unfortunately, there is one, but huge caveat on using Dynamic fields in the usual Query - a class where all the dictionary keys are declared as properties needs to exist in order to run the query.

But I knew all the keys upfront, I wouldn't have used a dictionary (or list of classes).

[Arkadiusz Palinski]

Oh, yes. Then let's go with your second approach. If you modify the index to:

public class Persons_ForList : AbstractIndexCreationTask<Person>

{

   public class Persons_ForList()
   {
      Map = persons => 
         from p in persons

              from customFields in p.CustomFields

         select new
      {

p.Title,

CustomFields_Key = customFields.Key,

CustomFields_Value = customFields.Value

 }

    }

}

Then you'll be able to do:

from index 'Persons/ForList' 

where CustomFields_Key="1-a" and CustomFields_Value="Paddington"

[Alexander Klaus]

Apologies if I appear as a grumpy old man, but this approach introduces two issues we rather avoid:

• it's a fanout index;
• In a scenario, where filtering on CustomFields is optional, either 
• we'll have to deal with receiving multiple duplicated records (as many as each record has custom fields);
• we'll have to maintain a separate almost identical index, which doesn't have CustomFields, but it'll bear performance and disk space costs.

Number of CustomFields per person may vary from 1 to 100.

[Oren Eini (Ayende Rahien)]

When in doubt, cheat. 

Full code below, but here is the gist of it:

You use the dictionary as you would normally would

             var q = s.Query<Person, MyIndex>()

                    .Where(p => p.Title == "Super" && p.CustomFields["Age"].Equals(4));

This generates the following query:

from index 'MyIndex' where Title = $p0 and CustomFields_Age = $p1

So you know what the client side expects the server side field to be. Then just use it like so in the index:

                    _ = p.CustomFields.Select(k => CreateField("CustomFields_" + k.Key, k.Value))

Full code:

public static class Program

{

    public class Person

    {

        public string Title { get; set; }

        public Dictionary<string, object> CustomFields { get; set; }

    }

    public class MyIndex : AbstractIndexCreationTask<Person>

    {

        public MyIndex()

        {

            Map = people =>

                from p in people

                select new

                {

                    p.Title,

                    _ = p.CustomFields.Select(k => CreateField("CustomFields_" + k.Key, k.Value))

                };

        }

    }

    public static async Task Main(string[] args)

    {

        using(var store = new DocumentStore

        {

            Urls  = new[] {"http://live-test.ravendb.net"},

            Database = "Test"

        })

        {

            store.Initialize();

            new MyIndex().Execute(store);

            using (var s = store.OpenSession())

            {

                s.Store(new Person

                {

                    Title = "Super",

                    CustomFields = new Dictionary<string, object>

                    {

                        ["Age"] = 4

                    }

                });

                s.Advanced.WaitForIndexesAfterSaveChanges();

                s.SaveChanges();

            }

            using (var s = store.OpenSession())

            {

                var q = s.Query<Person, MyIndex>()

                    .Where(p => p.Title == "Super" && p.CustomFields["Age"].Equals(4));

                Console.WriteLine(q.ToString());

                foreach (var person in q)

                {

                    Console.WriteLine(s.Advanced.GetDocumentId(person));

                }

            }

        }

    }

}

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

[Alexander Klaus]

Yes, it works! 

(relieved sigh)

Just want to give some feedback, as I've being almost there since Friday, but had faulty results due to:

• The key of the dictionary are numeric. As a result of that, your example fails with ParseException
• If I fix that and add a prefix by string concatenation (e.g. s.Query<Person, MyIndex>().Where(p => p.Title == "Super" && p.CustomFields["F_"+field].Equals(5));), your example fails with InvalidOperationException: 'Can't extract value from expression of type: Add'

So I was quite hopeless.

Thank you for your help!

Hope, LINQ integration will get better in future versions.

Best regards,

Alex Klaus

[Alexander Klaus]

On that topic again... Could you please suggest a way how to cheat for <, > comparisons, when it comes to numeric and date values?

It works in RQL:

from index 'Personss/ForList' 

where CustomFields_F_1-A > "2010-01-01"

so the question is how to build a query for a such request?

[Arkadiusz Palinski]

s.Advanced.DocumentQuery<Person, MyIndex>()
                        .WhereGreaterThan(x => x.CustomFields["Date"], "2010-01-01");

will be translated to the following RQL:

from index 'MyIndex' where CustomFields_Date > $p0

[Alexander Klaus]

Hi Arkadiusz,

Yes, I know about this approach, but it doesn't solve my problem, as my requirements (as per the discussion above) are to use the usual Query (IRavenQueryable).

Oren Eini suggested an interesting solution three posts above on how to use the usual Query, which works for all the data types, but unfortunately, it caters only exact matches and some string operations (like string.StartsWith() method).

So, digging deeper, I got completely blocked by a problem of comparing custom fields to dates and numeric values.

[Oren Eini (Ayende Rahien)]

Hi,

The problem is that the C# language is blocking you. Given that you know more than the compiler, let's use that.

 var q = s.Query<Person, MyIndex>()

     .Where(p => p.Title == "Super" && (int)p.CustomFields["Age"] >= 4);

from index 'MyIndex' where Title = $p0 and CustomFields_Age >= $p1

There is a small issue there with parsing the query, tracking here:  http://issues.hibernatingrhinos.com/issue/RavenDB-11442

Will be fixed in the next nightly

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

[Alexander Klaus]

Thank you! Can confirm that v4.0.6.nightly works.

Now I'm thinking about a more complicated scenario, maybe I'm stretching Raven's possibilities though...

If we were to store complex objects in the custom fields (e.g. a contact reference as { Id: 'contacts/1-A', Name: 'Homer Simpson'}), then would it possible to filter on properties of these complex objects?

RQL below doesn't work (and doesn't throw errors):

from index 'Persons/MyIndex' where CustomFields_Contact_Id == 'contacts/1-A'

where the stored object is

{

   "Title": "Marge Simpson"

    "CustomFields": {

        "Contact": {

            "Id": "contacts/1-A",

            "Name": "Homer Simpson"

        }

    }

}

Any thoughts on whether the filtering like that is possible in Raven with Dynamic fields index?

Best regards,

Alex Klaus

[Oren Eini (Ayende Rahien)]

Yes, but you will need to break it to fields on the index.

In other words:

            _ = p.CustomFields.Select(k => CreateField("CustomFields_" + k.Key, k.Value))

    

This will index the full field as a json value.

You need to use:

            _ = p.CustomFields.Select(k => CreateField("CustomFields_" + k.Key + "_Name", k.Value.Name))

    

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

[Alexander Klaus]

It's interesting.

Would Raven be able to build/maintain the index if not all objects of the collection have the Name field?

So in a scenario, where the model looks like

{ 

   public string Title { get; set; }

   public Dictionary<string, object> CustomFields { get; set; }

}

and the index 

Map = persons => 

   from p in persons

   select new 
   {

       p.Title,

       p.CustomFields.Select( x => CreateField ("CustomFields_"+x.Key, x.Value)),

       p.CustomFields.Select( x => CreateField ("CustomFields_"+x.Key+"_Name", ((EntityReference)x.Value).Name)),

   }

Raven will correctly handle  that some of the custom fields values are instances of EntityReference class and have the Name property and some are other data types?

P.S. Perhaps I should have tried it first before asking a question. Sorry, don't have a computer handy at the moment to try the idea.

[Oren Eini (Ayende Rahien)]

Yes, if the value doesn't have a property, this is ignored.

 

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