Return IQueryable containing 2 Subclasses

Question

Can you return IQueryable which is composed of two or more different subclasses ? Here's my attempt to show what I mean. It throws the error:

System.NotSupportedException: Types in Union or Concat have members assigned in different order..

var a = from oi in db.OrderItems
        where oi.OrderID == ID
            && oi.ListingID != null
        select new OrderItemA {
            // etc
        } as OrderItem;

var b = from oi in db.OrderItems
        where oi.OrderID == ID
            && oi.AdID != null
        select new OrderItemB {
            //etc
        } as OrderItem;

return a.Concat<OrderItem>(b);

Answer 1

My guess is that this is because you are using LINQ in an LINQ-to-SQL context.

So using Concat means that LINQ2SQL will need to join both query into a SQL UNION query which might be where the System.NotSupportedException originated from.

Can you try this:

return a.ToList().Concat<OrderItem>(b.ToList());

And see if it make any difference?

What the above does is that it executes the query twice and then concatenate them in-memory instead of hot-off-SQL as to avoid the query translation problem.

It might not be the ideal solution, but if this work, my assumption is probably correct, that it's a query translation problem:

More information about Union and Concat translation to SQL:

• http://blog.benhall.me.uk/2007/08/linq-to-sql-difference-between-concat.html
• http://msdn.microsoft.com/en-us/library/bb399342.aspx
• http://msdn.microsoft.com/en-us/library/bb386979.aspx

Hope this helps.

Answer 2

Can you do the projection after the concat?

// construct the query
var a = from oi in db.OrderItems
        where oi.OrderID == ID
            && oi.ListingID != null
        select new {
            type = "A"
            item = oi
        }

var b = from oi in db.OrderItems
        where oi.OrderID == ID
            && oi.AdID != null
        select new {
            type = "B"
            item = oi
        }

var temp = a.Concat<OrderItem>(b);

// create concrete types after concatenation
// to avoid inheritance issue

var result = from oi in temp
             select (oi.type == "A"
                 ? (new OrderItemA {
                         // OrderItemA projection

                     } as OrderItem)

                 : (new OrderItemB {
                         // OrderItemB projection

                     } as OrderItem)
             );

return result

Not sure if the ternary operator works in LINQ2SQL in the above scenario but that might help avoid the inheritance issue.

Answer 3

Try doing the concat on IEnumerable instead of IQueryable:

return a.AsEnumerable().Concat(b.AsEnumerable());

If you need an IQueryable result you could do this:

return a.AsEnumerable().Concat(b.AsEnumerable()).AsQueryable();

Doing this will force the concat to happen in-memory instead of in SQL, and any additional operations will also happen in-memory (LINQ To Objects).

However, unlike the .ToList() example, the execution should still be deferred (making your data lazy loaded).

Answer 4

Interestingly after reading your post and a bit of testing, I realized that what your actually doing does seem to work just fine for me given that the projection part you show as ellipsis in both of your queries match. You see, LINQ to SQL appears to construct the underlying projection for the SQL select command based off of the property assignment statements as opposed to the actual type being materialized so as long as both sides have the same number, type, and order (not sure about this) of member assignments the UNION query should be valid.

My solution that I've been working with is to create a property on my DataContext class which acts much like a SQL View in that it allows me to write a query (in my case a Union between two different tables) and then use that query as if it is itself like a table when composing read-only select statements.

public partial class MyDataContext
{
    public IQueryable<MyView> MyView
    {
        get
        {
            var query1 = 
                from a in TableA
                let default_ColumnFromB = (string)null
                select new MyView()
                {
                    ColumnFromA = a.ColumnFromA,
                    ColumnFromB = default_ColumnFromB,
                    ColumnSharedByAAndB = a.ColumnSharedByAAndB,
                };

            var query2 = 
                from a in TableB
                let default_ColumnFromA = (decimal?)null
                select new MyView()
                {
                    ColumnFromA = default_ColumnFromA,
                    ColumnFromB = b.ColumnFromB,
                    ColumnSharedByAAndB = b.ColumnSharedByAAndB,
                };

            return query1.Union(query2);
        }
    }
}

public class MyView
{
    public decimal? ColumnFromA { get; set; }
    public string ColumnFromB { get; set; }
    public int ColumnSharedByAAndB { get; set; }
}

Notice two key things:

First of all the projection formed by the queries which make up both halves of the Union have the same number, type, and order of columns. Now LINQ may require the order to be the same (not sure about this) but it is definitely true that SQL does for a UNION and we can be sure that LINQ will require at least the same type and number of columns and these "columns" are known by the member assignments and not from the properties of the type you are instantiating in your projection.

Secondly LINQ currently doesn't allow for multiple constants to be used within a projections for queries which formulate a Concat or Union and from my understanding this is mainly because these two separate queries are separately optimized before the Union operation is processed. Normally LINQ to SQL is smart enough to realize that if you have a constant value which is only being used in the projection, then why send it to SQL just to have it come right back the way it was instead of tacking it on as a post process after the raw data comes back from SQL Server. Unfortunately the problem here is that this is a case of LINQ to SQL being to smart for it's own good, as it optimizes each individual query too early in the process. The way I've found to work around this is to use the let keyword to form a range variable for each value in the projection which will be materialized by getting it's value from a constant. Somehow this tricks LINQ to SQL into carrying these constants through to the actual SQL command which keeps all expected columns in the resulting UNION. More on this technique can be found here.

Using this techinque I at least have something reusable so that no matter how complex or ugly the actual Union can get, especially with the range variables, that in your end queries you can write queries to these pseudo views such as MyView and deal with the complexity underneath.