Hi so to put a bit of context I have a list of Orders, these orders have a property named referral link, as well as a userID linked to this order, and I am being asked to query all Orders in a certain date range that either has a referral link or the first order made by the user has a referral link. Currently, this was my attempt but this is giving me an exception when I debug telling me this is not translatable to SQL.

public async Task<List<List<Order>>> GetOrdersForRevenueReportFromMarketingTeam(RevenueReportDatesRequest dates)
{
    var query = Table.AsQueryable()
        .Include(o => o.ReferralLink)
        .GroupBy(o => o.UserId)
        .Where(group => group
        .First().ReferralLink != null)
        .Select(group => group
            .Where(o =>
                o.CreatedDate >= dates.StartDate &&
                o.CreatedDate <= dates.EndDate &&
                o.Fulfilled == true &&
                o.Status.Contains("refunded") == false
            ).ToList());

    return query.ToList();
}

I would appreciate any help!

CodePudding user response：

Try the following query.

Changed returned type to Task<List<Order>>

public Task<List<Order>> GetOrdersForRevenueReportFromMarketingTeam(RevenueReportDatesRequest dates)
{
    var orders = Table.AsQueryable();

    var query = 
        from o in orders
        from first in orders
            .Where(first => first.UserId == o.UserId)
            .OrderBy(first => first.CreatedDate)
            .Take(1)
            .DefaultIfEmpty()
        where first.ReferralLink != null &&
            o.CreatedDate >= dates.StartDate &&
            o.CreatedDate <= dates.EndDate &&
            o.Fulfilled == true &&
            o.Status.Contains("refunded") == false
        select o;

    return query.ToListAsync();
}