Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What construction can I use instead of Contains?

I have a list with ids:

var myList = new List<int>();

I want to select all objects from db with ids from myList:

var objList= myContext.MyObjects.Where(t => myList.Contains(t.Id)).ToList();

But when myList.Count > 8000 i get an error:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I think that it's because i used Contains(). What can I use instead of Contains?

like image 566
Kliver Max Avatar asked Jan 27 '15 08:01

Kliver Max


People also ask

What can be used instead of Contains in C#?

Answers. You can use String. IndexOf instead of Contains.

How add contains in Linq?

LINQ Contains will not be supported in Query syntax it will be available only in the method syntax. Let's see the following syntax for LINQ-Contains, the Contains extension method available in two overloaded methods, public static bool Contains<TSource>(this IEnumerable<TSource> source, TSource value);

How do you use LINQ to check if a list of strings contains any string in a list?

Any(tag => list. Contains(tag)); Or: var list = new List<string> { ... }; var query = query.


2 Answers

You can perform the query on the client side by adding AsEnumerable() to "hide" the Where clause from Entity Framework:

var objList = myContext
  .MyObjects
  .AsEnumerable()
  .Where(t => myList.Contains(t.Id))
  .ToList();

To improve performance you can replace the list with a HashSet:

var myHashSet = new HashSet<int>(myList);

and then modify the predicate in Where accordingly:

  .Where(t => myHashSet.Contains(t.Id))

This is the "easy" solution in terms of time to implement. However, because the query is running client side you may get poor performance because all MyObjects rows are pulled to the client side before they are filtered.

The reason you get the error is because Entity Framework converts you query into something like this:

SELECT ...
FROM ...
WHERE column IN (ID1, ID2, ... , ID8000)

So bascially all 8000 ID's from the list is included in the generated SQL which exceeds the limit of what SQL Server can handle.

What Entity Framework "looks for" to generate this SQL is ICollection<T> which is implemented by both List<T> and HashSet<T> so if you try to keep the query on the server side you get no improved performance by using HashSet<T>. However, on the client side the story is different where Contains is O(1) for HashSet<T> and O(N) for List<T>.

like image 108
Martin Liversage Avatar answered Sep 21 '22 13:09

Martin Liversage


If you wan't this to perform well I'd suggest you use table valued parameters and a stored procedure.

in your database, using TSQL,

CREATE TYPE [dbo].[IdSet] AS TABLE
(
    [Id] INT
);
GO

CREATE PROCEDURE [dbo].[Get<table>]
    @ids [dbo].[IdSet] READONLY
AS
    SET NOCOUNT ON;

    SELECT
                <Column List>
        FROM
                [dbo].[<table>] [T]
        WHERE
                [T].[Id] IN (SELECT [Id] FROM @ids);
RETURN 0;
GO

Then, in C#

var ids = new DataTable()
ids.Columns.Add("Id", typeof(int));

foreach (var id in myList)
{
    ids.Rows.Add(id);
}

var objList = myContext.SqlQuery<<entity>>(
    "[dbo].[Get<table>] @ids",
    new SqlParameter("@ids", SqDbType.Structured)
        { 
            Value = ids,
            TypeName = "[dbo].[IdSet]"
        }));
like image 41
Jodrell Avatar answered Sep 22 '22 13:09

Jodrell