question

robs23-9535 avatar image
0 Votes"
robs23-9535 asked DuaneArnold-0443 edited

Turning off part of long running Entity Framework query conditionally

In my Asp.Net Web Api project, there's a GET method to fetch all processes. Process in my app is an entity to log various maintenence activities, it has many properties, e.g. beginning and end. Each processes can be handled by many users and so single Process can have many Handlings associated with it. Each handling also has begining and end timestamps. Recently I added "HandlingsLength" property to calculate for each process its handlings length. Unfortunately, adding this property made all requests targeting the method run almost twice as long..

Now, I'm looking for a way to improve the execution time of my method, either by complete rewrite, or by using some smart workaround. There are requests which doesn't mind waiting long for response, but there are also requests that don't need this property calculated and need to be fast too. My first idea was passing handlingsLength argument to my method and depending on its value HandlingsLength would be calculated or just set to null.

 HandlingsLength = handlingsLength == null || handlingsLength == false ? null : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn)) == null ? grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, DateTime.Now)) : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn))

Unfortunately it doesn't work, even if I pass handlingsLenght=null to indicate this property shouldn't be calculated, run time is more-less the same. Probably my linq statement is compiled to SQL at compilation so it doesn't know the value parameter provided on runtime. If there's a way to effectively 'turn off' this property at runtime, it's very welcome.

Here's complete body of my linq to entity statement:

 items = (from p in db.JDE_Processes
                          join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into finished
                          from fin in finished.DefaultIfEmpty()
                          join t in db.JDE_Tenants on p.TenantId equals t.TenantId
                          join u in db.JDE_Users on p.CreatedBy equals u.UserId
                          join at in db.JDE_ActionTypes on p.ActionTypeId equals at.ActionTypeId
                          join uu in db.JDE_Users on p.StartedBy equals uu.UserId into started
                          from star in started.DefaultIfEmpty()
                          join lsu in db.JDE_Users on p.LastStatusBy equals lsu.UserId into lastStatus
                          from lStat in lastStatus.DefaultIfEmpty()
                          join pl in db.JDE_Places on p.PlaceId equals pl.PlaceId
                          join comp in db.JDE_Components on p.ComponentId equals comp.ComponentId into comps
                          from components in comps.DefaultIfEmpty()
                          join s in db.JDE_Sets on pl.SetId equals s.SetId
                          join a in db.JDE_Areas on pl.AreaId equals a.AreaId
                          join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hans
                          from ha in hans.DefaultIfEmpty()
                          where p.TenantId == TenantId && p.CreatedOn >= dFrom && p.CreatedOn <= dTo
                          group new { p, fin, t, u, at, started, lastStatus, lStat, pl, s, a, ha }
                          by new
                          {
                              p.ProcessId,
                              p.Description,
                              p.StartedOn,
                              p.StartedBy,
                              p.FinishedOn,
                              p.FinishedBy,
                              p.PlannedFinish,
                              p.PlannedStart,
                              p.PlaceId,
                              pl.SetId,
                              SetName = s.Name,
                              pl.AreaId,
                              AreaName = a.Name,
                              pl.Image,
                              p.Reason,
                              p.CreatedBy,
                              CreatedByName = u.Name + " " + u.Surname,
                              p.CreatedOn,
                              p.ActionTypeId,
                              p.Output,
                              p.InitialDiagnosis,
                              p.RepairActions,
                              p.TenantId,
                              p.MesId,
                              p.MesDate,
                              p.Comment,
                              TenantName = t.TenantName,
                              p.IsActive,
                              p.IsCompleted,
                              p.IsFrozen,
                              p.IsSuccessfull,
                              p.IsResurrected,
                              ActionTypeName = at.Name,
                              FinishedByName = fin.Name + " " + fin.Surname,
                              StartedByName = star.Name + " " + star.Surname,
                              PlaceName = pl.Name,
                              ComponentId = p.ComponentId,
                              ComponentName = components.Name,
                              LastStatus = p.LastStatus == null ? (ProcessStatus?)null : (ProcessStatus)p.LastStatus, // Nullable enums handled
                          p.LastStatusBy,
                              LastStatusByName = lStat.Name + " " + lStat.Surname,
                              p.LastStatusOn
                          } into grp
                          orderby grp.Key.CreatedOn descending
                          select new Process
                          {
                              ProcessId = grp.Key.ProcessId,
                              Description = grp.Key.Description,
                              StartedOn = grp.Key.StartedOn,
                              StartedBy = grp.Key.StartedBy,
                              StartedByName = grp.Key.StartedByName,
                              FinishedOn = grp.Key.FinishedOn,
                              FinishedBy = grp.Key.FinishedBy,
                              FinishedByName = grp.Key.FinishedByName,
                              ActionTypeId = grp.Key.ActionTypeId,
                              ActionTypeName = grp.Key.ActionTypeName,
                              IsActive = grp.Key.IsActive,
                              IsFrozen = grp.Key.IsFrozen,
                              IsCompleted = grp.Key.IsCompleted,
                              IsSuccessfull = grp.Key.IsSuccessfull,
                              PlaceId = grp.Key.PlaceId,
                              PlaceName = grp.Key.PlaceName,
                              PlaceImage = grp.Key.Image,
                              SetId = grp.Key.SetId,
                              SetName = grp.Key.SetName,
                              AreaId = grp.Key.AreaId,
                              AreaName = grp.Key.AreaName,
                              Output = grp.Key.Output,
                              TenantId = grp.Key.TenantId,
                              TenantName = grp.Key.TenantName,
                              CreatedOn = grp.Key.CreatedOn,
                              CreatedBy = grp.Key.CreatedBy,
                              CreatedByName = grp.Key.CreatedByName,
                              MesId = grp.Key.MesId,
                              InitialDiagnosis = grp.Key.InitialDiagnosis,
                              RepairActions = grp.Key.RepairActions,
                              Reason = grp.Key.Reason,
                              MesDate = grp.Key.MesDate,
                              Comment = grp.Key.Comment,
                              ComponentId = grp.Key.ComponentId,
                              ComponentName = grp.Key.ComponentName,
                              PlannedStart = grp.Key.PlannedStart,
                              PlannedFinish = grp.Key.PlannedFinish,
                              LastStatus = grp.Key.LastStatus,
                              LastStatusBy = grp.Key.LastStatusBy,
                              LastStatusByName = grp.Key.LastStatusByName,
                              LastStatusOn = grp.Key.LastStatusOn,
                              IsResurrected = grp.Key.IsResurrected,
                              OpenHandlings = grp.Where(ph => ph.ha.HandlingId > 0 && (ph.ha.IsCompleted == null || ph.ha.IsCompleted == false)).Count(),
                              AllHandlings = grp.Where(ph => ph.ha.HandlingId > 0).Count(),
                              AssignedUsers = (from pras in db.JDE_ProcessAssigns
                                               join uu in db.JDE_Users on pras.UserId equals uu.UserId
                                               where pras.ProcessId == grp.Key.ProcessId
                                               select uu.Name + " " + uu.Surname),
                              GivenTime = givenTime == null || givenTime == false ? 0 : (from prac in db.JDE_ProcessActions
                                                                                         join a in db.JDE_Actions on prac.ActionId equals a.ActionId
                                                                                         where prac.ProcessId == grp.Key.ProcessId
                                                                                         select a.GivenTime).Sum(),
                              FinishRate = finishRate == null || finishRate == false ? 0 : db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId)==0 
                                                                                         ? 100 : (((float)db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId && i.IsChecked == true)
                                                                                         / (float)db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId))*100),
                              HasAttachments = db.JDE_FileAssigns.Any(f => f.ProcessId == grp.Key.ProcessId),
                              HandlingsLength = handlingsLength == null || handlingsLength == false ? null : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn)) == null ? grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, DateTime.Now)) : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn))
                          });


Please note that this question has also been asked at StackOverflow.com


dotnet-entity-framework
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered DuaneArnold-0443 edited

You could look into using SQLCLR and MS SQL Server Broker that can be used for a long running process involving SQL Server Service Broker

https://en.wikipedia.org/wiki/SQL_CLR

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-ver15

https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics

You could make a DLL for your code and refernce it in SQLCLR stored procedure both being hosted by SB

From an ASP.NET solution, you can contact SB in an async manner to execute the SQLCLR stored procedure.

Your DLL solution can be using EF too.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.