public void TestSqlConnectionQueryAllAsyncViaTableNameWithOrderByAndWithHints()
{
// Setup
var tables = Helper.CreateIdentityTables(10);
var orderBy = OrderField.Parse(new { Id = Order.Ascending });
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
connection.InsertAll(tables);
// Act
var result = connection.QueryAllAsync(ClassMappedNameCache.Get <IdentityTable>(),
orderBy: orderBy,
hints: SqlServerTableHints.NoLock).Result;
// Assert
Assert.AreEqual(tables.Count, result.Count());
result.AsList().ForEach(item =>
{
var entity = tables.First(t => t.Id == item.Id);
Assert.AreEqual(entity.Id, item.Id);
Assert.AreEqual(entity.RowGuid, item.RowGuid);
Assert.AreEqual(entity.ColumnBit, item.ColumnBit);
Assert.AreEqual(entity.ColumnDateTime, item.ColumnDateTime);
Assert.AreEqual(entity.ColumnDateTime2, item.ColumnDateTime2);
Assert.AreEqual(entity.ColumnDecimal, item.ColumnDecimal);
Assert.AreEqual(entity.ColumnFloat, item.ColumnFloat);
Assert.AreEqual(entity.ColumnNVarChar, item.ColumnNVarChar);
});
}
}
public void TestWithExpressions()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithExpressionsClass>();
var where = QueryGroup.Parse(new
{
Field1 = "Test"
});
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, where, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [TestWithExpressionsClass] " +
$"WHERE ([Field1] = @Field1) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithWhereAndWithOrderByAndWithTopAndWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var where = new QueryGroup(new QueryField("Id", 1, Helper.DbSetting), Helper.DbSetting);
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending, Field2 = Order.Descending }, Helper.DbSetting);
var top = 100;
var hints = SqlServerTableHints.NoLock;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
orderBy: orderBy,
top: top,
hints: hints);
var expected = $"" +
$"SELECT TOP (100) [Field1], [Field2], [Field3] " +
$"FROM [Table] WITH (NOLOCK) " +
$"WHERE ([Id] = @Id) " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestCreateBatchQueryWithMultipleOrderedColumnsAndWithoutAttributes()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateBatchQueryWithMultipleOrderedColumnsAndWithoutAttributesClass>();
var orderBy = OrderField.Parse(new
{
Field1 = Order.Descending,
Field2 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, null, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] DESC, [Field2] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [TestCreateBatchQueryWithMultipleOrderedColumnsAndWithoutAttributesClass] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] DESC, [Field2] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public IList <EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple?skipPast, int take)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
var page = 0;
if (skipPast != null)
{
var lastNameField = new QueryField("LastName", lastName);
var firstNameField = new QueryField("FirstName", Operation.GreaterThan, skipPast.FirstName);
var firstNameAndEmployeeKeyFields = new QueryGroup(new[]
{
new QueryField("FirstName", skipPast.FirstName),
new QueryField("EmployeeKey", Operation.GreaterThan, skipPast.EmployeeKey)
});
var group = new QueryGroup(lastNameField,
new QueryGroup(firstNameField.AsEnumerable(),
firstNameAndEmployeeKeyFields.AsEnumerable(), Conjunction.Or));
return(BatchQuery(page,
take,
orderBy,
group).AsList());
}
else
{
return(BatchQuery(page,
take,
orderBy,
e => e.LastName == lastName).AsList());
}
}
public void TestCreateBatchQueryWithClassMapping()
{
// Setup
var statementBuilder = new SqlDbStatementBuilder();
var queryBuilder = new QueryBuilder <TestCreateBatchQueryWithClassMappingClass>();
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, null, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [ClassName] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public async Task <PreProcessedCursorSlice <Droid> > GetDroidsPaginatedAsync(
[Service] ICharacterRepository repository,
//THIS is now injected by Pre-Processed extensions middleware...
[GraphQLParams] IParamsContext graphQLParams
)
{
var repoDbParams = new GraphQLRepoDbMapper <CharacterDbModel>(graphQLParams);
//********************************************************************************
//Get the data and convert to List() to ensure it's an Enumerable
// and no longer using IQueryable to successfully simulate
// pre-processed results.
//NOTE: Selections (e.g. Projections), SortFields, PagingArgs are all pushed
// down to the Repository (and underlying Database) layer.
var charactersSlice = await repository.GetPagedDroidCharactersAsync(
repoDbParams.GetSelectFields(),
repoDbParams.GetSortOrderFields() ?? OrderField.Parse(new { Name = Order.Ascending }),
repoDbParams.GetCursorPagingParameters()
);
//With a valid Page/Slice we can return a PreProcessed Cursor Result so that
// it will not have additional post-processing in the HotChocolate pipeline!
//NOTE: Filtering can be applied but ONLY to the results we are now returning;
// Because this would normally be pushed down to the Sql Database layer.
return(charactersSlice.AsPreProcessedCursorSlice());
//********************************************************************************
}
public void TestSqlStatementBuilderCreateQueryAllWithOrderBy()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending,
Field2 = Order.Descending
});
// Act
var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateBatchQuerySecondBatch()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2" }, Helper.DbSetting);
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending }, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 1,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [Table] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 11 AND 20) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateBatchQueryWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "[dbo].[Table]";
var fields = Field.From("Field1", "Field2");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null,
hints: SqlServerTableHints.NoLock);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [dbo].[Table] WITH (NOLOCK) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionIfTheObjectIsNull()
{
// Prepare
var orderBy = (object)null;
// Act/Assert
Assert.Throws(typeof(NullReferenceException), () => OrderField.Parse(orderBy));
}
public void TestOrderFieldParseExpressionForDescending()
{
// Act
var parsed = OrderField.Parse <OrderFieldTestClass>(p => p.Id, Order.Descending);
// Assert
Assert.AreEqual(Order.Descending, parsed.Order);
}
public void ThrowExceptionIfTheFieldValueIsNotAnOrderType()
{
// Prepare
var orderBy = new { Id = "NotAnOrderType" };
// Act/Assert
OrderField.Parse(orderBy);
}
public void ThrowExceptionIfFieldValueIsNotAnOrderType()
{
// Prepare
var orderBy = new { Id = "NotAnOrderType" };
// Act/Assert
Assert.Throws(typeof(InvalidOperationException), () => OrderField.Parse(orderBy));
}
public void ThrowExceptionIfTheObjectIsNull()
{
// Prepare
var orderBy = (object)null;
// Act/Assert
OrderField.Parse(orderBy);
}
public void TestOrderFieldParseExpressionFromMappedPropertyForDescending()
{
// Act
var parsed = OrderField.Parse <OrderFieldTestMappedClass>(p => p.PropertyString, Order.Descending);
// Assert
Assert.AreEqual("PropertyText", parsed.Name);
Assert.AreEqual(Order.Descending, parsed.Order);
}
public void TestDescending()
{
// Prepare
var orderBy = new { Id = Order.Descending };
// Act
var orderField = OrderField.Parse(orderBy);
// Assert
Assert.AreEqual(Order.Descending, orderField.First().Order);
}
public void TestOrderFieldParseForDynamicObjectWithDescendingOrder()
{
// Prepare
var orderBy = new { Id = Order.Descending };
// Act
var orderField = OrderField.Parse(orderBy);
// Assert
Assert.AreEqual(Order.Descending, orderField.First().Order);
}
public void TestSqlTransactionForBatchQuery()
{
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Prepare
using (var transaction = connection.EnsureOpen().BeginTransaction())
{
// Act
connection.BatchQuery <IdentityTable>(0, 10, OrderField.Parse(new { Id = Order.Ascending }), it => it.Id != 0, transaction: transaction);
}
}
}
public void TestParseDynamicAscendingAndDescending()
{
// Prepare
var orderBy = new { Id = Order.Ascending, Value = Order.Descending };
// Act
var orderField = OrderField.Parse(orderBy);
// Assert
Assert.AreEqual(Order.Ascending, orderField.First().Order);
Assert.AreEqual(Order.Descending, orderField.Last().Order);
}
public void TestSqlTransactionForBatchQueryAsync()
{
using (var connection = new MySqlConnection(Database.ConnectionString))
{
// Prepare
using (var transaction = connection.EnsureOpen().BeginTransaction())
{
// Act
connection.BatchQueryAsync <CompleteTable>(0, 10, OrderField.Parse(new { Id = Order.Ascending }), it => it.Id != 0, transaction: transaction).Wait();
}
}
}
public void ThrowExceptionOnPostgreSqlStatementBuilderCreateBatchQueryIfThePageValueIsNullOrOutOfRange()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
builder.CreateBatchQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name"),
-1,
10,
OrderField.Parse(new { Id = Order.Ascending }));
}
public void ThrowExceptionOnPostgreSqlStatementBuilderCreateBatchQueryIfThereAreNoFields()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
builder.CreateBatchQuery(new QueryBuilder(),
"Table",
null,
0,
10,
OrderField.Parse(new { Id = Order.Ascending }));
}
public IList <EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
return(BatchQuery(page,
pageSize,
orderBy,
e => e.LastName == lastName).AsList());
}
public async Task <IEnumerable <ICharacter> > GetCharacterFriendsAsync(int characterId)
{
await using var sqlConn = CreateConnection();
var results = await sqlConn.QueryAsync <CharacterFriendDbModel>(
where : f => f.FriendOfId == characterId,
//Always include a Default Sort Order (for paging)
orderBy : OrderField.Parse(new { Name = Order.Ascending })
);
var mappedResults = MapDbModelsToCharacterModels(results);
return(mappedResults);
}
public void ThrowExceptionOnSqLiteStatementBuilderCreateBatchQueryIfTheRowsPerBatchValueIsNullOrOutOfRange()
{
// Setup
var builder = StatementBuilderMapper.Get <SQLiteConnection>();
// Act
builder.CreateBatchQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name"),
0,
-1,
OrderField.Parse(new { Id = Order.Ascending }));
}
public void ThrowExceptionOnPostgreSqlStatementBuilderCreateQueryIfOrderFieldsAreNotPresentAtTheFields()
{
// Setup
var builder = StatementBuilderMapper.Get <NpgsqlConnection>();
// Act
builder.CreateQuery(new QueryBuilder(),
"Table",
Field.From("Id", "Name", "Address"),
null,
OrderField.Parse(new { Id = Order.Descending, SSN = Order.Ascending }),
null,
null);
}
public IList <EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
var page = skip / take;
return(BatchQuery(page,
take,
orderBy,
e => e.LastName == lastName).AsList());
}
public void TestDescending()
{
// Prepare
var orderBy = new { Id = Order.Descending };
// Act
var orderField = OrderField.Parse(orderBy);
// Assert
Assert.IsNotNull(orderField);
Assert.AreEqual(1, orderField.Count());
Assert.AreEqual("Id", orderField.First().Name);
Assert.AreEqual(Order.Descending, orderField.First().Order);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheOrderFieldIsNotCovered()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new { Id = Order.Ascending, Field1 = Order.Ascending });
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
}