C# driver and linq v3 to join collections

I’m having issues with the joining of collections and coming back null when there is data.

    var query = from cabinet in filteredCabinet.AsQueryable()
                join meds in _med.AsQueryable() on cabinet.Id equals meds.MedicineCabinetId into joined
                where (cabinet.AccountProfileId == accountProfileId)
                select new { cabinet, joined };

Is there something I’m doing wrong?

Hi, @Annette_Varndell,

Welcome to the MongoDB Community Forums. I understand that you’re having problems with a LINQ join query not returning the expected data. Based on your description, you have configured LINQ3. Rendering this query with LINQ3 produces the following MQL. (Your collection names may differ, but the remaining MQL should be the same.)

test.cabinets.Aggregate([{ "$project" : { "_outer" : "$$ROOT", "_id" : 0 } }, { "$lookup" : { "from" : "medicines", "localField" : "_outer._id", "foreignField" : "MedicineCabinetId", "as" : "_inner" } }, { "$project" : { "cabinet" : "$_outer", "joined" : "$_inner", "_id" : 0 } }, { "$match" : { "cabinet.AccountProfileId" : 42 } }, { "$project" : { "cabinet" : "$cabinet", "joined" : "$joined", "_id" : 0 } }])

Running this query on a collection with a single cabinet and 2 medicines within that cabinet returns the expected results - a cabinet along with an IEnumerable<Medicine> containing the two medicines. So your code is doing something different than my simple repro.

In order to investigate further, please provide:

  • MongoDB driver and server versions
  • Self-contained repro demonstrating the problem (including sample data)
  • List of expected results

Sincerely,
James

Thanks for your reply. I’m using 2.17.1 driver. and it’s returning the cabinet but no medications. I will put together a repo.