T-SQL Tips and Tricks: Efficiently Querying Audit Entries created by Entity Framework in MS SQL

Maintaining audit entries in database is required for Healthcare, Financial, Insurance and many other key industries for legal and compliance purpose.

There are many ways to maintain audit entries in database. If you are using Entity Framework to create audit-entries in SQL database, you would need to query these audit-entries. This is useful when you need to display in report or in UI history of the changes made to certain tables by users.

Here is a T-SQL function that can be easily used to query audit entries from the tables created by Entity-Framework.

/*
	sharpquest.fun_audit 
		returns all the audit entries for a given table and given id. 
@schemaName - if NULL then dbo is assumed
	@tableName - must be passed
	@tableIdName - must be passed - 
either primary id or parent table id name to get child table audit entries 
	@tableIdValue - if value is passed, audit entries returned for specific record; 
if NULL is passed, audit entries returned for all records for given table 
	@fieldNames - comma separated list of fields whose old/new values are returned
if NULL is passed, audit for all fields are returned 
	@UpdatedBy	- entries updated by particular user are returned 
		- if NULL then entries updated by all users are returned
	@dateFrom/To - entries updated within specific date range are returned
		- if NULL then all entries are returned 

	example
-- returns all changes in Customer table 
		select * from common.fun_audit('Customer', 'CustomerId', NULL, NULL, NULL, NULL, NULL); 

 -- returns changes in Customer table where CustomerId is 15531
		select * from common.fun_audit('Customer', 'CustomerId', 15531, NULL, NULL, NULL, NULL);

-- returns changes in Customer table for fields CustomerName,Address where CustomerId is 15531
		select * from common.fun_audit('Customer', 'CustomerId', 15531, 'CustomerName,Address', NULL, NULL, NULL); 

 -- returns changes in Customer table for CustomerName within december,2023
		select * from common.fun_audit('Customer', 'CustomerId', NULL, 'CustomerName', NULL, '2023-12-01', '2023-12-31');
*/
create or alter function sharpquest.fun_audit(
	@schemaName varchar(100),
	@tableName varchar(100),
	@tableIdName varchar(100),
	@tableIdValue bigint,
	@fieldNames varchar(max),
	@UpdatedBy bigint,
	@dateFrom datetime,
	@dateTo datetime
)
returns table
as
	return
       SELECT ae.EntityTypeName, 
				aepi.Propertyname AS EntityIdName,  
                aepi.NewValue AS EntityId,  
				ae.State,
				ae.StateName,
                aep.PropertyName,   
                aep.OldValue AS OldValue,  
                aep.NewValue AS NewValue,  
                CreatedDate as UpdatedDate,  
				ae.CreatedBy
       FROM AuditEntries ae WITH (NOlock)  
             inner join AuditEntryProperties aepi WITH (NOlock)  
                    on ae.AuditEntryid = aepi.AuditEntryid  
                      AND aepi.PropertyName = @tableIdName  
             inner join AuditEntryProperties aep WITH (NOlock)  
                    on aep.AuditEntryid = ae.AuditEntryid  
                       AND aep.PropertyName <> aepi.PropertyName 
					   AND aep.PropertyName NOT IN ('Created', 'CreatedBy', 'Updated', 'UpdatedBy')
       WHERE ae.EntityTypeName = @tableName  
	   AND ae.EntitySetName = ISNULL(@schemaName, 'dbo') 
	   AND (ISNULL(aep.OldValue, '') <> ISNULL(aep.NewValue, ''))
	   AND (@tableIdValue IS NULL OR aepi.NewValue = @tableIdValue) 
	   AND (@fieldNames IS NULL OR aep.PropertyName IN (SELECT [data] from SplitCommas(@fieldNames)))
	   AND (@UpdatedBy IS NULL OR ae.CreatedBy = @UpdatedBy)
	   AND (@dateFrom IS NULL OR [CreatedDate] >= @dateFrom)
	   AND (@dateTo IS NULL OR convert(date, [CreatedDate]) <= @dateTo)
GO