Is there a library to use Linq to directly query databases, dynamically?

Question

I have a very dynamic SQL Server problem to solve. I am developing a tool that verifies matched data in multiple databases. [NOTE: the requirements on this project are pretty strict]

I need to make a C# tool that lets a user select any database from a dynamic list, then lets the user select a table (acting as a source db/table). Once that is done, the user does it again for another database and table (or multiple tables as the destination)

Once a source and destination are chosen, the user may select which fields map-on-to which other fields.

Finally, the program needs to loop over the records in the source table and find any matching keys in the destination table. It needs to check things like, only one key matches, all the other mapped fields have the same data, etc...

I would like to use Linq in my code and just pass strings for the fields/tables that I am running this basic logic against. The problem is that the databases & schema are unknown at compile time.

I am looking for direction on this matter. I can't seem to find a solution. The closest I have come is the dynamic Linq-to-sql library that is out there. But that is only dynamic within the bounds of you data model. I can't even have a data model.

I was thinking of dropping the whole Linq thing all together an just writing my own quick and dirty method chaining to generate the basic SQL that I need (basically a fancy string builder).

Answer 1

I don't think Linq-to-SQL is very well suited for this task. L2S excels at getting your business objects from database, and updating them again - one (or a few) at a time.

I don't think using Linq-to-SQL will greatly benefit you in this case, where you're really dealing with the metadata about the databases and tables in question. In this case, you can either use straight SQL and ADO.NET (SqlDataReader preferably) to query the system catalog views (like sys.tables, sys.columns and so forth), or you could think about using the SQL Server Management Objects (SMO) library to get access to the innards of your SQL Servers.

Either way, you'll probably end up with lists of objects you've assembled yourself from the bits and pieces - and you can, of course, use Linq-to-Objects on those in-memory lists and collections, no problem.

Some resources to check out:

• MSDN OVerview of SMO
• As if to prove me wrong: Linq-to-SQL Data Synchronizer (using SMO under the covers)