Generating LINQ to DB2?

Question

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!

Answer 1

This is on my list of things to do as I run a big IBM DB2 system and have .net develoeprs. Not that easy to get the drivers and plugins for visual studio 2008/2010.

ORM - I know (Ive read) that nHibernate and Entity Framework both support the DB2 SQL syntax. nHibernate has a larger learning curve and we are undecided if we will jump on this. But entity framework (the new one) look very good and currently usign against SQL server 2008, but have in mind to point at DB2 as well.

Both nhibernate and Entity framework support runnign LINQ against them, without any issues.

Thansk

Simon

Answer 2

I'm not a DB2 man, but here's some ideas you may or may not have seen.

• IBM's documentation of DB2 Entity Framework support: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.ms.doc/doc/c0054118.html
• LLBLGen, a LINQ provider (Note: the page claims not to support iSeries, but the company has been really good with us in the past; you might consider giving them a phone call) http://www.llblgen.com/defaultgeneric.aspx
• A not-very-promising SO post from last February: http://stackoverflow.com/questions/2276490/using-linq-with-ibm-i

I hope this helps.

Good luck!

Answer 3

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.

Answer 4

Well the simple answer for you is yes, you can do. How?

1. Download DB2 data server client 9.7 and install. Replace all your older clients.

2. You may have to rewrite your connection string to DB2. In C:\Program Files\IBM\SQLLIB\Bin\Testconn.exe will help you to create and test a connection string.

3. Make sure your IBM.Data.Informix.dll is in working condition.

4. Use VS2008. As of date VSAI for DB2 for VS2010 is not released yet.

5. Like any other database create ADO.NET Entity model - edmx file for your database.

6. Most likely with large number of tables in your database (which is most likely with DB2), you have to get EDMGEN2.exe from www.codeplex.com. You may have to place a text file called "Tables.txt" filled with tables, views, Proceduce names preceded with schema name. EdmGen2.exe will use this text file and create EDMX file for the specified tables only. Later you may add additional tables.

7. IBM has a support site where they tell the limitations of the functions supported in Linq in DB2 client 9.7 due to DB2 SQL limitations.

Answer 5

The solution I'm using on my current project is a "Linked Server" in Microsoft SQL Server which is linked via OleDB to the DB2 database server.

In my project's database I have created views that mirror the DB2 tables. Running sqlmetal.exe with /views option will then generate the mapping file for these as well.

Mind that you'll need to update the mapping manually in order to add information on primary keys and nullable fields.

It makes sense in my project to do it this way as I'm have to read/write database on both DB2 and SQL Server and using a Linked Server, I can do it all on the same ADO.NET connection, committing changes to both database simultaneously.