#region License Information /* HeuristicLab * Copyright (C) 2002-2017 Heuristic and Evolutionary Algorithms Laboratory (HEAL) * * This file is part of HeuristicLab. * * HeuristicLab is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * HeuristicLab is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with HeuristicLab. If not, see . */ #endregion using System; using System.Collections.Generic; using System.Data.Linq; using System.Linq; namespace HeuristicLab.Services.Hive.DataAccess.Daos { public class ProjectDao : GenericDao { public ProjectDao(DataContext dataContext) : base(dataContext) { } public override Project GetById(Guid id) { return GetByIdQuery(DataContext, id); } public IEnumerable GetProjectsByChildId(Guid id) { return DataContext.ExecuteQuery(GetProjectsByChildIdQuery, id); } public IEnumerable GetProjectIdsByChildId(Guid id) { return DataContext.ExecuteQuery(GetProjectIdsByChildIdQuery, id); } #region Compiled queries private static readonly Func GetByIdQuery = CompiledQuery.Compile((DataContext db, Guid projectId) => (from project in db.GetTable() where project.ProjectId == projectId select project).SingleOrDefault()); #endregion #region String queries private const string GetProjectsByChildIdQuery = @" WITH ptree AS ( SELECT ProjectId, ParentProjectId FROM [Project] UNION ALL SELECT pt.ProjectId, p.ParentProjectId FROM [Project] p JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId ) SELECT DISTINCT pro.* FROM ptree, [Project] pro WHERE ptree.ProjectId = {0} AND ptree.ParentProjectId = pro.ProjectId "; private const string GetProjectIdsByChildIdQuery = @" WITH ptree AS ( SELECT ProjectId, ParentProjectId FROM [Project] UNION ALL SELECT pt.ProjectId, p.ParentProjectId FROM [Project] p JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId ) SELECT DISTINCT ptree.ParentProjectId FROM ptree WHERE ptree.ProjectId = {0} "; #endregion } }