CODEWITHSUNDEEP
c#linq
Obbles
Obbles

Reputation: 563

How do I convert this looped code to a single LINQ implementation?

I am trying to optimise the code below which loops through objects one by one and does a database lookup. I want to make a LINQ statement that will do the same task in one transaction.

This is my inefficient looped code;

IStoreUnitOfWork uow = StoreRepository.UnitOfWorkSource.GetUnitOfWorkFactory().CreateUnitOfWork(); 
var localRunners = new List<Runners>();
foreach(var remoteRunner in m.Runners) {                                                           
    var localRunner = uow.CacheMarketRunners.Where(x => x.SelectionId == remoteRunner.SelectionId && x.MarketId == m.MarketId).FirstOrDefault();  
    localRunners.Add(localRunner);
}

This is my very feable attempt at a single query to do the same thing. Well it's not even an attempt. I don't know where to start. The remoteRunners object has a composite key.

IStoreUnitOfWork uow = StoreRepository.UnitOfWorkSource.GetUnitOfWorkFactory().CreateUnitOfWork(); 
var localRunners = new List<Runners>();
var localRunners = uow.CacheMarketRunners.Where(x =>
   x.SelectionId in remoteRunners.SelectionId &&
   x.MarketId    in remoteRunners.MarketId);

Thank you for looking

Upvotes: 0

Views: 91

Answers (2)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

So you have an object m, which has a property MarketId. Object m also has a sequence of Runners, where every Runner has a property SelectionId.

Your database has CacheMarketRunners. Every CacheMarketRunner has a MarketId and a SelectionId.

Your query should return allCacheMarketRunners with a MarketId equal to m.MarketId and a SelectionId that is contained in the sequence m.Runners.SelectionId.

If your m does not have too many Runners, say less then 250, consider using Queryable.Contains

var requestedSelectionIds = m.Runners.Select(runner => runner.SelectionId);
var result = CacheMarketRunners.Where(cacheMarketRunner =>
     cacheMarketRunner.MarketId == m.MarketId
     && requestedSelectionIds.Contains(cacheMarketRunner.SelectionId));

Upvotes: 2

Ken Hung
Ken Hung

Reputation: 782

To improve performance, you need caching transaction results:

var marketRunners = uow.CacheMarketRunners.Where(x => x.MarketId == m.MarketId).ToList();

Transaction results regarding uow are stored in the List, such that you don't have transaction in the for loop. Hence performance should be improved:

var localRunners = new List<Runners>();
foreach(var remoteRunner in m.Runners) {                                                           
    var localRunner = marketRunners.FirstOrDefault(x => x.SelectionId == remoteRunner.SelectionId);
    localRunners.Add(localRunner);
}

You can even remove the for loop:

var localRunners = m.Runners.Select(remoteRunner => marketRunners.FirstOrDefault(x => x.SelectionId == remoteRunner.SelectionId)).ToList();

Upvotes: 0

Related Questions