# Entity Framework

## Content

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.

>type=note
> **Note**: The [Scaffolding](/componentone/docs/services/online-dataconnector/scaffolding) feature supports the user to create a model and dbcontext when you create a model in the Entity Framework for all dataconnectors.

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

```csharp
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 QuickBooks Online. This is done by overriding the **OnConfiguring** method, which invokes the **UseQuickBooksOnline** method of the **DbContextOptionsBuilder** object to configure the context and establish a connection to the QuickBooks Online service.

```csharp
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.

>type=note
> **Note**: For LINQ queries, the code must have declared "using System.Linq".

**INSERT**

```csharp
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**

```csharp
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**

```csharp
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**

```csharp
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, the **ExchangeRates** table. EFCore considers such tables as the View table where you can only perform the select operation.

```csharp
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 tables, therefore you have to manually add the View Table to DbContext as shown in the following code snippet.

```csharp
public virtual DbQuery<ExchangeRates> ExchangeRates { get; set; }
```

```csharp
modelBuilder.Query<ExchangeRates>(entity =>
{
    entity.Property(e => e.MetaDataLastUpdatedTime).HasColumnName("MetaData_LastUpdatedTime");
});
```