C# Linq join works but the same structured Linq GroupJoin fails

Question

I have two entities - Clients and Jobs. Clients have 0 to many Jobs associated with them.

Client is as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using JobsLedger.INTERFACES;

namespace JobsLedger.DATA.ENTITIES
{
#nullable enable
    public class Client : IEntityBase, IAuditedEntityBase
    {
        public Client()
        {
            ClientNotes = new List<Note>();
            Jobs = new List<Job>();
        }

        [Key]
        public int Id { get; set; }
        public string ClientNo { get; set; } = default!;
        public bool Company { get; set; }
        public string? CompanyName { get; set; }
        public string? Abn { get; set; }
        public bool IsWarrantyCompany { set; get; }
        public bool RequiresPartsPayment { set; get; }
        public string? ClientFirstName { get; set; }
        public string ClientLastName { get; set; } = default!;
        public string? Email { get; set; }
        public string? MobilePhone { get; set; }
        public string? Phone { get; set; }
        public string? Address1 { get; set; }
        public string? Address2 { get; set; }
        public string? BankName { get; set; }
        public string? BankBSB { get; set; }
        public string? BankAccount { get; set; }
        public bool Active { get; set; }
        public DateTime? DateDeActivated { get; set; }
        public bool Activity { get; set; }

        // One warranty company client to a job.
        public int? WarrantyCompanyId { get; set; }

        public virtual Job? WarrantyCompany { get; set; }

        // One suburb to a client.
        public int? SuburbId { get; set; }
        public virtual Suburb? Suburb { get; set; }

        // If its a warranty company then we simply link it one to one to the brand id.
        public virtual Brand? Brand { get; set; }

        // Multiple notes for each client.
        public virtual ICollection<Note> ClientNotes { get; set; }

        // Multiple jobs for each client.
        public virtual ICollection<Job> Jobs { get; set; }

        public virtual ICollection<Job> WarrantyCompanyJobs { get; } = default!;
    }
#nullable disable
}

Job is as follows:

using System.Collections.Generic;
using JobsLedger.INTERFACES;

namespace JobsLedger.DATA.ENTITIES
{
    public class Job : IEntityBase, IAuditedEntityBase
    {
        public Job()
        {
            JobNotes = new List<Note>();
            Visits = new List<Visit>();
        }

        public string? JobNo { get; set; }
        public string? AgentJobNo { get; set; }


        public int ClientId { get; set; } = default!;
        public virtual Client Client { get; set; } = default!;

        public int? BrandId { get; set; }
        public virtual Brand? Brand { get; set; }

        public int? TypeId { get; set; }
        public virtual JobType? Type { get; set; }

        public int? StatusId { get; set; }
        public virtual Status? Status { get; set; }

        public int? WarrantyCompanyId { get; set; }
        public virtual Client? WarrantyCompany { get; set; }

        public string? Model { get; set; }
        public string? Serial { get; set; }
        public string? ProblemDetails { get; set; }
        public string? SolutionDetails { get; set; }
        public virtual ICollection<Note> JobNotes { get; set; }

        public virtual ICollection<Visit> Visits { get; }

        public int Id { get; set; }
    }
#nullable disable
}

This Linq Join works and I get back a list of ClientIndexDtos.

    public IQueryable<ClientIndexDto> GetClients()
    {
        var result = this._context.Clients.Join(this._context.Jobs, c => c.Id, j => j.Id, (c, j) =>
            new ClientIndexDto
            {
                Id = c.Id,
                ClientNo = c.ClientNo,
                Active = c.Active,
                ClientFirstName = c.ClientFirstName,
                ClientLastName = c.ClientLastName,
                Company = c.Company,
                CompanyName = c.CompanyName,
                MobilePhone = c.MobilePhone,
                IsWarrantyCompany = c.IsWarrantyCompany,
                //JobsCount = j.Count().ToString(CultureInfo.CurrentCulture)
            });
        return result;
    }

But.. I wanted the number of jobs (if any) for each client... so I asked this question on SO and it this was suggested:

    public IQueryable<ClientIndexDto> GetClients()
    {
        var result = this._context.Clients.GroupJoin(this._context.Jobs, c => c.Id, j => j.Id, (c, j) =>
            new ClientIndexDto
            {
                Id = c.Id,
                ClientNo = c.ClientNo,
                Active = c.Active,
                ClientFirstName = c.ClientFirstName,
                ClientLastName = c.ClientLastName,
                Company = c.Company,
                CompanyName = c.CompanyName,
                MobilePhone = c.MobilePhone,
                IsWarrantyCompany = c.IsWarrantyCompany,
                JobsCount = j.Count().ToString(CultureInfo.CurrentCulture)
            });
        return result;
    }

Whilst it works for the join version I am getting the following error when run with groupJoin..

The LINQ expression 'DbSet<Client>()
    .GroupJoin(
        inner: DbSet<Job>(), 
        outerKeySelector: c => c.Id, 
        innerKeySelector: j => j.Id, 
        resultSelector: (c, j) => new ClientIndexDto{ 
            Id = c.Id, 
            ClientNo = c.ClientNo, 
            Active = c.Active, 
            ClientFirstName = c.ClientFirstName, 
            ClientLastName = c.ClientLastName, 
            Company = c.Company, 
            CompanyName = c.CompanyName, 
            MobilePhone = c.MobilePhone, 
            IsWarrantyCompany = c.IsWarrantyCompany 
        }
    )' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I note that the URL - https://go.microsoft.com/fwlink/?linkid=2101038 discusses client - server evaluation... well I naturally want this to occur on the database but am perplexed as to why one (Join) works swimmingly and the other (GroupJoin) falters.

Can someone answer why its not working firstly and then tell me what I need to do to fix it. I would use Join except I need to know how many jobs exist on each client. GroupJoin will give me that if I can get it working...

I'm aware that I would like it to be performed at the database end ie obtain an IQueryable etc so it doesnt drag more records back to the client than necessary..

Any help appreciated.

Answer 1

You have to be aware about the difference between an object that implements IQueryable and an object that implements an IEnumerable.

An IEnumerable represents a sequence of similar items. You can get the first element of the sequence, and once you've got such an element, you can get the next element.

This is usually done using foreach, or LINQ methods like ToList(), Count(), Any(), FirstOrDefault() etc. Deep inside they all use GetEnumerator() and MoveNext() / Current.

On the other hand, an object that implements IQueryable represents the potential to create an IEnumerable object. It does not represent the IEnumerable object itself.

The IQueryable holds an Expression and a Provider. The Expression is a generic form of what must be queried. The Provider knows who must execute the query (usually a database management system) and what language this DBMS uses (usually SQL).

As soon as you start enumerating the IQueryable (GetEnumerator), the Expression is sent to the Provider, who will translate the Expression into SQL and ask the DBMS to execute the query. The returned data is represented as an IEnumerator, so you can call MoveNext / Current.

What has this to do with my problem?

The problem is that the Provider needs to know how to translate your Expression into SQL. Although it is quite smart in how to translate expressions, it is limited in its functionality. It does not know your own classes and methods; the Provider can't translate them to SQL. In fact, there are several LINQ methods that are not supported. See Supported and Unsupported LINQ Methods (LINQ to Entities).

In your case, the problem is that your Provider does not know how to translate ToString(CultureInfo.CurrentCulture) into SQL. It does not know class CultureInfo.

So I can't return my JobsCount as a string?

No you can't.

Normally this is not a problem, because it is not wise to put a number in a string. Apart from that everyone would say that a JobsCount is a number, not some text, it will bring users of your method into trouble if they want to do something with the returned JobsCount, for instance calculate the average JobsCount during a month.

The only reason to convert numbers to strings is because humans can't interpret bits very well, they are much better interpreting textual representation of computer numbers.

Therefore: a proper design would be that numbers are stored in ints, decimals, doubles, etc.

Just before displaying they are converted into a textual representation. The same if for user input: the user types some text. This is checked for correctness and translated into a number. After that it remains a number. The advantage is that you are certain that once you converted it to a number, you'll never have to check the correctness of the format again.

Besides: numbers are way more efficient for computers than strings.

So my advice would be: keep it a number and only convert it to a text when you have to: to display it, to save it in a text file (json, xml), to communicate over internet etc. Try to do the conversion as late as possible.

But I can't change my class, I have to convert it to a string!

Well, in that case: fetch your data as a number, and use AsEnumerable(). This moves your number to local process. There you can convert your number using any local code that you want.

var result = this._context.Clients.GroupJoin((client, jobsForThisClient) => new
{
    Id = client.Id,
    ClientNo = client.ClientNo,
    ...

    JobsCount = jobsForThisClient.Count(),
})

// Execute the query and move the fetched data to local process
AsEnumerable()

// put the fetched data in a ClientIndexDto
.Select(fetchedData => new ClientIndexDto
{
    Id = fetchedData .Id,
    ClientNo = fetchedData .ClientNo,
    ...

    JobsCount = fetchedData.JobsCount.ToString(CultureInfo.CurrentCulture),
});

This is not less efficient. In fact: it might be even more efficient, as your JobsCount is transferred as an Int32: four bytes only. Most textual representations of a JobsCount takes more than four bytes.

Don't forget to thwack the designer who thought that a JobsCount is a piece of text.