Closed RezaRahmatzadeh closed 3 months ago
roji
commented
3 months ago The above is a snippet - can you please post a minimal, runnable console app that shows the error happening (this should always be done when submitting an issue)? Please make the effort of narrowing down your query to the minimum that reproduces the error, removing unneeded parts.
RezaRahmatzadeh
commented
3 months ago The above is a snippet - can you please post a minimal, runnable console app that shows the error happening (this should always be done when submitting an issue)? Please make the effort of narrowing down your query to the minimum that reproduces the error, removing unneeded parts.
Sure, here is a sample that simulates that exact error using a LocalDB SQL server and some sample tables:
https://github.com/RezaRahmatzadeh/EFCore.CannotAggregationInSubQuery.SQL.Error.Sample
and also here is the code:
public class TableName
{
public int Id { get; set; }
public DateTime? IncidentDate { get; set; }
public DateTime? ReportReceived { get; set; }
public DateTime? InitiatedDate { get; set; }
public DateTime? FinalizedDate { get; set; }
public string ClaimId { get; set; }
public string ClaimIdentifier { get; set; }
public UnderwritingPeriod UnderwritingPeriod { get; set; }
}
public class Reserve
{
public int Id { get; set; }
public string ClaimId { get; set; }
public string ReserveType { get; set; }
public DateTime TransactionDate { get; set; }
}
public class UnderwritingPeriod
{
public int Id { get; set; }
public string PolicyNumber1 { get; set; }
public string PolicyNumber2 { get; set; }
public string PolicyNumber3 { get; set; }
public Policy Policy { get; set; }
}
public class Policy
{
public int Id { get; set; }
public string PolicyIdentifier { get; set; }
}
public class ClaimsReactionsReportDto
{
public DateTime? IncidentDate { get; set; }
public DateTime? ReportReceivedDate { get; set; }
public DateTime? InitiatedDate { get; set; }
public DateTime? FinalizedDate { get; set; }
public DateTime? FirstReserveDate { get; set; }
public DateTime? FirstPaymentDate { get; set; }
public int? IncidentToReceivedDays { get; set; }
public int? ReceivedToInitiatedDays { get; set; }
public int? InitiatedToFirstReserveDays { get; set; }
public int? InitiatedToFirstPaymentDays { get; set; }
public int? InitiatedToFinalizedDays { get; set; }
public string ClaimIdentifier { get; set; }
public string Category { get; set; }
public string PolicyIdentifier { get; set; }
}
public class ClaimsReactionsAVGReportDto
{
public double? IncidentToReceivedDaysAVG { get; set; }
public double? ReceivedToInitiatedDaysAVG { get; set; }
public double? InitiatedToFirstReserveDaysAVG { get; set; }
public double? InitiatedToFirstPaymentDaysAVG { get; set; }
public double? InitiatedToFinalizedDaysAVG { get; set; }
public int Year { get; set; }
public int Month { get; set; }
public DateTime DateString { get; set; }
}
public class AppDbContext : DbContext
{
public DbSet<TableName> TableName { get; set; }
public DbSet<Reserve> Reserves { get; set; }
public DbSet<UnderwritingPeriod> UnderwritingPeriods { get; set; }
public DbSet<Policy> Policies { get; set; }
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Add any additional model configurations here
}
}
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting demo application...");
// Setup DbContext
var options = new DbContextOptionsBuilder<AppDbContext>()
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=ClaimsDemoDb;Trusted_Connection=True;")
.Options;
using (var context = new AppDbContext(options))
{
// Ensure database is created
Console.WriteLine("Creating database...");
context.Database.EnsureCreated();
// Seed data
Console.WriteLine("Seeding data...");
SeedData(context);
// Run the problematic query
Console.WriteLine("Running query...");
RunProblematicQuery(context);
}
Console.WriteLine("Demo completed. Press any key to exit.");
Console.ReadKey();
}
private static void SeedData(AppDbContext context)
{
if (!context.TableName.Any())
{
var policy = new Policy { PolicyIdentifier = "POL001" };
context.Policies.Add(policy);
var underwritingPeriod = new UnderwritingPeriod
{
PolicyNumber1 = "PN001",
Policy = policy,
PolicyNumber2 = "PN001",
PolicyNumber3 = "PN003"
};
context.UnderwritingPeriods.Add(underwritingPeriod);
var claim = new TableName
{
IncidentDate = DateTime.Now.AddDays(-30),
ReportReceived = DateTime.Now.AddDays(-28),
InitiatedDate = DateTime.Now.AddDays(-27),
FinalizedDate = DateTime.Now.AddDays(-5),
ClaimId = "CL001",
ClaimIdentifier = "CI001",
UnderwritingPeriod = underwritingPeriod
};
context.TableName.Add(claim);
var reserve1 = new Reserve
{
ClaimId = "CL001",
ReserveType = "XYZ",
TransactionDate = DateTime.Now.AddDays(-26)
};
var reserve2 = new Reserve
{
ClaimId = "CL001",
ReserveType = "ABC",
TransactionDate = DateTime.Now.AddDays(-25)
};
context.Reserves.AddRange(reserve1, reserve2);
context.SaveChanges();
}
}
private static void RunProblematicQuery(AppDbContext dbContext)
{
try
{
var avgGrid = dbContext.TableName.Select(z => new
{
IncidentDate = z.IncidentDate,
ReportReceivedDate = z.ReportReceived,
InitiatedDate = z.InitiatedDate,
FinalizedDate = z.FinalizedDate,
FirstReserveDate = dbContext.Reserves.Where(x => x.ClaimId == z.ClaimId && x.ReserveType == "XYZ").OrderBy(x => x.TransactionDate).FirstOrDefault().TransactionDate,
FirstPaymentDate = dbContext.Reserves.Where(x => x.ClaimId == z.ClaimId && (x.ReserveType == "ABC" || x.ReserveType == "EFG")).OrderBy(x => x.TransactionDate).FirstOrDefault().TransactionDate,
ClaimIdentifier = z.ClaimIdentifier,
Category = z.UnderwritingPeriod == null ? "" :
z.UnderwritingPeriod.PolicyNumber1 != null ? "Type1" :
z.UnderwritingPeriod.PolicyNumber2 != null ? "Type2" :
z.UnderwritingPeriod.PolicyNumber3 != null ? "Type3" : "",
PolicyIdentifier = z.UnderwritingPeriod == null ? "" : z.UnderwritingPeriod.Policy.PolicyIdentifier,
}).Select(z => new ClaimsReactionsReportDto
{
IncidentDate = z.IncidentDate,
ReportReceivedDate = z.ReportReceivedDate,
InitiatedDate = z.InitiatedDate,
FinalizedDate = z.FinalizedDate,
FirstReserveDate = z.FirstReserveDate,
FirstPaymentDate = z.FirstPaymentDate,
IncidentToReceivedDays = (z.IncidentDate.HasValue && z.ReportReceivedDate.HasValue) ? EF.Functions.DateDiffDay(z.IncidentDate, z.ReportReceivedDate).Value : (int?)null,
ReceivedToInitiatedDays = (z.ReportReceivedDate.HasValue && z.InitiatedDate.HasValue) ? EF.Functions.DateDiffDay(z.ReportReceivedDate, z.InitiatedDate).Value : (int?)null,
InitiatedToFirstReserveDays = (z.InitiatedDate.HasValue) ? EF.Functions.DateDiffDay(z.InitiatedDate, z.FirstReserveDate).Value : (int?)null,
InitiatedToFirstPaymentDays = (z.InitiatedDate.HasValue) ? EF.Functions.DateDiffDay(z.InitiatedDate, z.FirstPaymentDate).Value : (int?)null,
InitiatedToFinalizedDays = (z.InitiatedDate.HasValue && z.FinalizedDate.HasValue) ? EF.Functions.DateDiffDay(z.InitiatedDate, z.FinalizedDate).Value : (int?)null,
ClaimIdentifier = z.ClaimIdentifier,
Category = z.Category,
PolicyIdentifier = z.PolicyIdentifier,
}).GroupBy(y => new { y.IncidentDate.Value.Year, y.IncidentDate.Value.Month })
.Select(e => new ClaimsReactionsAVGReportDto
{
IncidentToReceivedDaysAVG = e.Average(x => x.IncidentToReceivedDays),
ReceivedToInitiatedDaysAVG = e.Average(x => x.ReceivedToInitiatedDays),
InitiatedToFirstReserveDaysAVG = e.Average(x => x.InitiatedToFirstReserveDays),
InitiatedToFirstPaymentDaysAVG = e.Average(x => x.InitiatedToFirstPaymentDays),
InitiatedToFinalizedDaysAVG = e.Average(x => x.InitiatedToFinalizedDays),
Year = e.Key.Year,
Month = e.Key.Month,
DateString = EF.Functions.DateTimeFromParts(e.Key.Year, e.Key.Month, 3, 1, 1, 1, 0),
});
var test = avgGrid.ToList(); // This line will throw the error
Console.WriteLine("Query executed successfully");
}
catch (Exception ex)
{
Console.WriteLine($"Error occurred while executing the query:");
Console.WriteLine(ex.Message);
}
}
}Duplicate of #34256
roji
commented
3 months ago Am going to use #34256 to track working around the SQL Server limitation in EF.
When I ran this query against my database, I encountered an error due to the aggregations in the final Select() projection. I need to return an IQueryable<>, and I want to avoid using AsEnumerable() to switch to in-memory processing, even though placing AsEnumerable() before the last Select() solves the problem.
I'm looking for a way to run the entire query on the database side. It's worth mentioning that this query worked properly in a .NET Framework project. After migrating to the latest .NET version, I encountered this error. Based on my research, the issue arises because older EF versions automatically switched to in-memory processing, which no longer happens in the latest version. This change is beneficial as it makes developers more aware of what's happening under the hood.
Here is the query. Could you help me figure out how to run the entire query on the database side without encountering SQL errors?