Linq Query Optimization in Entity Framework

Question

I have the following query, and what I'm looking for is to optimize the where clause. I don't like the multiple FirstOrDefault uses. Is there a way to implement it with Join maybe, or some other way so I will be able to access the FirstOrDefault only once?

var promos = await this._context
                       .SinglePromotions
                       .Include(p => p.Rewards)
                       .Include(p => p.InAppProduct)
                       .Include(p => p.PlayerSinglePromotions)
                       .ThenInclude(sp => sp.Player)
                       .ThenInclude(pl => pl.Purchases)
                       .Where(p => p.MinimumPlayerLevel <= Player.Level && 
                                   p.Placement == placement && 
                                   p.IsActive && 
                                   (p.PlayerSinglePromotions.All(sp => sp.PlayerID != Player.ID) || 
                                    (p.PlayerSinglePromotions.FirstOrDefault(sp => sp.PlayerID == Player.ID).PurchaseAmount < p.PurchaseLimit)) &&
                                    (p.PlayerSinglePromotions.All(sp => sp.PlayerID != Player.ID) || (p.PlayerSinglePromotions.FirstOrDefault(sp => sp.PlayerID == Player.ID).Player.Purchases.Count <= p.MaxInAppPurchasesByPlayer))
                       )
                       .ToListAsync();

write it how you would in SQL then written the linq which represents the SQL you wrote. LinqPad is a great tool to help you verify that the linq you are writing is turning into the correct sql. — Seabizkit
– Seabizkit, Commented Jun 6, 2019 at 14:40
Dose this transfer into sql, I am not 100% sure can you post the sql output and the actual query that gets genetated? — Filip Cordas
– Filip Cordas, Commented Jun 8, 2019 at 21:29

ocuenca · Accepted Answer · 2019-06-06 14:47:33Z

1

Well, probably there is a better solution, but you could replace your conditions where you use FirstOrDefault using Any extension method instead:

var promos = await this._context.SinglePromotions
.Include(p => p.Rewards)
.Include(p => p.InAppProduct)
.Include(p => p.PlayerSinglePromotions)
    .ThenInclude(sp => sp.Player)
    .ThenInclude(pl => pl.Purchases)
.Where(
    p => p.MinimumPlayerLevel <= Player.Level && 
    p.Placement == placement && 
    p.IsActive &&
   (p.PlayerSinglePromotions.All(sp => sp.PlayerID != Player.ID) ||
    p.PlayerSinglePromotions.Any(sp => sp.PlayerID == Player.ID 
                                   && (sp.Player.Purchases.Count <= p.MaxInAppPurchasesByPlayer || sp.PurchaseAmount < p.PurchaseLimit))))
.ToListAsync();

answered Jun 6, 2019 at 14:47

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

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

Comments

Gert Arnold · Accepted Answer · 2019-06-08 20:41:27Z

1

You can prevent repetition of long expression in LINQ queries by using query syntax with the let clause. It's like declaring variables that contain the result of an expression. In your query two parts are repeated that can be captured in let variables:

var promos = await (
    from p in this._context.SinglePromotions
        .Include(p => p.Rewards)
        .Include(p => p.InAppProduct)
        .Include(p => p.PlayerSinglePromotions)
        .ThenInclude(sp => sp.Player)
        .ThenInclude(pl => pl.Purchases)
    let pspNotOfPlayer = p.PlayerSinglePromotions.All(sp => sp.PlayerID != Player.ID)
    let firstPromotion = p.PlayerSinglePromotions.FirstOrDefault(sp => sp.PlayerID == Player.ID)
    where
        p.MinimumPlayerLevel <= Player.Level 
        && p.Placement == placement 
        && p.IsActive
        && pspNotOfPlayer || firstPromotion.PurchaseAmount < p.PurchaseLimit
        && pspNotOfPlayer || firstPromotion.Player.Purchases.Count <= p.MaxInAppPurchasesByPlayer
    select p
    )
    .ToListAsync();

In many cases this will not only make the code more readable but also improve the generated SQL because repetitive subqueries can be replaced by CROSS APPLY clauses.

edited Jun 8, 2019 at 20:41

answered Jun 8, 2019 at 20:36

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

4 Comments

Filip Cordas Over a year ago

Sql optimization engine tends to handle this sort of stuff well so I am not sure how much better in regards with performance it will be, best way to see is to measure it. And is first or default supported in ef core?

Gert Arnold Over a year ago

@FilipCordas True, and yes, FirstOrDefault() is supported.

Filip Cordas Over a year ago

probably just me but I found EF core to frustrate me because you don't see what query is not supported until you look at the output console and see that it just pulled everything in memory and did the query locally.

Gert Arnold Over a year ago

@FilipCordas Not just you. So far to I think it's impossible to use EF core for enterprise applications because not all of the most common LINQ query operations are supported. Esp. grouping is a problem when it comes to server-side operation.

Collectives™ on Stack Overflow

Linq Query Optimization in Entity Framework

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related