public void OffsetFetchAndTotalRowCount()
{
var results =
DefineCommand <dynamic>(
@"SELECT Int, String
FROM (VALUES ('1', 'One'), ('2', 'Two'), ('3', 'Three'), ('4', 'Four')) AS SampleSet(Int, String)")
.SendNullValues(NullValueMode.FilterOnlyFull)
.WithResultsAs <dynamic>()
.Compile()
.WithTransforms(source => new[]
{
SqlServerTransforms.QueryWrapperWithTotalRowCount(source),
Transforms.WhereFilter(source, typeof(object)),
Transforms.OrderByExpression(),
SqlServerTransforms.OffsetFetch(source)
})
.Execute(Setup.DatabaseManager, null, new { RowCount = 2, PageNumber = 2, OrderBy = "Int" });
Assert.IsNotNull(results);
Assert.AreEqual(2, results.Count());
var first = results.First();
var last = results.Last();
Assert.AreEqual(4, first.TotalRows);
Assert.AreEqual("3", first.Int);
Assert.AreEqual("Three", first.String);
Assert.AreEqual("4", last.Int);
Assert.AreEqual("Four", last.String);
}
public void KeyValueWithWhereFilterAndOffsetFetch()
{
var results =
DefineCommand <KeyValuePair <string, string> >(
@"SELECT Int, String FROM ( VALUES ('1', 'One'), ('2', 'Two'), ('3', 'Three'), ('4', 'Four')) AS SampleSet(Int, String)")
.IncludeProperty(o => o.Key, parameter => parameter.ParameterName = "Int")
.IncludeProperty(o => o.Value, parameter => parameter.ParameterName = "String")
.SendNullValues(NullValueMode.FilterOnlyFull)
.WithResultsAs <KeyValuePair <string, string> >(result =>
{
result.MapPropertyToColumn(pair => pair.Key, "Int");
result.MapPropertyToColumn(pair => pair.Value, "String");
})
.Compile()
.WithTransforms(source => new[]
{
SqlServerTransforms.QueryWrapperWithTotalRowCount(source),
Transforms.WhereFilter(source, typeof(KeyValuePair <string, string>)),
Transforms.OrderByExpression(),
SqlServerTransforms.OffsetFetch(source)
})
.Execute(Setup.DatabaseManager,
new KeyValuePair <string, string>(null, "o"),
new { OrderBy = "Int DESC", RowCount = 2, PageNumber = 2 });
Assert.IsNotNull(results);
Assert.AreEqual(1, results.Count());
var first = results.First();
Assert.AreEqual("1", first.Key);
Assert.AreEqual("One", first.Value);
}
public void KeyValueWithWhereFilterAndOffsetFetch()
{
var results = CommandManager.Instance.DefineCommand <KeyValuePair <string, string> >(
@"SELECT Int, String FROM ( VALUES ('1', 'One'), ('2', 'Two'), ('3', 'Three'), ('4', 'Four')) AS SampleSet(Int, String)", CommandType.Text)
.IncludeProperty(o => o.Key, parameter => parameter.ParameterName = "Int")
.IncludeProperty(o => o.Value, parameter => parameter.ParameterName = "String")
.SendNullValues(NullValueMode.FilterOnlyFull)
.DefineResults <KeyValuePair <string, string> >()
.ForResults(expression =>
{
expression.ForProperty(pair => pair.Key,
configuration => configuration.UseAlias("Int"));
expression.ForProperty(pair => pair.Value,
configuration => configuration.UseAlias("String"));
})
.Realize()
.ApplyTransforms(source => new[]
{
SqlServerTransforms.QueryWrapperWithTotalRowCount(source),
Transforms.WhereFilter(source),
Transforms.OrderByExpression(),
SqlServerTransforms.OffsetFetch(source)
})
.Execute(Setup.DatabaseManager,
new KeyValuePair <string, string>(null, "o"),
new { OrderBy = "Int DESC", RowCount = 2, PageNumber = 2 });
Assert.IsNotNull(results);
Assert.AreEqual(1, results.Count());
var first = results.First();
Assert.AreEqual("1", first.Key);
Assert.AreEqual("One", first.Value);
}
public void KeyValueWithOffsetFetch()
{
var results =
DefineCommand <KeyValuePair <string, string> >(
@"SELECT Int, String
FROM (VALUES ('1', 'One'), ('2', 'Two'), ('3', 'Three'), ('4', 'Four')) AS SampleSet(Int, String)
ORDER BY Int")
.ExcludeProperty(o => o.Key)
.ExcludeProperty(o => o.Value)
.SendNullValues(NullValueMode.FilterOnlyFull)
.WithResultsAs <KeyValuePair <string, string> >(result =>
{
result.MapPropertyToColumn(pair => pair.Key, "Int");
result.MapPropertyToColumn(pair => pair.Value, "String");
})
.Compile()
.WithTransforms(source => new[]
{
SqlServerTransforms.OffsetFetch(source)
})
.Execute(Setup.DatabaseManager, default(KeyValuePair <string, string>), new { RowCount = 2, PageNumber = 2 });
Assert.IsNotNull(results);
Assert.AreEqual(2, results.Count());
var first = results.First();
var last = results.Last();
Assert.AreEqual("3", first.Key);
Assert.AreEqual("Three", first.Value);
Assert.AreEqual("4", last.Key);
Assert.AreEqual("Four", last.Value);
}
public void KeyValueWithOffsetFetch()
{
var results = CommandManager.Instance.DefineCommand <KeyValuePair <string, string> >(@"
SELECT Int, String
FROM (VALUES ('1', 'One'), ('2', 'Two'), ('3', 'Three'), ('4', 'Four')) AS SampleSet(Int, String)
ORDER BY Int", CommandType.Text)
.ExcludeProperty(o => o.Key)
.ExcludeProperty(o => o.Value)
.SendNullValues(NullValueMode.FilterOnlyFull)
.DefineResults <KeyValuePair <string, string> >()
.ForResults(expression =>
{
expression.ForProperty(pair => pair.Key,
configuration => configuration.UseAlias("Int"));
expression.ForProperty(pair => pair.Value,
configuration => configuration.UseAlias("String"));
})
.Realize()
.ApplyTransforms(source => new[]
{
SqlServerTransforms.OffsetFetch(source)
})
.Execute(Setup.DatabaseManager, default(KeyValuePair <string, string>), new { RowCount = 2, PageNumber = 2 });
Assert.IsNotNull(results);
Assert.AreEqual(2, results.Count());
var first = results.First();
var last = results.Last();
Assert.AreEqual("3", first.Key);
Assert.AreEqual("Three", first.Value);
Assert.AreEqual("4", last.Key);
Assert.AreEqual("Four", last.Value);
}
