CODEWITHSUNDEEP
c#databaselinqasp.net-coreentity-framework-core
Yura Sokolov
Yura Sokolov

Reputation: 207

Linq Query Optimization in Entity Framework

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();

Upvotes: 1

Views: 701

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109109

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.

Upvotes: 1

ocuenca
ocuenca

Reputation: 39326

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();

Upvotes: 1

Related Questions