Join Into - $project or $group does not support {document}

I have two classes (job and service) which share a JobId and I’m trying to locate all of the job records that do not have any corresponding service records as follows;

public List<Job> GetAbandonedJobs()
{
	IMongoCollection<Job> jobColl = DatabaseService.GetCollection<Job>(_db, COLLECTION_NAME);
	IMongoCollection<Service> svcColl = DatabaseService.GetCollection<Service>(_db, "Service");

	var query = from jobs in jobColl.AsQueryable()
				join services in svcColl.AsQueryable() on jobs.JobId equals services.JobId into joinGroup
				from services in joinGroup.DefaultIfEmpty()
				where services.JobId == null
				select new Job
				{
					ID = jobs.ID,
					JobId = jobs.JobId,
				};

	var results = query.ToList();
	return results;
}

This however is returning the error $project or $group does not support {document}. I have also tried;

public List<Service> GetAbandonedJobs()
{
	IMongoCollection<Job> jobColl = DatabaseService.GetCollection<Job>(_db, COLLECTION_NAME);
	IMongoCollection<Service> svcColl = DatabaseService.GetCollection<Service>(_db, "Service");

	var query = from job in jobColl.AsQueryable()
				join service in svcColl.AsQueryable() on job.JobId equals service.JobId into joinGroup
				from service in joinGroup.DefaultIfEmpty()
				select new Service
				{
					ID = job.ID,
					JobId = job.JobId,
					ServiceId = service.ServiceId,
				};

	List<Service> results = query.ToList();
	results = results.Where(x => x.ServiceId == null).ToList();
	return results;
}

Which returns numerous records but when they are filtered to return only those where ServiceId is null I get zero records. I know this cant be correct as the following query in Compass returns multiple records;

db.Job.aggregate([
  {
    $lookup: {
      from: "Service",
      localField: "JobId",
      foreignField: "JobId",
      as: "matchingRecords"
    }
  },
  {
    $match: {
      matchingRecords: { $size: 0 }
    }
  }
])

Hi, @Raymond_Brack,

Based on the exception message, it looks like you’re using the LINQ2 provider, which does not support the LINQ join syntax that you are using. Our newer LINQ3 provider does support this LINQ construct. LINQ3 is the default LINQ provider in 2.19.0 or later driver. For driver versions 2.14.0 to 2.18.x, you can opt into LINQ3 using code similar to the following:

var connectionString = "mongodb://localhost";
var clientSettings = MongoClientSettings.FromConnectionString(connectionString);
clientSettings.LinqProvider = LinqProvider.V3;
var client = new MongoClient(clientSettings);

Please try the new LINQ3 provider and let us know if it returns the expected results from your query.

Sincerely,
James

Hi James,

Thanks for the prompt reply.

I updated to 2.19.2 and tried it with the following and got no error however I still got no records.

IQueryable<Job> query = from jobs in jobQuery
						join services in svcQuery on jobs.JobId equals services.JobId into joinGroup
						from services in joinGroup.DefaultIfEmpty()
						where services.JobId == null
						select new Job
						{
							ID = jobs.ID,
							RunId = jobs.RunId,
							JobId = jobs.JobId,
						};

I wrote some code to loop through each job record and count how many of those had no service records and got to 36 before I stopped running the code, there are over 100,000 job records so it was taking it a while.

I would recommend reviewing the MQL generated by this LINQ query to understand why no results are being returned. You can view the MQL by calling query.ToString(), setting a breakpoint to view the value of query in the debugger (which also implicitly calls ToString()), or installing the MongoDB Analyzer in your project (which will display the MQL as a tooltip). In most cases the easiest solution is to simply write the query to the console (which implicitly calls ToString()):

Console.WriteLine(query);

Hopefully by reviewing the generated MQL you will be able to tweak your LINQ query to resolve the issue.

Sincerely,
James

Hi James,

Viewing the MQL resolved the issue - the joinGroup.DefaultIfEmpty() option was creating an empty JobID, UUID("00000000-0000-0000-0000-000000000000") rather than a null. Changing the where to service.JobId == Guid.Empty resolved the issue.

Thanks again for your help.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.