CODEWITHSUNDEEP
c#mongodblinqjoiniqueryable
Rony Patel
Rony Patel

Reputation: 367

How to join multiple documents?

I want to join multiple documents in MongoDB. I have used following code, but it isn't working. Did I do any think wrong?. I have added base entity model which contains Id. 

var products = _productRepository.GetCollection(_dbContext);
var prodWarhoseMapping = _productWarehouseMapRepository.GetCollection(_dbContext);
var warhouses = _warehouseRepository.GetCollection(_dbContext);

public class Product:BaseEntity
{
    public string product_code { get; set; }
    public decimal fat_tax { get; set; }
}

public class ProductWarehouseMap :BaseEntity
{
    public ObjectId product_id { get; set; }
    public ObjectId warehouse_id { get; set; }
    public int qty { get; set; }
    public decimal price { get; set; }
}
public class Warehouse :BaseEntity
{
    public ObjectId supplier_id { get; set; }
    public string warehouse_code { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public string phone { get; set; }
    public string street_address { get; set; }
    public string city { get; set; }
    public string postal_code { get; set; }
    public string region { get; set; }
    public string country { get; set; }
    public string manager_name { get; set; }
    public string fax { get; set; }
}

//works proper
var query1 = (from p in products
            join pm in prodWarhoseMapping on p._id equals pm.product_id
                        select new
                        {
                            Products = p,
                            ProductMapping = pm,
                        }).ToList();

//Getting error 
var query = (from p in products
    join pm in prodWarhoseMapping on p._id equals pm.product_id
    join wh in warhouses on pm.warehouse_id equals wh._id
    select new
    {
         Products = p,
         ProductMapping = pm,
         Warehouse = wh
     }).ToList();

Bellow is the base entity I forgot to add base entity model

public class BaseEntity
{
    [BsonIgnoreIfDefault]
    public ObjectId _id { get; set; }

}

Bellow is error message

Expression of type 'System.Collections.Generic.IEnumerable1[Project.Communication.EntityModels.ProductWarehouseMap]' cannot be used for parameter of type 'System.Linq.IQueryable1[Project.Communication.EntityModels.ProductWarehouseMap]' of method 'System.Linq.IQueryable1[Project.Communication.EntityModels.ProductWarehouseMap] Where[ProductWarehouseMap](System.Linq.IQueryable1[Project.Communication.EntityModels.ProductWarehouseMap], System.Linq.Expressions.Expression1[System.Func2[Project.Communication.EntityModels.ProductWarehouseMap,System.Boolean]])' Parameter name: arg0

Upvotes: 0

Views: 920

Answers (1)

Keyur PATEL
Keyur PATEL

Reputation: 2329

Your error message makes it seem Where is used, but it isn't (assuming the code you are showing is exactly what you tried compiling). Nevertheless, you can try some variations such as

Performing Select() in memory using IEnumerable:

var query = (from p in products
    join pm in prodWarhoseMapping on p._id equals pm.product_id
    join wh in warhouses on pm.warehouse_id equals wh._id)
    .AsEnumerable()
    .Select(s => new 
    {
         Products = p,
         ProductMapping = pm,
         Warehouse = wh
    }).ToList();

Or using AsQueryable()

var query = (from p in products.AsQueryable()
    join pm in prodWarhoseMapping.AsQueryable() on p._id equals pm.product_id
    join wh in warhouses.AsQueryable() on pm.warehouse_id equals wh._id
    select new
    {
         Products = p,
         ProductMapping = pm,
         Warehouse = wh
    }).ToList();

EDIT: the above approaches will take very long and use a lot of memory if the data sets are huge.

In order to find the underlying cause, could you try this query and let me know what it returns:

var query2 = (from pm in prodWarhoseMapping
        join wh in warhouses on pm.warehouse_id equals wh._id
                    select new
                    {
                        ProductMapping = pm,
                        Warehouse = wh,                            
                    }).ToList();

Upvotes: 1

Related Questions