Aggregate functions


This function concatenates varchar/nvarchar columns in aggregation queries.


Suppose we have the following tables.


Column Type
id int
title varchar(200)
year int


Column Type
id int
name varchar(100)


Column Type
bookId int
tagId int

We can write a query like below to get list of books and their tags.

	chm.Concat(t.[name], ',') as [tags]
from            dbo.Books       b
	inner join  dbo.BookTags    bt  on b.[id]     = bt.[bookId]
	inner join  dbo.Tags        t   on bt.[tagId] = t.[id]
group by b.[title]

chm.BitwiseAnd / chm.BitwiseOr

These functions apply bitwise and/or merge on integer columns in aggregation queries.

Practical Example, ACL system

In this section we implement a simple ACL system to show a practical usecase for BitwiseAnd nad BitwiseOr aggregate functions. List of tables we use are as follows:


Column Type
id int
username varchar(50)
password varchar(50)


Column Type
id int
name varchar(50)


Column Type
id int
name varchar(100)


Column Type
roleId int
entityId int
value tinyint


Column Type
userId int
roleId int

In this ACL system permissions are defined over entities and they are granted to roles, not users. Each role can have a number of permissions over an entity.

Permissions of a role is defined as a tinyint value. It holds a combination of permissions. Each bit in a tinyint grant value means a specific permission. They are listed in the following table:

Bit Number Meaning
0 View
1 Add
2 Edit
3 Delete
4 Reserved
5 Reserved
6 Reserved
7 Reserved

As it is seen, our ACL system supports 4 permissions. The rest 4 bits are reserved for later usage. Having this system, for a role like Operator to have View/Add/Edit permissions over 'Book' entity, a 7 value will be calculated.

		  R R R R D E A V
0 0 0 0 0 1 1 1  = 7

And permission value for an Admin role with full control over 'Book' entity would be 15.

		  R R R R D E A V
0 0 0 0 1 1 1 1  = 15

Now, since users can have multiple roles, a user can have multiple permissions over an entity. We need to perform a bitwise merge operation in order to retrieve final permissions of a user.

In fact, we need to have a polciy to decide what we should do when a user has multiple same permissions over an entity (e.g. he has an Add permission over Books using his Admin role, but he does not have such a permission over Books using his Member role).

We can use a bitwise merge over user permissions. This could be performed both as a bitwise-or or bitwise-and. It depends on our policy which one to choose. A bitwise-or is more permissive while a bitwise-and is more restrictive.

Now, we can write a helper UDF like below to get permissions of a given user over an entity:

		  create or alter dbo.GetPermission
	@username   varchar(50),
	@entity     varchar(100)
returns tinyint
	declare @result tinyint

		@result = chm.BitwiseOr(rp.[vale])
	from                dbo.Users           u
		inner join  dbo.UserRoles       ur  on u.[id]        = ur.[userId]
		inner join  dbo.Roles           r   on ur.[roleId]   = r.[id]
		inner join  dbo.RolePermissions rp  on r.[roleId]    = rp.[roleId]
		inner join  dbo.Entities        e   on rp.[entityId] = e.[id]
	where u.[username] = @username and e.[name] = @entity

	set @result = isnull(@result, 0)

	return @result

Here, we used bitwise-or policy. In order to switch to bitwise-and, we can simply replace chm.BitwiseOr() with chm.BitwiseAnd().

This simple and easy-to-use ACL system could be used in any type of application with any type of technology. After retrieving permissions of a user over an entity we can decide to show or hide buttons like 'Add', 'Edit', 'Delete' in say, administration panel of the application.

Here is a helper C# class that provides boolean getter properties for a loaded permission of a user:

		  public class UserPermission
	public readonly byte Value { get; private set; }
	public UserPermission(byte value)
		Value = value;
	public bool Check(byte index)
		var bitValue = Math.Power(2, index);

		return Value & bitValue == bitValue;
	public bool View
		get { return Check(0); }
	public bool Add
		get { return Check(1); }
	public bool Edit
		get { return Check(2); }
	public bool Delete
		get { return Check(3); }

Having this class, using it will be easy. The following is a Web API that returns permission of current user for a requested entity.

public class PermissionController: ControllerBase
	readonly IConfiguration __configuration;
	public PermissionController(IConfiguration configuration)
		_configuration = configuration;
	public UserPermission Get(string entity)
		var constr = _configuration.GetConnectionString("DefaultConnection");

		using (var con = new SqlConnection(constr))
			using (var cmd = new SqlCommand("select dbo.GetPermission(@username, @entity)", con)))
				cmd.Parameters.AddWithValue("@username", User.Identity.Name);
				cmd.Parameters.AddWithValue("@entity", entity);

				var permission = Convert.ToByte(cmd.ExecuteScaler());

				return new UserPermission(permission);

If we use, Util.Db in Chameleon.Core, we can make Get() action simpler:

public UserPermission Get(string entity)
	Util.Db.ConnectionString = _configuration.GetConnectionString("DefaultConnection");
	var permission = Util.Db.ExecuteSingleSql("select dbo.GetPermission(@username, @entity)",
							reader => new UserPermission(Convert.ToByte(reader[0])),
							new { username = User.Identity.Name, entity = entity });
	return permission;

For the final piece to complete the puzzle, here is a sample BookList React component that displays list of books and conditionally shows Add/Edit/Delete buttons based on current user's permissions fetched from the server -using the API provided above.

		  import React, { useState, useEffect } from 'react';
import { Link } from 'react-router-dom';
import { Button } from 'reactstrap';

const BookList = () => {
	const [books, setBooks] = useState([]);
	const [permission, setPermission] = useState({});

	const fetchBooks = async () => {
		const res = await fetch('/api/books');
		const books = await res.json();


	useEffect(() => {
		(async () => {
			const res = await fetch('/api/permission/books');
			const permission = await res.json();


			await fetchBooks();
	}, []);

	const handleDelete = async (bookId) => {
		if (confirm('Are you sure?')) {
			const res = await fetch(`/api/books/${bookId}`, {
				method: 'DELETE'

			const result = await res.json();

			if (result) {
				await fetchBooks();

	return <>
		{permission.add ? <Link to="/books/add">
			<Button variant="primary" size="md">
		</Link>: null}
		<Table className="table">
			{permission.edit ? <th></th>: null}
			{permission.delete ? <th></th>: null}
			{, index) => <tr>
				<th scope="row">{index}</th>
				{permission.edit ? <td>
					<Link to={`/books/edit/${}`}>
						<Button variant="success" size="md">
				</td>: null}
				{permission.delete ? <td>
					<Button variant="danger" size="md" onClick={() => handleDelete(}>
				</td>: null}

export default BookList;