public List<ExpandedDependency> GetDependenciesInLockHorizon(int instanceId, DateTimeOffset now, DateTimeOffset horizon)
        {
            using (var cnt = new ApplicationDbContext())
            {
                var query = from j in cnt.Jobs
                            join d in cnt.Dependencies on j.Id equals d.JobId
                            join d1 in cnt.Dependencies on d.TaskBId equals d1.TaskAId into DepJoin
                            from dep in DepJoin.DefaultIfEmpty()
                            join t1 in cnt.Tasks on d.TaskAId equals t1.Id
                            join t2 in cnt.Tasks on d.TaskBId equals t2.Id
                            join t3 in cnt.Tasks on dep.TaskBId equals t3.Id into tasksjoin
                            from tas in tasksjoin.DefaultIfEmpty()
                            join s1 in cnt.Segments on t1.Id equals s1.TaskId
                            join s2 in cnt.Segments on t2.Id equals s2.TaskId
                            join s3 in cnt.Segments on tas.Id equals s3.TaskId
                            where
                   d.TaskA.InstanceId == instanceId &&
                   j.Status != JobStatus.Reserved && j.Status != JobStatus.Excluded && j.Status != JobStatus.Historic && j.ValidationStatus == ValidationStatus.VALIDATED &&
                                //!d.TaskA.AutoWorkerFixed &&
                                s1.ActivitySubType == SubType.PREPARE && s2.ActivitySubType == SubType.REVIEW &&
                   (
                       (
                           (d.TaskA.WorkerId != null && d.TaskA.Start.HasValue && d.TaskA.Start < horizon.UtcDateTime && d.TaskA.End.HasValue && d.TaskA.End > now)
                           || (d.TaskB.WorkerId != null && d.TaskB.Start.HasValue && d.TaskB.Start < horizon.UtcDateTime && d.TaskB.End.HasValue && d.TaskB.End > now)
                       )
                      || (d.TaskA.AutoWorkerFixed || d.TaskB.AutoWorkerFixed || tas.AutoWorkerFixed)
                   )
                            group new { s1, s2 ,s3} by
                            new
                            {
                                jobStatus = j.Status,
                                jobValidationStatus = j.ValidationStatus,
                                prepId = t1.Id,
                                prepFixed = t1.AutoWorkerFixed,
                                prepStart = t1.Start,
                                revId = t2.Id,
                                revFixed = t2.AutoWorkerFixed,
                                revStart = t2.Start,
                                qrId = tas.Id,
                                qrFixed = tas.AutoWorkerFixed,
                                qrStart = tas.Start,
                            } into grp
                            select new ExpandedDependency
                            {
                                PrepareTask = new ExpandedTask
                                {
                                    obj = new { Id = grp.Key.prepId, AutoWorkerFixed = grp.Key.prepFixed, Start = grp.Key.prepStart },
                                    JobStatus = grp.Key.jobStatus,
                                    JobValidationStatus = grp.Key.jobValidationStatus,
                                    TaskActivities = grp.Where(g => g.s1.ActivityId.HasValue).Select(g => g.s1.ActivityId.Value).ToList()
                                }
                                ,
                                ReviewTask = new ExpandedTask
                                {
                                    obj = new { Id = grp.Key.revId, AutoWorkerFixed = grp.Key.revFixed, Start = grp.Key.revStart },
                                    JobStatus = grp.Key.jobStatus,
                                    JobValidationStatus = grp.Key.jobValidationStatus,
                                    TaskActivities = grp.Where(g => g.s2.ActivityId.HasValue).Select(g => g.s2.ActivityId.Value).ToList()
                                },
                                QrTask = new ExpandedTask
                                {
                                    obj = new { Id = grp.Key.qrId, AutoWorkerFixed = grp.Key.qrFixed, Start = grp.Key.qrStart },
                                    JobStatus = grp.Key.jobStatus,
                                    JobValidationStatus = grp.Key.jobValidationStatus,
                                    TaskActivities = grp.Where(g => g.s3.ActivityId.HasValue).Select(g => g.s3.ActivityId.Value).ToList()
                                }
                            };

                return query.ToList();
            }
        }