Data Management Global

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.

Prerequisites

A valid DB2 Connect license.

• Download, and install respective DS Driver i.e. 32-bit or 64-bit, and VSAI from this link
• DB2 EF6 Provider

Development Environment

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:

1. Providing filtering information, "Add/Modify Connection dialog" that shows up while establishing a connection via

- 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'
- Table type filter, by default logical and physical files are not selected assuming it's 'SQL access'

2. Customizing Code Generation to qualify a library name dynamically,

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: https://msdn.microsoft.com/en-US/data/dn753860

   - NuGet package link: https://www.nuget.org/packages/EntityFramework.CodeTemplates.CSharp/ after installation

      you'll see something similar in your project,

ii) Replace Context and EntityType templates under the CodeTemplates\EFModelFromDatabase\ by the ones in this attached file

Highlights of the custom code generation:

- Context.cs.t4

- EntityType.cs.t4

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.

3. Using 'chaos' for non-journaled objects,

We need to add the ‘key=value’ pair ‘IsolationLevel=chaos’ to ‘ourDbContext’ named connection string to be able to perform any DML operations otherwise we’d see the following exception thrown:

IBM.Data.DB2.DB2Exception: ERROR [55019] [IBM][AS] SQL7008NREXX variable "CUSTOVRD" contains inconsistent data.

            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:

References

- To Validate DB2 .NET Runtime and Tooling setup we may use testconn40 and testconn40_32  on 32-bit and 64-bit platforms respectively

  https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.adonet.doc/doc/r0059251.html

- To understand how we can customize code generation to meet our further needs:

  https://msdn.microsoft.com/en-us/data/gg558520.aspx (Basics of T4, and its applications in EF)

  https://msdn.microsoft.com/en-US/data/dn753860 (Custom code gen in EF 6 for 'Code First from DB')

  https://github.com/dotnet/ef6 (Design Details for 'Reverse Engineer to Code First')

- 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