Geek Answers Handbook

Is it Any Better or Not Than It Contains?

I am using EF6 and I would like to get the entries in the table that are in the identifier group.

In my test, for example, I use 4 identifiers.

I try two options, the first with any.

dbContext.MyTable
.Where(x => myIDS.Any(y=> y == x.MyID));

And the T-SQL that this linq exrepsion generates:

SELECT 
    *
    FROM [dbo].[MiTabla] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            [UnionAll2].[C1] AS [C1]
            FROM  (SELECT 
                [UnionAll1].[C1] AS [C1]
                FROM  (SELECT 
                    cast(130 as bigint) AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    cast(139 as bigint) AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
            UNION ALL
                SELECT 
                cast(140 as bigint) AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
        UNION ALL
            SELECT 
            cast(141 as bigint) AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
        WHERE [UnionAll3].[C1] = [Extent1].[MiID]
    )

As you can see, T-SQL is “where it exists”, which uses many subqueries and joins.

The second option contains s.

dbContext.MyTable
.Where(x => myIDS.Contains(x.MiID));

And T-SQL:

SELECT 
    *
    FROM [dbo].[MiTabla] AS [Extent1]
    WHERE [Extent1].[MiID] IN (cast(130 as bigint), cast(139 as bigint), cast(140 as bigint), cast(141 as bigint))

Contains a translation of "where in", but the request is much less complex.

I read that any of them is used faster, so I doubt that any of them, although it is more difficult at first glance, is faster or not.

Many thanks.

EDIT: I have a test (I don't know, this is the best way to test this).

System.Diagnostics.Stopwatch miswContains = new System.Diagnostics.Stopwatch();
                        miswContains.Start();
                        for (int i = 0; i < 100; i++)
                        {
                            IQueryable<MyTable> iq = dbContext.MyTable
                            .Where(x => myIDS.Contains(x.MyID));

                            iq.ToArrayAsync();
                        }
                        miswContains.Stop();



                        System.Diagnostics.Stopwatch miswAny = new System.Diagnostics.Stopwatch();
                        miswAny.Start();
                        for (int i = 0; i < 20; i++)
                        {
                            IQueryable<MyTable> iq = dbContext.Mytable
                            .Where(x => myIDS.Any(y => y == x.MyID));

                            iq.ToArrayAsync();
                        }
                        miswAny.Stop();

, miswAny 850 , miswContains 4251 .

, contaions .

+4
3

- , ( ), MiTabla.MiID .

in: SQL IN ?.

+2

, LINQ2SQL Count() SQL- ( Any Contains):

dbContext.MyTable
.Where(x => myIDS.Count(y=> y == x.MyID) > 0);

SQL count :

DECLARE @p0 Decimal(9,0) = 12345
SELECT COUNT(*) AS [value]
FROM [ids] AS [t0]
WHERE [t0].[id] = @p0
+1

, Any . myIDS (, ~ 50) , SQL, .

Contains . , .

, , Any . Contains .

, ,

LINQ-to-objects, , . LINQ SQL-, SQL - , .

+1
source

More articles:


All Articles