生成 LINQ to DB2？
我的工作中有一个现有的 DB2 数据库。 （至少，我认为它是 DB2。他们将其称为“iSeries”，它的外观和感觉就像某些大型机硬件上的 DB2。）多年来，我部门的 .NET 开发人员只是手动编写 ADO 命令和查询从特定表中获取特定数据等。
目前，我正在构建一些基础设施，以帮助简化内部软件的开发和支持，我想解决的一件事就是数据访问。我对各种 ORM 工具有点陌生，但我熟悉 LINQ 语法，这就是我想要了解的地方。这就是我们现在所拥有的：
- 一个包含许多表的已建立的 DB2 数据库。
- 代码中没有已建立的业务对象。
- 表和任何潜在业务对象之间没有有用的关系。
所以我想做的是在代码和 DB2 数据库之间创建一个抽象层，开发人员基本上可以更流畅、更高效地完成他们已经做的事情（获取数据并填充自定义对象）。也就是说，无需创建经典的 ADO 对象并填充 DataSet，只需编写一个简单的 LINQ 语句，该语句返回一个匿名 IQueryable，其中包含用于填充自定义对象的字段。 （作为一个额外的好处，我真的很喜欢当程序员搞砸一些东西时出现编译时错误而不是运行时错误，就像当拼写错误进入 SQL 命令字符串文字时发生的那样。另外，甚至不要理解我开始研究 SQL 注入漏洞。）
问题是，我还没有找到任何方法来完成这个任务。我完全有可能忽略了一些简单的事情，如果是这样的话，那么我欢迎在正确的方向上稍微推动一下。但就目前情况而言，我在网上找到的所有内容都符合以下类别之一：
- 是的，你可以做到这一点！您所需要的只是 IBM 提供的驱动程序，但没有下载链接！
- 这是一个方便的教程，用于为您的业务对象生成出色的 ORM，从而从头开始创建数据库。 （NHibernate，主要是，如果我可以用它来完成我想要做的事情，那么它会很好用。）
- 下载一些工具并生成您的 ORM。 （工具不支持 DB2，或者声称支持，但在我尝试时失败。）
以前有人遇到过类似的情况吗？我是否以完全错误的方式接近它？任何有关此事的建议将不胜感激，谢谢。
编辑：我要继续赏金这个。我和一些人交谈过，听起来“这还不能完成”仍然是目前的答案，但如果有人解决了这个问题并找到了解决方案，我很想听听。
更新：感谢下面的答案将我指向 DB_Linq 项目。实际上，我不需要花太多时间就可以添加一些基本的 DB2 支持，现在我有了一个经过测试且可以正常工作的 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:
- An established DB2 database with many tables.
- No established business objects in code.
- 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:
- Yes, you can do this! All you need is a driver from IBM that there's no download link for!
- 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.)
- 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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您不坚决反对进行一些编码:-) 获取 LINQ to MySql、Oracle 和 Postgress 并修改它可能也会为您节省很多工作。请记住，LINQ to SQL 仍然使用相同的 ADO.NET 连接。
请注意，LINQ to Entities 与 LINQ to SQL 不同，即使您使 IBM 库正常工作，您也必须首先检查是否允许您在现有数据库上使用它，而无需进行大量验证（它可以轻松绊倒并尝试修改现有数据库 - 如果您想深入研究该方面，请查看此线程：http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/f3a5b27a-5b73-476a-8c38-1eaedc8f197c）。
您还可以访问 IBM 的实体框架Beta 线程（从一些人声称工作结果的末尾开始）。
重要的是不要试图获得 MS SQL 所获得的一切 - 您只需要表表示类，并且您可以顺利进行第一轮。
您可以尝试的另一件事是打开到 DB2 的正常 ADO.NET 连接（或者如果发现非 ODBC 连接太窥探和敏感，则强制使用 ODBC）到 DB2，并尝试像 SQL Server 一样运行。如果事实证明 SqlMetal 同意使用该连接，您已经基本完成了 - 它将为您自动生成表表示类。
如果它犹豫不决，您还需要 MS SQL Server，至少出于开发目的。对于从 DB2 中取出一些表的启动脚本，在 SQL Server 中创建然后运行 SqlMetal 并查看源代码。您将看到它为表表示创建了漂亮的氏族类，并且只是在它们上放置了大量但直接的属性 - 这意味着易于复制和粘贴，甚至可以通过良好的脚本生成。一旦您看到一个小的自动生成文件的样子，您还会发现您可以将更多代码附加到提供的挂钩或删除一些现有代码。再次检查 LINQ to MySql 等。
LINQ 本身只需要表表示类，因此您可以相当自由地创建自己的 System.Data.Linq.DataContext 派生类，几乎可以满足您的愿望，并且我认为 DB2 中的架构几乎不会更改，因此您不需要更改太频繁了。一般来说，LINQ 是相当开放的系统（许多 LINQ to some libs）意味着如果修改 DataContext 派生还不够，您还可以接管整个 LINQ 表达式。
一旦您有了几个表的概念证明，如果 DB2-s 表创建脚本不能在 SQL Server 上完整运行（有总是有一些语法偏差）并且你确实采取了最简单的路线。
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.
当我运行大型 IBM DB2 系统并拥有 .net 开发人员时，这是我要做的事情清单上的事情。获取 Visual Studio 2008/2010 的驱动程序和插件并不容易。
ORM - 我知道（我读过）nHibernate 和 Entity Framework 都支持 DB2 SQL 语法。 nHibernate 的学习曲线较大，我们尚未决定是否要继续学习。但是实体框架（新的）看起来非常好，目前针对 SQL Server 2008 使用，但也请记住针对 DB2。
nhibernate 和实体框架都支持针对它们运行 LINQ，没有任何问题。
谢谢西蒙
This is on my list of things to do as I run a big IBM DB2 system and have .net developers. 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 running LINQ against them, without any issues.
Thanks
Simon
我不是 DB2 专家，但这里有一些您可能见过或没见过的想法。
我希望这会有所帮助。
祝你好运！
I'm not a DB2 man, but here's some ideas you may or may not have seen.
I hope this helps.
Good luck!
对你来说，简单的答案是肯定的，你可以做到。
如何？
下载 DB2 数据服务器客户端 9.7 并安装。替换所有旧客户端。
您可能必须重写 DB2 的连接字符串。在 C:\Program Files\IBM\SQLLIB\Bin\Testconn.exe 将帮助您创建和测试连接字符串。
确保您的 IBM.Data.Informix.dll 处于工作状态。
使用VS2008。截至目前，VS2010 的 VSAI for DB2 尚未发布。
像任何其他数据库一样，为您的数据库创建 ADO.NET 实体模型 - edmx 文件。
像
很可能您的数据库中有大量表（最有可能是 DB2），您必须从 www.codeplex.com 获取 EDMGEN2.exe。您可能必须放置一个名为“Tables.txt”的文本文件，其中包含表、视图、过程名称，前面带有模式名称。 EdmGen2.exe 将使用此文本文件并仅为指定的表创建 EDMX 文件。稍后您可以添加其他表。
IBM 有一个支持站点，他们在其中介绍了由于 DB2 SQL 限制而导致 DB2 客户端 9.7 中的 Linq 支持的功能的限制。
IBM 有一个支持站点，在
Well the simple answer for you is yes, you can do.
How?
Download DB2 data server client 9.7 and install. Replace all your older clients.
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.
Make sure your IBM.Data.Informix.dll is in working condition.
Use VS2008. As of date VSAI for DB2 for VS2010 is not released yet.
Like any other database create ADO.NET Entity model - edmx file for your database.
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.
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.
我在当前项目中使用的解决方案是 Microsoft SQL Server 中的“链接服务器”，它通过 OleDB 链接到 DB2 数据库服务器。
在我的项目数据库中，我创建了镜像 DB2 表的视图。使用 /views 选项运行 sqlmetal.exe 也会生成这些映射文件。
请注意，您需要手动更新映射才能添加主键和可为空字段的信息。
在我的项目中这样做是有意义的，因为我必须在 DB2 和 SQL Server 上读/写数据库并使用链接服务器，我可以在同一个 ADO.NET 连接上完成这一切，并对两者提交更改数据库同时进行。
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.
您可以使用轻量级 ORM，例如 dapper-dot-net。 http://code.google.com/p/dapper-dot-net/
You can use a light weight ORM such as dapper-dot-net. http://code.google.com/p/dapper-dot-net/