.netormdb2data-access-layer

Generating LINQ to DB2?

I have an existing DB2 database at my job. (At least, I think it's DB2. They refer to it as "the iSeries" and it looks and feels like DB2 on some mainframe-ish hardware.) For years the .NET developers in my department have just manually written ADO commands and queries to get specific data from specific tables, etc.

At the moment I'm currently building some infrastructure to help ease the development and support of our internal software, and one thing I'd like to tackle is this data access. I'm a little new to the various ORM tools out there, but I'm familiar with LINQ syntax and that's where I'd like to get. This is what we have right now:

  1. An established DB2 database with many tables.
  2. No established business objects in code.
  3. No useful relation between tables and any potential business objects.

So what I'm trying to do is create an abstraction layer between the code and the DB2 database where the developers can essentially do what they already do (grab data and populate a custom object) just more fluently and efficiently. That is, instead of creating classic ADO objects and populating a DataSet, just write a simple LINQ statement that returns an anonymous IQueryable with the fields to populate a custom object. (As an added bonus I really like the idea of compile-time errors when programmers mess something up instead of run-time errors, as happens when a typo makes its way into a SQL command string literal. Also, don't even get me started on SQL injection vulnerabilities around here.)

The thing is, I haven't been able to find any way to accomplish this yet. It's entirely possible that I'm overlooking something simple, and if that's the case then I welcome a little nudge in the right direction. But as it stands everything I find online fits into one of these categories:

  1. Yes, you can do this! All you need is a driver from IBM that there's no download link for!
  2. Here's a handy tutorial for generating a great ORM for your business objects that creates a database from scratch. (NHibernate, mostly, which would be great to use if I can use it for what I'm trying to do.)
  3. Download some tool and generate your ORM. (Tool doesn't support DB2, or claims to but fails when I try.)

Has anybody run into anything like this before? Am I approaching it in a completely incorrect way? Any advice on the matter would be much appreciated, thank you.

Edit: I'm going to go ahead and bounty this one. I've talked with some people and it sounds like "it can't be done yet" is still the going answer, but if anybody has worked around this and found a solution I'd love to hear it.

Update: Thanks to the answer below for pointing me to the DB_Linq project. It actually didn't take much for me to add some basic DB2 support to that and now I have a tested and working LINQ to DB2 provider! It's pretty simple for now, and very customized for our environment, so no plans to contribute it back to the project yet. But hopefully I'll be able to mature my fork over time and send it back. Thanks!

Solution

  • If you are not firmly oposed to doing some coding :-) getting LINQ to MySql, Oracle and Postgress and modifying it would probably save you a lot of work as well. Remember that LINQ to SQL still uses the same ADO.NET connection.

    Beware that LINQ to Entities is not the same as LINQ to SQL and even if you get that IBM lib working you'll first have to check if you are going to be allowed to use it against existing DB without a lot of proofing (it can easily trip and try to modify existing DB - check out this thread if you want to dig into it that aspect: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/f3a5b27a-5b73-476a-8c38-1eaedc8f197c).

    You can also go to IBM's Entity Framework Beta thread (start from the end where some ppl claimed working results).

    Important thing is not to try to get absolutely everything that MS SQL got - you just need table representation classes and you are good to go for the first round.

    One other thing you could try is to open normal ADO.NET connection to DB2 (or force ODBC if it turns out that non-ODBC connection is too snoopy and sensitive) to DB2, and try acting like it's SQL Server so to speak. If it turns out that SqlMetal agrees to work with that connection you are pretty much done - it will autogen table representation classes for you.

    If it balks, you'll need MS SQL Server as well, at lest for development purposes. For a start script out a few tables from DB2, create then in SQL Server and then run SqlMetal and look into the source code. You'll see that it creates pretty clan classes for table representation and just puts hefty but straight forward attributes on them -- meaning easy to copy&paste or even generate by a good script. Once you see how a small autogenned file looks like you'll also see that you can attach more code to provided hooks or remove some existing code. Check that LINQ to MySql etc. again.

    LINQ itself just needs table representation classes so you'll be reasonably free to make your own System.Data.Linq.DataContext derivative almost to your heart's desires and I suppose that the schema in DB2 hardly ever changes so you won't need to change it too frequently. LINQ is pretty open system in general (as evidenced by many LINQ to something libs) meaning that if modifying DataContext derivative is not enough you can take over the whole LINQ expressions as well.

    Once you have a proof of concept with a few tables you may need to write a perl or python script (or powershell or C#) to do a little regex replacing if DB2-s table creation scripts don't run on SQL Server intact (there's always a few syntax deviations) and you really have take the logest route.