Strange Behavior of LINQ Union Operator

I'm having problems using the LINQ Union operator to emulate a T-SQL UNION query that's often used to demonstrate SQL UNION query syntax:

SELECT c.CityAS City, c.CompanyName,'Customer'AS [Type]

FROM CustomersAS c, SuppliersAS s

WHERE c.City= s.City

UNIONSELECT s.CityAS City, s.CompanyName,'Supplier'

FROM CustomersAS c, SuppliersAS s

WHERE c.City= s.City

ORDERBY City

that returns the following resultset:

Following is the code for a hack to take advantage of the Country field as a temporary holder for Type values (dcNwind is a new instance of a dbml with Customers, Orders, Order_Details, Products, and Suppliers tables):

dcNwind.ObjectTrackingEnabled = false; // don't track objects
var cust = (from c in dcNwind.Customers // get the Customers
join s in dcNwind.Suppliers
on c.City equals s.City
select new { c.City, c.CompanyName, c.Country }).Distinct();
foreach (var c in cust)
{
c.Country = "Customer";
// to verify update
// sbQuery.Append(c.City + "\t" + c.CompanyName + "\t" + c.Country + System.Environment.NewLine);
}
var lstCust = cust.ToList(); // convert to a List (or Array)
cust = null;
var vend = (from c in dcNwind.Customers
join s in dcNwind.Suppliers
on c.City equals s.City
select new { s.City, s.CompanyName, s.Country }).Distinct();
foreach (var v in vend)
{
v.Country = "Supplier";
// to verify update
// sbQuery.Append(v.City + "\t" + v.CompanyName + "\t" + v.Country + System.Environment.NewLine);
}
var lstVend = vend.ToList(); // convert to a List (or Array)
dcNwind.Dispose(); // get rid of the DataContext
var both = lstVend.Union(lstCust); // perform the Union operation
foreach (var u in both)
{
sbQuery.Append(u.City + "\t" + u.CompanyName + "\t" + u.Country + System.Environment.NewLine);
}
var sort = from j in both // sorted version
orderby j.City
select j;
foreach (var u in sort)
{
//sbQuery.Append(u.City + "\t" + u.CompanyName + "\t" + u.Country + System.Environment.NewLine);
}

Regardless of whether I change the data type to an Array to List, applying the Union operator replaces the Country member values with the original country names from the database. The "DLinq Overview for C# Developers" says on page 7:

"To avoid executing multiple times convert the results into any number of standard collection classes.It is easy to convert the results into a List or Array using the Standard Query OperatorsToList() orToArray()."

The dcNwind.Log shows the following query executes four times, no matter how I change the data types, etc.

Am I on the wrong track here? I don't want to get into inheritance and a discriminator column at this point. I'm trying to stay as close as possible to the original T-SQL construct.

--rj

[6785 byte] By [RogerJennings] at [2008-1-6]