Any() Linq Method making several queries on the same object. C# EF CORE

c# entity-framework-core linq sql-server

Question

I made several searchs and I'm still struggling with this. I want to perform this query on EF core, using sql server Exists():

The problem is, that in sql server the query below takes like 2 seconds to perform. And on C#, using the Any() method it takes more then 1 minute. It makes several queries on HutOperationHistory and HutPropertyValue tables.

// takes 1-2 secs

select * From Hut h where exists(
    select 1 from HutOperationHistory 
             where HutId = h.HutId and HutOperationId = 4
)
and exists(
    select 1 from HutPropertyValue 
             where HutId = h.HutId and Value = 'HUT_SUPPLIER_PROPERTY_NAME'
)
and exists(
    select 1 from HutPropertyValue 
             where HutId = h.HutId and Value = 'BUY_ORDER_PROPERTY_NAME'
)
and exists(
    select 1 from HutPropertyValue 
             where HutId = h.HutId 
             and Value = 'WORK_ORDER_PROPERTY_NAME'
)

I made it on linq and it goes like this:

// takes more then 1 minute



  var huts = coreDbContext.Huts
        .Include(x => x.PropertyValuesList).ThenInclude(y => y.HutProperty)
        .Include(x => x.OperationHistoriesList)
        .Where(
            x => 
             x.OperationHistoriesList.Any(
                z => ( z.HutOperation.hutOperationId = 4 )
            )
            && x.PropertyValuesList.Any(b => 
                    b.Value.Equals(hutSupplierPropName, StringComparison.CurrentCultureIgnoreCase)                  
                )
            && x.PropertyValuesList.Any(b =>
                b.Value.Equals(buyOrderPropName, StringComparison.CurrentCultureIgnoreCase)             
            )
            && x.PropertyValuesList.Any(b =>
                b.Value.Equals(workOrderPropName, StringComparison.CurrentCultureIgnoreCase)                
            )
        )
        .OrderBy(x => x.Order)
        .ToList();

Is there anything i can do make it faster?

Thx in advance!

1
0
10/17/2019 5:30:56 PM

Popular Answer

Instead of Any(), try Count() > 0. I have had performance gains using this in the past

0
10/17/2019 8:56:51 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow