LINQ with existing databases and unknown schema

Question

I'm working on a database heavy project, where the Microsoft SQL databases are very mature (16 or more years-old mature), and an old product uses VB6 and ADO to generate sql which interacts with the database. I've been given the task of porting/re-writing the ancient version with a new .NET version.

I'd love to use LINQ-to-* to ensure easy maintainability, but having tried for the last several weeks I feel like LINQ-to-SQL isn't flexible enough, LINQ-to-Entities has too much overhead, and LINQ-to-Datasets is pointless since I would be just as happy using Ado.Net.

The program operates on two databases at once: one is a database with a very consistent schema containing meta-data, and the other a database which has a varying schema, is tightly coupled to the meta-database, and dictates what information from the meta-database you are interested in at any given time. Furthermore, I need non-LINQ information from both databases (such as system-stored procedures, and system-tables).

Is there any way to use LINQ intelligently here? I'd love the static typing, but if I can't have it I don't want to force my square app into a round framework.

Answer 1

Just an FYI, you can get access system tables (and sys stored procs too?) using LINQ. Here is how:

• Create a connection to the server you want.
• Right-click the server and choose Change View > Object Type.
• You should now see System Tables and User Tables. You should see sysjobs there, and you can easily drag it onto a .dbml surface.

Above was stolen from this post.

Answer 2

The best answer seems to be to use ADO.NET completely. I have the option of using Linq-to-Sql over the metabase and ADO.NET for any other database access, but that would make the code feel too inconsistent for me.