4

Hi I'm building an MVC 4 report, and using EF5. Database is on SQL 2005.

The report has a large amount of long string filters, when there are a lot of them selected, I got this error:"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

Filters are selected and return a List, and in the LINQ query I use:

DataContext.Entity.Where(list.Contains(column));
return IQueryable<Entity>;

I guess it's the LINQ-generated SQL query that gets over the limit, and I don't know what is the limit.

Is there any way we could control this limit? or please point out if my guess was wrong.

Thanks a lot.

Thanks for the link below, provided by @AdrianFaciu , and it's really helpful, I think it's similar issue. (I guess my each string filter's length is too long, and there are a lot of them.) Hitting the 2100 parameter limit (SQL Server) when using Contains()

I have read a few workarounds, but still looking for a proper solution, not by generating string queries. It seems at least for now, I have to load data step by step to reduce the length of the query.

Community
  • 1
  • 1
Winter Winter
  • 173
  • 2
  • 11
  • 1
    The error is generated by the Contains call. You can have a look here for more info about that: http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains – Adrian Fâciu Dec 18 '12 at 13:13
  • What database engine and what version are you using? – Jonas Elfström Dec 18 '12 at 14:50
  • There might be a way to rewrite that as a join instead of a `.Contains` / `IN (c1, c2, ...)` but it's hard to tell by the information you give. – Jonas Elfström Dec 18 '12 at 14:51
  • Thanks a lot for everyone's reply. And @AdrianFaciu , link is really helpful, however my boss really hate stringed queries, and we just converted all reports from SProc to Linq, so can't use that. It seems I have to do it step by step to reduce the query length. – Winter Winter Dec 18 '12 at 16:50
  • I had a similar problem: Too many parameters because a contains() call. Join has been the answer. – yonexbat Dec 18 '12 at 17:38
  • Yes, join is one way to work around it, but only works if you have a fairly small data table to read from. – Winter Winter Dec 19 '12 at 09:09
  • I have to ask... why aren't you (as in "you"="your company") not using SQL Server Reporting Services ? Anyway: you could create some views to help you lighten the number of parameters. – Alex Dec 19 '12 at 12:43
  • @Alex because the bosses and sales on the road a lot, so they have to have a web based reports. App vs Web issue. – Winter Winter Dec 21 '12 at 15:11

2 Answers2

4

The proper solution is using SQL directly. EF and Linq are not tools for writing report queries. It is ORM - you use it to get objects from database work with them and maybe also modify them and store them back to database.

If you need complex query just to pull data from database to build a report or some complex search engine you should simply pass the complexity of ORM and go to low level SQL - and if you reached size limit of the query or parameters you really need it. It will make your query much simpler, smaller and faster and it will allow you using some advanced features like table valued parameters to avoid large contains calls.

Changing all your reports from SProcs to Linq was really stupid ... You are wasting your time to produce much worse solution.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • I agree with @Ladislav. Also, note that EF5 now supports sprocs – Matt Dec 19 '12 at 12:09
  • @Ladislav Due to code based is strong typed structure and you can run tests on it. Database based is fast, direct, but not flexible enough to do complex reports, also difficult to change and debug complex SProcs. We had over 3000 lines SProc before. – Winter Winter Dec 21 '12 at 15:17
  • Many of my stored procedures were over 20,000 lines some with more than 50 joins in a single query (not simple code, but then, what it was doing is quite complex). I'd rather change a complex stored procedure than changing compiled code (and then have another 2 weeks of testing) any day. – Kevin Williams Jul 01 '13 at 18:10
  • @KevinWilliams: Somehow I would say that such stored procedures are insane anyway. Once such big SQL queries become common part of your reports is time to think about separate database or cube designed specially for reporting. – Ladislav Mrnka Jul 01 '13 at 18:28
  • @Ladislav - Actually, that wasn't part of reporting, it was part of a complex medical practice management application that searched for open appointments as much as a year out across many schedules with complex relationships between each appointment in the appointment set. And, it was able to do this in under 60 seconds for up to 5 appointments (5 was an arbitrary number, it could have easily handled 15 or 20 appointments, such as used in operating room scheduling). Medical applications seem to be among the most complex. – Kevin Williams May 11 '16 at 16:34
0

I had a similar problem on EF and Firebird – When linq query in where clause had two contains. Solution Was simple but not elegant – LoadAll, and filter them in memory.

Foundedlist.RemoveAll(x=> !RolesList.contains(x.id));

Lightning3
  • 375
  • 7
  • 18