servicestack - ORMLite 服务栈自引用表

问题描述

我有一类公司和子公司。这些可以嵌套到任何级别并显示在树视图中。我试图弄清楚如何在 ormlite 中进行自我引用以使用下面的 DTO 构建层次结构。在下面我得到模棱两可的列名错误。这种方法对服务堆栈来说是个坏主意吗？

public class Company : DTOServiceStackBase
{
    [AutoIncrement]
    [PrimaryKey]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    public string Address { get; set; }

    [References(typeof(Company))]
    public int ParentId { get; set; }
}

下面的 DTO 在 ORMLite 中工作正常。我更喜欢上面更干净的实现。

public class Company : DTOServiceStackBase
    {
        [AutoIncrement]
        [PrimaryKey]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public string Address { get; set; }

        [Reference] // Save in SubCompanies table
        public List<SubCompany> SubCompanies { get; set; }
    }

    public class SubCompany : Company
    {
        [References(typeof(Company))]
        public int ChildCompanyId { get; set; }

        [References(typeof(Company))]
        public int ParentCompanyId { get; set; }
    }

根据 Mythz 的回复进行编辑这是我的工作代码，供任何想要使用它的人使用。

[Route("/Company/{Id}", "GET")]
public class GetCompaniesById : IReturn<GetCompaniesFlatTree>
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public int? ParentId { get; set; }
}

[Route("/CompaniesFlatTree", "GET")]
public class GetCompaniesFlatTree : IReturn<GetCompaniesFlatTree>
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public int? ParentId { get; set; }
}

[Route("/CompaniesTree", "GET")]
public class GetCompaniesTree : IReturn<Company>{}


    public class DTOServiceStackBase {
public ResponseStatus ResponseStatus { get; set; } //Automatic exception handling
    }

  public class Company : DTOServiceStackBase
    {
        [AutoIncrement]
        [PrimaryKey]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public string Address { get; set; }

        public int? ParentId { get; set; }

        [IgnoreDataMember]
        public List<Company> SubCompanies { get; set; }
    }

[Authenticate]
    public class CompanyService : Service
    {
        /// <summary>
        /// Calling SQL directly and casting to the GetCompaniesFlatTree object
        /// Don't do this methond of direct SQL unless you cannot do it any other way
        /// Why?? Becuase the SQL is not automatically updated when we updated the database schema
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public object Get(GetCompaniesFlatTree request)
        {
            //This retun uses the DB.Select and works correctly
            //return Db.Select<GetCompaniesFlatTree>($"SELECT SC.* FROM Company C Join Company SC ON SC.ParentId = C.Id Where C.ID = {request.Id}");
            //This query uses Db.Query due to the BEGIN and CTE Usage
            //This does not work with SQL in Memory because it does not support CTE Statements
            return Db.Query<GetCompaniesFlatTree>("BEGIN WITH q AS ( SELECT * FROM [Company] WHERE ParentId IS NULL UNION ALL SELECT  m.* FROM [Company] m JOIN q ON m.parentId = q.Id) SELECT * FROM q END;");
        }

        /// <summary>
        /// Table Alias is required in this Select due to the self join on company.
        /// Table Alisa allows the join to specify which table to return the data from.
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public object Get(GetCompaniesById request)
        {
            var q = Db.From<Company>(Db.TableAlias("c1"))
                .Join<Company>((ChildComp, ParentCompany) =>
                    ChildComp.Id == ParentCompany.ParentId
                    && ParentCompany.Id == request.Id, Db.TableAlias("c2")).Select<Company>(p => new {Id = Sql.TableAlias(p.Id, "c2"), Name = Sql.TableAlias(p.Name, "c2")});

            var results = Db.Select<GetCompaniesById>(q);
            //See the SQL that was generated
            var lastSql = Db.GetLastSql();
            return results;
        }

        /// <summary>
        /// Get all Compaines and build the hierarchy
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public object Get(GetCompaniesTree request)
        {
            //Get all companies
            var results = Db.Select<Company>();
            //Get Top node
            Company topCompany = results.Single(x => x.ParentId == null);
            //Find all children
            var allChildrenRecursive = GetAllChildrenRecursive(topCompany, results);

            return allChildrenRecursive;
        }

        /// <summary>
        /// Populates a Companies collection of child companies
        /// </summary>
        /// <param name="parent"></param>
        /// <param name="results"></param>
        /// <returns></returns>
        private Company GetAllChildrenRecursive(Company parent, List<Company> results)
        {
            List<Company> retVal = new List<Company>();
            retVal.Add(parent);
            //Get Children
            var children = results.Where(x => x.ParentId == parent.Id).ToList();
            parent.SubCompanies = children;
            foreach (var child in children)
            {
                GetAllChildrenRecursive(child, results);
            }

            return parent;
        }
}

标签： servicestackormlite-servicestack

servicestack - ORMLite 服务栈自引用表

问题描述

解决方案

推荐阅读

servicestack - ORMLite 服务栈 自引用表

问题描述

解决方案

推荐阅读

servicestack - ORMLite 服务栈自引用表