EF6 applications targeting DB2 for i (IBM i) file access i.e. physicals and logicals
Devendra_Bhadauria 2700053EXT Visits (8785)
In the modern world of writing Data Driven/Data intensive applications the real trade-off is between the developer's productivity and application's performance. Microsoft (MS) Entity Framework i.e. an Object Relational Mapper tries to strike a balance here by providing an abstraction layer for Rapid Application Development (RAD), while keeping tabs on efficiency. This modernization of applications is showing promise in developing new applications as well as migrating the legacy applications.
We've seen such traction in writing EF6 applications accessing DB2 for i Data Servers. DB2 for i supports SQL as well as file access to access, and manipulate its data. To have a high level of understanding, and analogy between SQL vs File access, please refer this link.
This blog primarily focuses on accessing physical, and logical files in an IBM i (DB2 for i) Data Server via EF6 applications.
A valid DB2 Connect license.
Tooling/Runtime: EF6 supporting VS versions >= VS 2012, this link further details it.
IBM Data Server: DB2 for i versions - 7.1, 7.2
Configuration and Customization
After setting up required Tooling/Runtime environment, follow the below steps:
- Server Explorer
- EDM Wizard
Use highlighted filtering options as enumeration of various DB objects may take time due to large number of objects in the database.
- Schema filter, it'd translate to the library name(s) in 'file access'
Microsoft EF requires each database object to be identified uniquely from which we've reverse engineered a conceptual object or an entity. We need to plant this library/schema information to uniquely identify a database object in *EDMX/.cs file(s). We can't even leave it blank or use *LIBL, and try using CurrentSchema's value at runtime as Microsoft EF would qualify a database object with its default 'dbo' schema and Server wouldn't recognize it as a valid schema name during SQL execution. To address the issue of static binding of a library name (used during model generation) to qualify all database objects while SQL generation phase we may resort to custom code generation as follows:
i) Install corresponding NuGet package to add custom code generation templates to your project,
- how to link: http
- NuGet package link: http
you'll see something similar in your project,
ii) Replace Context and EntityType templates under the Code
We need to rebuild our project so that custom code gen will take precedence over the default code gen. Now the reverse engineering process will produce Entities without Schema names, and the code in DbContext will read CurrentSchema value dynamically.
iii) Add CurrentSchema keyword to the connection string with its value of desired schema name, and now the SQL generation will default to this schema. We recommend using CurrentSchema keyword as the ADO .NET Provider understands it. It also conveys our intention clearly that we would like to use the specified schema name for SQL generation.
Note: We didn't put any Exception Handling in the code so that we can easily isolate the issues instead of going through redirection and nesting.
We need to add the ‘key=value’ pair ‘Iso
i) We need to to do this manual edit if the model generation is already done.
ii) We could use 'Advanced Properties' of 'Add/Modify Connection' dialog to set it up via
'VSAI' prior to the model generation:
- To Validate DB2 .NET Runtime and Tooling setup we may use testconn40 and testconn40_32 on 32-bit and 64-bit platforms respectively
- To understand how we can customize code generation to meet our further needs:
- To post queries about the issues faced while following the instructions given in this blog, please comment or use
.NET Development with DB2 and IDS forum