CODEWITHSUNDEEP
linqentity-frameworkperformance
Marc
Marc

Reputation: 2083

Improve Linq subquery with DefaultIfEmpty

I have the following database structure (simplified)

Store

StoreId
RateId

Products

ProductId
Name

Rates

RateId
Name
IsDefault

Price

PriceId
ProductID
RateId
UnitPrice

I have products each of one can have multiples prices depending on the rate table. I have multiple stores each of one having a default rate (rateId). If no price for the product is found in the prices table for a given rate (rateId) the price for the default rateId and Product is returned.

now the UI code:

    public ActionResult All() {

        // Retrieve all products from database
        var products = db.GetAllStoreProducts(StoreSettings.Default.StoreId)
            .OrderBy(p => p.DateCreated);

        var viewModel = new StoreBrowseViewModel() {
            Name = "Todos los Productos",
            Description = "Todos los Productos que forman parte de nuestro catálogo",
            StoreProducts = products.ToList()
        };

        return View("Browse1", viewModel);
    }

The Linq code:

    public IQueryable<Product> GetProducts() {

        return storeDB.Products
            .Include("Price");
    }

    public IQueryable<StoreProduct> GetAllStoreProducts(Guid storeId) {

        var store = storeDB.Stores
            .SingleOrDefault(s => s.StoreId == storeId);

        var products = GetProducts()
                .Where(p => p.Visible)
                .OrderBy(p => p.Name)
                .Select(p => new StoreProduct() {
                    Family = p.Family,
                    Category = p.Category,
                    MetricType = p.MetricType,
                    Name = p.Name,
                    PublicArtUrl = p.ProductArtUrl,
                    DateCreated = p.DateCreated,
                    DateUpdated = p.DateModified,
                    UnitPrice = p.Prices
                        .Where(pc => pc.Rate.RateId == store.RateId)
                        .Select(b => b.UnitPrice)
                        .DefaultIfEmpty(p.Prices.FirstOrDefault(p2 => p2.Rate.IsDefault).UnitPrice)
                        .FirstOrDefault()
                });

        return products;
    }

The code Is working fine, I'm getting the correct prices for the given store or the default price if not 'override' is found but ... any idea to improve the performance of the linq query ? (don't want to use sproc)

Upvotes: 3

Views: 1688

Answers (1)

Stephane
Stephane

Reputation: 1052

  • You are executing 2 queries, which could be combined into one.
  • You select the entire store entity for just the RateId property.

Also: we used following rules to create very high performance linq queries:

  • Use compiled queries It speeds up the query performance dramatically. It provides compilation and caching of queries for reuse. After the query is compiled once, your program can just execute it with other parameters.
  • Select PresentationModels instead of entities. Entities are much heavier than a simple class representing your entities.
  • Never use "Include". It's a real perfomance hog. Just create one query which gets all information at once.

Upvotes: 1

Related Questions