CODEWITHSUNDEEP
c#linqentity
Alex
Alex

Reputation: 3968

Entity framework, Linq, "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

I have the following code:

        var branches =Context.HierarchyBranches.Where(x => dataItemIds.Any(y => y == x.DataItemID));
        return await branches.ToListAsync();

With List<int> dataItemIds

and it generates the following exception:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

When the dataItemIds array is larger than about 80 items, but works fine when less items in the list.

So clearly the issue is to do with the size of the dataItemIds list

My question is, how else can I write this query?

I know I can use stored procedures etc, but is there a way to do this with Linq, Entity etc?

Upvotes: 0

Views: 551

Answers (1)

Macuistin
Macuistin

Reputation: 66

I'm fairly sure if you replace your list with an array and use the following, that you will get a better answer:

var branches = Context.HierarchyBranches.Where(x => dataItemIds.Contains(x.DataItemID));

In Linq to SQL 'Contains' becomes the 'IN' statement, whereas 'Any' requires a predecate and can create inner SQL statements.

Upvotes: 1

Related Questions