public class CategoryDomainModel
{
        public string? _id { get; set; }
        public string? RestaurantID { get; set; }    
        public string? CategoryName { get; set; }
        public Enums.Status Status { get; set; }
}

public class MenuItemDomainModel
{
        public string? _id { get; set; }
        public string? CategoryID { get; set; }
        public string? ImageLink { get; set; }
        public string? ItemName { get; set; }
        public string? ItemDescription { get; set; }
}

Imagine you have these two tables in mongodb and a category has many menus. When you want to join the two tables and get all categories menus by Restaurant ID with a result like this

public class CategoryAndMenusDomainModel
{
        public string? _id { get; set; }
        public string? RestaurantID { get; set; }
        public string? CategoryName { get; set; }
        public Enums.Status Status { get; set; }
        public List<MenuItemDomainModel>? Menus { get; set; }
}

How do you go about it?

Ive tried:

var categoryCollection = database.GetCollection<CategoryDomainModel>("Categories");
var menuCollection = database.GetCollection<MenuItemDomainModel>("Menus");

            var categoriesAndMenus = (from b in categoryCollection.AsQueryable()
                                      join c in menuCollection.AsQueryable()
                                      on b._id equals c.CategoryID
                                      where b.RestaurantID == restautantID
                                      select new CategoryAndMenusDomainModel
                                      {
                                            _id = b._id,
                                            CategoryName = b.CategoryName,
                                            RestaurantID = b.RestaurantID,
                                            Menus = new List<MenuItemDomainModel>
                                            {
                                                new MenuItemDomainModel
                                                {
                                                    _id = c._id,
                                                    ItemName = c.ItemName,
                                                    ItemDescription = c.ItemDescription
                                                }
                                            }
                                      }).ToList();

But its throwing an exception:

"$project or $group does not support new List`1()

CodePudding user response：

Consider this workaround:

        var categoriesAndMenus = (from b in categoryCollection.AsQueryable()
                                  join c in menuCollection
                                  on b._id equals c.CategoryID
                                  where b.RestaurantID == restautantID
                                  select new
                                  {
                                      _id = b._id,
                                      CategoryName = b.CategoryName,
                                      RestaurantID = b.RestaurantID,
                                      C_id = c._id,
                                      C_CategoryName = c.ItemName,
                                      C_ItemDescription = c.ItemDescription
                                  }
                                  )
                                  .ToList()
// below step is client side tranformation, but all required data is already received from the server, so it's just minor projection
                                  .Select(i => new CategoryAndMenusDomainModel
                                  {
                                      _id = i._id,
                                      CategoryName = i.CategoryName,
                                      RestaurantID = i.RestaurantID,
                                      Menus = new List<MenuItemDomainModel>
                                        {
                                            new MenuItemDomainModel
                                            {
                                                _id = i._id,
                                                ItemName = i.CategoryName,
                                                ItemDescription = i.C_ItemDescription
                                            }
                                        }
                                  });

The generated MQL query for this case will be:

{
    "aggregate": "Categories",
    "pipeline": [
        {
            "$lookup": 
            {
                "from": "Menus",
                "localField": "_id",
                "foreignField": "CategoryID",
                "as": "c"            
            }
        }, 
        { "$unwind": "$c" }, 
        { "$match": { "RestaurantID": "1" } }, 
        {
            "$project": 
            {
                "_id": "$_id",
                "CategoryName": "$CategoryName",
                "RestaurantID": "$RestaurantID",
                "C_id": "$c._id",
                "C_CategoryName": "$c.ItemName",
                "C_ItemDescription": "$c.ItemDescription"
            }
        }
    ]
}

I'm 99% confident that client side projection can be moved to the server side, for example via a raw MQL query with $map, see MongoDB project into an array, but it will require more investigation

CodePudding user response：

Linq translates the C# code into SQL. SQL doesn't know about C# lists, so it doesn't know how to format that. You need to do this in two steps: 1) get the data from the database in flat rows, then 2) in C#, put in into the structure you want.

Here I've used the standard Northwind database, but you should be able to adapt it to your data.

var rows = (from c in Categories 
        join p in Products on c.CategoryID equals p.CategoryID
        //where c.CategoryID == 1
        select new 
        {
            _id = c.CategoryID,
            CategoryName = c.CategoryName,
            RestaurantID = c.CategoryID,
            menu_id = p.ProductID,
            ItemName = p.ProductName,
            ItemDescription = p.ProductName
        }).ToList();
        
var categoriesAndMenus =
        (from r in rows
         group r by r._id into grp
         let cat = grp.First()
         select new
         {
             _id = cat._id,
             cat.CategoryName,
             cat.RestaurantID,
             Menu = grp.Select(g =>new
             {
                 _id = g.menu_id,
                 g.ItemName,
                 g.ItemDescription
             } )
         }).ToList();