One to many query?

[adamlubek]

I'm looking through linq provider unite tests and I can't see any example of using Horizontal lists (https://docs.arangodb.com/AqlExamples/Join.html).

For example, I have addresses collection

[{"Id":1},{"Id":2] 

And people collection:

[{"AddressId":1,"LastName":"a"},{"AddressId":2,"LastName":"b"},{"AddressId":1,"LastName":"c"}]  

I would like to get resulting object:

[
    {
        "address": {
            "Id" : 1
            "People": [
                {
                    "LastName" : "a"
                },
                {
                    "LastName" : "c"
                }
            ]
        }
    }
    {
        "address": {
            "Id" : 2
            "People": [
                {
                    "LastName" : "b"
                }
            ]
        }
    }
]

in AQL I would write:

FOR address IN addresses
    RETURN {
        address: address,
        people: (
            FOR person IN people
                FILTER person.AddressId == address.Id
                    RETURN person
        )
    }

Is this supported in linq provider?

[ra0o0f]

yes it's supported, aql joins in linq is done by SelectMany

your query(much like subquery) would be like this:

var query3 = db.Query<Address>()
                    .Select(a => new
                    {
                        address = a,
                        people = db.Query<Person>()
                            .Where(p => a.Id == p.AddressId)
                            .Select(p => p)
                    })
                    .GetQueryData();

                // print aql query
                Console.WriteLine(query.QueryReplacedWithVariables(db));

join sample would be:

var query = db.Query<Person>()
                    .For(p => db.Query<Address>()
                    .Where(a => a.Id == p.AddressId)
                    .Select(a => new
                    {
                        a.StreatName,
                        p.LastName
                    }))
                    .GetQueryData();

                // print aql query
                //Console.WriteLine(query.QueryReplacedWithVariables(db));

                var query2 = (from p in db.Query<Person>()
                              from a in db.Query<Address>()
                              where a.Id == p.AddressId
                              select new
                              {
                                  a.StreatName,
                                  p.LastName
                              }).GetQueryData();

                // print aql query
                //Console.WriteLine(query2.QueryReplacedWithVariables(db));

[adamlubek]

How do I execute the query against db?

When trying to do

db.Query<Address>()
                    .Select(a => new
                    {
                        address = a,
                        people = db.Query<Person>()
                            .Where(p => a.Id == p.AddressId)
                            .Select(p => p)
                    }).ToList()

I get:

{"Cannot create and populate list type System.Linq.IQueryable`1[Cw.BaApi.DataLayer.ArangoDb.Collections.LineItem]. Path 'people', line 1, position 385."}

[ra0o0f]

GetQueryData is an extention method to see what linq is produce, i use it to show you what query text will be, there is no need for that:

var result = db.Query<Address>()
                    .Select(a => new
                    {
                        address = a,
                        people = db.Query<Person>()
                            .Where(p => a.Id == p.AddressId)
                            .Select(p => p)
                    })
                    .ToList();

[adamlubek]

I'm getting "ArangoDB.Client.Common.Newtonsoft.Json.JsonSerializationException" when trying to do above.

Looks like it's due to issue with deserializing the people list (exception is thrown in ArangoDB.Client.Common.Newtonsoft.Json.Serialization.JsonSerializerInternalReader: case JsonToken.StartArray: return CreateList(reader, objectType, contract, member, existingValue, null);

when code reaches JsonSerializerInternalReader.CreateNewList method, contract.CanDeserialize is false and serialization exception is thrown:

if (!contract.CanDeserialize) throw JsonSerializationException.Create(reader, "Cannot create and populate list type {0}.".FormatWith(CultureInfo.InvariantCulture, contract.CreatedType));

Looks like issue is with objectType being System.Linq.IQueryable (people subquery type) at case JsonToken.StartArray: return CreateList(reader, objectType, contract, member, existingValue, null);

which throws exception when it's attempted to be deserialized

[ra0o0f]

sorry i should have know, try this:

var query3 = db.Query<Address>()
                    .Select(a => new
                    {
                        address = a,
                        People = AQL.As<List<Person>>(db.Query<Person>()
                            .Where(p => a.Id == p.AddressId)
                            .Select(p => p))
                    })
                    .ToList();

this tells json.net to deserialize it to the List<Person>

[adamlubek]

That's working now, thanks a lot ra0o0f :) It would be nice if you had this either documented or unit tested so others encountering this wouldn't need to bother you with questions like this issue.