[CSHARP-1775] Support Left Outer Joins Created: 04/Oct/16  Updated: 08/Feb/23

Status: Backlog
Project: C# Driver
Component/s: Linq
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Dan Cumings Assignee: Unassigned
Resolution: Unresolved Votes: 7
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

example:

    var query = from p in Db.GetCompanyCollection<AwardProviderProduct>(companyIdentifier).AsQueryable()
                join o in Db.GetGlobalCollection<Product>() on p.Sku equals o.Sku
                where p.Reference[ProductReferenceType.AwardchoiceSource.ToString()] == "NORC"
                select new AwardProviderProduct()
                {
                  
                    Sku = p.Sku,
                  
                };

creates

 
db['terryberry-AwardProviderProduct'].aggregate(
[
{ "$lookup" : { "from" : "Product", "localField" : "Sku", "foreignField" : "Sku", "as" : "o" } }, 
{ "$unwind" : "$o" },
{ "$project" :
{
     "Sku" : "$Sku"
}
}       
,
{"$match" : { "Reference.AwardchoiceSource" : "NORC" }} ])

The query does not work because of the seemingly useless unwind statement

Unknown macro: { "$unwind" }

,



 Comments   
Comment by Daniel Schlieckmann [ 17/Oct/19 ]

Craig any news? I would like to have this feature implemented, i dont like implementing a workaround for it

Comment by Carlos Solano [ 30/Aug/19 ]

I would love to have this feature, any workarounds for now??

Comment by Ed Lomonaco [ 22/Jan/19 ]

that'd be great to see fixed

Comment by Dan Cumings [ 22/Jan/19 ]

This appears to still be broken.  Any plans on getting this working?  Any workarounds?

Comment by Craig Wilson [ 02/Nov/16 ]

Hi Daniel,

The "join" keyword implies an inner join. $unwind will eliminate the right side where there are no items in the array. To do a left outer join, you need to follow with a from joined.DefaultIfEmpty().

var query = from p in Db.GetCompanyCollection<AwardProviderProduct>(companyIdentifier).AsQueryable()
                join o in Db.GetGlobalCollection<Product>() on p.Sku equals o.Sku into joined
                from j in joined.DefaultIfEmpty()
                where p.Reference[ProductReferenceType.AwardchoiceSource.ToString()] == "NORC"
                select new AwardProviderProduct()
                {
             
                    Reference = p.Reference,
                    Sku = p.Sku
         
                };

This will get you a left outer join. However, it appears as though we still don't include the proper "includeNullAndEmptyArrays" in the $unwind stage. So, for now, Left Joins simply aren't supported. I'll convert this into a feature request.

Thanks for reporting,
Craig

Comment by Dan Cumings [ 05/Oct/16 ]

Basically the: $unwind need to be changed so it includes null arrays(new in Mongo 3.2) .

	
	{$lookup:  { "from" : "Product", "localField" : "Sku", "foreignField" : "Sku", "as" : "o" }},
	{ "$unwind" : { "path" : "$o", "preserveNullAndEmptyArrays" : true } },

I had to re-arrange the pipeline in my case so that $unwind happend directly after the $lookup.

 public static async Task<List<T>> GetIQueryAbleResult<T>(this IMongoCollection<T> collection,
                            IMongoQueryable<T> originalQuery, List<BsonDocument> additionalPipeline = null,int ? batchSize=null,int ? skip=null)
        {
            string originalQueryString = originalQuery.ToString();
            var queryString = originalQueryString.Replace("aggregate(", "").Replace(", \"_id\" : 0","").RemoveFromEnd(")");
 
 
            var list = BsonSerializer.Deserialize<List<BsonDocument>>(queryString);
            var newList = new List<BsonDocument>();
            foreach (BsonDocument bsonDocument in list)
            {
                string name = bsonDocument?.Elements?.FirstOrDefault().Name;
              
                if ( name == "$unwind" )
                {
                    newList.Add(BsonSerializer.Deserialize<BsonDocument>($"{{ $unwind:{{path:\"{bsonDocument.FirstOrDefault().Value.ToString()}\",preserveNullAndEmptyArrays:true}}}}"));
                }
                else
                {
 
                    newList.Add(bsonDocument);
                }
 
 
          
            }
           
            if ( additionalPipeline.IsNotEmpty() )
            {
                foreach ( BsonDocument additionalQueries in additionalPipeline )
                {
                    newList.Add(additionalQueries);
                }    
            }
            
 
            PipelineDefinition<T, T> query = new BsonDocumentStagePipelineDefinition<T, T>(newList);
 
            AggregateOptions options = new AggregateOptions()
            {
                BatchSize = batchSize>0 ? batchSize:null
                
            };
            var cursor = collection.Aggregate<T>(query,options);
           
            string message = cursor.ToString();
            Terryberry.Architecture.Logging.Logger.Trace(message);
            return await cursor.ToListAsync();
        }
    }

Comment by Dan Cumings [ 04/Oct/16 ]

I stripped out too much the actual working sample would look like this:

            var query = from p in Db.GetCompanyCollection<AwardProviderProduct>(companyIdentifier).AsQueryable()
                join o in Db.GetGlobalCollection<Product>() on p.Sku equals o.Sku
                where p.Reference[ProductReferenceType.AwardchoiceSource.ToString()] == "NORC"
                select new AwardProviderProduct()
                {
             
                    Reference = p.Reference,
                    Sku = p.Sku
         
                };

is

db['terryberry-AwardProviderProduct'].aggregate(
[
{ "$lookup" : { "from" : "Product", "localField" : "Sku", "foreignField" : "Sku", "as" : "o" } }, 
{ "$unwind" : "$o" },
{ "$project" :
{ 
    "Reference" : "$Reference", 
     "Sku" : "$Sku",
    
}
}
         
,
{"$match" : { "Reference.AwardchoiceSource" : "NORC" }} ])

Generated at Wed Feb 07 21:40:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.