Documentation

EDI to DB

Article author
Admin
  • Updated

EDI Tools for .NET provides EDI templates that are compliant with Entity Framework 6 and are ready to be used as entity models.

A DBContext is provided for each X12 and EDIFACT transaction. To further improve the performance of reading\writing to the database, please refer to Entity Framework's patterns and guides. To automatically create databases from EDI templates - use Entity Framework Migrations.

edi-net-framework.png

 

Create a database with Migrations

Use the Entity Framework Migrations commands to create a new database. All HIPAA EDI templates are in a separate folder, one for each transaction. This allows only the tables required for that transaction to be created.

Due to the complexity of the transactions, it is recommended that the template and DB context are further modified and the unnecessary segments and elements are removed.

To create a fresh new database for HIPAA 5010 version, transaction 834, do the following steps:

  1. Create a new ConsoleApplication project and install Entity Framework 6 and EdiFabric from NuGet.
  2. Locate all files in the Entity Framework\834 folder and add them to the project

    mceclip0.png

  3. Use Entity Framework migrations to create the database by
    • Install Entity Framework from NuGet
    • Run the Enable-Migrations command in Package Manager Console
    • Run the Add-Migration command in Package Manager Console
    • Run the Update-Database command in Package Manager Console
  4. Ensure that a valid connection string exists in the App.config and the database name refers to the transaction (HIPAA_5010_834 by default):

    <connectionStrings>
    <add name="Hipaa5010837PConnectionString" connectionString="Data Source=.;Initial Catalog=HIPAA_5010_837P;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration> </connectionStrings> 

    The connection string is loaded in the DBContext like this:

    mceclip5.png

  5. The final DB structure looks like this:

    mceclip6.png

 

Create a database with Code First

Using the Code First functionality of Entity Framework, a new database will be created automatically the first time a transaction is saved.

To create a fresh new database for any X12 or EDIFACT version, do the following steps:

  1. Create a new ConsoleApplication project and install Entity Framework 6 and EdiFabric from NuGet.
  2. Add all EDI Templates from 4010 version, together with any shared files and the DbContext.cs file.
  3. Translate a sample X12 or EDIFACT purchase order

    Add the following code to Program.cs:

    using System.Collections.Generic;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using EdiFabric.Core.Model.Edi;
    using EdiFabric.Framework.Readers;
    using EdiFabric.Templates.X12004010;
    
    Stream edi = File.OpenRead(@"C:\\PurchaseOrder.txt");

    List<IEdiItem> ediItems;
    using (var reader = new X12Reader(edi))
    ediItems = reader.ReadToEnd().ToList();

    var purchaseOrders = ediItems.OfType<TS850>();

    using (var db = new X12Context()) { db.TS850.AddRange(purchaseOrders); db.SaveChanges(); }

    Change the path in File.OpenRead to the path of the sample file you'll be using.

  4. Edit the connection string in App.config to point to your desired SQL Server instance

    A default connection string would look like this:

    mceclip0.png

  5. Edit the DbContext.cs file

    Add the following to the DbContext file to use the supplied connection string and to (optionally) remove pluralizing the table names:

    public class X12Context : DbContext
    {
        public X12Context() : base("name=X12ConnectionString")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

    Also, add DbSets for the control segments if not present: 

    public DbSet<ST> ST { get; set; }
    public DbSet<SE> SE { get; set; }
  6. Run the code

    The first time this is executed, a new database with the name "X12_4010" (specified in the connection string) will be created.

 

EDI to DB Examples

 

Entity Framework Specifics

In order to work with Entity Framework, the EDI templates are modify in the following way:

  • Id property is included for every class
  • Complex properties are defined as virtual
  • All EDI templates implement an interface and inherit from a base class. This allows all the derived segment and complex element classes to be represented with a single Entity Framework entity (and a single database table) following the Table Per Hierarchy Inheritance concept

      /// <summary>
      /// Organization Summary Remittance
      /// </summary>
      [Serializable()]
      [DataContract()]
      [Segment("ENT")]
      public class ENT_OrganizationSummaryRemittance : ENT, I_ENT
    
  • All EDI Templates use List<string> to represent repeatable data elements (in both segments or complex elements). Entity Framework, however, doesn't work with collections of primitive types. To work around this limitation, all occurrences of List<string> in the templates have been amended in the following way:

    EQ segment defines a repeatable data element, ServiceTypeCode at position one:

      [Serializable()]
      [DataContract()]
      [Segment("EQ", typeof(X12_ID_1365_3), typeof(X12_ID_235_12))]
      public class EQ_DependentEligibilityorBenefitInquiry : 
    EQ, I_EQ<C003_CompositeMedicalProcedureIdentifier, C004_CompositeDiagnosisCodePointer> { /// <summary> /// Service Type Code /// </summary> [DataMember] [RequiredAny(2)] [ListCount(99)] [DataElement("1365", typeof(X12_ID_1365_3))] [Pos(1)] public virtual List<string> ServiceTypeCode_01 { get { return _serviceTypeCode; } set { _serviceTypeCode = value; } }

    The default getter and setter have been reworked to get\set a protected property from the base class, defined like this:

    [Serializable()]
    [DataContract()]
    public class EQ
    { [XmlIgnore] [IgnoreDataMember] public int Id { get; set; } [DataMember] public virtual string CoverageLevelCode_03 { get; set; } [DataMember] public virtual string InsuranceTypeCode_04 { get; set; }
    protected List<string> _serviceTypeCode { get; set; } public string ServiceTypeCode { get { if (_serviceTypeCode != null) return string.Join("^", _serviceTypeCode); return null; } set { if (!string.IsNullOrEmpty(value)) _serviceTypeCode = value.Split('^').ToList(); } } }
Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.