Techne

The data we use in the App is located in a SQLServer local database and we are using Entity Framework Core to manage the data access.

We will explain the arquitecture of the .NET Standard project Data included in the app. This project holds the data access logic of the app and it's a cross-platform library that can be shared for example with a Xamarin mobile app, a WPF desktop app, etc.

Let's explain Entity Framework Core first and then we will review how the data access has been implemented in detail.

Entity Framework Core

Entity Framework Core is a lightweight, extensible, and cross-platform version of the popular Entity Framework data access technology.

Entity Framework is an object-relational mapper (ORM) that allow us to work with a database using .Net objects. Also it supports many database engines.

To use Entity Framwork Core we need to install the corresponding Nuget package throuh the Package Manager Console:

• Install-Package Microsoft.EntityFrameworkCore
• Install-Package Microsoft.EntityFrameworkCore.Sqlite
• Install-Package Microsoft.EntityFrameworkCore.SqlServer

There are two ways to face a development with Entity Framework:

• Code first: Code first is when you are facing a development creating the Models before the database. With Entity Framework you can generate migration files that will reflect every change you do in your models and it will be applied in the database. This is the approach we are using in the App.

• Database first: In this case, you already have a database, and the changes you do in the data structure needs to be reflected in your Models code.

Models definitions

The database entities are represented in code through models. As we are implementing database first development, our model need to have the same fields that have been defined in the database. This is an example of one of the models we use in the app:

[Table("CountryCodes")]
public partial class CountryCode
{
    [MaxLength(2)]
    [Key]
    public string CountryCodeID { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; }
}

Let's explain now the Data Annotations attributes that we used to define our models.

Data Annotations

Data Annotations are used to represent certain restrictions that will be reflected in the database entities mapped with our models.

Data Annotations attributes are .NET attributes which can be applied on an entity class or properties to override default conventions in EF Core. These attributes are not only used in Entity Framework but they can also be used with ASP.NET MVC or data controls.

In the model defined above we can distinguish the folowing attirbutes:

• [Table("CountryCodes")]: Entity class attribute to configure the corresponding table name and schema in the database.
• [Key]: Property attribute to specify a key property in an entity and make the corresponding column a PrimaryKey column in the database.
• [MaxLenght(2)]: Property attribute to specify the maximum string length allowed in the corresponding column in the database.
• [Required]: Property attribute to specify that the corresponding column is a NotNull column in the database.

There's also available to use:

• Timestamp: Can be applied to a property to specify the data type of the corresponding column in the database as rowversion.
• ConcurrencyCheck: Can be applied to a property to specify that the corresponding column should be included in the optimistic concurrency check.
• MinLength: Can be applied to a property to specify the minimum string length allowed in the corresponding column in the database.
• StringLength: Can be applied to a property to specify the maximum string length allowed in the corresponding column in the database.
• Column: Can be applied to a property to configure the corresponding column name, order and data type in the database.
• ForeignKey: Can be applied to a property to mark it as a foreign key property.
• NotMapped: Can be applied to a property or entity class which should be excluded from the model and should not generate a corresponding column or table in the database.
• DatabaseGenerated: Can be applied to a property to configure how the underlying database should generate the value for the corresponding column e.g. identity, computed or none.
• InverseProperty: Can be applied to a property to specify the inverse of a navigation property that represents the other end of the same relationship.

DbContext

DbContext is an important class in Entity Framework Core. It represent the bridge between our entity models previously explained and the database.

The Airhandlers CASS App uses SQLServer as the default data source, but is prepared to work with the DbContext of your prefference. There's a different derived class of DbContext for each source of data. This is the implementation for SQLite:

public class SQLServerDb : DbContext, IDataSource
{
    private string _connectionString = null;

    public SQLServerDb(string connectionString)
    {
        // TODO: Remove default connection string
        connectionString = connectionString ?? "Data Source=Airhandlers.db";
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(_connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SubCategory>().HasKey(e => new { e.CategoryID, e.SubCategoryID });
        modelBuilder.Entity<OrderItem>().HasKey(e => new { e.OrderID, e.OrderLine });
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Project> Projects { get; set; }
    public DbSet<Units> Units { get; set; }

    public DbSet<Category> Categories { get; set; }
    public DbSet<SubCategory> SubCategories { get; set; }

    public DbSet<CountryCode> CountryCodes { get; set; }
    public DbSet<ProspectType> ProspectType { get; set; }
    public DbSet<Price> Prices { get; set; }
    public DbSet<ProjectStatus> ProjectStatus { get; set; }
    public DbSet<Term> Term { get; set; }
}

The principal methods of the DbContext class are:

Method	Usage
Entry	Gets an DbEntityEntry for the given entity. The entry provides access to change tracking information and operations for the entity.
SaveChanges	Executes INSERT, UPDATE and DELETE commands to the database for the entities with Added, Modified and Deleted state.
SaveChangesAsync	Asynchronous method of SaveChanges()
Set	Creates a DbSet that can be used to query and save instances of TEntity.
OnModelCreating	Override this method to further configure the model that was discovered by convention from the entity types exposed in DbSet properties on your derived context.

Lifetime

The lifetime of the context begins when the context is declared and finishes when the context is disposed or when is garbage-collected. Therefore, the correct way to use it is with a using clause to make sure that the context is disposed after operate with the database:

using (var context = new SQLiteDb())
{
    ...
}

DbSet

A DbSet property is exposed in the DbContext class as follows:

public DbSet<Customer> Customers { get; set; }

What is does to expose the table Customers in a way that we are allowed to query its data using LinQ, and execute Inserts, Updates and Deletes over the corresponding table.

Most important methods of DbSet are:

Method Name	Description
Add	Adds the given entity to the context with the Added state. When the changes are saved, the entities in the Added states are inserted into the database. After the changes are saved, the object state changes to Unchanged. Example: dbcontext.Customers.Add(customerEntity)
Include	Returns the included non-generic LINQ to Entities query against a DbContext. (Inherited from DbQuery) Example: var customerList = dbcontext.Customers.Include(s => s.Projects).ToList<Customer>();
Remove	Marks the given entity as Deleted. When the changes are saved, the entity is deleted from the database. The entity must exist in the context in some other state before this method is called. Example: dbcontext.Customers.Remove(customerEntity);

Just accesing a DbSet property from our context, it's not executing a query over the database. The query is executed when:

• It is enumerated by a foreach.
• It is enumerated by a collection operation such as ToArray, ToDictionary, or ToList.
• LINQ operators such as First or Any are specified in the outermost part of the query.
• The following methods are called: the Load extension method on a DbSet, DbEntityEntry.Reload, and Database.ExecuteSqlCommand.

Commit changes to database

The way Entity Framework works is through a ChangeTracker. When you work with an entity, you can modify it, or insert a new one or delete it, and these changes will be recorded in the ChangeTracker but the changes won't be pushed to the database until the method SaveChanges is called:

This is how to use it:

public async Task<int> UpdateCustomerAsync(Customer customer)
{
    if (customer.CustomerID > 0)
    {
        _dataSource.Entry(customer).State = EntityState.Modified;
    }
    else
    {
        customer.CustomerID = UIDGenerator.Next();
        customer.CreatedOn = DateTime.UtcNow;
        _dataSource.Entry(customer).State = EntityState.Added;
    }
    customer.LastModifiedOn = DateTime.UtcNow;
    customer.SearchTerms = customer.BuildSearchTerms();
    int res = await _dataSource.SaveChangesAsync();
    return res;
}

Other considerations

In order to use Entity Framework properly, there are a few things that a developer needs to take into account:

• Connections: By default, the context manages connections to the database. The context opens and closes connections as needed. For example, the context opens a connection to execute a query, and then closes the connection when all the result sets have been processed. There are cases when you want to have more control over when the connection opens and closes. For example, when working with SQL Server Compact, opening and closing the same connection is expensive. You can manage this process manually by using the Connection property.

• Multithreading: The context is not thread safe. You can still create a multithreaded application as long as an instance of the same entity class is not tracked by multiple contexts at the same time.

One of the most important parts of the application is how we access to the different Data Sources and how this logic is decoupled from the rest of the app.

We will review in detail the App.Data project and how we use it in the application.

App.Data project

Since our data source is a relational database, we are using Entity Framework Core to manipulate the data and expose it.

We can split the project in three principal parts:

Data

The Data folder contains all the data models, representing each table of the database:

These models will be our Data Transfer Objects or DTOs of our app.

IDataSource

This interface represents the database context. We will have a class implementing IDataSource for each context representing a database.

public interface IDataSource : IDisposable
{
    DbSet<CountryCode> CountryCodes { get; }
    DbSet<PaymentType> PaymentTypes { get; }
    DbSet<TaxType> TaxTypes { get; }
    DbSet<ProjectStatus> ProjectStatus { get; }
    DbSet<Shipper> Shippers { get; }

    DbSet<Customer> Customers { get; }
    DbSet<Project> Projects { get; }
    DbSet<Unit> Units { get; }
    DbSet<Product> Products { get; }

    EntityEntry<TEntity> Entry<TEntity>(TEntity entity) where TEntity : class;

    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken));
}

We can find two implementations of this interface: SQLiteDb and SQLServerDb, representing two different databases that will share the same logic to access, manipulate and expose the data.

IDataService

The interface IDataService is used for access and manipulate data from the database.

public interface IDataService : IDisposable
{
    Task<Customer> GetCustomerAsync(long id);
    Task<IList<Customer>> GetCustomersAsync(int skip, int take, DataRequest<Customer> request);
    Task<IList<Customer>> GetCustomerKeysAsync(int skip, int take, DataRequest<Customer> request);
    Task<int> GetCustomersCountAsync(DataRequest<Customer> request);
    Task<int> UpdateCustomerAsync(Customer customer);
    Task<int> DeleteCustomersAsync(params Customer[] customers);

    Task<Project> GetProjectAsync(long id);
    Task<IList<Project>> GetProjectsAsync(int skip, int take, DataRequest<Project> request);
    Task<IList<Project>> GetProjectKeysAsync(int skip, int take, DataRequest<Project> request);
    Task<int> GetProjectsCountAsync(DataRequest<Project> request);
    Task<int> UpdateProjectAsync(Project Project);
    Task<int> DeleteProjectsAsync(params Project[] Projects);

    Task<ProjectItem> GetProjectItemAsync(long ProjectID, int ProjectLine);
    Task<IList<ProjectItem>> GetUnitsAsync(int skip, int take, DataRequest<ProjectItem> request);
    Task<IList<ProjectItem>> GetProjectItemKeysAsync(int skip, int take, DataRequest<ProjectItem> request);
    Task<int> GetUnitsCountAsync(DataRequest<ProjectItem> request);
    Task<int> UpdateProjectItemAsync(ProjectItem ProjectItem);
    Task<int> DeleteUnitsAsync(params ProjectItem[] Units);

    Task<Product> GetProductAsync(string id);
    Task<IList<Product>> GetProductsAsync(int skip, int take, DataRequest<Product> request);
    Task<IList<Product>> GetProductKeysAsync(int skip, int take, DataRequest<Product> request);
    Task<int> GetProductsCountAsync(DataRequest<Product> request);
    Task<int> UpdateProductAsync(Product product);
    Task<int> DeleteProductsAsync(params Product[] products);


    Task<IList<Category>> GetCategoriesAsync();
    Task<IList<CountryCode>> GetCountryCodesAsync();
    Task<IList<ProjectStatus>> GetProjectStatusAsync();
    Task<IList<PaymentType>> GetPaymentTypesAsync();
    Task<IList<Shipper>> GetShippersAsync();
    Task<IList<TaxType>> GetTaxTypesAsync();
}

Accessing the data from the app

Data Service Factory

The first thing we have to define in the app, it's the source of the data we are going to consume. The interface IDataServiceFactory is the one responsable of provide the inplementation of IDataService that we use in the app.

public interface IDataServiceFactory
{
    IDataService CreateDataService();
}

The possible data services to be used in the app are: SQLite and SQLServer, and they are defined in the following enum class:

public enum DataProviderType
{
    SQLite,
    SQLServer
}

To establish the Data Service to use, we just need to set the property DataProvider of the AppSettings class. By default, we are loading the SQLite data provider:

public DataProviderType DataProvider
{
    get => (DataProviderType)GetSettingsValue("DataProvider", (int)DataProviderType.SQLite);
    set => LocalSettings.Values["DataProvider"] = (int)value;
}

With the data provider defined, we can review now how we are accessing to the data from our ViewModels.

Data Services

We have additional services, one per functionality of the app located in the Services folder inside the App.ViewModels project:

Let's have a look a one of them:

public interface ICustomerService
{
    Task<CustomerModel> GetCustomerAsync(long id);
    Task<IList<CustomerModel>> GetCustomersAsync(DataRequest<Customer> request);
    Task<IList<CustomerModel>> GetCustomersAsync(int skip, int take, DataRequest<Customer> request);
    Task<int> GetCustomersCountAsync(DataRequest<Customer> request);

    Task<int> UpdateCustomerAsync(CustomerModel model);

    Task<int> DeleteCustomerAsync(CustomerModel model);
    Task<int> DeleteCustomerRangeAsync(int index, int length, DataRequest<Customer> request);
}

This contract is offering us all the possible operations that we can do when we work with Customers.

Also, these services are not only accessing the data source, they are also acting as mappers between our DTOs and the Models that we used to represent the data visually. There are some advantajes in mapping our DTOs to Models:

• Not all the info of the DTO needs to be displayed in our views.
• DTOs should be normally simple classes with no behaviour defined in Project to facilitate serialization.
• The Models are the ones implementing the interface INotifyPropertyChanged, reducing complexity in our DTOs.

Let's check the inplementation of the ICustomerService:

public class CustomerService : ICustomerService
{
    public CustomerService(IDataServiceFactory dataServiceFactory)
    {
        DataServiceFactory = dataServiceFactory;
    }

    public IDataServiceFactory DataServiceFactory { get; }

    public async Task<CustomerModel> GetCustomerAsync(long id)
    {
        using (var dataService = DataServiceFactory.CreateDataService())
        {
            var item = await dataService.GetCustomerAsync(id);
            if (item != null)
            {
                return await CreateCustomerModelAsync(item, includeAllFields: true);
            }
            return null;
        }
    }
}

As you can see, we are using the IDataServiceFactory to get the IDataService interface to consult a customer information.

Finally, we just need to check how we are injecting this ICustomerService in the CustomerDetailViewModel class to check how all the pieces are connected:

public class CustomerDetailsViewModel : GenericDetailsViewModel<CustomerModel>
{
    public CustomerDetailsViewModel(ICustomerService customerService, ICommonServices commonServices) : base(commonServices)
    {
        CustomerService = customerService;
    }

    public ICustomerService CustomerService { get; }
}