Creating an index on a collection with a Where clause

[Alvin Saldanha]

In the below example, I have a model which has a Dictionary<string, string> called as "CustomFields". I need to be able to index on this property based on a where clause.

The moment I add a where clause, the index does not contain any entries though the where clause evaluates to true for some records. In the example below "system_Key_Index" does not get created with the where clause. How do I create an index with a filtered list?

using Raven.Client.Documents;

using Raven.Client.Documents.Indexes;

using Raven.Client.Documents.Operations;

using Raven.Client.Documents.Queries;

using System;

using System.Collections.Generic;

using System.Linq;

namespace RavenDbEntityFilterBug

{

public static class WhereClauseIndex

{

public class Matter

{

public string Title { get; set; }

public string Author { get; set; }

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

}

public class Filter_key_Index : AbstractIndexCreationTask<Matter>

{

public Filter_key_Index()

{

Map = matters =>

from e in matters

select new

{

system_Key_Index =

e.CustomFields.Where(x => x.Key == "filter_key") // Commenting this line creates the index

.Select(x => new KeyValuePair<string, string>(x.Key + "some_Text", x.Value.ToString()))

};

}

}

public static void Main(string[] args)

{

DocumentStore documentStore = new DocumentStore();

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

documentStore.Database = "AmaltheaTest";

using (var store = documentStore)

{

store.Initialize();

documentStore.Operations.Send(new DeleteByQueryOperation(

new IndexQuery { Query = "from @all_docs as d" },

new QueryOperationOptions

{

RetrieveDetails = true

}

));

new Filter_key_Index().Execute(store);

List<Matter> books = new List<Matter>();

books.Add(new Matter

{

Title = "abc",

Author = "john",

CustomFields = new Dictionary<string, string>

{

{ "filter_key", "value1" },

{ "some_other_key", "value2" }

}

});

books.Add(new Matter

{

Title = "xyz",

Author = "doe",

CustomFields = new Dictionary<string, string>

{

{ "filter_key", "value3" },

{ "some_other_key", "value4" }

}

});

using (var s = store.OpenSession())

{

s.Store(books.First());

s.Store(books.Last());

s.Advanced.WaitForIndexesAfterSaveChanges();

s.SaveChanges();

}

using (var s = store.OpenSession())

{

var booksByJohn = s.Query<Matter, Filter_key_Index>().ToList();

Console.WriteLine(booksByJohn.ToString());

foreach (var book in booksByJohn)

{

Console.WriteLine(book);

}

}

Console.ReadLine();

}

}

}

}

[Oren Eini (Ayende Rahien)]

Try using:

system_Key_Index =

e.CustomFields.Select(x=>x).Where(x => x.Key == "filter_key") 

.Select(x => new KeyValuePair<string, string>(x.Key + "some_Text", x.Value.ToString()))

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

>>e.CustomFields.Select(x=>x).Where(x => x.Key == "filter_key") 

Thanks for your help! 

Honestly, there is no way that a human in sober mind would have figured it out! Is there any logic in that?

[Oren Eini (Ayende Rahien)]

On the server side, RavenDB isn't aware that you have Dictionary, it is just a dynamic object.

This object implements a Select, but not Where. Once you call Select, everything works normally.

This is a bug, tracked here:

http://issues.hibernatingrhinos.com/newIssue?draftId=57-27117

--

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

[Paweł Pekról]

http://issues.hibernatingrhinos.com/issue/RavenDB-11980

[Alvin Saldanha]

Thanks Oren for the .Select(x=>x). hack. That worked. In the below code, I was able to create the index. However when selecting the data from the index I am having issues with the serialization. Please see the highlighted section in the code. If we load the CustomField data within the query, ContactFields fails to deserialize. If we comment the Load the deserialization works. 

using Raven.Client.Documents;

using Raven.Client.Documents.Indexes;

using Raven.Client.Documents.Linq;

using Raven.Client.Documents.Operations;

using Raven.Client.Documents.Queries;

using System;

using System.Collections.Generic;

using System.Linq;

namespace RavenDB.Sandbox

{

public static class WhereClauseIndex

{

public class MatterDto

{

public string Id { get; set; }

public string Name { get; set; }

public IEnumerable<EntityReference> ContactFields { get; set; }

}

public static void Main(string[] args)

{

DocumentStore documentStore = new DocumentStore();

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

documentStore.Database = "Test";

using (var store = documentStore)

{

store.Initialize();

documentStore.Operations.Send(new DeleteByQueryOperation(

new IndexQuery { Query = "from @all_docs as d" },

new QueryOperationOptions

{

RetrieveDetails = true

}

));

new Matters_ForList().Execute(store);

using (var s = store.OpenSession())

{

var cf = new CustomField { Id = "CustomFields/1-A", Name = "Father's Name", FieldType = CustomFieldType.Contact };

s.Store(cf);

cf = new CustomField { Id = "CustomFields/2-A", Name = "Salary", FieldType = CustomFieldType.Numeric };

s.Store(cf);

cf = new CustomField { Id = "CustomFields/3-A", Name = "Referred by", FieldType = CustomFieldType.Contact };

s.Store(cf);

s.Advanced.WaitForIndexesAfterSaveChanges();

s.SaveChanges();

}

var matters = new List<Matter>();

matters.Add(CreateTestMatterWithCustomFields("Matter 1", new Dictionary<string, object>

{

{ "F_1-A", "customFieldValue1_1" }, { "F_2-A", "customFieldValue2_1" }, { "F_3-A", "customFieldValue3_1" }

}));

matters.Add(CreateTestMatterWithCustomFields("Matter 2", new Dictionary<string, object>

{

{ "F_1-A", "customFieldValue1_2" }, { "F_2-A", "customFieldValue2_2" }, { "F_3-A", "customFieldValue3_3" }

}));

using (var s = store.OpenSession())

{

s.Store(matters.First());

s.Store(matters.Last());

s.Advanced.WaitForIndexesAfterSaveChanges();

s.SaveChanges();

}

using (var s = store.OpenSession())

{

var result = (from m in s.Query<Matter, Matters_ForList>().As<Matters_Index>()

//The desirialization fails after Adding the below line. IF the below line is commented, deserialization succeeds.

   let custField = RavenQuery.Load<CustomField>($"CustomFields/1-A")

   select new MatterDto()

   {

   ContactFields = m.ContactFields,

   Id = m.Id,

   Name = m.Title,

   }).ToList();

foreach (var contactField in result)

{

Console.WriteLine(contactField);

}

}

Console.ReadLine();

}

}

private static Matter CreateTestMatterWithCustomFields(string title, Dictionary<string, object> customFields)

{

return new Matter

{

Title = title,

CustomFields = customFields

};

}

}

public class EntityReference 

{

public EntityReference() { }

public EntityReference(string id, string name)

{

Id = id;

Name = name;

}

public string Id { get; set; }

public string Name { get; set; }

}

public class Matters_ForList : AbstractIndexCreationTask<Matter, Matters_Index>

{

public Matters_ForList()

{

// Add fields that are used for filtering and sorting

Map = matters =>

from e in matters

select new

{

ContactFields = e.CustomFields.Select(x => x).Where(x => LoadDocument<CustomField>($"CustomFields/{x.Key.Replace("F_", "")}").FieldType == CustomFieldType.Contact)

.Select(x => new EntityReference { Name = LoadDocument<CustomField>($"CustomFields/{x.Key.Replace("F_", "")}").Name, Id = x.Value.ToString() })

};

Store(x => x.ContactFields, FieldStorage.Yes);

}

}

public class Matters_Index : Matter

{

public IEnumerable<EntityReference> ContactFields { get; set; }

}

public class Matter

{

public Matter() { }

public string Id { get; set; }

public string Title { get; set; }

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

}

public class CustomField

{

public string Id { get; set; }

public string Name { get; set; }

public CustomFieldType FieldType { get; set; }

}

public enum CustomFieldType

{

Text,

Date,

Checkbox,

Contact,

Email,

Numeric,

List,

Currency

}

}

[Maxim Buryak]

Hi, 

I can confirm it was reproduced on my side as well, looking into that.

[Maxim Buryak]

It seems that in JS projection (that is caused by the load expression) we parse the stored field value as an array of strings (it is actually stored in the index as an array of strings, and it seems that our non JS projcetion handles this differently, I'll continue investigating, but meanwhile a work around would be using a direct call to json parsing, see query:

var resultQuery = (from m in s.Query<Matter, Matters_ForList>().As<Matters_Index>()

                                               //The desirialization fails after Adding the below line. IF the below line is commented, deserialization succeeds.

                                                 let custField = RavenQuery.Load<CustomField>($"CustomFields/1-A")

                                           select new MatterDto()

                                           {

                                               ContactFields = RavenQuery.Raw(m.ContactFields,"map(x=>JSON.parse(x))"),

                                               Id = m.Id,

                                               Name = m.Title,

                                           });

[Maxim Buryak]

You can track the issue here: http://issues.hibernatingrhinos.com/issue/RavenDB-11985

[Maxim Buryak]

Hi,

Just letting you know that the issue was resolved and a fix can be found in the latest nightly version

Best Regards,

Maxim Buryak Team Leader   /   Hibernating Rhinos LTD Mobile:  +972542177751 Sales:  sa...@ravendb.net Skype:  bumaximka Support:  sup...@ravendb.net