Sunday, October 26, 2008

LINQ to SQL cross database queries

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.

C# | LINQ

Comments [1] |

Sunday, October 26, 2008 1:33:44 PM (Eastern Daylight Time, UTC-04:00)

Another option is to call ToArray on each query and perform a local join. Even though this is an option, and it should give you the same results, you should probably never use it.

George Tsiokos

Comments are closed.