我有一类公司和子公司。这些可以嵌套到任何级别并显示在树视图中。我试图找出如何使用以下DTO在ormlite中进行自我参考以建立层次结构。与下面的我得到模棱两可的列名错误。这种方法对于Service Stack是个坏主意吗?
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;
}
}
要保持树关系,您只需要int? ParentId
在Company
表中具有nullable即可,其中以NULL
ParentId为公司的Company是根公司,同时遍历其余公司以填充Dictionary<int,List<Company>>
由父ID编制的索引。
这与OrmLite自我引用无关,后者仅意味着将FK引用维护为包含该引用的表上的其他表。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句