Avoiding the 2100 rpc limit with LINQ Select and Lambda

Question

I have the following query. In it I'm doing a Take(2100) to avoid the 2100 rpc limit imposed.

 var query =
     from result in staging_sparc_sophis_trade.AsQueryable()
     where deals.Take(2100).Contains(result.TRADE_ID)
     select new traded_product()
     {                   
        Deal = result.TRADE_ID,
        CostCentre = result.FTP_COSTCTR,
       InvolvedPartyId = R_GEN_002(result.hsbc_source_system_instance, "", result.CNPTY_ACRONYM
     };

What I want to know is, is there a way I can remove the Take(2100) part and replace it with a lambda statement in the Deal line to check if the deal is in the list (deals) I'm searching for?

Answer 1

I have found 2 ways round the problem. Solution 1. If I run the query on the SqlServer physical machine directly, the problem does not occur.

Do the filtering in the next part: Remove the "Where clause"

var query =
 from result in staging_sparc_sophis_trade.AsQueryable()
 --where deals.Take(2100).Contains(result.TRADE_ID)
 select new traded_product()
 {                   
    Deal = result.TRADE_ID,
    CostCentre = result.FTP_COSTCTR,
   InvolvedPartyId = R_GEN_002(result.hsbc_source_system_instance, "", result.CNPTY_ACRONYM
 };

Add the filtering part in this part of your code. It runs a bit slower, but still does the trick. I think it runs slower because I'm searching the full list in every single itteration.

foreach (var result in query)
{
if (!deals.contains(result.TRADE_ID))
{
--Actions
}
}