locked
DLINQ across multiple databases RRS feed

  • Question

  • Hi!
    My problem is that our business system resides in one database, and my own system resides in another database, but they are on the same machine.
    It seems that I cant have more that one database in one datacontext, is it going to be possible to have multiple databases in one datacontext in the release version of dlinq, so that i could do a join between the users in my database and the orders in the business system?

    // Leon
    Wednesday, April 4, 2007 11:13 AM

Answers

  • LINQ to SQL is not currently designed to allow for queries across multiple databases; the designer and the SQLMetal tool will only build you DataContext's that refer to items from a single database/catalog.  You can, however, trick LINQ to SQL into doing your bidding by including fully qualified names for your tables.  Instead of simply Name="Customers" in the mapping use Name="[mydb].[dbo].[Customers]".  Using this technique you can make a DataContext that lists tables from any database/catalog as long as it is hosted by the same server.

     

    Thursday, April 12, 2007 5:01 AM

All replies

  • I don't have access to my LINQ environment to try it out, so shooting from the hip:

     

    You could make a view in one database to see the tables in the other:

    CREATE VIEW dbo.Users

    AS
    SELECT fieldlist FROM UserDB.dbo.Users

     

    Then the LINQ object mapper should be able to see the view and should be able to query objects in that other database just fine through the view (does the mapper see views?)

     

    Another route to go is to use LINQ to Entities, where the Entity model knows where the tables are and builds the SQL to query them appropriately.

    Wednesday, April 4, 2007 11:48 PM
  • I've been able to do it with views. but then I loose the insert and update features, which is a must have.

    I hade a look at LINQ to Entities and it looks promising, but it looks like there wont be a wysisyg designer for it in the orcas release which is a bummer.
    Thursday, April 5, 2007 9:57 AM
  • LINQ to SQL is not currently designed to allow for queries across multiple databases; the designer and the SQLMetal tool will only build you DataContext's that refer to items from a single database/catalog.  You can, however, trick LINQ to SQL into doing your bidding by including fully qualified names for your tables.  Instead of simply Name="Customers" in the mapping use Name="[mydb].[dbo].[Customers]".  Using this technique you can make a DataContext that lists tables from any database/catalog as long as it is hosted by the same server.

     

    Thursday, April 12, 2007 5:01 AM
  • So, is there any wayt to use link to build (or simulate) a join between two tables on entirely separate servers ? We have a highly partitioned environment, and end up doing a lot of dataset/datatable merges between query results from multiple servers. LINQ seems like an ideal tool to abstract this away if there's a way to support it. Would be great if it supported PostgreSQL as well, but now I'm just talking crazy. Thanks.
    Monday, May 7, 2007 1:49 AM
  • Sure, LINQ supports joins just fine, take this code for an example: 

    Code Snippet

    var query = from c in context.Customers
        select c;

    var q4 = from s in new string[] { "ALFKI", "AAAAA" }
             select s;

    var q5 = from c in query.ToList()
             join s in q4 on c.Id equals s
             select new { Id = s, Name = c.ContactName };

     

    It joins customers from Northwind with a string array. Note that it has to execute query (by calling ToList()) first and only then do the join. There is also LINQ to DataSet that might help you.

    But there are other problems for you: LINQ to SQL doesn't work with anything other than Sql Server. You have to look for LINQ to Entities/Entity Framework if you wish support for other databases as well.

    Monday, May 7, 2007 9:40 AM
  • Right, but as Matt suggested above, you can't join across servers. The dataset option is something I could possibly use instead of merging, after I've already done the db queries manually. Is the best workaround ? Just to make this concrete, assume the Customers table lives in a MySQL database, and the Orders table is in SQL Server. Thanks.
    Monday, May 7, 2007 2:28 PM
  • At this point, LINQ to SQL only works against SQL Server (2000+) and SQL Express. It does not work against MySql. (There is a project to enable it with MySql at http://code2code.net/Linq_Mysql/ if you are interested in that project).

    The DataContext lies at the heart of LINQ to SQL for both parsing the expression trees and issuing commands to the server. You could set up separate data contexts for different servers, but you wouldn't be able to create a single query across two data contexts. As an alternative, you could use LINQ to Objects to handle the hetrogeneous join after you cast the results of queries from both sources ToArray/ToList. However, the penalty of heterogenous joins on the client is quite high from a bandwidth and memory perspective.

     

    Jim Wooley

    http://linqinaction.net

    http://devauthority.com/blogs/jwooley

    Monday, May 7, 2007 4:59 PM
  • It is very much possible to perform cross database join using LINQ-to-SQL

    Refer to the below link, good example of how to perform cross database join operation

    http://www.a2zmenu.com/LINQ/Perform%20Cross%20Database%20LINQ%20join%20Operation.aspx

    Thursday, June 3, 2010 7:57 PM