0

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

dfarr
  • 33
  • 5
  • 1
    Please show a textual or visual representation of the two sets of data, and the expected results. I am not understanding the question - showing the inputs and expected outputs may help my weak brain. – mjwills Jul 19 '21 at 22:38
  • Pass the whole list through as a single table parameter, not sure if it's properly supported in EF, see https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter, barring that XML, JSON or CSV might be a way forward, given that it's just a simple string list – Charlieface Jul 20 '21 at 00:02
  • Or am I misunderstanding something here: why do you need `ToList` anyway, and why do you need to `foreach`, if all the data is server-side, then build a full LINQ query to get what you want – Charlieface Jul 20 '21 at 00:04
  • I added a diagram that I hope will help visualize what's going on! – dfarr Jul 20 '21 at 16:36

1 Answers1

1

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)

Sowmyadhar Gourishetty
  • 1,843
  • 1
  • 8
  • 15
  • I did try that one out - I do have to modify it because users has many orgs, so you have to do it like user.Orgs.org instead of user.orgs. The former queries the data at the UserOrgs table level and the latter queries it at the Orgs table so you have to drive one more level to get name, foo, and bar properties. The main issue though is that string.format (either version) does not have a sql equivalent so an error is generated. – dfarr Jul 20 '21 at 16:07
  • can you please show us the Users Entity properties, I don't think we need `user.Orgs.org`. And regarding the string concatenation, we can directly use the normal string Concat i.e (`org.Name + " - " + org.foo + org.bar`) – Sowmyadhar Gourishetty Jul 20 '21 at 16:12
  • I have added a basic diagram to the question, let me know if that helps! – dfarr Jul 20 '21 at 16:36
  • Does `userorgs` has property for `Orgs`? Can we access `org name` as `user.userorgs.FirstOrDefault().org.Name`? – Sowmyadhar Gourishetty Jul 20 '21 at 17:04
  • users is a list of users, which has a list property for userorgs, which has a list property for org, and name, foo, and bar are strings properties of org. I wasn't able to get that suggestion to work out, and there has to be a few selects in the linq because each of those lists (a users list of userorgs and a userorgs list of orgs) have to be accessed before you can get to the granular details that make up the display name. – dfarr Jul 20 '21 at 20:04
  • Am still confused, why `userorgs` has list of `orgs` again. If that is the case you need to change and add a single property. `userorgs` has columns (org_id, user_id) and when we have a Navigation property from `userorgs` to `orgs` we should have only one `org` as `org_id` is the key in `orgs` table. – Sowmyadhar Gourishetty Jul 21 '21 at 02:35
  • userorgs is a mapping table that linq and the ef datacontext use to map user objects with org memberships. Without the table, you can't build a list of org memberships for a user using linq-to-sql. One user can have unlimited orgs, so the primary key of userorgs is unique and both the org id and the user id are foreign keys. The org table has the ids used by userorgs as unique primary keys - the FK to userorgs.orgid. This way they conform to the normal forms. – dfarr Jul 22 '21 at 14:15