GroupBy() isn't working as expected unless I first load the individual items into memory #32014
Comments
|
@SoftCircuits can you please submit a minimal repro for this? |
|
@roji My project is large. I think these are the relevant parts. Queryvar results = await DbContext.Trucks
.Where(t => t.Departure.HasValue)
.Select(t => new
{
t.Departure!.Value.Year,
t.Departure!.Value.Month,
Quantity = t.InboundQuantity + t.ToTransfers.Sum(x => x.Quantity) - t.FromTransfers.Sum(x => x.Quantity)
})
.GroupBy(t => new { t.Year, t.Month })
.ToDictionaryAsync(g => new DateTime(g.Key.Year, g.Key.Month, 1), g => g.Sum(x => x.Quantity));Configurationbuilder.Services.AddDbContext<ApplicationDbContext>(options =>
{
string? connectionString = builder.Configuration.GetConnectionString(connectionName);
if (connectionString == null)
throw new Exception("Connection string not configured.");
options.UseSqlServer(
connectionString,
builder => builder.MigrationsAssembly("PegasusEntities"));
});ApplicationDbContextEntities |
|
@SoftCircuits please make the effort of making the above snippets into a runnable, minimal repro. Not doing so effectively asks us to try to create the repro in your place, but we don't actually have the code or failure. |
|
@roji I've created a stand-alone project to demonstrate this issue. At the core, I have two simple queries that do not get the same results. Seems like they should. |
|
Note for triage: The final GroupBy for very simple queries with projection of a simple type behaves differently than when done in LINQ to Objects. Minimal repro: using (var context = new SomeDbContext())
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
context.AddRange(
new Foo { Departure = new(2020, 5, 10)},
new Foo { Departure = new(2020, 5, 11)},
new Foo { Departure = new(2020, 6, 12)},
new Foo { Departure = new(2020, 6, 13)},
new Foo { Departure = new(2020, 7, 14)});
await context.SaveChangesAsync();
}
using (var context = new SomeDbContext())
{
// Query 1
var results1 = context.Foo
.Select(t => t.Departure.Month)
.ToList()
.GroupBy(t => new { t })
.ToList();
Console.WriteLine($"Query 1 : {results1.Count} results");
foreach (var result in results1)
{
Console.WriteLine($" Grouping key: {result.Key}");
foreach (var item in result)
{
Console.WriteLine($" {item}");
}
}
// Query 2
var results2 = context.Foo
.Select(t => t.Departure.Month)
.GroupBy(t => new { t })
.ToList();
Console.WriteLine($"Query 2 : {results2.Count} results");
foreach (var result in results2)
{
Console.WriteLine($" Grouping key: {result.Key}");
foreach (var item in result)
{
Console.WriteLine($" {item}");
}
}
}
public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Data Source=localhost;Database=One;Integrated Security=True;TrustServerCertificate=true")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
public DbSet<Foo> Foo { get; set; }
}
public class Foo
{
public int Id { get; set; }
public DateTime Departure { get; set; }
}Output: |
|
Duplicate of #34256 |
|
Am going to use #34256 to track working around the SQL Server limitation in EF. |
# for free
to join this conversation on GitHub.
Already have an account?
# to comment
I have the following query. I'm expecting a number of results but I get only one.
Results:
If I rewrite the query to load all the rows into memory and then group, I then get the expected number of results.
Results:
If I add a second
Select()after theGroupBy(), I get an run-time exception.I understand that some expressions cannot be converted, but it seems odd that it would actually run without any error and then produce different results. In this case, an exception might actually be better.
The text was updated successfully, but these errors were encountered: