ServiceStack - [参考] 还是 [忽略]? [英] ServiceStack - [Reference] or [Ignore]?

问题描述

我们有一个 DTO - 员工 - 有许多(> 20)个相关的 DTO 和 DTO 集合.出于返回 JSON 大小"的原因，我们将这些关系标记为 [Ignore].然后由客户端来填充他们想要使用其他 REST 调用的任何相关 DTO.

We have a DTO - Employee - with many (> 20) related DTOs and DTO collections. For "size of returned JSON" reasons, we have marked those relationships as [Ignore]. It is then up to the client to populate any related DTOs that they would like using other REST calls.

我们尝试了一些方法来满足客户希望获得一些相关员工信息但并非全部信息的愿望:

We have tried a couple of things to satisfy clients' desire to have some related Employee info but not all:

我们创建了一个新的 DTO - EmployeeLite - 它具有使用RelatedTableNameRelatedFieldName"方法定义的请求最多的字段，并使用了 QueryBase 重载并且效果很好.

We created a new DTO - EmployeeLite - which has the most-requested fields defined with "RelatedTableNameRelatedFieldName" approach and used the QueryBase overload and that has worked well.

我们还尝试向请求 DTO 添加一个属性 - References" - 这是客户端想要填充的相关 DTO 的逗号分隔列表.然后我们迭代响应并使用相关的 DTO 或列表填充每个员工.迭代大型列表时存在性能问题.

We've also tried adding a property to a request DTO - "References" - which is a comma-separated list of related DTOs that the client would like populated. We then iterate the response and populate each Employee with the related DTO or List. The concern there is performance when iterating a large List.

我们想知道是否有建议的方法来解决我们正在尝试做的事情?

We're wondering if there a suggested approach to what we're trying to do?

感谢您提出的任何建议.

Thanks for any suggestions you may have.

更新:

这是我们请求 DTO 的一部分:

Here is a portion of our request DTO:

[Route("/employees", "GET")]
public class FindEmployeesRequest : QueryDb<Employee> {
    public int? ID { get; set; }
    public int[] IDs { get; set; }
    public string UserID { get; set; }
    public string LastNameStartsWith { get; set; }
    public DateTime[] DateOfBirthBetween { get; set; }
    public DateTime[] HireDateBetween { get; set; }
    public bool? IsActive { get; set; }
}

该服务没有代码(使用 QueryDb 自动实现)，所以我添加了一些代码来尝试合并"方法:

There is no code for the service (automagical with QueryDb), so I added some to try the "merge" approach:

public object Get(FindEmployeesRequest request) {
    var query = AutoQuery.CreateQuery(request, Request.GetRequestParams());

    QueryResponse<Employee> response = AutoQuery.Execute(request, query);

    if (response.Total > 0) {
        List<Clerkship> clerkships = Db.Select<Clerkship>();

        response.Results.Merge(clerkships);
    }

    return response;
}

此操作失败，无法在父雇员"上找到文员"的子参考

因为在员工中我们有:

    [Ignore]
    public List<Clerkship> Clerkships { get; set; }

我们这样做是因为我们不希望每个请求都带有Clerkships".如果我将 [Ignore] 更改为 [Reference] 我不需要在服务中使用上面的代码 - 列表会自动出现.所以看起来 .Merge 只适用于我们不想做的 [Reference].

which we did because we don't want "Clerkships" with every request. If I change [Ignore] to [Reference] I don't need the code above in the service - the List comes automatically. So it seems that .Merge only works with [Reference] which we don't want to do.

我不确定如何在 AutoQuery 服务中使用自定义加载引用"方法.而且，AFAIKT，自定义字段"方法不能用于相关的 DTO，只能用于基表中的字段.

I'm not sure how I would use the "Custom Load References" approach in an AutoQuery service. And, AFAIKT, the "Custom Fields" approach can't be use for related DTOs, only for fields in the base table.

更新 2:

带有 include[] 的 LoadSelect 对我们来说效果很好.我们现在试图涵盖在查询字符串中使用 ?fields= 但客户端没有请求相关 DTO 的 ID 字段的情况:

The LoadSelect with include[] is working well for us. We are now trying to cover the case where ?fields= is used in the query string but the client does not request the ID field of the related DTO:

public partial class Employee {
    [PrimaryKey]
    [AutoIncrement]
    public int ID { get; set; }
    .
    .
    .
    [References(typeof(Department))]
    public int DepartmentID { get; set; }
    .
    .
    .

public class Department {
    [PrimaryKey]
    public int ID { get; set; }
    public string Name { get; set; }
    .
    .
    .
}

所以，对于请求

/employees?fields=id,departmentid

我们会得到部门的回应.但是对于请求

we will get the Department in the response. But for the request

/employees?fields=id

我们不会在回复中得到部门.

we won't get the Department in the response.

我们正在尝试通过修改 query.SelectExpression 并将 , "Employee"."DepartmentID" 添加到 来为请求者安静地修复"这个问题>SELECT 在执行 Db.LoadSelect 之前.调试显示 query.SelectExpression 正在被修改，但根据 SQL Profiler，"Employee"."DepartmentID" 未被选中.

We're trying to "quietly fix" this for the requester by modifying the query.SelectExpression and adding , "Employee"."DepartmentID" to the SELECT before doing the Db.LoadSelect. Debugging shows that query.SelectExpression is being modified, but according to SQL Profiler, "Employee"."DepartmentID" is not being selected.

我们还应该做些什么来将 "Employee"."DepartmentID" 添加到 SELECT 中?

Is there something else we should be doing to get "Employee"."DepartmentID" added to the SELECT?

谢谢.

更新 3:

Employee 表具有三个 1:1 关系 - EmployeeType、Department 和 Title:

The Employee table has three 1:1 relationships - EmployeeType, Department and Title:

public partial class Employee {
    [PrimaryKey]
    [AutoIncrement]
    public int ID { get; set; }

    [References(typeof(EmployeeType))]
    public int EmployeeTypeID { get; set; }

    [References(typeof(Department))]
    public int DepartmentID { get; set; }

    [References(typeof(Title))]
    public int TitleID { get; set; }
    .
    .
    .
}

public class EmployeeType {
    [PrimaryKey]
    public int ID { get; set; }
    public string Name { get; set; }
}

public class Department {
    [PrimaryKey]
    public int ID { get; set; }
    public string Name { get; set; }

    [Reference]
    public List<Title> Titles { get; set; }
}

public class Title {
    [PrimaryKey]
    public int ID { get; set; }
    [References(typeof(Department))]
    public int DepartmentID { get; set; }
    public string Name { get; set; }
}

4.0.55 的最新更新允许这样做:

The latest update to 4.0.55 allows this:

/employees?fields=employeetype,department,title

我取回了所有 Employee 表字段以及三个相关的 DTO - 一件奇怪的事情 - Employee 的 ID 字段填充了 Employee 的 TitleID 值(我想我们以前见过这个?).

I get back all the Employee table fields plus the three related DTOs - with one strange thing - the Employee's ID field is populated with the Employee's TitleID values (I think we saw this before?).

此请求修复了该异常:

/employees?fields=id,employeetypeid,employeetype,departmentid,department,titleid,title

但我丢失了所有其他员工字段.

but I lose all of the other Employee fields.

这听起来像是吃你的蛋糕，也吃它"的请求，但有没有一种方法可以让我获得所有员工字段和选择性相关的 DTO?类似的东西:

This sounds like a "have your cake and eat it too" request, but is there a way that I can get all of the Employee fields and selective related DTOs? Something like:

/employees?fields=*,employeetype,department,title

推荐答案

自动查询可自定义字段

不确定这是否相关，但 AutoQuery 内置支持 使用 ?fields=Field1,Field2 选项自定义要返回的字段.

AutoQuery Customizable Fields

Not sure if this is Relevant but AutoQuery has built-in support for Customizing which fields to return with the ?fields=Field1,Field2 option.

由于您没有提供任何源代码，因此不清楚您要实现的目标或现有解决方案的低效率所在，但您不想做任何 N+1代码> SELECT 查询.如果是，请查看如何合并断开连接的 POCO 结果 在一起，这将使您可以根据使用 OrmLite 引用定义的关系合并来自单独查询的结果，例如，下面的示例使用 2 个不同的查询将客户与其订单连接起来:

As you've not provided any source code it's not clear what you're trying to achieve or where the inefficiency with the existing solution lies, but you don't want to be doing any N+1 SELECT queries. If you are, have a look at how you can merge disconnected POCO results together which will let you merge results from separate queries based on the relationships defined using OrmLite references, e.g the example below uses 2 distinct queries to join Customers with their orders:

//Select Customers who've had orders with Quantities of 10 or more
List<Customer> customers = db.Select<Customer>(q =>
    q.Join<Order>()
     .Where<Order>(o => o.Qty >= 10)
     .SelectDistinct());

//Select Orders with Quantities of 10 or more
List<Order> orders = db.Select<Order>(o => o.Qty >= 10);

customers.Merge(orders); // Merge disconnected Orders with their related Customers

自定义加载引用

您可以通过在调用 OrmLite 的 Load* API 时指定它们来选择性地控制 OrmLite 应加载的引用，例如:

Custom Load References

You can selectively control which references OrmLite should load by specifying them when you call OrmLite's Load* API's, e.g:

var customerWithAddress = db.LoadSingleById<Customer>(customer.Id, 
    include: new[] { "PrimaryAddress" });

在 AutoQuery 中使用自定义加载引用

您可以通过在自定义 AutoQuery 实现中使用 Db.Select 而不是 Db.LoadSelect 来自定义 AutoQuery 请求以不返回任何引用，例如:

Using Custom Load References in AutoQuery

You can customize an AutoQuery Request to not return any references by using Db.Select instead of Db.LoadSelect in your custom AutoQuery implementation, e.g:

public object Get(FindEmployeesRequest request) 
{
    var q = AutoQuery.CreateQuery(request, Request);
    var response = new QueryResponse<Employee>
    {
        Offset = q.Offset.GetValueOrDefault(0),
        Results = Db.Select(q),
        Total = (int)Db.Count(q),
    };
    return response;
}

同样，如果您只想有选择地加载 1 个或多个引用，您可以更改 LoadSelect 以传入一个 include: 数组，其中仅包含您想要包含的引用字段，例如:

Likewise if you only want to selectively load 1 or more references you can change LoadSelect to pass in an include: array with only the reference fields you want included, e.g:

public object Get(FindEmployeesRequest request) 
{
    var q = AutoQuery.CreateQuery(request, Request);
    var response = new QueryResponse<Employee>
    {
        Offset = q.Offset.GetValueOrDefault(0),
        Results = Db.LoadSelect(q, include:new []{ "Clerkships" }),
        Total = (int)Db.Count(q),
    };
    return response;
}

这篇关于ServiceStack - [参考] 还是 [忽略]?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！