I built tiny test project available at https://github.com/MyGitHubTests/MongoNestedTests/tree/master/MongoTest
Here's my JSON document example github.com/MyGitHubTests/MongoNestedTests/blob/master/MongoTest/MongoTest/TestJSON/MongoTest2.json :
{
"Cars": [
{
"Engine": { "Type": "V8", "Displacement": 4.8 },
"Body": { "Style": "Targa", "Color": "White" },
"Drivers": [
{
"Name": { "First": "Bob", "Last": "Fast" },
"Address": { "Street": "Top Ct", "City": "Seattle" },
"Contact": { "Phone": "(000) 000-0000", "Email": "[email protected]" }
}
],
"Trips": [
{
"Note" : "Trip F",
"Mileage": { "Start": 1000, "End": 2000 },
"Period": { "Start": "2017-12-01T00:00:00", "End": "2017-12-02T00:00:00" },
"Cargo": [
{
"Size": { "Height": 100, "Wight": 200, "Lenght": 300 },
"Info": { "Weight": 400, "Name": "Cargo Name F1"}
},
{
"Size": { "Height": 500, "Wight": 600, "Lenght": 700 },
"Info": { "Weight": 800, "Name": "Cargo Name F2"}
},
{
"Size": { "Height": 900, "Wight": 1000, "Lenght": 1100 },
"Info": { "Weight": 1200, "Name": "Cargo Name F3"}
}
]
}
]
}
]
}
Poco classes representing JSON above https://github.com/MyGitHubTests/MongoNestedTests/blob/master/MongoTest/MongoTest/Poco.cs , as you can see it's quite simple and straight forward with exception of nested objects:
public class Cargo
{
public Size Size { get; set; }
public Info Info { get; set; }
}
public class Trip
{
public string Note { get; set; }
public Mileage Mileage { get; set; }
public Period Period { get; set; }
public List<Cargo> Cargo { get; set; }
}
public class Car
{
public Engine Engine { get; set; }
public Body Body { get; set; }
public List<Driver> Drivers { get; set; }
public List<Trip> Trips { get; set; }
}
public class Poco
{
public ObjectId Id { get; set; }
public List<Car> Cars { get; set; }
}
I'm trying to use database.GetCollection().AsQueryable() to query data and it produces rather odd results.
Here's my code github.com/MyGitHubTests/MongoNestedTests/blob/master/MongoTest/MongoTest/Program.cs
var collection = database.GetCollection<Poco>("cars");
var queryable = collection.AsQueryable<Poco>();
var count = queryable.Count();
Console.WriteLine("Queryable {0} count: {1}", queryable, count);
// Working example
var cars = queryable.Select(a => a.Cars);
foreach (IList<Car> car in cars)
{
Console.WriteLine("car.Count {0}", car.Count);
}
// Working example
var qry = queryable.Select(a => a.Cars.Select(b => b.Engine));
foreach (IList<Engine> engines in qry)
foreach (Engine engine in engines)
{
Console.WriteLine("Engine Type {0}, Displacement {1}", engine.Type, engine.Displacement);
}
// Working example
var result = from a in queryable
from b in a.Cars
select b.Engine;
foreach (Engine engine in result)
{
Console.WriteLine("Engine Type {0}, Displacement {1}", engine.Type, engine.Displacement);
}
// Working example
// Note: it's not clear why I have to use List here instead of IList as above
var drivers = queryable.Select(a => a.Cars.Select(b => b.Drivers));
foreach (List<List<Driver>> listA in drivers)
foreach (List<Driver> listB in listA)
foreach (Driver driver in listB)
{
Console.WriteLine("Driver Name: {0}, {1}", driver.Name.First, driver.Name.Last);
}
// 'System.NotSupportedException' : {"$project or $group does not support {document}."}
//var resultDriver = from a in queryable
// from b in a.Cars
// from c in b.Drivers
// select c;
//foreach (Driver driver in resultDriver)
//{
// Console.WriteLine("Driver Name: {0}, {1}", driver.Name.First, driver.Name.Last);
//}
// Working example
// Note: it's not clear why I have to use IEnumerable here instead of List as above
var cargoList = queryable.Select(a => a.Cars.Select(b => b.Trips.Select(c => c.Cargo)));
foreach (List<IEnumerable<List<Cargo>>> listA in cargoList)
foreach (IEnumerable<List<Cargo>> listB in listA)
foreach (List<Cargo> listC in listB)
foreach (Cargo cargo in listC)
{
Console.WriteLine("Cargo Name: {0}, Height: {1}", cargo.Info.Name, cargo.Size.Height);
}
Here's my questions:
1) Look at the last example where returned type of cargoList is List<IEnumerable<List<Cargo>>>
- I can't not figure out why there's combination of List - IEnumerable - List, my classes only use List<>
so naturally this is what I expect.
2) Case where I can select drivers via var drivers = queryable.Select(a => a.Cars.Select(b => b.Drivers));
works but if I try to do same thing using var resultDriver = from a in queryable from b in a.Cars from c in b.Drivers select c;
I get 'System.NotSupportedException' : {"$project or $group does not support {document}."}
3) Finally is there a way to unwind above results from List<IEnumerable<List<Cargo>>>
to just List<Cargo>
?