[CSHARP-2826] MongoDB Collections Join Created: 30/Oct/19  Updated: 31/Mar/22

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

Type: New Feature Priority: Major - P3
Reporter: Farooq Awan Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Mongodb collection join using c# driver and LINQ. I found two relevant solutions list below
 
{{ var table1 = _myRepo.GetAll();var table2 = table1.Where(x => x.Type == "MyType");var try1 = (from x in table1
join y in table2 on new

{ a = x.ClaimId, b = x.CensusId }

equals new

{ a = y.ClaimId, b = y.CensusId }

select x).ToList();}}

This query will throw exception The Join query operator is not supported.
 
{{ var try2 = (from x in table1 from y in table2 where x.ClaimId == y.ClaimId && x.CensusId == y.CensusId select x ).Select(x => x).ToList();}}

This query will throw exception The SelectMany query operator is not supported

But below query works
 
{{ var table1 = _arRepository.GetAll();var table2 = table1.Where(x => x.Type == "MyType");var claimIds = table2.Select(x => x.ClaimId).Distinct().ToList();var censusIds = table2.Select(x => x.CensusId).Distinct().ToList();var data = table1.Where(x => claimIds.Contains(x.ClaimId) && censusIds.Contains(x.CensusId));}}

The problem with this is that it loads the (claimIds and censusIds ) in memory, Actually I want to executes this on server so that it return only required data. I also found a blog for newly mongodb feature (Lookup) discussed in

Blog: https://www.axonize.com/blog/iot-technology/joining-collections-in-mongodb-using-the-c-driver-and-linq/ I flow the blog to install required drivers
 
<PackageReference Include="mongocsharpdriver" Version="2.9.2" /><PackageReference Include="MongoDB.Bson" Version="2.9.2" /><PackageReference Include="MongoDB.Driver" Version="2.9.2" /><PackageReference Include="MongoDB.Driver.Core" Version="2.9.2" />


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