c# - Linq 的静态函数 'Contain' 在嵌套查询中弄乱了它的内容？

Question

我正在尝试减少数据库查询的数量，并在这样做时尝试一次性检索一系列分层实体(之前以递归方式获取)。

我有一个包含一组字符串的 Labels 属性:

public string[] Labels { get; set; } // new string[] {"{{a}}", "{{b}}", "{{c}}", "{{d}}", "{{e}}"};

我用它来构建第一个查询:

var IDeferredTopLabels= 
    db.labels
        .Where(l =>
            l.site_id == this.site_id &&
            this.Labels.Contains(l.name_for_code)
        )
    .Select(l => new LabelWithParentId { Label = l, ParentId = null });

如果在调试时检查以上内容，将生成以下 TSQL

SELECT [t0].[id], [t0].[name_for_code], [t0].[site_id], [t0].[priority_level]
FROM [dbo].[labels] AS [t0]
WHERE ([t0].[site_id] = 15) AND ([t0].[name_for_code] IN ('{{a}}', '{{b}}', '{{c}}', '{{d}}', '{{e}}')) 

到目前为止一切顺利，在 IDeferredToplabels 声明之后，我正在使用 Union 扩展第一个查询，指定子 Label 实体应该如何获取:

var IDeferredWithChildLabels = IDeferredTopLabels
    .Union(
        db.label__labels
        .Where(ll => 
            db.labels
                .Where(l =>
                    l.site_id == this.site_id &&
                    this.Labels.Contains(l.name_for_code)
                )
            .Select(l => l.id)
            .Contains(ll.parent_label_id)
        )
    .Select(ll => new LabelWithParentId { Label = ll.label1, ParentId = ll.parent_label_id})
    );

现在，如果我检查 TSQL 生成的是:

SELECT [t4].[id], [t4].[name_for_code], [t4].[site_id], [t4].[priority_level], [t4].[value] AS [ParentId]
FROM (
    SELECT [t0].[id], [t0].[name_for_code], [t0].[site_id], [t0].[priority_level], NULL AS [value]
    FROM [dbo].[labels] AS [t0]
    WHERE ([t0].[site_id] = 15) AND ([t0].[name_for_code] IN ('{{a}}', '{{b}}', '{{c}}', '{{d}}', '{{e}}'))
    UNION
    SELECT [t2].[id], [t2].[name_for_code], [t2].[site_id], [t2].[priority_level], [t1].[parent_label_id] AS [value]
    FROM [dbo].[label__label] AS [t1]
    INNER JOIN [dbo].[labels] AS [t2] ON [t2].[id] = [t1].[label_id]
    WHERE EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[labels] AS [t3]
        WHERE ([t3].[id] = [t1].[parent_label_id]) AND ([t3].[site_id] = 15) AND ([t3].[name_for_code] IN ('{{a}}', '{{b}}', '{{c}}', '{{a}}'0, '{{a}}'1))
        )
    ) AS [t4]

如果您在第二个 TSQL 查询的末尾看到 IN ('{{a}}', '{{b}}', '{{c}}', '{{a }}'0, '{{a}}'1))，字符串集在倒数第二个和最后一个元素旁边有一些未考虑的 0 和 1，而且它不再是初始字符串集(即'{{a}}', '{{b}}', '{{c}}', '{{d}}', '{{e}}') 而不是 '{{a} }'、'{{b}}'、'{{c}}'、'{{a}}'、'{{a}}'。

我做错了什么!？

我一无所知，感谢您的帮助，谢谢。

编辑:

我只是尝试使用 int[] 并比较 id 而不是 name_for_code 我仍然得到 1 和 0附加到数组的最后 2 个元素以及数组元素是错误的:

SELECT [t4].[id], [t4].[name_for_code], [t4].[site_id], [t4].[priority_level], [t4].[value] AS [ParentId]
FROM (
    SELECT [t0].[id], [t0].[name_for_code], [t0].[site_id], [t0].[priority_level], NULL AS [value]
    FROM [dbo].[labels] AS [t0]
    WHERE ([t0].[site_id] = 15) AND ([t0].[id] IN (1, 2, 3, 4, 5))
    UNION
    SELECT [t2].[id], [t2].[name_for_code], [t2].[site_id], [t2].[priority_level], [t1].[parent_label_id] AS [value]
    FROM [dbo].[label__label] AS [t1]
    INNER JOIN [dbo].[labels] AS [t2] ON [t2].[id] = [t1].[label_id]
    WHERE EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[labels] AS [t3]
        WHERE ([t3].[id] = [t1].[parent_label_id]) AND ([t3].[site_id] = 15) AND ([t3].[id] IN (1, 2, 3, 10, 11))
        )
    ) AS [t4]

Answer 1

好的，我发现了问题，实际上它并没有在运行代码时发生，而是仅在 Debug模式下发生，具体来说，它似乎是 Scott Gu 的 LINQ to SQL Debug Visualizer 中的一个错误。

这就是为什么我抓取的 TSQL 没有参数化，@David B 你说得很好!

我会在 Scott Gu 的页面上针对这个问题发表评论。

谢谢大家!