Convert this EF query to SQL-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.