Geek Answers Handbook

See Temporary Table in Entity Framework Query

There is a list in the list of 50,000 product identifiers. I would like to get all these Products from the database. Using dbContext.Products.Where(p => list.contains(p.ID))creates a giant IN in SQL - WHERE ID IN (2134,1324543,5675,32451,45735...)and it takes forever. This is partly due to the fact that SQL Server takes time to parse such a large string, as well as an execution plan. (I know this from trying to use a temporary table).

So, I used SQLBulkCopy to insert identifiers into a temporary table and then ran

dbContext.Set<Product>().SqlQuery("SELECT * FROM Products WHERE ID IN (SELECT ID FROM #tmp))"

This provided good performance. However, now I need products with their suppliers (several for each product). Using a custom SQL command does not return the complex object that I know of. So, how can I get products with my suppliers using a temporary table?

(If I can somehow refer to the temporary table in LINQ, then everything will be fine - I could just do it dbContext.Products.Where(p => dbContext.TempTable.Any(t => t.ID==p.ID)). If I could reference it in UDF, which would also be good, but you cannot I can not use real table, as concurrent users will leave it in an inconsistent state.)

thanks

+4
source share
2 answers

(TempTable ), - UserId SessionId, ProductID's:

  • ,

, (.. dbContext), , .

+1

sql, Join, Contains. :

IQueryable<Product> queryable = Uow.ProductRepository.All;
List<int> inMemKeys = new int[] { 2134, 1324543, 5675, 32451, 45735 }.ToList();
string sql1 = queryable.Where(p => inMemKeys.Contains(p.ID)).ToString();

string sql2 = queryable.Join(inMemKeys, t => t.ID, pk => pk, (t, pk) => t).ToString();

sql, Contains (sql1)

SELECT
    [extent1].[id] AS [id],...etc
FROM [dbo].[products] AS [extent1]
WHERE ([extent1].[id] IN (2134, 1324543, 5675, 32451, 45735))

sql, Join:

SELECT
    [extent1].[id] AS [id],...etc
FROM [dbo].[products] AS [extent1]
    INNER JOIN (SELECT
        [unionall3].[c1] AS [c1]
    FROM (SELECT
        [unionall2].[c1] AS [c1]
    FROM (SELECT
        [unionall1].[c1] AS [c1]
    FROM (SELECT
        2134 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable1] UNION ALL SELECT
        1324543 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable2]) AS [unionall1] UNION ALL SELECT
        5675 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable3]) AS [unionall2] UNION ALL SELECT
        32451 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable4]) AS [unionall3] UNION ALL SELECT
        45735 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable5]) AS [unionall4]
        ON [extent1].[id] = [unionall4].[c1]

, sql select, union all , . Sql , - , .

, , IN. ... UNION ALL.... ... ,

+2

More articles:


All Articles