MongoDB - multiple lookups using .NET driver and LINQ (with grouping and filtering)

Viewed 919

Given following exemplary MongoDB collection models:

public class House
{
    public int Id { get; set; }
    public string Address { get; set; }
    public double SquareFeet { get; set; }
    public int NumberOfBedrooms { get; set; }
    public int NumberOfKitchens { get; set; }
}

public class Mortgage
{
    public int Id { get; set; }
    public int HouseId {get; set; } // FK 
    public decimal Sum { get; set; }
    public string CurrencyName { get; set; }
}

public class Mortgagee
{
    public int Id { get; set; }
    public int MortgageId { get; set; } // FK
    public string InstitutionName { get; set; }
    public string InstitutionAddress { get; set; }
}

public class Mortgagor
{
    public int Id { get; set; }
    public int MortgageId { get; set; } // FK
    public string Name { get; set; }
    public string Address { get; set; }
}


public class Owner
{
    public int Id { get; set; }
    public int HouseID { get; set; } // FK
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

I would like to produce following query output:

{
    "House": {
        "Id": 123,
        "Address": "Some city, 1234, Unknown St.",
        "SquareFeet": "12345.67",
        "NumberOfBedrooms": 4,
        "NumberOfKitchens": 2,
    },
    "Mortgages": [
        {
            "Id": 234,
            "HouseId": 123,
            "Sum": 1234.56,
            "CurrencyName": "USD",
            "Mortgagee": {
                "Id": 345,
                "MortgageId": 234,
                "InstitutionName": "Some institution",
                "InstitutionAddress": "Some city, 5678, Unknown St."
            },
            "Mortgagors": [
                {
                    "Id": 456,
                    "MortgageId": 234,
                    "Name": "John Smith",
                    "Address": "Some city, 1234, Unknown St."
                },
                {
                    "Id": 567,
                    "MortgageId": 234,
                    "Name": "Ann Smith",
                    "Address": "Some city, 1234, Unknown St."
                }
            ]
        }
    ],
    "Owners": [
        {
            "Id": 678,
            "HouseId": 123,
            "FirstName": "John",
            "LastName": "Smith"
        },
        {
            "Id": 789,
            "HouseId": 123,
            "FirstName": "Ann",
            "LastName": "Smith"
        }
    ]
}

I understand that it's possible to use Lookup function to perform join operations, however all examples I can find (both in MongoDB documentation and on SO, blogs etc.) are pretty simple and don't include any grouping and filtering operations for each lookup (aggregations) which I would like to do, as real model is more complex.

So essentially what I would like to do is:

  1. Create filter for house
  2. Match all houses matching house filter and group them
  3. Lookup all mortgages for house
  4. Filter mortgages and group them
  5. Lookup all mortgagees for all mortgages
  6. Lookup all mortgagors for all mortgages
  7. Lookup all owners for house
  8. Filter owners and group them
  9. ...

This should happen within one DB call.

What I can do now is:

public class HouseResult: House
{
    public IList<Mortgage> Mortgages { get; set; }
    public IList<Owner> Owners { get; set; }
}

var housesWithMortgagesAndOwners = housesCollection
    .Aggregate()
    .Match(houseFilter)
    .Lookup<House, Mortgage, HouseResult>(
        mortgagesCollection,
        localField => localField.Id,
        foreignField => foreignField.HouseId,
        output => c.Mortgages)
    .Lookup<HouseResult, Owner, HouseResult>(
        ownersCollection,
        localField => localField.Id,
        foreignField => foreignField.HouseId,
        c => c.Owners)
    .ToList();

However I'm not sure how to proceed with Lookup() for nested arrays (in example above - how to do lookup of all Mortgagees/Mortgagors for every Mortgage. I know that I can Unwind() array, but then structure I was building before will be gone, as I will replace my root with list of mortgages.

Additionally - I'm now sure how I should effectively apply grouping/filtering to Lookup().

Thanks for all suggestions!

0 Answers
Related