how can I return two collections with a cypher query in the neo4j .net client

Question

I'd like to return two collections in one query "tags" and "items" where each tag can have 0..many items. It looks like if I use the projection, it will assume a single collection with two columns rather than two collections, is that correct? Is there a better way to run this search query?

I'm getting "the query response contains columns Tags, Items however ...anonymous type does not contain settable properties to receive this data"

var query =  client
    .Cypher
    .StartWithNodeIndexLookup("tags", "tags_fulltext", keyword)
    .Match("tags<-[:TaggedWith]-items")
    .Return((items, tags) => new 
    {
        Tags = tags.As<Tag>(),
        Items = items.As<Item>()
    });

var results = await query.ResultsAsync;

return new SearchResult
{
    Items = results.Select(x => x.Items).ToList(),
    Tags = results.Select(x => x.Tags).Distinct().ToList()
};

Answer 1

Option 1

Scenario: You want to retrieve all of the tags that match a keyword, then for each of those tags, retrieve each of the items (in a way that still links them to the tag).

First up, this line:

.StartWithNodeIndexLookup("tags", "tags_fulltext", keyword)

Should be:

.StartWithNodeIndexLookup("tag", "tags_fulltext", keyword)

That is, the identity should be tag not tags. That's because the START clause results in a set of nodes which are each a tag, not a set of nodes called tags. Semantics, but it makes things simpler in the next step.

Now that we're calling it tag instead of tags, we update our MATCH clause to:

.Match("tag<-[:TaggedWith]-item")

That says "for each tag in the set, go and find each item attached to it". Again, 'item' is singular.

Now lets return it:

.Return((tag, item) => new 
{
    Tag = tag.As<Tag>(),
    Items = item.CollectAs<Item>()
});

Here, we take each 'item' and collect them into a set of 'items'. My usage of singular vs plural in that code is very specific.

The resulting Cypher table looks something like this:

-------------------------
| tag      | items      |
-------------------------
| red      | A, B, C    |
| blue     | B, D       |
| green    | E, F, G    |
-------------------------

Final code:

var query = client
    .Cypher
    .StartWithNodeIndexLookup("tag", "tags_fulltext", keyword)
    .Match("tag<-[:TaggedWith]-item")
    .Return((tag, item) => new
    {
        Tag = tag.As<Tag>(),
        Items = item.CollectAs<Item>()
    });

That's not what fits into your SearchResult though.

Option 2

Scenario: You want to retrieve all of the tags that match a keyword, then all of the items that match any of those tags, but you don't care about linking the two together.

Let's go back to the Cypher query:

START tag=node:tags_fulltext('keyword')
MATCH tag<-[:TaggedWith]-item
RETURN tag, item

That would produce a Cypher result table like this:

--------------------
| tag      | item  |
--------------------
| red      | A     |
| red      | B     |
| red      | C     |
| blue     | B     |
| blue     | D     |
| green    | E     |
| green    | F     |
| green    | G     |
--------------------

You want to collapse each of these to a single, unrelated list of tags and items.

We can use collect to do that:

START tag=node:tags_fulltext('keyword')
MATCH tag<-[:TaggedWith]-item
RETURN collect(tag) AS Tags, collect(item) AS Items

-----------------------------------------------------------------------------
| tags                                            | items                   |
-----------------------------------------------------------------------------
| red, red, red, blue, blue, green, green, green  | A, B, C, B, D, E, F, G  |
-----------------------------------------------------------------------------

We don't want all of those duplicates though, so let's just collect the distinct ones:

START tag=node:tags_fulltext('keyword')
MATCH tag<-[:TaggedWith]-item
RETURN collect(distinct tag) AS Tags, collect(distinct item) AS Items

--------------------------------------------
| tags              | items                |
--------------------------------------------
| red, blue, green  | A, B, C, D, E, F, G  |
--------------------------------------------

With the Cypher working, turning it into .NET is an easy translation:

var query = client
    .Cypher
    .StartWithNodeIndexLookup("tag", "tags_fulltext", keyword)
    .Match("tag<-[:TaggedWith]-item")
    .Return((tag, item) => new
    {
        Tags = tag.CollectDistinct<Tag>(),
        Items = item.CollectDistinct<Item>()
    });

Summary

1. Always start with the Cypher
2. Always start with the Cypher
3. When you have working Cypher, the .NET implementation should be almost one-for-one

Problems?

I've typed all of this code out in a textbox with no VS support and I haven't tested any of it. If something crashes, please report the full exception text and query on our issues page. Tracking crashes here is hard. Tracking crashes without the full exception text, message, stack trace and so forth just consumes my time by making it harder to debug, and reducing how much time I can spend helping you otherwise.