Documentation

Search

EDI to DB

Article author
Admin

EDI Tools for .NET provides EDI templates that are compliant with Entity Framework (EF6 and EF Core) 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 6 Migrations or Entity Framework Core 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.

 

Entity Framework 6

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\EF6\834 folder and add them to the project

    mceclip0.png

  3. Use Entity Framework 6 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

 

Entity Framework Core

The supported versions for Entity Framework Core are 3.x, 5.x, and 6.x.

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

  1. Create a new ConsoleApplication project and install the following packages from nuget.org:
    • EdiFabric
    • Microsoft.EntityFrameworkCore
    • Microsoft.EntityFrameworkCore.Proxies
    • Microsoft.EntityFrameworkCore.SqlServer
    • Microsoft.EntityFrameworkCore.Tools

  2. Locate all files in the Entity Framework\EFCore\834 folder and add them to the project

    mceclip0.png

  3. Use Entity Framework Core Migrations to create the database by
    • Set the ConsoleApplication project to be the startup project
    • Run the Add-Migration InitialCreate command in Package Manager Console
    • Run the Update-Database command in Package Manager Console

  4. The connection string is in the _DB_Context file, change it if needed. It points to the local instance of SQL Server and the database name is HIPAA_5010_834.

  5. The final DB structure looks like this:

    mceclip6.png

 

Create a database with Code First (EF6 only)

Using the Code First functionality of Entity Framework 6, 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 EF6 Examples

 

EDI to DB EF Core Examples

 

Entity Framework 6 Specifics

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

  • An ID property is included for every class to act as the primary key for the underlying table.
  • All Complex properties are defined as virtual

  • All HIPAA 5010 EDI templates 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();
        }
    }
}

 

Entity Framework Core Specifics

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

  • All the changes required for Entity Framework 6.
  • All HIPAA 5010 EDI templates inherit from a base class, however, the base class must only contain common properties. All properties, apart from the ID, have been removed from the base classes.

  • Due to the previous, all List<string> properties have been moved to the derived classes:

      [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>
  {
          private List<string> _serviceTypeCode { get; set; }
      /// <summary>
      /// Service Type Code
      /// </summary>
          [NotMapped]
      [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; }
          }
          public virtual string ServiceTypeCode
      {
              get
              {
                 if (_serviceTypeCode != null)
                    return string.Join("^", _serviceTypeCode);
                 
                 return null;
               }
               set
               {
                 if (!string.IsNullOrEmpty(value))
                    _serviceTypeCode = value.Split('^').ToList();
               }
          }
     }

    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; }
}
  • Removed the override keyword from all the properties in the derived classes.

  • For .NET 6 - disable the Nullable project setting:

    net6-nullable.png

 

How to migrate the DB Context from EF6 to EF Core?

  • Follow the instructions in Porting an EF6 Code-Based Model to EF Core article.
  • Change System.Data.Entity namespace (and related sub-namespaces) to the Microsoft.EntityFrameworkCore namespace.
  • Remove the protected override void OnModelCreating(DbModelBuilder modelBuilder) method.
  • Remove the constructor.
  • Add protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) method.

Porting Example:

mceclip0.png

mceclip1.png

Share this:

Was this article helpful?

Comments

2 comments

  • Comment author
    Dariana Contreras

    Hi, we are using ediFabric with .NET Core, it's very useful, but we are having issues with EntityFrameworkCore, it seems it's not compatible with this version, it creates the databases through migrations, but it does'nt save the data correctly, it saves NULL in every  column, even though the template has the data correctly. 

    0
  • Comment author
    Admin
    • Edited

    Hi, please follow the instructions in the Entity Framework Core Specifics and How to migrate the DB context paragraphs to modify the templates. We also released the Entity Framework Core templates for HIPAA 5010

    0

Please sign in to leave a comment.