The objective of our project is to develop a simple yet versatile inventory system. Our aim is to create a generic inventory system that allows for item categorization and the addition of custom attributes. This flexibility will enable users to organize their inventory based on categories and further customize their items using attributes. Additionally, categories can be nested within each other, providing a hierarchical structure and the ability to use categories as "tags" in specific scenarios.
To achieve this, we will focus on two main models: Category and Product, although the system can accommodate additional models if needed. Our goal is to design a unified repository class that can handle interactions with the database, minimizing code duplication and maximizing the reusability of SQL queries.
To simplify the interaction with the database and streamline SQL operations, we will leverage the capabilities of the Dapper micro-ORM. Dapper offers convenient CRUD (Create, Read, Update, Delete) functionalities and syntactic sugar for executing SQL queries. By utilizing Dapper, we can enhance our productivity and maintain a clean and efficient codebase.
By developing a flexible inventory system that incorporates categories, custom attributes, and reusable code, we can provide users with a powerful tool for managing their inventory in a structured and customizable manner. Our solution will empower users to organize, search, and retrieve items efficiently, simplifying the complexities associated with inventory management.
Inventory System with Dapper, Categories, and Custom Attributes
To create a versatile inventory system with Dapper, Categories, and Custome Attributes, we will start by designing the data model in C#. We aim to develop a generic system that can handle categorization and custom attributes for items. By implementing a unified table structure and utilizing efficient practices, we can build an effective and adaptable system.
First, we will define a base class called Instance that will serve as the common subset of information for all entities in our system. This base class will provide essential properties and relationships. Here is the initial implementation:
public abstract record class Instance
{
[Key]
public int InstanceId { get; set; }
public string Name { get; set; }
public string Description { get; set; } = "";
public bool SystemOwned { get; set; } = false;
[Computed]
public CategoryCollection Categories { get; set; } = new CategoryCollection();
[Computed]
public DateTime CreatedTimestamp { get; set; }
[Computed]
public CustomAttributes CustomAttributes { get; set; } = new CustomAttributes();
}
The Instance model contains the following properties:
InstanceId: An identifier for the entity.
Name: A unique name for the entity.
Description: An optional description of the entity.
SystemOwned: A flag indicating whether the entity is owned by our code or defined by the user (useful for building modules on top of the structure).
Categories: The associated categories for the instance.
CreatedTimestamp: The timestamp when the entity was first created.
CustomAttributes: Any custom attributes associated with this instance.
In addition, we use attributes specific to Dapper: [Key] signifies the primary key, and [Computed] indicates properties that should not be included in updates (using Dapper.Contrib).
Next, we define the CategoryCollection class, which represents a collection of categories associated with an instance:
public class CategoryCollection : List<CategoryCollectionEntry>
{
public CategoryCollection() : base() { }
public CategoryCollection(IEnumerable<CategoryCollectionEntry> entries) : base(entries) { }
public CategoryCollection(IEnumerable<int> categoryIds) : base(categoryIds.Select(i => new CategoryCollectionEntry { CategoryId = i }).ToArray()) { }
public CategoryCollection(params int[] categoryIds) : base(categoryIds.Select(i => new CategoryCollectionEntry { CategoryId = i }).ToArray()) { }
}
public class CategoryCollectionEntry
{
public string Category { get; set; }
public int CategoryId { get; set; }
}
The CategoryCollection class extends List<CategoryCollectionEntry> and provides constructors to conveniently initialize the collection with entries or category IDs.
Similarly, we define the CustomAttributes class, which extends Dictionary<string, string> to hold custom attributes associated with an instance:
public class CustomAttributes : Dictionary<string, string>
{
public CustomAttributes() : base() { }
public CustomAttributes(IDictionary<string, string> values) : base(values) { }
}Now that we have the base model in place, we can create specific data models for our inventory system.
Category Instance:
A category acts as a "tag" for any entity in our system and can have its own associated categories. This hierarchical structure provides flexibility in organizing the inventory. For example, we can have different types of products with separate instances of the Category model. Let's define the CategoryInstance model:
public record class CategoryInstance : Instance
{ }Product Instance:
A product is an entity that can be inventoried and tracked. In addition to having associated categories and custom attributes, we will introduce two properties specific to products: ProductImageUris and ValidSkus. These properties are used for specific customer cases in our system. Here is the ProductInstance model definition:
public record class ProductInstance : Instance
{
public StringCollection ProductImageUris { get; set; } = new StringCollection();
public StringCollection ValidSkus { get; set; } = new StringCollection();
}The ProductInstance model includes two additional properties: ProductImageUris and ValidSkus. These properties are collections of strings and are implemented using the StringCollection class.
The StringCollection class is a simple wrapper around List<string> that adds clarity and expresses the intent of the collection. Here is its definition:
public class StringCollection : List<string>
{
public StringCollection() : base() { }
public StringCollection(IEnumerable<string> items) : base(items) { }
public StringCollection(params string[] items) : base(items) { }
}By designing the data model with these classes, we create a foundation for our flexible inventory system. The models accommodate categories, custom attributes, and specific properties for products. With these structures in place, we can proceed to implement the functionality and utilize the power of Dapper to simplify our SQL interactions.
Table Structure and Design
Now that we have our models, let’s create our tables. Each entity will need a set of three tables: Instance Table, Category Table, and Attributes Table. The core structure of these tables is virtually the same.
Category Instance
Table: Instances
CREATE TABLE [Instances].[Categories]
(
[InstanceId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Name] VARCHAR(64) NOT NULL,
[Description] VARCHAR(256) NOT NULL,
[SystemOwned] BIT NOT NULL DEFAULT 0,
[CreatedTimestamp] DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
)
GO
CREATE UNIQUE INDEX [UQ_Categories_Name] ON [Instances].[Categories] ([Name])Table: Category
CREATE TABLE [Instances].[CategoryCategories]
(
[InstanceId] INT NOT NULL,
[CategoryInstanceId] INT NOT NULL,
CONSTRAINT [PK_CategoryCategories]
PRIMARY KEY ([InstanceId], [CategoryInstanceId]),
CONSTRAINT [FK_CategoryCategories_Categories]
FOREIGN KEY ([InstanceId])
REFERENCES [Instances].[Categories]([InstanceId]) ON DELETE CASCADE,
CONSTRAINT [FK_CategoryCategories_Categories_Categories]
FOREIGN KEY ([CategoryInstanceId])
REFERENCES [Instances].[Categories]([InstanceId])
)Table: Attribute
CREATE TABLE [Instances].[CategoryAttributes]
(
[InstanceId] INT NOT NULL,
[Key] VARCHAR(64) NOT NULL,
[Value] VARCHAR(4096) NOT NULL,
CONSTRAINT [PK_CategoryAttributes]
PRIMARY KEY ([InstanceId], [Key]),
CONSTRAINT [FK_CategoryAttributes_Categories]
FOREIGN KEY ([InstanceId])
REFERENCES [Instances].[Categories]([InstanceId]) ON DELETE CASCADE
)Attribute Table
The last two tables are a standard set of link tables where each category can have any number of other categories. And each category can have any number of custom attributes. The primary keys are utilized to provide uniqueness on the link tables.
Product Instance
CREATE TABLE [Instances].[Products]
(
[InstanceId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Name] VARCHAR(256) NOT NULL,
[Description] VARCHAR(256) NOT NULL,
[ProductImageUris] VARCHAR(MAX) NOT NULL,
[ValidSkus] VARCHAR(MAX) NOT NULL,
[SystemOwned] BIT NOT NULL DEFAULT 0,
[CreatedTimestamp] DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
)
GO
CREATE INDEX [IX_Products_Name] ON [Instances].[Products] ([Name] ASC, [InstanceId] ASC)CREATE TABLE [Instances].[ProductCategories]
(
[InstanceId] INT NOT NULL,
[CategoryInstanceId] INT NOT NULL,
CONSTRAINT [PK_ProductCategories]
PRIMARY KEY ([InstanceId], [CategoryInstanceId]),
CONSTRAINT [FK_ProductCategories_Products]
FOREIGN KEY ([InstanceId])
REFERENCES [Instances].[Products]([InstanceId]) ON DELETE CASCADE,
CONSTRAINT [FK_ProductCategories_Categories]
FOREIGN KEY ([CategoryInstanceId])
REFERENCES [Instances].[Categories]([InstanceId]) ON DELETE CASCADE
)CREATETABLE [Instances].[ProductAttributes]
(
[InstanceId] INT NOT NULL,
[Key] VARCHAR(64) NOT NULL,
[Value] VARCHAR(4096) NOT NULL,
CONSTRAINT [PK_ProductAttributes]
PRIMARYKEY ([InstanceId], [Key]),
CONSTRAINT [FK_ProductAttributes_Products]
FOREIGN KEY ([InstanceId])
REFERENCES [Instances].[Products]([InstanceId]) ON DELETE CASCADE
)
GO
CREATE INDEX [IX_ProductAttributes_Key_Value] ON [Instances].[ProductAttributes] ([Key] ASC, [Value] ASC)There are a couple of differences between these tables.
The name of the product is not required to be unique. Through experience, products can have the same name but have different manufacturers.
The product has additional properties in the table where the type is VARCHAR(MAX). I would not normally condone this in a design, but for our use case, it works.
User-Defined Table Types
In order to get some of this information into the system efficiently, we’re going to need a couple of UDTT in SQL Server. In particular, we’re going to want to upload data for CategoryCollection and CustomAttributes.
Custom Attribute List UDTT for uploading CustomAttributes
CREATE TYPE [dbo].[CustomAttributeList] AS TABLE
(
[Key] VARCHAR(64) NOT NULL,
[Value] VARCHAR(512) NOT NULL
)Integer List UDTT for uploading CategoryCollection
CREATE TYPE [dbo].[IntegerList] AS TABLE
(
[Value] INT NOT NULL
)With these two types, we’ll be able to upload a single entity with one INSERT statement.
Attribute Usage
Each of our models has different tables associated. Yes, the tables are very similar. However, we’re going to need to tell our repository service about these tables somehow. I prefer to keep these definitions as close to the model as possible.
To do this, we’re going to use attributes. We’re going to use an existing attribute from Dapper.Contrib is called TableAttribute and creates a custom attribute called PropertyTableAttribute.
The TableAttribute tells Dapper.Contrib what table to perform the CRUD operations. By default, the table name will be the name of the entity pluralized. However, we are using schemas and we’ll need to customize them to match by using this attribute.
The second attribute, PropertyTableAttribute, is used to define the other tables for Categories and Custom Attributes.
[AttributeUsage(AttributeTargets.Class, AllowMultiple=true, Inherited=true)]
public class PropertyTableAttribute : Attribute
{
public PropertyTableAttribute(string propertyName, string name)
{
PropertyName = pr opertyName;
Name =name;
}
public string PropertyName { get; }
public string Name { get; }
}We will then decorate each of our models with these attributes based on the tables we created.
[Table("[Instances].[Categories]")]
[PropertyTable("CustomAttributes", "[Instances].[CategoryAttributes]")]
[PropertyTable("Categories", "[Instances].[CategoryCategories]")]
public record class CategoryInstance : Instance
{
// ... Properties
}
[Table("[Instances].[Products]")]
[PropertyTable("CustomAttributes", "[Instances].[ProductAttributes]")]
[PropertyTable("Categories", "[Instances].[ProductCategories]")]
public record class ProductInstance : Instance
{
// ... Properties
}Service Definition
We have defined our models and tables, now we will define our service.
Service Interface
The repository interface for our service will be called IInstanceRepository. We have decided to focus on three specific CRUD operations for this interface: AddAsync, GetAsync, and FindAsync. These operations allow us to add instances asynchronously, retrieve instances based on their IDs, and perform searches for instances based on specified criteria, respectively.
By leaving out other CRUD operations, we can keep the interface focused and aligned with our current requirements.
public interface IInstanceRepository<TInstance>
where TInstance : Instance
{
Task<int> AddAsync(T instance);
Task<IEnumerable<TInstance>> GetAsync(params int[] instanceIds);
Task<IEnumerable<TInstance>> FindAsync(ExactMatchQuery query);
}
public class ExactMatchQuery
{
/// <summary>
/// [Performance Enhancement]
/// Used to ensure that a match is found on a specific instance id
/// </summary>
public int? InstanceId { get; set; }
public string InstanceName { get; set; }
/// <summary>
/// The categories the instance must have applied. Multiple categories apply as AND operations.
/// For instance, if a search is performed with categories "X1" and "X2" then only instances
/// with X1 AND X2 will be returned.
/// </summary>
public string[] Categories { get; set; }
/// <summary>
/// The categories the instance must not have applied. Multiple categories apply as OR operations.
/// For instance, if a search is performed with except categories "X1" and "X2" then only instances
/// that do not contain "X1" OR "X2" will be returned.
/// </summary>
public string[] ExceptCategories { get; set; }
/// <summary>
/// The categories the instance must have applied. Multiple categories apply as AND operations.
/// For instance, if a search is performed with category ids 1 and 2 then only instances
/// with category ids 1 AND 2 will be returned.
/// </summary>
public int[] CategoryIds { get; set; }
/// <summary>
/// The custom attributes and values that the instance must exactly match. Multiple
/// attributes apply as AND operations.
/// </summary>
public CustomAttributes CustomAttributes { get; set; } = new CustomAttributes();
public bool IncludeCustomAttributes => CustomAttributes?.Any() ?? false;
}SQL Server Implementation
We will create an implementation using SQL Server, where we will leverage Dapper, along with a custom ISqlExecutor, to interact with the database.
public interfaceISqlExecutor<TInstance> : ISqlExecutor
whereTInstance : Instance
{
string InstanceTableName { get; }
string InstanceCustomAttributesTableName { get; }
string InstanceCategoriesTableName { get; }
Task WriteCategories(SqlConnectionsqlConn, int instanceId, IntegerCollectioncategories, IDbTransactiontransaction);
Task WriteCustomAttributes(SqlConnectionsqlConn, int instanceId, CustomAttributesattributes, IDbTransactiontransaction);
Task<IEnumerable<TInstance>> FindInner(string query, object parameters=null);
Task<IEnumerable<TInstance>> FindInnerFilter(string filterQuery, object parameters=null);
}
public interface ISqlExecutor
{
Task ExecuteAsync(Func<SqlConnection, SqlTransaction, Task> command);
Task<T> ExecuteAsync<T>(Func<SqlConnection, SqlTransaction, Task<T>> command);
}SQL Executor Interface
public class InstanceSqlServerExecutor<TInstance> :
ISqlExecutor<TInstance>
where TInstance : Instance, new()
{
private readonly ISqlExecutor_Executor;
private readonly InstanceReader<TInstance> _Reader;
public InstanceSqlServerExecutor(ISqlExecutorexecutor)
{
_Executor=executor??throw new ArgumentNullException(nameof(executor));
InstanceTableName=DapperExtensions.GetTableName<TInstance>();
InstanceCustomAttributesTableName=DapperExtensions.GetCustomAttributesTableName<TInstance>();
InstanceCategoriesTableName=DapperExtensions.GetCategoriesTableName<TInstance>();
_Reader=new InstanceReader<TInstance>();
}
public string InstanceTableName { get; }
public string InstanceCustomAttributesTableName { get; }
public string InstanceCategoriesTableName { get; }
public Task ExecuteAsync(Func<SqlConnection, SqlTransaction, Task> command)
=>_Executor.ExecuteAsync(command);
public Task<T> ExecuteAsync<T>(Func<SqlConnection, SqlTransaction, Task<T>> command)
=>_Executor.ExecuteAsync<T>(command);
public async Task<IEnumerable<TInstance>> FindInner(string query, object parameters=null)
=>await_Executor.ExecuteAsync(async (sqlConn, transaction) =>
{
using (var multi=await sqlConn.QueryMultipleAsync(query, parameters, transaction: transaction))
return await _Reader.ReadInstances(multi);
});
public async Task<IEnumerable<TInstance>> FindInnerFilter(string filterQuery, object parameters=null)
=>
await _Executor.ExecuteAsync(async (sqlConn, transaction) =>
{
using (var multi=await sqlConn.QueryMultipleAsync(GenerateFindQuery(filterQuery), parameters, transaction: transaction))
return await_Reader.ReadInstances(multi);
});
public async Task WriteCategories(SqlConnectionsqlConn, int instanceId, IntegerCollectioncategories, IDbTransactiontransaction)
{
stringSQL=$@"
DELETE FROM {InstanceCategoriesTableName} WHERE [InstanceId] = @InstanceId;
INSERT INTO {InstanceCategoriesTableName} ([InstanceId], [CategoryInstanceId])
SELECT @InstanceId, C.Value
FROM @Categories C;
";
await sqlConn.ExecuteAsync(SQL, new
{
InstanceId=instanceId,
Categories=categories.ToSqlParameter()
}, transaction: transaction);
}
public async TaskWriteCustomAttributes(SqlConnectionsqlConn, int instanceId, CustomAttributesattributes, IDbTransactiontransaction)
{
string SQL=$@"
DELETE FROM {InstanceCustomAttributesTableName} WHERE [InstanceId] = @InstanceId;
INSERT INTO {InstanceCustomAttributesTableName} ([InstanceId], [Key], [Value])
SELECT @InstanceId, CA.[Key], CA.[Value]
FROM @CustomAttributes CA";
await sqlConn.ExecuteAsync(SQL, new
{
InstanceId=instanceId,
CustomAttributes=attributes.ToSqlParameter()
}, transaction: transaction);
}
private string GenerateFindQuery(string filterQuery) =>$@"
DECLARE @Ids TABLE ([Id] INT NOT NULL)
;
INSERT INTO @Ids ([Id])
{filterQuery}
;
SELECT *
FROM {InstanceTableName}
WHERE [InstanceId] IN (SELECT [Id] FROM @Ids)
;
SELECT *
FROM {InstanceCustomAttributesTableName}
WHERE [InstanceId] IN (SELECT [Id] FROM @Ids)
;
SELECT
INST_CAT.[InstanceId] AS [InstanceId],
CAT.[InstanceId] AS [CategoryInstanceId],
CAT.[Name] AS [CategoryName]
FROM {InstanceCategoriesTableName} INST_CAT
JOIN [Instances].[Categories] CAT
ON CAT.[InstanceId] = INST_CAT.[CategoryInstanceId]
WHERE INST_CAT.[InstanceId] IN (SELECT [Id] FROM @Ids)
;
";
}SQL Executor Implementation
There is a significant amount of functionality within this class, and it could warrant an entire article on its own. However, for now, it would be best to examine the class and address any questions that arise. The following key points should be noted:
In the constructor, we extract the attributes and store the results in local properties. Since this class should be a singleton, this is a one-time cost.
When writing categories and custom attributes, we utilize the previously defined user-defined table types (UDTT) and generate a custom parameter using the .ToSqlParameter() extensions for Dapper.
The approach for overwriting categories and custom attributes is simplified; we delete all associated rows first and then add them back in. Although not the most performant method, it can be further optimized.
Within the GenerateFindQuery method, we use a generalized filter query to initially find the instance identifiers. We then execute several SELECT queries based on the results to return multiple result sets. This approach has proven highly efficient in terms of indexing.
The SQL statements are concatenated. When developing code of this nature, it is crucial to exercise extreme caution and ensure that no user data ends up in the SQL statements without being properly parameterized.
There is one additional class utilized here, which has not been mentioned yet: the InstanceReader class. This class is responsible for taking the results from a Get or Find query and creating concrete instances. The class definition is provided below.
internal class InstanceReader<TInstance> where TInstance : Instance, new()
{
public async Task<IEnumerable<TInstance>> ReadInstances(SqlMapper.GridReaderreader)
{
var instances= (awaitreader.ReadAsync<TInstance>()).OrderBy(o=>o.InstanceId).ToArray();
var attributes= (awaitreader.ReadAsync<CustomAttributeValueDto>()).OrderBy(o=>o.InstanceId).ToArray();
var categories= (awaitreader.ReadAsync<CategoryDto>()).OrderBy(o=>o.InstanceId).ToArray();
var joinedData=instances
.GroupJoin(attributes, o=>o.InstanceId, i=>i.InstanceId,
(inst, attrs) =>new { Instance=inst, Attributes=attrs.ToCustomAttributes() })
.GroupJoin(categories, o=>o.Instance.InstanceId, i=>i.InstanceId,
(inst, cats) =>new { inst.Instance, inst.Attributes, Categories=cats });
foreach (var inst in joinedData)
{
inst.Instance.CustomAttributes=inst.Attributes;
inst.Instance.Categories = new CategoryCollection(inst.Categories.Select(c=>newCategoryCollectionEntry
{
Category=c.CategoryName,
CategoryId=c.CategoryInstanceId
}).ToArray());
}
return instances;
}
private class CategoryDto
{
public int InstanceId { get; set; }
public int CategoryInstanceId { get; set; }
public string CategoryName { get; set; }
public static IntegerCollectionGetCategories(IEnumerable<CategoryDto> categories, intinstanceId) => new IntegerCollection(categories.Where(cat=>cat.InstanceId==instanceId).Select(cat=>cat.CategoryInstanceId));
}
}Basic Add and Get Implementation
Till now we have established the core framework for our shared instances, it's time to implement the functions in the repository. In this section, we will specifically focus on the implementation of the AddAsync and GetAsync functions.
However, before proceeding to advanced searching, we need to make some additions to our framework.
public partial class SqlServerInstanceRepository<TInstance> :
IInstanceRepository<TInstance>
where TInstance : Instance, new()
{
protected readonly ISqlExecutor<TInstance> _Executor;
public SqlServerInstanceRepository(ISqlExecutor<TInstance> executor)
{
_Executor=executor??throw new ArgumentNullException(nameof(executor));
}
public async virtualTask<int> AddAsync(TInstanceinstance)
{
return await_Executor.ExecuteAsync(async (sqlConn, transaction) =>
{
instance.CreatedTimestamp=DateTime.Now;
var id = await sqlConn.InsertAsync(instance, transaction: transaction);
await _Executor.WriteCustomAttributes(sqlConn, id, instance.CustomAttributes, transaction);
await _Executor.WriteCategories(sqlConn, id, instance.Categories, transaction);
return id;
});
}
public virtual Task<IEnumerable<TInstance>> GetAsync(params int[] instanceIds) =>_Executor.FindInnerFilter(
$@"SELECT [Value] FROM @InstanceIds;",
new { InstanceIds = new IntegerCollection(instanceIds).ToSqlParameter() });
}Task<int> AddAsync(TInstance instance)This function is used to add the instance into the system.
To achieve this, we employ the 'InsertAsync' method from Dapper.Contrib, as documented. This method automatically handles the parameterization of properties and inserts the record into the corresponding table. In order for this process to work smoothly, we need to ensure that the property names of our records match the column names in the database. Once this alignment is established, the data is seamlessly added.
Following this step, we proceed to write the attributes and categories using the executor that was defined in the previous section.
Now, if you observe carefully, you might notice that something has been omitted from the process thus far. While this function works perfectly for all the simple parameters we have created, what happens when we encounter special 'StringCollection' properties defined in the 'ProductInstance' class?
In order for the 'InsertAsync' function to handle these cases seamlessly, we need to provide instructions on how to handle them. Therefore, during the initialization of the system, at some point, we must add a type handler for 'StringCollection' to the 'SqlMapper' class in Dapper."
public class SqlMapperInitializer : IInitializer
{
private readonly IDataSerializer_Serializer;
public SqlMapperInitializer(IDataSerializerserializer)
{
_Serializer=serializer??throw new ArgumentNullException(nameof(serializer));
}
public int Priority=>0;
public Task Initialize()
{
SqlMapper.AddTypeHandler(new CustomTypeHandler<IntegerCollection>(_Serializer));
SqlMapper.AddTypeHandler(new CustomTypeHandler<StringCollection>(_Serializer));
SqlMapper.AddTypeHandler(new CustomTypeHandler<CustomAttributes>(_Serializer));
returnTask.CompletedTask;
}
}Without going too deep into details, it's important to understand that our collection of strings will be converted into a JSON string, which can then be stored in a VARCHAR(MAX) field. Alternatively, depending on our requirements, we could also opt for a simple comma-delimited list.
Task<IEnumerable<TInstance>> GetAsync(params int[] instanceIds)
The 'GetAsync' function is utilized to retrieve multiple instances in bulk from the system. I chose to define it this way because it's easier to transition from multiple instances to one using an extension method, rather than the other way around. By leveraging efficient querying methods, we don't have to compromise on performance when dealing with a small number of instances compared to larger lists.
In this method, we simply provide a filter that selects all the instance IDs provided. The purpose and significance of using this filter will become more apparent when we perform advanced searches.
Framework for SQL Statement Building
Here we are avoiding the use of excessive black box implementations. So, we will not utilize Entity Framework or other ORM (Object-Relational Mapping) frameworks that generate SQL statements automatically. Instead, we value the control and flexibility to optimize my queries according to my own preferences.
However, when dealing with complex queries, encountered significant challenges in building them efficiently. To address this issue, we will develop a simple set of classes that alleviate the difficulties involved in filtering and generating reports.
Now, let's delve into these classes.
public class WhereClause : IEnumerable<string>
{
private readonly List<string> _Backer = new List<string>();
public void Add(string clause)=>_Backer.Add(clause);
public override string ToString()
{
if (_Backer.Count==0)
return string.Empty;
return "WHERE "+string.Join(" AND ", _Backer);
}
public IEnumerator<string> GetEnumerator()
=>_Backer.GetEnumerator();
IEnumerator IEnumerable.GetEnumerator()
=>_Backer.GetEnumerator();
This class allows me to quickly build a where clause assuming AND operations.
public class JoinTables : IEnumerable<string>
{
private readonly List<string> _Backer = new List<string>();
public void Add(string clause)
=> _Backer.Add(clause);
public void Add(string tableName, string abbv, string onClause)
=> _Backer.Add($"JOIN {tableName} {abbv} ON {onClause}");
public override string ToString()
{
if (_Backer.Count == 0)
return "";
return string.Join("\n", _Backer);
}
public IEnumerator<string> GetEnumerator()
=> _Backer.GetEnumerator();
IEnumerator IEnumerable.GetEnumerator()
=> _Backer.GetEnumerator();
}This class allows me to quickly add JOIN tables to my query.
public class KeyValuePivotTable
{
public string TableName { get; set; }
public string PivotAlias { get; set; } ="PVT";
public string InstanceIdColumnName { get; set; } ="[InstanceId]";
public string ValueColumnName { get; set; } ="[Value]";
public string KeyColumnName { get; set; } ="[Key]";
public Dictionary<string, string> PivotValues { get; set; } =new Dictionary<string, string>();
public string ToJoin()
{
string pivotColumns=string.Join(", ", PivotValues.Keys.Select(k=>"["+k.Trim('[', ']') +"]"));
String Builderbuilder=new StringBuilder();
builder.AppendLine($"SELECT {InstanceIdColumnName}, {pivotColumns}");
builder.AppendLine($"FROM {TableName}");
builder.AppendLine($"PIVOT (MIN({ValueColumnName}) FOR {KeyColumnName} IN ({pivotColumns})) {PivotAlias}");
return builder.ToString();
}
public string ToWhereClause(stringjoinAlias, BooleanJoinjoiner=BooleanJoin.And)
{
stringBOOL_JOINER="AND";
if (joiner==BooleanJoin.Or)
BOOL_JOINER="OR";
var stringifiedValues=PivotValues
.Select(kv=>new
{
Key="["+kv.Key.Trim('[', ']') +"]",
kv.Value
})
// TODO: MUST FIGURE OUT HOW TO PARAMETERIZE THIS EFFECTIVELY; THIS IS NOT GOOD!!
.Select(kv=>$"{joinAlias}.{kv.Key} = '{kv.Value?.Replace("'", "''")}'");
return string.Join($" {BOOL_JOINER} ", stringifiedValues);
}
public enum BooleanJoin
{
And,
Or
}
}Now, we will discuss the pivot table. There is an issue with my queries when it came to searching for custom attributes, as performing naive searches leads to false results. Initially, basic inner joins and filtered on these joins for each attribute were used. However, faced a problem when attempting to search for a product that had two attributes matching. This approach resulted in retrieving instances that had either one or both of the attribute values (acting as an OR operation).
To rectify this, a pivot table is required that enables the use of an AND operation with multiple attributes. This particular class helps alleviate some of the challenges associated with that.
Advanced Searching
Moving on to the 'FindAsync(ExactMatchQuery)' method, the objective of this method is to perform a search based on various optional properties provided by the function. First, let's take a look at the code, and then we'll discuss its workings in detail.
public partial class SqlServerInstanceRepository<TInstance>
{
public virtual asyncTask<IEnumerable<TInstance>> FindAsync(ExactMatchQueryquery)
{
WhereClauseWHERE = new WhereClause();
JoinTablesCATEGORY_JOIN=newJoinTables();
if (query.Categories?.Any() ??false)
{
stringCAT_SQL=$@"
SELECT COUNT(*) AS InstanceCount, INST_CAT.InstanceId AS InstanceId
FROM {_Executor.InstanceTableName} INST_CAT
JOIN {_Executor.InstanceCategoriesTableName} CAT_INST_CAT
ON CAT_INST_CAT.InstanceId = INST_CAT.InstanceId
JOIN [Instances].[Categories] CAT_CAT
ON CAT_CAT.InstanceId = CAT_INST_CAT.CategoryInstanceId
WHERE
(CAT_CAT.Name IN (SELECT [Value] FROM @Categories))
GROUP BY INST_CAT.InstanceId";
CATEGORY_JOIN.Add($"JOIN ({CAT_SQL}) CAT ON CAT.InstanceId = INST.InstanceId AND CAT.InstanceCount = @CategoryCount");
}
if (query.ExceptCategories?.Any() ??false)
{
stringEXCEPT_SQL=$@"
SELECT INST_EX.InstanceId
FROM {_Executor.InstanceTableName} INST_EX
JOIN {_Executor.InstanceCategoriesTableName} CAT_INST_EX
ON CAT_INST_EX.InstanceId = INST_EX.InstanceId
JOIN [Instances].[Categories] CAT_EX
ON CAT_EX.InstanceId = CAT_INST_EX.CategoryInstanceId WHERE
(CAT_EX.Name IN (SELECT [Value] FROM @ExceptedCategories))";
WHERE.Add($"(INST.InstanceId NOT IN ({EXCEPT_SQL}))");
}
if (query.CategoryIds?.Any() ??false)
{
stringCAT_SQL=$@"
SELECT COUNT(*) AS InstanceCount, INST_CATID.InstanceId AS InstanceId
FROM {_Executor.InstanceTableName} INST_CATID
JOIN {_Executor.InstanceCategoriesTableName} CAT_INST_CATID
ON CAT_INST_CATID.InstanceId = INST_CATID.InstanceId WHERE
(CAT_INST_CATID.CategoryInstanceId IN (SELECT [Value] FROM @CategoryIds))
GROUP BY INST_CATID.InstanceId";
CATEGORY_JOIN.Add($"JOIN ({CAT_SQL}) CATID ON CATID.InstanceId = INST.InstanceId AND CATID.InstanceCount = @CategoryIdCount");
}
if (!string.IsNullOrWhiteSpace(query.InstanceName))
WHERE.Add("(INST.Name = @InstanceName OR @InstanceName IS NULL)");
if (query.InstanceId!=null)
WHERE.Add("(INST.InstanceId = @InstanceId)");
if (query.IncludeCustomAttributes&& (query.CustomAttributes?.Any() ??false))
{
KeyValuePivotTablepivot=newKeyValuePivotTable
{
TableName=_Executor.InstanceCustomAttributesTableName,
PivotValues=query.CustomAttributes
};
WHERE.Add($"({pivot.ToWhereClause("CUST_ATTR")})");
stringSQL=$@"
SELECT DISTINCT INST.InstanceId
FROM {_Executor.InstanceTableName} INST
JOIN ({pivot.ToJoin()}) CUST_ATTR
ON CUST_ATTR.InstanceId = INST.InstanceId
{CATEGORY_JOIN}
{WHERE}";
returnawait_Executor.FindInnerFilter(SQL, new
{
Categories = new StringCollection(query.Categories??newstring[] { }).ToSqlParameter(),
CategoryCount=query.Categories?.Count() ??0,
ExceptedCategories = new StringCollection(query.ExceptCategories??newstring[] { }).ToSqlParameter(),
CategoryIds=newIntegerCollection(query.CategoryIds??newint[] { }).ToSqlParameter(),
CategoryIdCount=query.CategoryIds?.Count() ??0,
query.InstanceName,
CustomAttributes = new CustomAttributes(query.CustomAttributes??newDictionary<string, string>()).ToSqlParameter()
});
}else
{
returnawait_Executor.FindInnerFilter($@"
SELECT DISTINCT INST.InstanceId
FROM {_Executor.InstanceTableName} INST
{CATEGORY_JOIN}
{WHERE}
", new
{
Categories=newStringCollection(query.Categories??newstring[] { }).ToSqlParameter(),
CategoryCount=query.Categories?.Count() ??0,
ExceptedCategories=newStringCollection(query.ExceptCategories??newstring[] { }).ToSqlParameter(),
CategoryIds=newIntegerCollection(query.CategoryIds??newint[] { }).ToSqlParameter(),
CategoryIdCount=query.CategoryIds?.Count() ??0,
query.InstanceName,
});
}
}There is a considerable amount of complex SQL logic involved in this code. However, let's focus on the essence of the algorithm:
We begin by creating sub-query joins for properties such as Categories, ExceptionCategories, and CategoryIds. The reason for using sub-queries is due to the nature of the different link tables defined at the instance level.
Next, we proceed with the WHERE clause. Based on the properties set in the query, we add entries to our custom 'WhereClause' class using conditional checks.
Considering the complexity of 'CustomAttributes,' we check if it is required for the search and construct the pivot table accordingly. If 'CustomAttributes' are not needed, we optimize the scenario accordingly.
Following that, we create our filter query. It's important to note that the sole purpose is to retrieve the instance IDs. The rest of the query, responsible for retrieving the actual data, is abstracted into the implementation of 'ISqlExecutor<TInstance>'.
Finally, we add the SQL parameters and execute the query.
The result of this query is a collection of found instances. This flexible approach to querying allows me to easily incorporate new filtering capabilities by adding additional optional properties to the query, along with their respective implementations as new if statements.