Strong LINQ to SQL.Union () error

Question

(see below for full playback)

With the following object ...

[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; } } } 

... this request failed with a NotSupportedException error, with the message "Types in union or Concat" built incompatible. ":

 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); 

I really don’t understand why both queries create the same type of entity, so it should not be a problem to combine them?

The following is the full text:

 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

The problem is that the foreign key column is allowed to be null. Try changing the ManagerId column so that it does not allow null (just add the placeholder "nobody" value that points to itself to represent the root of the hierarchy) and try to merge again, it should work now. Don't ask me why, though, still digging out the Linq2Sql source code ...

Update (preliminary answer, from the top of my head): As I suspected, the exception is due to the fact that ManagerId is NULL. The exception text is misleading: an error does not occur because the two query results are of an incompatible type, but because the internal representations of the left and right queries are of incompatible types. Linq2Sql takes a different code path when it finds that FK (i.e. ManagerId ) is NULL. There is a connection in the query that you see ( employee.Manager ), and if ManagerId is of type Int32 , then Linq2Sql knows that it can perform an internal connection. However, if ManagerId is a null int, then Linq2Sql discovers that it needs to perform a left join, although in the above example, it might leave with the inner join due to a filter clause.

One way to solve the problem is to materialize one or both of these questions (for example, call .ToList () or another suitable extension method) before performing the join.

Answer 2

Another answer is correct regarding the reason. Here's a workaround that does not require loading all the results into the application space:

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

Since managerWithOverpaidSubordinates now selects Employees from the main table (with an explicit join), Linq to SQL understands that it is a source that will not include null values ​​and agrees to join.