Simple LINQ to Entities translation

Question

Before anyone jumps on a mark as duplicate, I have looked and everyone is doing something slightly more complicated than I am trying.

So I'm working on a database where there's a lot of data to check and LINQ's Any() extension translated to SQL isn't as fast as SQL's Count(1) > 0, so everywhere I'm writing:

var someBool = Ctx.SomeEntities.Count(x => x.RelatedEntity.Count(y => y.SomeProperty == SomeValue) > 0) > 0;

In Pseudo: Does any of my entities have a relationship with some other entity that has a property with a value of SomeValue.

This works fine and it works fast. However, it's not exactly readable (and I have lots of them, more embedded than that in cases) so what I'd like to do is replace it with:

var someBool = Ctx.SomeEntities.AnyX(x => x.RelatedEntity.AnyX(y => y.SomeProperty == SomeValue));

with:

public static bool AnyX<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate) => source.Count(predicate) > 0;

So you see I'm not doing anything that LINQ can't translate to SQL, I'm not doing anything that LINQ doesn't already translate to SQL, but just by creating an additional extension I get:

LINQ to Entities does not recognize the method Boolean AnyX etc...

There must be some way of writing my extension or some way of telling LINQ just to take a look at the code and you'll see you can do it.

Answer 1

Not an answer to your specific question, but I suggest you rethink how you're approaching the query.

Let's use some descriptive names that make it easier to understand: do any households have a resident with the first name of "Bobby"?

// your way
Ctx.Households.Count( hh => hh.Residents.Count( r => r.FirstName == "Bobby" ) > 0 ) > 0

Yuck, it's backwards. Start with residents:

Ctx.Residents.Count( r => 
    r.FirstName == "Bobby"
    && r.Household != null ) // if needed
    > 0;

Now, will that generate SQL significantly different than the below?

Ctx.Residents.Any( r => r.FirstName == "Bobby" && r.Household != null)

edit:

Here's a true MCVE that results in the opposite of your conclusion:

/*
create table TestDatum
(
    TestValue nchar(36) not null
)
*/

/*
set nocount on
declare @count int
declare @start datetime
declare @end datetime
set @count = 0

set @start = GETDATE()

while @count < 14000000
begin
    insert TestDatum values( CONVERT(nchar(36), NEWID()) )

    set @count = @count + 1

    if (@count % 100000) = 0
    begin
        print convert(nvarchar, @count)
    end
end

set @end = GETDATE()

select CONVERT(nvarchar, DATEDIFF(ms, @start, @end))
*/

/*
-- "Any" test
declare @startdt datetime, @enddt datetime
set @startdt = GETDATE()
DECLARE @p0 NVarChar(1000) = '%abcdef%'

SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM TestDatum AS [t0]
            WHERE [t0].TestValue LIKE @p0
            ) THEN 1
        ELSE 0
     END) AS [value]
set @enddt = GETDATE()
select DATEDIFF(ms, @startdt, @enddt) -- ~7000ms
*/
/*
-- "Count" test
declare @startdt datetime, @enddt datetime
set @startdt = GETDATE()
-- Region Parameters
DECLARE @p0 NVarChar(1000) = '%abcdef%'
-- EndRegion
SELECT COUNT(*) AS [value]
FROM TestDatum AS [t0]
WHERE [t0].TestValue LIKE @p0
set @enddt = GETDATE()
select DATEDIFF(ms, @startdt, @enddt) -- > 48000ms
*/