When performing a somewhat convoluted query I get a “could not be translated” exception if I am selecting into a class that uses a constructor. Oddly I get a success if I use the same class but with property initialization instead. I cannot figure out why EF can translate one but not the other. What is the reason for this?

var singleEnvData = GetData().SingleOrDefault(d => d.EnvId == id);

Failing version

IQueryable<Foo> GetData()
{
    return EnvironmentTags
    .Where(et => !et.IsDeleted &&
                 et.PerforceServers.Any(ps => !ps.IsDeleted &&
                                              !ps.PerforceServer.IsDeleted &&
                                              ps.PerforceServer.Status != ServerStatus.Decommissioned) &&
                 et.PerforceServers.Count(ps => ps.PerforceServer.ServerType == ServerType.Commit) >= 1)
    .Select(et => new
    {
        et.Id,
        et.Name,
        Users = et.PerforceServers.SelectMany(ps =>
            ps.PerforceServer.Users.Where(u => u.IsDeleted == false && u.DeletedOnPerforceServer == false))
    })
    .Select(et => new Foo
    (
        et.Id,
        et.Name,
        et.Users.Count(),
        et.Users.Count(u => u.IsSuperUser != null && u.IsSuperUser.Value),
        et.Users.Count(u =>
            u.TicketExpiresOn != null && u.TicketExpiresOn.Value > DateTime.UtcNow.AddYears(1)),
        et.Users.Count(u =>
            u.LastAccessedOn == null || u.LastAccessedOn < DateTime.UtcNow.AddMonths(-3)),
        et.Users.Count(u => u.IsDisabledInAd != null && u.IsDisabledInAd.Value),
        et.Users.Count(u => u.PerforceGroups.Any())
    ));
}

Successful version

IQueryable<Foo> GetData()
{
    return EnvironmentTags
    .Where(et => !et.IsDeleted &&
                 et.PerforceServers.Any(ps => !ps.IsDeleted &&
                                              !ps.PerforceServer.IsDeleted &&
                                              ps.PerforceServer.Status != ServerStatus.Decommissioned) &&
                 et.PerforceServers.Count(ps => ps.PerforceServer.ServerType == ServerType.Commit) >= 1)
    .Select(et => new
    {
        et.Id,
        et.Name,
        Users = et.PerforceServers.SelectMany(ps =>
            ps.PerforceServer.Users.Where(u => u.IsDeleted == false && u.DeletedOnPerforceServer == false))
    })
    .Select(et => new Foo
    {
        EnvId = et.Id,
        EnvName = et.Name,
        Total = et.Users.Count(),
        Supers = et.Users.Count(u => u.IsSuperUser != null && u.IsSuperUser.Value),
        UnlimitedTickets = et.Users.Count(u =>
            u.TicketExpiresOn != null && u.TicketExpiresOn.Value > DateTime.UtcNow.AddYears(1)),
        Inactive = et.Users.Count(u =>
            u.LastAccessedOn == null || u.LastAccessedOn < DateTime.UtcNow.AddMonths(-3)),
            DisabledInAd = et.Users.Count(u => u.IsDisabledInAd != null && u.IsDisabledInAd.Value),
            NoGroupMembership = et.Users.Count(u => u.PerforceGroups.Any())
    });
}

class Foo
{
    public int EnvId { get; set; }
    public string EnvName { get; set; }
    public int Total { get; set; }
    public int Supers { get; set; }
    public int UnlimitedTickets { get; set; }
    public int Inactive { get; set; }
    public int DisabledInAd { get; set; }
    public int NoGroupMembership { get; set; }

    public Foo(
        int envId,
        string envName,
        int total,
        int supers,
        int unlimitedTickets,
        int inactive,
        int disabledInAd,
        int noGroupMembership)
    {
        EnvId = envId;
        EnvName = envName;
        Total = total;
        Supers = supers;
        UnlimitedTickets = unlimitedTickets;
        Inactive = inactive;
        DisabledInAd = disabledInAd;
        NoGroupMembership = noGroupMembership;
    }
    
    public Foo()
    {
        
    }
}

Error*

InvalidOperationException: The LINQ expression ‘DbSet()
//ommitted for brevity
).EnvId == __id_0)’ 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 >’AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’.