LINQ across multiple databases

by

You can do this, even across servers, as long as you can access one database from the other. That is, if it’s possible to write a SQL statement against ServerA.DatabaseA that accesses ServerB.DatabaseB.schema.TableWhatever, then you can do the same thing in LINQ.

To do it, you’ll need to edit the .dbml file by hand. You can do this in VS 2008 easily like this: Right-click, choose Open With…, and select XML Editor.

Look at the Connection element, which should be at the top of the file. What you need to do is provide an explicit database name (and server name, if different) for tables not in the database pointed to by that connection string.

The opening tag for a Table element in your .dbml looks like this:

<Table Name="dbo.Customers" Member="Customers">

What you need to do is, for any table not in the connection string’s database, change that Name attribute to something like one of these:

<Table Name="SomeOtherDatabase.dbo.Customers" Member="Customers">
<Table Name="SomeOtherServer.SomeOtherDatabase.dbo.Customers" Member="Customers">

If you run into problems, make sure the other database (or server) is really accessible from your original database (or server). In SQL Server Management Studio, try writing a small SQL statement running against your original database that does something like this:

SELECT SomeColumn
FROM OtherServer.OtherDatabase.dbo.SomeTable

If that doesn’t work, make sure you have a user or login with access to both databases with the same password. It should, of course, be the same as the one used in your .dbml’s connection string.

More Related Contents:

  1. LINQ-to-SQL vs stored procedures? [closed]
  2. Linq: adding conditions to the where clause conditionally
  3. LINQ Between Operator
  4. Learning about LINQ [closed]
  5. How can I reject all changes in a Linq to SQL’s DataContext?
  6. LINQ with SQLite (linqtosql)
  7. How to create a dynamic LINQ join extension method
  8. LINQ not updating on .SubmitChanges()
  9. How to dynamically add OR operator to WHERE clause in LINQ
  10. LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator
  11. Linq To Sql – Dynamic OrderBy – Case When
  12. Is there a pattern using Linq to dynamically create a filter?
  13. Linq to SQL how to do “where [column] in (list of values)”
  14. Multiple WHERE clause in Linq
  15. How do I write this cross apply query in LINQ-to-SQL?
  16. Point connectionstring in dbml to app.config
  17. Building Dynamic LINQ Queries based on Combobox Value
  18. NHibernate vs LINQ to SQL
  19. Convert this sql command to c# linq
  20. LINQ equivalent of foreach for IEnumerable
  21. Is it possible to Pivot data using LINQ?
  22. How can I do an OrderBy with a dynamic string parameter?
  23. LINQ to Entities does not recognize the method ‘System.String Format(System.String, System.Object, System.Object)’
  24. LINQ to SQL and a running total on ordered results
  25. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  26. LINQ: Dot Notation vs Query Expression
  27. Convert Linq Query Result to Dictionary
  28. Linq To Entities – how to filter on child entities
  29. Will using LINQ to SQL help prevent SQL injection
  30. LINQ: How to convert the nested hierarchical object to flatten object

Leave a Comment