Is it possible to add two IQueryable's together?

Question

I've been using Union on IQueryable<> (with the same type) to try to get one collection produced from two collections, but it is not working. I believe my understanding is at fault with regards to the IQueryable members.

I thought I could use the union method to build (within a foreach) a SQL statement which would execute something like:

SELECT * FROM MYTABLE WHERE FK = 1
UNION
SELECT * FROM MYTABLE WHERE FK = 9
UNION
SELECT * FROM MYTABLE WHERE FK = 15

Previously I had thought that there must be an method available which produces the same effect on two IQueryable's as the old favourite ArrayList's AddRange, which is really the basic of what I'm trying to achieve here.

Answer 1

Actually, this works exactly as expected:

var q1 = from c in db.Customers where c.CustomerID.StartsWith("A") select c;
var q2 = from c in db.Customers where c.CustomerID.StartsWith("B") select c;
var q3 = from c in db.Customers where c.CustomerID.StartsWith("C") select c;

var q4 = q1.Union(q2).Union(q3);

(I'm using the Northwind database). It generates one SQL statement which unions the three queries.

UPDATE: I think I misunderstood what you meant by "in a loop". This works correctly:)

static void Main(string[] args)
{
    var db = new NorthwindDataContext();
    IQueryable<Customer> query = from c in db.Customers 
                                 where c.CustomerID.StartsWith("A") 
                                 select c;

    foreach (var ch in "EIOV")
    {
        var chh = ch.ToString();  // separate variable so closure work right

        query = query.Union(from c in db.Customers 
                            where c.CustomerID.StartsWith(chh) 
                            select c);
    }

    foreach (var c in query)
    {
        Console.WriteLine(c.CompanyName);
    }
}

Answer 2

I finally solved this by using a List<> and simply calling AddRange(). Once the list was completed I then called list.AsQueryable() to get the IQueryable collection back out.

Is this the best approach? This will create a SQL call for each addition to the list - is there no way to create a correct UNION statement?