.net - 奇怪的 LINQ to SQL .Union() 错误

Question

(完整复制请参见底部)

使用以下实体...

[Table]
internal sealed class Employee
{
    private EntityRef<Employee> manager;

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    private int Id;

    [Column]
    private int? ManagerId;

    [Column]
    internal bool IsOverpaid;

    [Association(Name = "Manager_Subordinate", Storage = "manager", ThisKey = "ManagerId", IsForeignKey = true)]
    internal Employee Manager
    {
        get { return this.manager.Entity; }
        set { this.manager.Entity = value; }
    }
}

...此查询失败并出现 NotSupportedException，并显示消息“Union 或 Concat 中的类型构造不兼容。”:

var overpaidTopManagers =
    from employee in context.Employees
    where employee.IsOverpaid && (employee.Manager == null)
    select employee;
var managersWithOverpaidSubordinates =
    from employee in context.Employees
    where employee.IsOverpaid && (employee.Manager != null)
    select employee.Manager;
var query = overpaidTopManagers.Union(managersWithOverpaidSubordinates);

我真的不明白为什么，两个查询都会产生相同类型的实体，所以合并它们应该不是问题吗？

完整复制如下:

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

internal static class Program
{
    private static void Main(string[] args)
    {
        using (var context = new Context("Whatever.sdf"))
        {
            if (!context.DatabaseExists())
            {
                context.CreateDatabase();
            }

            var overpaidTopManagers =
                from employee in context.Employees
                where employee.IsOverpaid && (employee.Manager == null)
                select employee;
            var managersWithOverpaidSubordinates =
                from employee in context.Employees
                where employee.IsOverpaid && (employee.Manager != null)
                select employee.Manager;
            var query = overpaidTopManagers.Union(managersWithOverpaidSubordinates);

            // This throws a NotSupportedException with the Message
            // "Types in Union or Concat are constructed incompatibly."
            foreach (var manager in query)
            {
                Console.WriteLine(manager.ToString());
            }
        }
    }
}

[Table]
internal sealed class Employee
{
    private EntityRef<Employee> manager;

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    private int Id;

    [Column]
    private int? ManagerId;

    [Column]
    internal bool IsOverpaid;

    [Association(Name = "Manager_Subordinate", Storage = "manager", ThisKey = "ManagerId", IsForeignKey = true)]
    internal Employee Manager
    {
        get { return this.manager.Entity; }
        set { this.manager.Entity = value; }
    }
}

internal sealed class Context : DataContext
{
    internal Table<Employee> Employees;

    internal Context(string fileOrServerOrConnection) : base(fileOrServerOrConnection)
    {
        this.Employees = this.GetTable<Employee>();
    }
}

Answer 1

问题与允许外键列为空的事实有关。尝试将 ManagerId 列更改为不允许 null(只需添加一个指向自身的“nobody”占位符值以表示层次结构的根)并再次尝试联合，它现在应该可以工作了。不过不要问我为什么，还在挖掘 Linq2Sql 源代码...

更新(初步答案，从我的脑海中浮出水面):
正如我所怀疑的，这个异常(exception)与 ManagerId 的事实有关。可以为空。异常文本具有误导性:错误不是因为两个查询结果的类型不兼容，而是因为左右查询的内部表示类型不兼容。当 Linq2Sql 发现 FK(即 ManagerId)可以为空时，它采用不同的代码路径。您看到的查询中隐藏了一个联接 ( employee.Manager ) 和 if ManagerId是 Int32 类型然后 Linq2Sql 知道它可以执行内部联接。如果 ManagerId但是，它是一个可为空的 int，然后 Linq2Sql 发现它需要进行左连接，即使在提供的示例中，由于 filter 子句，它可以使用内部连接。

解决该问题的一种方法是在执行联合之前具体化一个或两个有问题的查询(即调用 .ToList() 或另一种合适的扩展方法)。