CODEWITHSUNDEEP
c#sqllinqentity-frameworkwhere-clause
musium
musium

Reputation: 3072

Some part of your SQL statement is nested too deeply c# EF 5

I’m getting the following exception from EF 5: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Here is my Query:

String username = “test”;
IEnumerable<Int32> roles;
IEnumerable<Int32> applications;

cnx.Users.Where ( it =>
( userName != null ?  it.name = = userName : true )  &&
( !roles.Any () || roles.Contains ( it.role_id ) ) &&
( ! applications.Any () || applications.Contains ( it.application_id ) ) )
               .Count ();

Users is a simple table. Roles and applications are both typeof IEnumerable and can be empty.

How can I change my query, that it will work in EF 5 (.Net 4.0)?

Upvotes: 3

Views: 6254

Answers (2)

Tiago Gouv&#234;a
Tiago Gouv&#234;a

Reputation: 16740

Another solution, are iterate over the records getting the another ones and aggregate to an final list.

It's not fastest, not beauty, but will solve.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

For me it looks like userName, roles and applications are parameters to the query, i.e. the data comes from your application and not from the database. In that case, I would write the query like this:

IQueryable<User> query = cnt.Users;
if(userName != null)
    query = query.Where(x => x.name == userName)
if(roles.Any())
    query = query.Where(x => roles.Contains(x.role_id));
if(applications.Any())
    query = query.Where(x => applications.Contains(x.application_id))

var result = query.Count();

This is a lot more readable and doesn't include unnecessary clutter into the resulting query.

Please note that roles and applications must not represent the unexecuted result of another query. With "unexecuted result", I mean the following:

IEnumerable<Role> roles = context.Roles.Where(x => x = y);

Upvotes: 6

Related Questions