How can i make this unit testable?

I am using entity framework in my project, but I also need users to be able to create entities themselves. Please do not say this is not advisable etc, as I have to do it.

Anyway, in my code, I need to dynamically create the SQL and execute it. Most of the time, the code returns a record set, sometimes it doesn't.

This is all currently in my service layer, but I think that I need to move some of it too my Data layer (layer that contains my DbContext), but not sure how.

My DynamicData inherits from Dictionary<string, object>.

The SearchAsync takes a basic string and then should create and execute the SQL to search for that term in a table against all fields.

Here is an example of some code.

public class DynamicData : Dictionary<string, object>
{

}

public class QueryResult<DynamicData>
{
    public ICollection<DynamicData> Results { get; set; }

    public int TotalCount { get; set; }

    public QueryResult() {
        Results = new List<DynamicData>();
    }
}

// split this up, as this function will be called from other
// functions that will generate SQL statements
public async Task<DynamicData> GetDataFromReaderAsync(IDataReader dataReader, List<Field> fields)
{
    var entity = new DynamicData();

    for (var i = 0; i < dataReader.FieldCount; i++)
    {
        object value = null;

        if (!dataReader.IsDBNull(i))
        {
            value = dataReader.GetValue(i);
        }

        // find the matching field
        var field = (from a in fields.Where(e => e.ColumnName == dataReader.GetName(i)) select a).FirstOrDefault();

        // check if field is null, because IsDeleted and other system fields
        // will not be in field table
        if (field != null)
        {
            if (field.Type == FieldType.List && value != null)
            {
                value = ((string)value).Split(char.Parse(";"));
            }

            entity[field.ColumnName] = value;
        }
    }

    return entity;
}

public async Task<ICollection<DynamicData>> SearchAsync(string term)
{
    var fromQuery = new StringBuilder();
    var result = new QueryResult();

    // usually populated by other means
    var module = { 
        TableName = "myTable",
        Fields = new List<Field> {
            new Field { ColumnName = "Text1" },
            new Field { ColumnName = "Text2" },
            new Field { ColumnName = "Text3" }
        }
    }; 

    var fields = module.Fields.ToList();

    // from
    fromQuery.AppendLine("    FROM " + module.TableName + " MAIN");

    // term
    if (!string.IsNullOrEmpty(termClause))
    {
        fromQuery.AppendLine("    WHERE (");

        var termQuery = new List<string>();

        foreach (var col in fields)
        {
            termQuery.Add("        [" + col.ColumnName + "] LIKE @Term ");
        }

        fromQuery.AppendLine(string.Join("OR\n", termQuery.ToArray()));
    }

    var wholeQuery = new StringBuilder();
    wholeQuery.AppendLine("SELECT * FROM (");
    wholeQuery.AppendLine("    SELECT ROW_NUMBER() OVER (ORDER BY Name ASC AS RowNumber,");
    wholeQuery.AppendLine("    SELECT MAIN.* ");
    wholeQuery.Append(fromQuery);
    wholeQuery.AppendLine(") AS SEARCH ");

    wholeQuery.AppendLine("SELECT COUNT(*) ");
    wholeQuery.Append(fromQuery);

    using (var conn = new SqlConnection(_context.Database.Connection.ConnectionString))
    {
        await conn.OpenAsync();

        using (var cmd = new SqlCommand(wholeQuery.ToString(), conn))
        {
            cmd.Parameters.AddWithValue("@Term", "%" + term + "%");

            using (var dr = await cmd.ExecuteReaderAsync())
            {
                while (await dr.ReadAsync())
                {
                    result.Results.Add(await GetDataFromReaderAsync(dr));
                }

                await dr.NextResultAsync();

                if (await dr.ReadAsync())
                {
                    result.TotalCount = dr.GetInt32(0);
                }
            }
        }
    }

    return result;
}

I think the main part I am confused about making unit testable is the last part of the SearchAsync function, as it creates an SQLConnection, SQLCommand and executes a reader.

Am I right in thinking that all the code that generates the SQL, I would have to split up into functions, so I could test that each piece is returning the SQL that I would expect?

Any help appreciated