How to Improvise Multiple queries on same collection with changing parameter values using LINQ to Objects

Question

I need to fetch data based on below business requirement. We launch campaign's on items that we sell, which give the customer some discounts etc. We sell items of different brands, each brand has multiple products and each product has multiple models.

In the campaign table, if the campaign is on one specific model among the items, then a record is inserted with proper brandid of that model, productid of that model and model id too. If the campaign is on all models of a product, then a record is inserted with proper brandid ad productid and modelid as 0(which means applicable on all models of that product). If the campaign is on all models and products of a brand, then a record is inserted with proper brandid with productid, modelid as 0(which means applicable on all products and models of that brand).

Previously we were having the logic in stored procedure where we would check the count on modelid and if count is 0, then checked on product id with modelid as 0, and then at brand level with productid, modelid as 0. wherever the count is > 0, we would fetch the corresponding data.

Now we have moved this logic to business layer using linq queries. I have placed the campaign data for each brandid in cache. So cahce would be cache_brandid. I would fetch the brand specific data from cache and then check on modelid, if no data found then with product id with modelid as 0, and again if no data found then at brand id with productid, modelid as 0. However I am looking for more cleaner way of doing this, as the data I cache and apply the logic from this data from cache is more when compared to what I used to fetch from DB previously using SP. for each such data fetch now I am fetching many records from cache and iterating through them till I get the data, however SP would just check the count which is faster than fetching the data and would fetch only 1 record when it finds the count to be > 0

My current code looks like:

cacheList.FirstOrDefault(x=>x.brandid = b && x.productid == p && x.modelid == m && x.modelyear == myr)
??
cacheList.FirstOrDefault(x=>x.brandid == b && x.productid == p && x.modelid == m && x.modelyear == 0)
??
cacheList.FirstOrDefault(x=>x.brandid == b && x.productid == p && x.modelid == 0 && x.modelyear == myr)
??
cacheList.FirstOrDefault(x=>x.brandid == b && x.productid == p && x.modelid == 0 && x.modelyear == 0)
??
cacheList.FirstOrDefault(x=>x.brandid == b && x.productid == 0 && x.modelid == 0 && x.modelyear = 0);

Any assistance in making my approach cleaner and faster would greatly help me.

Thanks

I cant share the exact code but the code snippet I have mentioned in my question is almost the same. — Abdur Rahman
– Abdur Rahman, Commented Jan 16, 2020 at 6:42

Dmitrii Bychenko · Accepted Answer · 2020-01-15 07:24:40Z

1

You can try iterating just once: let's assign sime kind of score for each item and then obtain the item with the best score:

   var data = cacheList
     .Where(x => x.brandid == b &&
                (x.productid ==   p || x.productid == 0) &&
                (x.modelid   ==   m || x.modelid   == 0) && 
                (x.modelyear == myr || x.modelyear == 0))
     .Select(x => new {
        value =  x,
        score = (x.productid ==   p ? 1 : 0) * 100 + 
                (x.modelid   ==   m ? 1 : 0) * 10 + 
                (x.modelyear == myr ? 1 : 0)
      });

   // TODO: put the right type here (instead of MyObject)
   MyObject bestItem = null;
   int bestScore     =   -1;

   foreach (var item in data) {
     if (item.score > bestScore) {
       bestScore = item.score;
       bestItem  = item.value;

       // Maximum possible score 
       if (bestScore >= 111)
         break;
     } 
   }

answered Jan 15, 2020 at 7:24

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

4 Comments

Abdur Rahman Over a year ago

Is there any way that we don't need to have the complete brand level data in the cache which would be more records than the actual result. And instead be able to determine the exact matching record in faster manner. Because in the existing SP, we used to just check the count on each combination of parameters and whenever the count was > 0, we would fetch data of that particular record only. So performance wise, counts and fetching 1 record data was faster than getting many records from cache and working on them. So any suggestion on optimizing this approach would be of great help

Abdur Rahman Over a year ago

is there any way that we could achieve this without caching the data, as due to caching we will be caching lot more number of records than we would actually need to filter based on our inputs.

Dmitrii Bychenko Over a year ago

@Abdur Rahman: technically, you don't have to cache at all: you can move all the logics into sql

Abdur Rahman Over a year ago

Actually we want to move the code to our business layer as all these are the business rules being applied, and the sql stored procedure would only get me the data upon which I would be applying these business rules. Hence I would need to think of an alternative of using the cache or using SP to perform these business logic.

Collectives™ on Stack Overflow

How to Improvise Multiple queries on same collection with changing parameter values using LINQ to Objects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related