Creating an index on a collection with a Where clause

235 views
Skip to first unread message

Alvin Saldanha

unread,
Sep 20, 2018, 5:18:03 AM9/20/18
to RavenDB - 2nd generation document database

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)

unread,
Sep 20, 2018, 5:20:00 AM9/20/18
to ravendb
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
Skype:  ayenderahien
Support:  sup...@ravendb.net

Alexander Klaus

unread,
Sep 20, 2018, 7:59:01 AM9/20/18
to RavenDB - 2nd generation document database
>>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)

unread,
Sep 20, 2018, 4:40:19 PM9/20/18
to ravendb
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:



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

unread,
Sep 20, 2018, 4:56:56 PM9/20/18
to rav...@googlegroups.com

Alvin Saldanha

unread,
Sep 25, 2018, 3:53:05 AM9/25/18
to RavenDB - 2nd generation document database
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

unread,
Sep 25, 2018, 5:36:57 AM9/25/18
to rav...@googlegroups.com
Hi, 
I can confirm it was reproduced on my side as well, looking into that.

Maxim Buryak

unread,
Sep 25, 2018, 9:19:35 AM9/25/18
to rav...@googlegroups.com
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

unread,
Sep 25, 2018, 9:52:22 AM9/25/18
to rav...@googlegroups.com

Maxim Buryak

unread,
Oct 4, 2018, 12:07:43 PM10/4/18
to rav...@googlegroups.com
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

Reply all
Reply to author
Forward
0 new messages