DataConnector
Entity Framework
ADO.NET provider for QuickBooks Online > Entity Framework

The ADO.NET Provider for QuickBooks Online supports Entity Framework which requires C1.EntityFrameworkCore.QuickBooksOnline package to be installed. In this section, we have used the model first approach to build an Entity Framework model that maps data tables to classes for simpler access to QuickBooks Online.

Note: Scaffolding feature supports user to create the model and dbcontext when you create model in the Entity Framework for all dataconnectors.

The following code defines the Attachables class to map the datatable.

C#
コードのコピー 
public partial class Attachables
{
    public string Id { get; set; }
    public string SyncToken { get; set; }
    public DateTime? MetaDataCreateTime { get; set; }
    public DateTime? MetaDataLastUpdatedTime { get; set; }
    public string FileName { get; set; }
    public int? Size { get; set; }
    public string ContentType { get; set; }
    public string Category { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public string Note { get; set; }
    public string PlaceName { get; set; }
    public string Tag { get; set; }
    public string AttachableRef { get; set; }
    public string FileAccessUri { get; set; }
    public string TempDownloadUri { get; set; }
}

The next code example defines the QuickBooksOnlineContext class to access the Attachables datatable and establish a connection to the QuickBooks Online. This is done by overriding the OnConfiguring method, which invokes the UseQuickBooksOnline method of DbContextOptionsBuilder object to configure the context and establish a connection to the QuickBooks Online service.

C#
コードのコピー 
public partial class QuickBooksOnlineContext : DbContext
{
    public QuickBooksOnlineContext()
    {
        Database.AutoTransactionsEnabled = false;
    }

    public QuickBooksOnlineContext(DbContextOptions<QuickBooksOnlineContext> options)
        : base(options)
    {
        Database.AutoTransactionsEnabled = false;
    }

    public virtual DbSet<Attachables> Attachables { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseQuickBooksOnline("Company Id = *******; OAuth Client
            Id=*************; OAuth Client Secret=***********; OAuth Refresh Token =
             ************; OAuth Access Token = **********; Use SandBox=true;");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Attachables>(entity =>
        {
            entity.Property(e => e.MetaDataCreateTime)
                .HasColumnName("MetaData_CreateTime")
                .ValueGeneratedOnAddOrUpdate();

            entity.Property(e => e.MetaDataLastUpdatedTime)
                .HasColumnName("MetaData_LastUpdatedTime")
                .ValueGeneratedOnAddOrUpdate();

            entity.Property(e => e.SyncToken).ValueGeneratedOnAddOrUpdate();
        });
    }
}

You can now use the LINQ queries to perform different data operations to the mapped classes as demonstrated in the code below.

Note: For LINQ queries, the code must have declared "using System.Linq".
INSERT
C#
コードのコピー 
public static void Insert(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Insert...");
    Attachables account = new Attachables
    {
        Note = "Test Note " + DateTime.Now.Ticks.ToString(),
        Category = "Other"
    };

    context.Attachables.Add(account);

    int result = context.SaveChanges();
    Console.WriteLine("Number row insert: " + result);
}
SELECT
C#
コードのコピー 
public static void Select(QuickBooksOnlineContext context)
{
    Console.WriteLine("Query all Attachables...");
    var records =
        from p in context.Attachables
        select p;

    foreach (var attachable in records)
    {
        Console.WriteLine("{0} - {1} - {2} - {3}",
                        attachable.FileName, attachable.Note, attachable.Category, attachable.ContentType);
    }
}
UPDATE
C#
コードのコピー 
public static void Update(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Update...");
    var attachables = context.Attachables.Where(x => x.Id == "5000000000000504413");

    foreach (var attachable in attachables)
    {
        attachable.Note = "Test Note " + DateTime.Now.Ticks.ToString();
        attachable.Category = "Signature";
    }

    int result = context.SaveChanges();

    Console.WriteLine("Number row update: " + result);
}
DELETE
C#
コードのコピー 
public static void Delete(QuickBooksOnlineContext context)
{
    Console.WriteLine("\nQuery Delete...");
    var attachables = context.Attachables.Where(x => x.Id == "5000000000000504413");

    foreach (var attachable in attachables)
    {
        context.Attachables.Remove(attachable);
    }

    int result = context.SaveChanges();

    Console.WriteLine("Number row delete: " + result);
}

 

View Tables

QuickBooks Online has some tables which do not have a primary key. For example, ExchangeRates table. EFCore considers such tables as View table where you can only perform the select operation.

C#
コードのコピー 
public partial class ExchangeRates
{
    public string SourceCurrencyCode { get; set; }
    public string TargetCurrencyCode { get; set; }
    public double Rate { get; set; }
    public DateTime? AsOfDate { get; set; }
    public DateTime? MetaDataLastUpdatedTime { get; set; }
}

Currently, EFCore 2.1 Scaffolding doesn’t support view table, therefore you have to manually add the View Table to DbContext as shown in the following code snippet.

C#
コードのコピー 
public virtual DbQuery<ExchangeRates> ExchangeRates { get; set; }

 

C#
コードのコピー 
modelBuilder.Query<ExchangeRates>(entity =>
{
    entity.Property(e => e.MetaDataLastUpdatedTime).HasColumnName("MetaData_LastUpdatedTime");
});

 

 

Copyright © MESCIUS, Inc. All rights reserved.