Is .Any of this true for Kinetic?

From: Sign Up | LinkedIn

Or am I missing something?

I wonder behind the curtain what the difference is. Wowza. Never heard of Exists before

Looks like Linq to SQL doesn’t have EXISTS

It is also worth noting that instead of doing an Exists query in SQL ErpContext is doing the much more complex query below… (for .Any())

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [Erp].[PartTran_UD] AS [Extent1]
        INNER JOIN [Erp].[PartTran] AS [Extent2] ON [Extent1].[ForeignSysRowID] = [Extent2].[SysRowID]
        WHERE N'TEST_ANY' = [Extent2].[PartNum]
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [Erp].[ABCCode] AS [Extent1]
        WHERE N'TEST_ANY' = [Extent1].[ABCCode]
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Hi Jose,

The “Exists” belongs to type List, so you have to convert your ienum using ToList() first.
image

Regards,

Sure but to List() removes it from SQL brings the entire table into memory and then does an exists ( believe… checking now)

Correct. There is more allocations there so … I’m waiting for someone to benchmark it :slight_smile: :popcorn:

Yeah as I suspected that’s way worse it does a full table select into memory

if(Db.UD07.ToList().Exists(p=>p.Key1=="Exists Check"))
{
  Console.WriteLine("Hello World");
}

if(Db.UD07.Any(p=>p.Key1=="Any Check"))
{
  Console.WriteLine("Hello World");
}

Duration 27MS

SELECT 
    [Extent2].[Company] AS [Company], 
    [Extent2].[Key1] AS [Key1], 
    [Extent2].[Key2] AS [Key2], 
    [Extent2].[Key3] AS [Key3], 
    [Extent2].[Key4] AS [Key4], 
    [Extent2].[Key5] AS [Key5], 
    [Extent2].[Character01] AS [Character01], 
    [Extent2].[Character02] AS [Character02], 
    [Extent2].[Character03] AS [Character03], 
    [Extent2].[Character04] AS [Character04], 
    [Extent2].[Character05] AS [Character05], 
    [Extent2].[Character06] AS [Character06], 
    [Extent2].[Character07] AS [Character07], 
    [Extent2].[Character08] AS [Character08], 
    [Extent2].[Character09] AS [Character09], 
    [Extent2].[Character10] AS [Character10], 
    [Extent2].[Number01] AS [Number01], 
    [Extent2].[Number02] AS [Number02], 
    [Extent2].[Number03] AS [Number03], 
    [Extent2].[Number04] AS [Number04], 
    [Extent2].[Number05] AS [Number05], 
    [Extent2].[Number06] AS [Number06], 
    [Extent2].[Number07] AS [Number07], 
    [Extent2].[Number08] AS [Number08], 
    [Extent2].[Number09] AS [Number09], 
    [Extent2].[Number10] AS [Number10], 
    [Extent2].[Number11] AS [Number11], 
    [Extent2].[Number12] AS [Number12], 
    [Extent2].[Number13] AS [Number13], 
    [Extent2].[Number14] AS [Number14], 
    [Extent2].[Number15] AS [Number15], 
    [Extent2].[Number16] AS [Number16], 
    [Extent2].[Number17] AS [Number17], 
    [Extent2].[Number18] AS [Number18], 
    [Extent2].[Number19] AS [Number19], 
    [Extent2].[Number20] AS [Number20], 
    [Extent2].[Date01] AS [Date01], 
    [Extent2].[Date02] AS [Date02], 
    [Extent2].[Date03] AS [Date03], 
    [Extent2].[Date04] AS [Date04], 
    [Extent2].[Date05] AS [Date05], 
    [Extent2].[Date06] AS [Date06], 
    [Extent2].[Date07] AS [Date07], 
    [Extent2].[Date08] AS [Date08], 
    [Extent2].[Date09] AS [Date09], 
    [Extent2].[Date10] AS [Date10], 
    [Extent2].[Date11] AS [Date11], 
    [Extent2].[Date12] AS [Date12], 
    [Extent2].[Date13] AS [Date13], 
    [Extent2].[Date14] AS [Date14], 
    [Extent2].[Date15] AS [Date15], 
    [Extent2].[Date16] AS [Date16], 
    [Extent2].[Date17] AS [Date17], 
    [Extent2].[Date18] AS [Date18], 
    [Extent2].[Date19] AS [Date19], 
    [Extent2].[Date20] AS [Date20], 
    [Extent2].[CheckBox01] AS [CheckBox01], 
    [Extent2].[CheckBox02] AS [CheckBox02], 
    [Extent2].[CheckBox03] AS [CheckBox03], 
    [Extent2].[CheckBox04] AS [CheckBox04], 
    [Extent2].[CheckBox05] AS [CheckBox05], 
    [Extent2].[CheckBox06] AS [CheckBox06], 
    [Extent2].[CheckBox07] AS [CheckBox07], 
    [Extent2].[CheckBox08] AS [CheckBox08], 
    [Extent2].[CheckBox09] AS [CheckBox09], 
    [Extent2].[CheckBox10] AS [CheckBox10], 
    [Extent2].[CheckBox11] AS [CheckBox11], 
    [Extent2].[CheckBox12] AS [CheckBox12], 
    [Extent2].[CheckBox13] AS [CheckBox13], 
    [Extent2].[CheckBox14] AS [CheckBox14], 
    [Extent2].[CheckBox15] AS [CheckBox15], 
    [Extent2].[CheckBox16] AS [CheckBox16], 
    [Extent2].[CheckBox17] AS [CheckBox17], 
    [Extent2].[CheckBox18] AS [CheckBox18], 
    [Extent2].[CheckBox19] AS [CheckBox19], 
    [Extent2].[CheckBox20] AS [CheckBox20], 
    [Extent2].[ShortChar01] AS [ShortChar01], 
    [Extent2].[ShortChar02] AS [ShortChar02], 
    [Extent2].[ShortChar03] AS [ShortChar03], 
    [Extent2].[ShortChar04] AS [ShortChar04], 
    [Extent2].[ShortChar05] AS [ShortChar05], 
    [Extent2].[ShortChar06] AS [ShortChar06], 
    [Extent2].[ShortChar07] AS [ShortChar07], 
    [Extent2].[ShortChar08] AS [ShortChar08], 
    [Extent2].[ShortChar09] AS [ShortChar09], 
    [Extent2].[ShortChar10] AS [ShortChar10], 
    [Extent2].[ShortChar11] AS [ShortChar11], 
    [Extent2].[ShortChar12] AS [ShortChar12], 
    [Extent2].[ShortChar13] AS [ShortChar13], 
    [Extent2].[ShortChar14] AS [ShortChar14], 
    [Extent2].[ShortChar15] AS [ShortChar15], 
    [Extent2].[ShortChar16] AS [ShortChar16], 
    [Extent2].[ShortChar17] AS [ShortChar17], 
    [Extent2].[ShortChar18] AS [ShortChar18], 
    [Extent2].[ShortChar19] AS [ShortChar19], 
    [Extent2].[ShortChar20] AS [ShortChar20], 
    [Extent2].[GlobalUD07] AS [GlobalUD07], 
    [Extent2].[GlobalLock] AS [GlobalLock], 
    [Extent2].[SysRevID] AS [SysRevID], 
    [Extent1].[ForeignSysRowID] AS [ForeignSysRowID], 
    [Extent1].[UD_SysRevID] AS [UD_SysRevID], 
    [Extent1].[EditorID_c] AS [EditorID_c]
    FROM  [Ice].[UD07_UD] AS [Extent1]
    INNER JOIN [Ice].[UD07] AS [Extent2] ON [Extent1].[ForeignSysRowID] = [Extent2].[SysRowID]

VS
Duration 3MS

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [Ice].[UD07_UD] AS [Extent1]
        INNER JOIN [Ice].[UD07] AS [Extent2] ON [Extent1].[ForeignSysRowID] = [Extent2].[SysRowID]
        WHERE N'Any Check' = [Extent2].[Key1]
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Something like this makes more sense.


Both searching only on a ienum of integers.

1 Like

Still way slower cause you are returning a list of all invoices. We have over 1 million invoices that’s a million integers returned to the server’s memory.

Looks like in Linq2SQL at least Any() is the winner, though I don’t love how ErpContext is choosing to do a Case Statement instead of an Exists SQL Query.

Yes, I agree. Any() is what I use in my conditions if I do not need to access columns later on my code. Thanks for testing it though. The Any implementation is probably done by MS folks no? I guess they know what they doing lol.

I agree with Jose. IF we already have a List then one could use .Exist(), but for database calls, this is a non-starter.

2 Likes

I was trying to write that but you put it more eloquently.

It’s an object property/method, and is really not intended to be used with LINQ (2SQL).

Leave it to @Mark_Wonsil to find a way to get a Dad Joke in the title of the question.

Noice Thats Nice GIF

1 Like