After discovering that the LINQ to SQL Designer will only support tables from a single data source I set out to manually implement a cross database query using two data contexts.
The result is the following query which joins orders in the OrderDB to products in the VideoGameStoreDB.
public List<Order> FindOrders( string typename )
{
try
var productdb = new VideoGameStoreDBDataContext();
var orderdb = new OrderDBDataContext();
var query = from o in orderdb.Orders
join p in productdb.Products on o.ProductID equals p.ProductID
where p.ProductType.ProductTypeName.Contains( typename )
select o;
return query.ToList();
}
catch ( Exception exception )
Trace.WriteLine( exception );
throw;
LINQ to SQL is unable to resolve this query, even though both databases sit on the same server. The compiler will however not warn you not to do this, instead a runtime exception with message 'The query contains references to items defined on a different data context.' occurs.
In a scenario like this the only solution appears to be to write a stored procedure which can perform the cross database query and use that stored procedure from a data context.
Remember Me
a@href@title, strike