Adding an IQueryable<> value to an existing query kills performance with Entity Framework Core and PostgreSQL

Question

I have table A with millions of rows, and there's a one to many relationship to table B, where each entry for the item in A will only have 1-4 values. When I query, as part of my returned data, I need to include the most recent PromotionDay from B, so I did this, which runs in under a second:

var query = context.A.AsNoTracking().Select(x => new {
   PromotionDay = context.B.Max(p => p.PromotionDay),
   // Lots of other properties from A
});

This is a search API, so sometimes they'll send a PromotionDay filter, meaning I only want those records to be returned.

I naively tried this:

if (filters.PromotionDate is { } promotionDate)
    query = query.Where(x => x.PromotionDay == promotionDate);

Now the query takes over 40 seconds. When I look at the generated SQL, I see it's adding that WHERE clause as a subquery. What's the proper way to do this so the filter is applied in the database, not loading millions of rows to memory and then applying filters?

WHERE (
    SELECT max(promotion_day)
    FROM B
    WHERE ...
) = @__promotionDate_0

So it's now running that same MAX(promotion_day) query repeatedly, and the query takes 43 seconds to run.

Steve Py · Accepted Answer · 2025-04-16 23:49:58Z

0

Your query doesn't look correct, This line:

PromotionDay = context.B.Max(p => p.PromotionDay)

...will load the max promotion day out of the "B" table regardless of the relationship to "A" for every A row, rather than join the related values.

If table A has a 1-to-many relationship with B's containing this promotion day value and there are 1-4 expected rows then there should be a navigation property collection on A:

PromotionDay = x.Bs.Max(p => p.PromotionDay)

This ensures that the B records are Joined to their respective A rows. There should be few scenarios where you go back to a context DbSet within a query expression. Navigation properties ensure related data is joined correctly.

From there the additional query filter should work. Though I'm not familiar with that syntax. If the filters.PromotionDate is a null-able DateTime:

if (filters.PromotionDate.HasValue)
    query = query.Where(x => x.PromotionDay == filters.PromotionDate);

Even then this is likely not an ideal query scenario. To improve reading performance you might consider adding a computed column to your "A" table for something like the "CurrentPromotionDay" which computes the value from the related "B" records.

answered Apr 16 at 23:49

Steve Py

36.4k4 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gargoyle Apr 19 at 7:18

context was a typo on my part. I am using x like you showed.

Steve Py Apr 20 at 2:20

Can you update your question with the exact query expression(s) you are using? Often attempts to simplify examples hide the exact problem unless you confirm that the simplified examples also exhibit the same wayward behaviour.

Collectives™ on Stack Overflow

Adding an IQueryable<> value to an existing query kills performance with Entity Framework Core and PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related