I have inherited an application and I need to add a new feature but the existing database is poorly designed as well as the application.

I need to extract with a LINQ query all the employees and the count of their documents and how many of them have been opened.

To count the documents I do a simple count(), and for the opened numbers, I have a boolean field which indicates if the document has been opened or not. If this lat one was an integer with 0 or 1 value, it'd have been very easy, just a sum of this field.

I've tried to do it with the boolean also but it fails, as I can't use convert.toInt32 in Linq-to-Entities:

var employees = from e in context.Employees
                join d in context.EmployeeDocuments on e.EmployeeId equals d.EmployeeId into dj
                from d in dj.DefaultIfEmpty()
                group new { e, d } by new { e.EmployeeId, e.FirstName, e.LastName, e.FiscalCode, e.IdentificationNumber, e.EmploymentStartDate } into g
                select new EmployeeListItem
                           {
                               EmployeeId = g.Key.EmployeeId,
                               FirstName = g.Key.FirstName,
                               LastName = g.Key.LastName,
                               FiscalCode = g.Key.FiscalCode,
                               IdentificationNumber = g.Key.IdentificationNumber,
                               EmploymentStartDate = g.Key.EmploymentStartDate.ToString("dd/MM/yyyy"),
                               DocumentCount = g.Count(),
                               DocumentOpened = g.Sum(s => Convert.ToInt32(s.d.DownloadedByEmployee))
                           };

Any suggestion or workaround without making changes to the DB?

Please note that this query returns an IQueryable, because I need to return a paginated result set, therefore I cannot dump the entities to a list and then do the manipulation of the data.

UPDATE

Ivan's solution is perfect and as I'm still stuck on .Net Core 3.1 I need to use the conditional sum, which gets translated in the following SQL query:

SELECT [e].[EmployeeId], [e].[FirstName], [e].[LastName], [e].[FiscalCode], [e].[IdentificationNumber], [e].[EmploymentStartDate], COUNT(*), COALESCE(SUM(CASE
    WHEN [e0].[DownloadedByEmployee] = CAST(1 AS bit) THEN 1
    ELSE 0
END), 0)
FROM [Employees] AS [e]
LEFT JOIN [EmployeeDocuments] AS [e0] ON [e].[EmployeeId] = [e0].[EmployeeId]
GROUP BY [e].[EmployeeId], [e].[FirstName], [e].[LastName], [e].[FiscalCode], [e].[IdentificationNumber], [e].[EmploymentStartDate]