Is there a workaround when using linq to create a searchPredicate that exceeds 2100 parameters?

Question

Basically, I have a list of reports, and each report belongs to an area. Each user has permissions in each area. I need to list the reports that my user can see, that is, the reports that belongs to areas in which the logged user has at least read permissions.

I'm using linq expression to create a predicate and run through a detachedQuery.

I've been working on this issue for a while, found many suggestions, such as: - LinqKit - https://www.tabsoverspaces.com/233644-playing-with-parameters-limit-on-sql-server-with-entity-framework - Entity Framework Hitting 2100 Parameter Limit

None seem to be my case since I'm not exactly retrieving a list, I'm just running a predicate.

Using "contains" made get to this problem with 2100 parameters because I have more than 3000 areas in my database. What I tried to do first was to divide areas in two lists, the first includes areas that I have permission, the second includes not permitted areas. The smallest list goes to my predicate. This approach worked fine for a while, but now I have more than 4500 areas. Therefore that does not work anymore.

var predicate = PredicateBuilder.Create<Report>();

var areasWithPermission = user.Permissions.Where(v => v.Access != AccessType.NotAllowed).Select(v => v.Area.Id).ToList();
var areasWithoutPermission= user.Permissions.Where(v => v.Access == AccessType.NotAllowed).Select(v => v.Area.Id).ToList();

var predicateSearchPermissions = PredicateBuilder.Create<Report>();

if (areasWithPermission > areasWithoutPermission) {
    predicateSearchPermissions = predicateSearchPermissions.OrElse(a => !areasWithoutPermission.Contains(a.Area.Id))
}
else
{
    predicateSearchPermissions = predicateSearchPermissions.OrElse(a => areasWithPermission .Contains(a.Area.Id))
}

predicate = predicate.AndAlso(predicateSearchPermissions);

Here is a simple representation of my environment:

The limitation is not on `SQL` side, it's limited by `Entity Framework`. You could actually do a left join instead. Yes you end up with more record but you can always pass a `Distinct` or `GroupBy` after the fact. — Franck, Jul 16 '19 at 20:04
Why are you using `ToList` and pulling the data over to the client? — NetMage, Jul 16 '19 at 20:06
@Franck even doing a left join I would still need to use contains, no? — Arturio, Jul 17 '19 at 11:13
@NetMage I'm creating those two lists just because I need to pass the smallest as parameter to "contains".That used to work fine for me because if I had for example 5000 areas, a user would normally have edit permission on only 100 areas. But now I found a situation where a user would have edit permissions on 2500 areas and readOnly permissions on 2500. That destroys my logic — Arturio, Jul 17 '19 at 11:20
Are the permissions and the reports in the same database? If so you should not bring the permission up just to send them down again. — Magnus, Jul 17 '19 at 11:29
@ArthurMedeiros No, a left join is not a contain. For each record in the left table it does a select on the right table with a single key. So instead of one select with many keys it's many select with one key. — Franck, Jul 17 '19 at 12:12
@ArthurMedeiros Unfortunately no, i do not know the `Entity Framework` equivalent. I just know the `Contain` convert the query to `SELECT * FROM T WHERE Col IN (val1,val2,val3...)` which make a way to long array for `Entity Framework`. — Franck, Jul 17 '19 at 14:01
What happens if you simply leave the `ToList` off? Also remove the `if` and always do the `AreasWithPermission` test. — NetMage, Jul 17 '19 at 16:09
@NetMage I still get the error I described below saying "Specified method is not supported." — Arturio, Jul 17 '19 at 16:24
Your code doesn't seem to show a need to use `PredicateBuilder` - are you adding more conditions later? — NetMage, Jul 17 '19 at 18:35
@NetMage whenever I send an array using "Any" I get this error "Specified method is not supported". I do need to use predicate builder because I have other conditions. — Arturio, Jul 18 '19 at 12:11

Magnus · Answer 1 · 2019-07-17T12:06:30.623

0

Dont bring the permissions into memory just to send them down. "Join" the tables in a query instead. Something like:

var reports = from r in reports
              let areasWithPermission = user.Permissions.Where(v => v.Access != AccessType.NotAllowed).Select(v => v.Area.Id)
              let areasWithoutPermission = user.Permissions.Where(v => v.Access == AccessType.NotAllowed).Select(v => v.Area.Id)
              where areasWithPermission.Any(id => id == r.Area.Id) ||
                    !areasWithoutPermission.Any(id => id == r.Area.Id)
              select r;

edited Jul 17 '19 at 12:06

answered Jul 17 '19 at 11:42

Magnus

45,362
8
80
118

For a reason that I can't understand, using "Any" always returns the error 'Specified method is not supported.' – Arturio Jul 17 '19 at 13:43
Are you sure you dont have any ToList's in there? – Magnus Jul 17 '19 at 14:30
Yes, however `user.Permissions` returns an IList, and `areasWithPermission` becomes an IEnumerable. I'm trying something like `predicate = predicate.AndAlso(v => user.Permissions.Where(p => p.AccessType != AccessType.ReadOnly).Any(id => Id == Area.Id));` – Arturio Jul 17 '19 at 14:54
All tables needs to be IQueryable for the query to execute on the SQL server – Magnus Jul 18 '19 at 05:30

Is there a workaround when using linq to create a searchPredicate that exceeds 2100 parameters?

1 Answers1