C # linq generates request length over limit. Is there a way we could raise this limit?

Question

Hi, I am creating an MVC 4 report and using EF5. The database is on SQL 2005.

The report has a large number of long string filters, when there are many, I got this error: "Some of your SQL statement is nested too deep. Rewrite the query or split it into smaller queries."

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

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

I assume that the SQL query generated by LINQ has exceeded the limit, and I do not know what the limit is.

Is there any way to control this limit? or please indicate if my assumption was wrong.

Thank you very much.

Thanks for the link below provided by @AdrianFaciu, and it is really useful, I think this is a similar problem. (I think the length of each filter line is too long, and there are many). Reaching parameter limit 2100 (SQL Server) when using Contains ()

I read a few workarounds, but still looking for a suitable solution, and not by generating string queries. It seems, at least for now, that I should download the data step by step to reduce the length of the request.

Answer 1

The correct solution uses SQL directly. EF and Linq are not tools for writing report queries. This is ORM — you use it to get objects from the database from them, and you can also modify them and save them in the database.

If you need a complex query to pull data from a database to create a report or some kind of complex search engine, you just need to pass ORM complexity and switch to a low level of SQL - and if you have reached the limit on the size of the query or parameters, you really need to. This will make your query a lot simpler, smaller and faster, and it will allow you to use some additional features, such as table parameters, to avoid big calls.

Changing all of your reports from SProcs to Linq was really stupid ... you are wasting your time producing a much worse solution.

Answer 2

I had a similar problem with EF and Firebird. When a LINQ query contains a sentence containing two sentences. The solution was simple, but not elegant - LoadAll, and filter them in memory.

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