This tutorial guides you through the process of creating a simple application powered by ADO.NET Entity Framework. In less than 5 minutes you will have a ready-to-use data access layer for your business objects.

Please note that this tutorial is not applicable for Entity Framework Core. It is intended for previous Entity Framework versions.

In this walkthrough:

• Introducing the ADO.NET Entity Framework
• Requirements
• Entity Framework v6
• Generating Model from Database
• Querying Data
• Inserting New Data
• Updating Data
• Deleting Data
• Additional Information

Introducing the ADO.NET Entity Framework

ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. It is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.

Requirements

In order to connect to QuickBooks Online server you need the corresponding QuickBooks Online connection parameters, dotConnect for QuickBooks Online installed and IDE running.

For Entity Framework 6, you will also need a NuGet Visual Studio extension installed since it is used for adding EntityFramework NuGet package. Alternatively you may create model for Entity Framework v4, which don't require NuGet, in this tutorial.

In this sample we will create a simple console application. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial. Start Visual Studio and create a new console application.

Entity Framework v6

The following actions are required if you want to create an Entity Framework v6 model.

Open the Package Manager Console window and execute the following command in it.

install-package EntityFramework

After this add the following line:

<provider invariantName="Devart.Data.QuickBooks" type="Devart.Data.QuickBooks.Entity.QuickBooksEntityProviderServices, 
Devart.Data.QuickBooks.Entity.EF6, Version=1.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />

to the entityFramework -> providers section.

<entityFramework>
    <providers>
      <provider invariantName="Devart.Data.QuickBooks" type="Devart.Data.QuickBooks.Entity.QuickBooksEntityProviderServices, 
      Devart.Data.QuickBooks.Entity.EF6, Version=1.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
</entityFramework>

Note: replace 1.0.0.0 with the actual assembly version.

After this you need to rebuild the project before running the EDM wizard.

Generating Model from Database

1. In the Solution Explorer right-click on the project and choose Add | New Item.
2. In the dialog select ADO.NET Entity Data Model, click Add. This launches Entity Data Model Wizard.
3. In the wizard select Generate from database, click Next.
4. Pick an existing dotConnect for QuickBooks Online connection string or create a new one. When creating a new connection select QuickBooks Online Data Source in the Data Source list, then dotConnect for QuickBooks Online in the Data provider list. See the screenshot below.
   
5. Agree to include the sensitive data in the connection string.
6. In the Save entity connection settings... box type QuickBooksEntities. This will be the name of the main data access class. Click Next.
7. Select database objects that will be used in the model. Enter QuickBooksModel in the Model Namespace box.
   
8. Click Finish. The model will be generated and opened in EDM Designer.
   

The model you've just generated is ready to use. Its name is Model1.edmx, unless you changed it in the step 2. You can inspect it visually in the designer or take a look behind the scenes with XML Editor.

The wizard creates classes for all selected tables that represent entities. It also creates a descendant of System.Data.Objects.DbContext class, which controls the connection to the database, and the whole data flow. This class includes properties and methods named after your database objects. You will use these members to retrieve and modify data in the context. The code is contained in an autogenerated file Model1.Designer.cs (Model1.Designer.vb).

Note that if you have an association between two properties of non-coinciding numeric types, you can manually change both conceptual and storage types to the type that will be wide enough to include data for each property.

Querying Data

All Entity Framework operations are executed through a DbContext descendant (default since Visual Studio 2012) or through a ObjectContext descendant. In our tutorial, it's a DbContext descendant, which is named QuickBooksEntities. To retrieve data you have to first create an instance of the context, then prepare a query with LINQ to Entities or EntitySQL or their mix, and then access the object returned by the query, which may be a collection of objects or a single object.

Let's try performing a LINQ to Entities query against our model. Add the following block of code to the method Main:

    QuickBooksEntities context = new QuickBooksEntities();
    var query = from it in context.Term
                orderby it.Name
                select it;

    foreach (Term t in query)
        Console.WriteLine("{0} | {1} | {2}", t.Name, t.DueDays, t.Type);

    Console.ReadLine();

    Dim context As New QuickBooksEntities
    Dim query = From it In context.Term
        Order By it.Name
        Select it

    Dim t As Term
    For Each t In query
        Console.WriteLine("{0} | {1} | {2}", t.Name, t.DueDays, t.Type)
    Next

    Console.ReadLine()

As simple as that. You prepare a query and then iterate through it as you would do with a usual collection of objects. The database interaction is performed by Entity Framework in the background. Now let's see who is who in this code sample.

• QuickBooksEntities is the name of the class that knows all about your model and does everything to handle it. You named it in the step 6. All Entity Framework operations are performed within this class's properties and methods. It is recommended that you keep a single instance of the class throughout your application because it consumes lots of resources.
• query, it - these are arbitrary variable names in the LINQ to Entities statement. The former is used as the collection of data objects, the latter is not used outside the statement.
• context.ENTITY refers to a public property of QuickBooksEntities class. This property represents the collection of the corresponding objects in the context.

Here is the project's output in the console:

Note that the LINQ to Entities query code just describes the query. It does not execute it. This approach is known as deferred execution.

Now let's query data from two objects united with a relation. Replace the old code with this:

   // Add references to the following assemblies in your project:
   // Devart.Data.dll,  Devart.Data.SqlShim.dll, Devart.Data.QuickBooks.dll, Devart.Data.QuickBooks.Entity.EF6.dll
   var monitor = new Devart.Data.QuickBooks.QuickBooksMonitor() { IsActive = true };

    QuickBooksEntities context = new QuickBooksEntities();
    var query = from it in context.Term.Include("Customer")
                orderby it.Name
                select it;

    foreach (Term term in query)
        Console.WriteLine("{0} | {1} | {2}",
            term.Name, term.DueDays,
            term.Customer.FirstOrDefault() == null ? "null" : term.Customer.FirstOrDefault().DisplayName
            );

    Console.ReadLine();

   ' Add the references to the following assemblies in your project:
   ' Devart.Data.dll, Devart.Data.SqlShim.dll, Devart.Data.QuickBooks.dll, Devart.Data.QuickBooks.Entity.EF6.dll
   Dim monitor As New Devart.Data.QuickBooks.QuickBooksMonitor()
   monitor.IsActive = True

    Dim context As New QuickBooksEntities
    Dim query = From it In context.Term.Include("Customer")
        Order By it.Name
        Select it

    For Each term As Term In query
        Console.WriteLine("{0} | {1} | {2}", term.Name, term.DueDays,
                            If(term.Customer.FirstOrDefault() Is Nothing, "null", term.Customer.FirstOrDefault().DisplayName))
    Next

    Console.ReadLine()

This sample is much like the previous one, with exception that it adds the Include method that instructs the query to retrieve data from one more object.

Inserting New Data

What earlier was adding rows to tables, now is just adding new objects to context collections. When you are ready to send the changes to the database, call the SaveChanges() method of the context. Before doing this, you must first set all properties that do not support null (Nothing) values. The SaveChanges() method generates and executes commands that perform the equivalent INSERT, UPDATE, or DELETE statements against the data source.

By Microsoft design, DbContext.SaveChanges() should work within a distributed transaction, but it is not supported by API of QuickBooks Online. That's why SaveChanges() will submit all DML operations (till the failed one, if any). This code snippet includes enabled QuickBooksMonitor for tracing the SQL statements sent to data source.

In this tutorial we will create new Customer and Term objects. For the sake of this tutorial, we need to set the StoreGeneratedPattern property to Computed for the following entity properties in the model in order to allow QuickBooks Online to generate values for these fields:

• Term.Active
• Customer.Active
• Customer.Taxable

Let's add new objects to the data source. Replace the old code with this:

    // Add references to the following assemblies in your project:
    // Devart.Data.dll,  Devart.Data.SqlShim.dll, Devart.Data.QuickBooks.dll, Devart.Data.QuickBooks.EF6.dll
    var monitor = new Devart.Data.QuickBooks.QuickBooksMonitor() { IsActive = true };

    QuickBooksEntities context = new QuickBooksEntities();

    // Create a new customer
    var c = new Customer();
    c.Title = "new customer";
    c.DisplayName = "new customer";

    context.Customer.Add(c);

    // Create a term
    var t = new Term();
    t.Name = "new term";
    t.DueDays = 10;

    // Associate the customer with the term
    t.Customer.Add(c);
    context.Term.Add(t);

    // Send the changes to the database.
    // Until you do it, the changes are cached on the client side.
    context.SaveChanges();

    // Request the new term from the database
    var query = from i in context.Term.Include("Customer")
                where i.Name == "new term"
                select i;

    // Since we query for a single object instead of a collection, we can use the method First()
    Term term = query.First();
    Console.WriteLine("{0} | {1} ",
        term.Customer.First().DisplayName, term.Name);
    Console.ReadLine();

    ' Add the references to the following assemblies in your project:
    ' Devart.Data.dll, Devart.Data.SqlShim.dll, Devart.Data.QuickBooks.dll, Devart.Data.QuickBooks.EF6.dll
    Dim monitor As New Devart.Data.QuickBooks.QuickBooksMonitor()
    monitor.IsActive = True

    Dim context As New QuickBooksEntities

    ' Create a new customer
    Dim c = New Customer()
    c.Title = "new customer"
    c.DisplayName = "new customer"

    context.Customer.Add(c)

    ' Create a term
    Dim t = New Term()
    t.Name = "new term"
    t.DueDays = 10

    ' Associate the customer with the term
    t.Customer.Add(c)
    context.Term.Add(t)

    ' Send the changes to the database.
    ' Until you do it, the changes are cached on the client side.
    context.SaveChanges()

    ' Request the new term from the database
    Dim query = From i In context.Term.Include("Customer")
                Where i.Name = "new term"
                Select i

    ' Since we query for a single object instead of a collection, we can use the method First()
    Dim term = query.First()
    Console.WriteLine("{0} | {1} ",
        term.Customer.First().DisplayName, term.Name)
    Console.ReadLine()

The Add methods and others are automatically generated in the context. Such methods exist for every entity in your model.

Updating Data

Entity instances are modified as usual. The only thing to remember is that you have to invoke the SaveChanges() method to send the data to the database.

Append the following block to the existing code and launch the project:

    term.Name = "edited name";
    context.SaveChanges();

    term.Name = "edited name"
    context.SaveChanges()

Deleting Data

To extract an instance from a context use the DeleteObject method of the context. The object is removed from the collection of its type, but not destroyed. To delete the object's data from the database invoke the SaveChanges() method.

You can do this with a block of code like the following:

    context.Term.Remove(term);
    context.Customer.Remove(customer);
    context.SaveChanges();

    context.Term.Remove(term)
    context.Customer.Remove(customer)
    context.SaveChanges()

Additional Information

Now that you can perform the basic data manipulation with Entity Framework, you can move on to some advanced topics.

We recommend you to use Entity Developer (Devart Entity Model, *.edml) instead of EDM Designer (ADO.NET Entity Data Model, *.edmx) because it is adjusted for working with QuickBooks Online and offers advanced functionality. Additionally, Entity Developer registers Entity Framework v6 providers in app.config automatically and offers advanced visual designer and support for Database First / Model First approaches for EF Core.

Here are some useful links to MSDN:

• Entity Framework Core
• Entity Framework 6
• Entity Framework 5

For hands-on experience download the separate Entity Framework Query Samples (EF1/EF4/EF5/EF6) package or use samples shipped with dotConnect for QuickBooks Online. You can access the samples from the Start menu.

To understand deeper the works of Entity Framework engine you can watch the generated SQL statements in dbMonitor.

See Also

Entity Framework section  | Entity Framework Support Overview