CODEWITHSUNDEEP
.netlinq-to-sql
user49572
user49572

Reputation:

Strange LINQ to SQL .Union() error

(See at the bottom for a full repro)

With the following entity ...

[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 query fails with a NotSupportedException, with the message "Types in Union or Concat are constructed incompatibly.":

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 don't really understand why, both queries produce the same type of entity, so it shouldn't be a problem to union them?

Full repro follows:

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

Upvotes: 8

Views: 1752

Answers (2)

danarmak
danarmak

Reputation: 1190

The other answer is correct as to the cause. Here's a workaround that doesn't require loading all results into app 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);

Because managersWithOverpaidSubordinates now selects from the main Employees table (with an explicit join), Linq to SQL understands that it's a source that's not going to include null values, and agrees to perform the union.

Upvotes: 3

afrischke
afrischke

Reputation: 3866

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

Update (preliminary answer, out of the top of my head): As I suspected, the exception has to do with the fact that ManagerId is nullable. The exception text is misleading: The error doesn't occur because the two query results are of incompatible type, but because the internal representations of the left and the right query are of incompatible types. Linq2Sql takes a different code path when it finds, that the FK (i.e. ManagerId) is nullable. There is a join hidden in the query you see, (employee.Manager) and if ManagerId is of type Int32 then Linq2Sql knows that it can perform an inner join. If ManagerId is a nullable int however, then Linq2Sql finds that it needs to do a left join, even though in the example provided it could get away with an inner join because of the filter clause.

One way around the issue is to materialize one or both of the queries in question (i.e. call .ToList() or another suitable extension method) prior to performing the union.

Upvotes: 4

Related Questions