0

I've got this horrible EF code in a code base and I am scratching my head trying to figure out what it's trying to do. If I were looking to put this in a stored procedure instead how would this query look in SQL?

public void LoadNotifyListItems(UserProfileModel user, DbContext pc)
    {
        var allowedEvents = (from r in user.Roles
                             join near in pc.NotifyEventAllowedRoles on r.RoleId equals near.RoleId
                             join ne in pc.NotifyEvents on near.NotifyEventId equals ne.Id
                             select ne).Distinct();
        var NotifyListItems = from ne in allowedEvents
                          join pune in pc.UserNotifyEvents
                          on new { NotifyEventId = ne.Id, UserId = user.Id }
                          equals new { pune.NotifyEventId, pune.UserId }
                          into loj
                          from pune in loj.DefaultIfEmpty()
                          select new NotifyListItem
                          {
                              Event = ne,
                              Value = pune ?? new UserNotifyEvent
                              {
                                  NotifyEventId = ne.Id
                              }
                          };
    }

The issue I am having is the entirety of pc.UserNotifyEvents table is being queried. Monitoring the DB, EF is making this query when the LoadNotifyListItems method is being run:

[Extent1].[UserId] AS [UserId],
[Extent1].[NotifyEventId] AS [NotifyEventId],
[Extent1].[NotifyPrimaryEmail] AS [NotifyPrimaryEmail],   
[Extent1].[NotifyAlternateEmail] AS [NotifyAlternateEmail],
[Extent1].[NotifySmsNumber] AS [NotifySmsNumber],
[Extent1].[Threshold1] AS [Threshold1],
[Extent1].[Threshold2] AS [Threshold2]
FROM [UserNotifyEvents] AS [Extent1]

This isn't needed and its ingesting 200,000 rows everytime. I am thinking of moving the query to a Stored Proc and pass in userId as a parameter instead

Please let me know if there is not enough information to go off here.

CodeMuppet
  • 35
  • 7
  • 3
    What's the problem with the query ? It looks not so terrible. – Posio Apr 11 '22 at 12:19
  • Step 1: get all distinct `NotifyEvents` allowed by all `Roles` for this `user`. Then left-join all `UserNotifyEvents` for this `user` to that list and create `NotifyListItem` objects for each. – Charlieface Apr 11 '22 at 12:23
  • @Posio its currently querying the entire content of pc.UserNotifyEvents which is not intended. Table contains over 200,000 rows and there is no need to query for all of them. – CodeMuppet Apr 11 '22 at 12:23
  • The query you quote looks as if it is missing at least a `JOIN`. – Peter Smith Apr 11 '22 at 12:30
  • @PeterSmith I am not too familiar with EF. However, it looks like it's querying the entire table separately and then doing joins after the query. I can reproduce the issue I am having by running the query in the LoadNotifyListItems method. Everytime we can see the troublesome query being run (fetching the entire UserNotifyEvents table). – CodeMuppet Apr 11 '22 at 12:32
  • 1
    That's not the query I would expect to see from your `linq` – Peter Smith Apr 11 '22 at 12:33
  • Can you share the query from the profiler ? It's also important what EF Core version u use. – Posio Apr 11 '22 at 13:16

2 Answers2

0

The first part of the query is joining a list of roles in memory (user.Roles) with an IQueryable from the DbContext. This is probably the cause of your issue. You should use the pc.Roles property instead, and introduce a where clause to filter by the id of the provided user.

Jonas Høgh
  • 10,358
  • 1
  • 26
  • 46
0

If user info is in same database as other tables

Pass the user id to the stored proc. Inside the sproc, join user-roles to the other tables WHERE userid = passed-in parameter.

If user info is NOT in same database as other tables

First, get a list of role ids for the user.

var roleIds = user.Roles.Select(i=>i.RoleId).Distinct().ToList();

Then you're going to pass that list of role IDs to your stored proc. Try something like this Stack Overflow article, which tells you how to pass those role IDs as distinct strings to a stored proc.

The rest of this appears to be regular table joins within the database. The most critical part is getting those roles IDs for the user.

Xavier J
  • 4,326
  • 1
  • 14
  • 25