Does Entity Framework Core support queries based on a table of custom values?

I'm using a T-SQL query like

SELECT 
    WeekStart,
    (SELECT COUNT(DISTINCT UserId) 
     FROM Logins 
     WHERE Timestamp >= WeekStart 
       AND Timestamp < DATEADD(wk, 1, WeekStart)) ActiveUsers,
    (SELECT COUNT(*) 
     FROM Downloads 
     WHERE Timestamp >= WeekStart 
       AND Timestamp < DATEADD(wk, 1, WeekStart)) Downloads
FROM 
    (SELECT WeekStart 
     FROM 
         (VALUES ('2024-12-09'), ('2024-12-02'), ('2024-11-25'), ('2024-11-18') /* values generated by code, potentially more */) AS Weeks(WeekStart)) Weeks

It returns 2 values for each of the supplied input dates. The date values are dynamic, typically the list contains 20 dates. The result might look like this:

Is there any chance to convert this into Entity Framework Core code that does not depend on the SQL dialect?

It's possible to compose a raw SQL query with other Queryables, as long as the raw SQL itself is composable.

Using that principle, you can construct an IQueryable that returns a tally table and do all kinds of nice stuff with it, among which creating an on-the-fly composable IQueryable of dates:

using var context = new MyContext();

var startDate = DateTime.Today.AddYears(-1); // Decide here where to start.
var tally = """
            SELECT ROW_NUMBER() OVER(ORDER BY ao.object_id) - 1 AS Value
            FROM sys.all_objects ao
            """;
var tallyQuery = context.Database.SqlQueryRaw<long>(tally).Take(20);

var result = from d in tallyQuery.Select(l => startDate.AddDays(7 * l))
    select new 
    {
        d,
        ActiveUsers = context.Logins
           .Where(x => x.Timestamp >= d && x.Timestamp < d.AddDays(7))
           .Select(x => x.UserId).Distinct().Count(),
        Downloads = context.Downloads
            .Where(x => x.Timestamp >= d && x.Timestamp < d.AddDays(7))
            .Count()
    };

Of course, the part FROM sys.all_objects ao is database vendor-specific, but it could easily be replaced by any (relatively low-traffic) table in the application's schema that is large enough to supply the required amount of records.