MongoDB Linq query in C# with filters

Question

I am trying to do a LINQ query on several Mongo collections. All the collections have to be joined based on ApplicationId and an outer Join has to be done - so that persons that have no statuses are returned as well. The JOIN part and everything around it works as expected. The problem is that when I add a filter to one of the collections, the whole thing breaks

An exception of type 'System.ArgumentException' occurred in System.Linq.Expressions.dll but was not handled in user code: 'Expression of type 'System.Collections.Generic.IEnumerable`1[CDM.Person]' cannot be used for parameter of type 'System.Linq.IQueryable`1[CDM.Person]' of method 'System.Linq.IQueryable`1[CDM.Person] Where[Person](System.Linq.IQueryable`1[CDM.Person], System.Linq.Expressions.Expression`1[System.Func`2[CDM.Person,System.Boolean]])''

Here is my query

var applications = _dbContext.GetCollection<Application>(typeof(Application).Name).AsQueryable().Where(
                x => x.OrganizationID == TokenContext.OrganizationID);

var persons = _dbContext.GetCollection<Person>(typeof(Person).Name).AsQueryable().Where(p =>p.FirstName == "j");
var statuses = _dbContext.GetCollection<ApplicationStatus>(typeof(ApplicationStatus).Name).AsQueryable();
var mortgages = _dbContext.GetCollection<Mortgage>(typeof(Mortgage).Name).AsQueryable();

            var statusQuery = from a in applications
                                join p in persons on a.ApplicationID equals p.ApplicationID 
                                join s in statuses on a.ApplicationID equals s.ApplicationID into pas
                                join m in mortgages on a.ApplicationID equals m.ApplicationID into morgs
                                from subWHatever in pas.DefaultIfEmpty()
                              select new ApplicationStatusProjection
                              {
                                  ApplicationId = a.ApplicationID,
                                  FirstName = p.FirstName,
                                  LastName = p.Surname,
                                  Prefix = p.Prefix,
                                  DateOfBirth = p.DateOfBirth,
                                  Initials = p.Initials,
                                  PostalCode = p.Addresses.First().PostalCode,
                                  MortgageAmount = morgs.Sum(i => i.MortgageTotal) ?? 0,
                                  StatusExpireAt = subWHatever.ExpireAt ?? DateTime.MinValue,
                                  StatusMessageText = subWHatever.MessageText ?? "",
                                  StatusMessage = subWHatever.MessageStatus ?? ""

                              };

            if (!String.IsNullOrEmpty(orderBy))
            {
                statusQuery = statusQuery?.OrderBy(orderBy);
            }

            if (nrOfRecords != null)
            {
                statusQuery = statusQuery?.Take(nrOfRecords.Value);
            }

            // Execute the query
            var result = statusQuery?.ToList();
            return result;

I followed the guidelines here https://mongodb.github.io/mongo-csharp-driver/2.6/reference/driver/crud/linq/ and I also tried this

var statusQuery = 
    from a in applications
       join p in persons on a.ApplicationID equals p.ApplicationID into pa
    from paObject in pa.DefaultIfEmpty()
       join s in statuses on paObject.ApplicationID equals s.ApplicationID into pas

But I got the same error as before.

Thank you in advance.

Answer 1

So after may tryouts I have discovered that you cannot filter before the join because of the way the LINQ query is translated to Mongo query. The fix for this is to have the filtering afterwards, on the statusQuery object. In that case, the filtering has to happen on the projected object (so a new filter is needed). See below how I solved it:

 //get collections
            var applications = _dbContext.GetCollection<Application>(typeof(Application).Name).AsQueryable().Where(
                x => x.OrganizationID == TokenContext.OrganizationID);
            var persons = _dbContext.GetCollection<Person>(typeof(Person).Name).AsQueryable();
            var statuses = _dbContext.GetCollection<ApplicationStatus>(typeof(ApplicationStatus).Name).AsQueryable();
            var mortgages = _dbContext.GetCollection<Mortgage>(typeof(Mortgage).Name).AsQueryable();

            //query
            var query = from a in applications
                              join p in persons on a.ApplicationID equals p.ApplicationID
                              join s in statuses on a.ApplicationID equals s.ApplicationID into applicationStatusView
                              join m in mortgages on a.ApplicationID equals m.ApplicationID into morgs
                              from subStatus in applicationStatusView.DefaultIfEmpty()
                              select new ApplicationStatusProjection
                              {
                                  ApplicationId = a.ApplicationID,
                                  FirstName = p.FirstName,
                                  LastName = p.Surname,
                                  Prefix = p.Prefix,
                                  DateOfBirth = p.DateOfBirth,
                                  Initials = p.Initials,
                                  Addresses = p.Addresses ?? new List<Address>(),
                                  PostalCode = p.Addresses.First().PostalCode,
                                  MortgageAmount = morgs.Sum(i => i.MortgageTotal) ?? 0,
                                  StatusExpireAt = subStatus.ExpireAt ?? DateTime.MinValue,
                                  StatusMessageText = subStatus.MessageText ?? "",
                                  StatusMessage = subStatus.MessageStatus ?? "",
                                  StatusDate = subStatus.StatusDate

                              };

            //filter & order
            var filteredResult = ApplyFilters(query, searchCriteria);

            if (!String.IsNullOrEmpty(orderBy))
            {
                filteredResult = filteredResult?.OrderBy(orderBy);
            }

            if (nrOfRecords != null)
            {
                filteredResult = filteredResult?.Take(nrOfRecords.Value);
            }

            // Execute the query
            var result = filteredResult?.ToList();
            return result;

And applying the filters (there is probably a smarter way to do this):

 private IQueryable<ApplicationStatusProjection> ApplyFilters(IQueryable<ApplicationStatusProjection> query, ApplicationStatusProjectionSearch searchCriteria)
        {
            if (!string.IsNullOrEmpty(searchCriteria.FirstName))
            {
                query = query.Where(x => x.FirstName.ToLower().StartsWith(searchCriteria.FirstName));
            }

            if (!string.IsNullOrEmpty(searchCriteria.LastName))
            {
                query = query.Where(x => x.LastName.ToLower().StartsWith(searchCriteria.LastName));
            }

            if (!string.IsNullOrEmpty(searchCriteria.PostalCode))
            {
                query = query.Where(x => x.Addresses.Any(a => a.PostalCode.ToLower().StartsWith(searchCriteria.PostalCode)));
            }
//other irrelevant filters
            return query;
}