CODEWITHSUNDEEP
c#sqlasp.netlinqentity-framework-4
dfarr
dfarr

Reputation: 33

Improving performance of Linq to SQL list intersection

I've got some working code here but I'm really concerned that it's not efficient - but I can't think of a way to improve it. Any thoughts?

We have IQueryable<Users> users, which gets its data from a users table with entity framework mapping each user to a separate table of organizations, which is a one-to-many relationship. We also have a List<string> orgCriteria, which is a list of organizations we want to filter Users by. In essence we want to get a list of users who have a membership in any of the organizations in the criteria.

To compare the names of the user's orgs to the orgs in the filter criteria, we have to use some linq/EF mappings like this: var x = users.Select(x => x.Orgs.Name).ToList(); However, the display name is what we get from the criteria, which means we have to translate the partial name to the display name as well...

These are the tables that get pulled in to all this: User, Orgs, UserOrgs. User has a FK to the Id of Orgs, and UserOrgs has 3 columns: Id, UserId, OrgId where UserId and OrgId are FKs to their respective tables. A user can have 0 or as many as there are Orgs. Each org has a name, but the display name, which we map in the domain model normally, is composed of three columns: name, foo, and bar with bar being the nullable column.

I tried an intersect like this, but it doesn't work: users = users.Where(x => x.Select(y => string.Format("{0} - {1}{2}", y.Org.Name, y.Org.foo, y.Org.bar != null ? " - " + y.Org.bar : string.Empty)).Intersect(orgCriteria).Any()); because I get this error:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

So I can make it work by combining a foreach and an intersect, but I'm concerned... If we have 500 users who each have 20 orgs, it seems like this could be a very expensive filter.

This way works, but it makes me nervous:

foreach(var user in users)
{
    List<string> userOrgNames = user.Orgs.Select(x => string.Format("{0} - {1}{2}", y.Org.Name, y.Org.foo, y.Org.bar != null ? " - " + y.Org.bar : string.Empty)).ToList();

    if (!userOrgNames.Intersect(orgCriteria).Any())
        users = users.Where(x => x.Id != user.Id);
}

Any ideas?

Edit - Here is a rudimentary diagram! user org relationships

Upvotes: 0

Views: 309

Answers (1)

Sowmyadhar Gourishetty
Sowmyadhar Gourishetty

Reputation: 1878

You can try something below.

I did this LINQ based on this statement In essence we want to get a list of users who have a membership in any of the organizations in the criteria.

var filteredUsers = users.Where(user => 
             user.Orgs.Any(org => orgCriteria.Contains($"{org.Name} - {org.foo}{org.bar}")));

In your case if $"{org.Name} - {org.foo}{org.bar}" this does not work, use string.Format("{0} - {1}{2}", org.Name, org.foo, org.bar)

Upvotes: 1

Related Questions