Custom SQL with C#Bot

Developers can add custom SQL queries into C#Bot by utilising either the CrudService or the database context on the server-side.

In a C#Bot project the CrudService is a wrapper for the database context and manages the security and auditing of database operations. If these features are not needed then the database context can be used directly.

For this project, we will be using the ‘ZooSharp’ Project. You can follow along with the project by downloading the latest version from the public git page.

Task

User Story : As a Zoo Keeper I want to flag when a Tank enclosure is at capacity so that I can properly manage the locations of the animals.

For this requirement we will need to create a custom query to find all the instances where Tank enclosures are at capacity. The way this is done in Entity Framework is by adding conditions onto an IQueryable object, which acts as a delegate to fetch items from the database. This IQueryable can be fetched in one of 2 different ways, via the CRUD Service or via the database context. If the CRUD Service is used then the security rules from the security model will be applied to the resulting IQueryable.

All custom logic that exists inside of C#Bot should be placed into a service. These services can be resolved by a controller to return data to a user. The paradigm for C#Bot is to create services in serverside/src/Services. For this example we are going to make a class there called EnclosureManagementService.cs. The next thing that needs to be done is to register the service in the dependency injection graph.

Method 1(A): Using the CRUD Service

1. In the service we just created, populate it with the following contents.

    using System.Linq;
    using Zoo.Enums;
    using Zoo.Models;
   	
    namespace Zoo.Services
    {
        public class EnclosureManagementService
        {
            private readonly CrudService _crudService;
   	
            public EnclosureManagementService(CrudService crudService)
            {
                _crudService = crudService;
            }
   	
            public IQueryable<EnclosureEntity> GetFullTankEnclosures()
            {
                return _crudService.Get<EnclosureEntity>()
                    .Where(e => e.Capacity == true)
                    .Where(e => e.EnclosureType == AnimalEnclosureType.TANK);
            }
        }
    }
   

2. Add the following line into Startup.cs in the protected region labeled Add extra startup methods here.

    // % protected region % [Add extra startup methods here] on begin
    services.AddScoped<EnclosureManagementService>();
    // % protected region % [Add extra startup methods here] end
   

This will fetch the enclosures that the current user has access to get, filtering out any enclosures that are not tanks and are not at full capacity. Finally, it will return an IQueryable which, when converted to a list, will execute a query on the database.

Method 1(B): Using the Database Context

Sometimes we don’t want to run an operation within the constraints of the CRUD Service and instead want direct access to the database. To do so we can write a function that contacts the database directly.

1. First we need to add the database context as a dependency in the constructor.

    private readonly CrudService _crudService;
    private readonly ZooDBContext _dbContext;
   	
    public EnclosureManagementService(CrudService crudService, ZooDBContext dbContext)
    {
        _crudService = crudService;
        _dbContext = dbContext;
    }
   

2. Now we have a dependency on the database context we can write a function in the class that will fetch the data for us.

    public IQueryable<EnclosureEntity> GetFullTankEnclosuresDbContext()
    {
        return _dbContext.EnclosureEntity
            .Where(e => e.Capacity == true)
            .Where(e => e.EnclosureType == AnimalEnclosureType.TANK);
    }
   

This will fetch the data out and return an IQueryable the same as the CRUD Service.

Method 2: Using Raw SQL

Sometimes there are features that need to be implemented that are impossible to do in the context of Linq and instead need to be done using raw SQL. This method is only recommended if there is no way to perform the query in Linq itself.

public async Task<IList<Enclosure>> GetFullTankEnclosuresSql()
{
    const int tankValue = (int) AnimalEnclosureType.TANK;
    return await _dbContext
        .Enclosure
        .FromSql($@"
            SELECT *
            FROM Enclosure
            WHERE EnclosureType = {tankValue}
                AND Capacity = 'true'
        ")
        .ToListAsync();
}

In this case we convert the result to a list before we return the value. Performing IQueryable operations on query that is composed from raw SQL is unadvised by Entity Framework.

Solution

• Codebots Zoo

Related articles

• Custom SQL Scripts with C#Bot
• C#Bot: Custom Scheduled Tasks