public void FindByComplexQuery4()
{
var dateTime1 = new DateTime(1900, 1, 1);
var dateTime2 = new DateTime(1950, 1, 1);
var dateTime3 = new DateTime(1970, 1, 1);
var dateTime4 = new DateTime(2000, 1, 1);
const int expectedNumberOfResults = 5;
var query =
new SqlLambda <Employee>(e =>
(e.BirthDate > dateTime1 && e.BirthDate < dateTime2)
||
(e.BirthDate > dateTime3 && e.BirthDate < dateTime4));
var result = Connection.Query <Employee>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedNumberOfResults, result.Count);
foreach (var employee in result)
{
Assert.IsTrue((employee.BirthDate > dateTime1 && employee.BirthDate < dateTime2) ||
(employee.BirthDate > dateTime3 && employee.BirthDate < dateTime4));
}
}
public void GetEntitiesWithOneToManyRelationship()
{
var query = new SqlLambda <Category>()
.Select(c => c.CategoryName)
.Join <Product>((c, p) => c.CategoryId == p.CategoryId)
.Select(p => p.CategoryId, p => p.ProductId, p => p.ProductName, p => p.EnglishName, p => p.ReorderLevel, p => p.UnitPrice);
var result = new Dictionary <int, Category>();
Connection.Query <Category, Product, Category>(
query.QueryString,
(category, product) =>
{
if (!result.ContainsKey(product.CategoryId))
{
category.CategoryId = product.CategoryId;
category.Products = new List <Product>();
result.Add(category.CategoryId, category);
}
product.Category = result[product.CategoryId];
result[product.CategoryId].Products.Add(product);
return(category);
},
splitOn: query.SplitColumns[0]);
Assert.AreEqual(8, result.Count);
foreach (var category in result.Values)
{
foreach (var product in category.Products)
{
Assert.AreEqual(category.CategoryId, product.CategoryId);
}
}
}
public void FindByGuidFake()
{
var fakeGuid = new FakeGuid();
var query = new SqlLambda <FakeGuid>(p => p.Id == fakeGuid.Id.Value);
Assert.AreEqual("[FakeGuid].[Id] = @Param1", query.SqlBuilder.WhereConditions.First());
Assert.AreEqual(fakeGuid.Id.Value, query.QueryParameters.First().Value);
}
public void SelectEntityCount()
{
var query = new SqlLambda <Product>()
.SelectCount(p => p.ProductId);
var resultCount = Connection.Query <int>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(77, resultCount);
}
public void FindByStringEquality()
{
const string name = "Tofu";
var query = new SqlLambda <Product>(p => p.ProductName.Equals(name));
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(name, result.ProductName);
}
public void SelectRestrictedEntityCount()
{
var query = new SqlLambda <Product>()
.SelectCount(p => p.ProductId)
.Where(p => p.ReorderLevel == 25);
var resultCount = Connection.Query <int>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(12, resultCount);
}
public void SelectAllFields()
{
const int productId = 14;
var query = new SqlLambda <Product>(p => p.ProductId == productId)
.Select(p => p);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(1, results.Count);
}
public void FindByComplexQuery2Negated()
{
const int expectedResultCount = 13;
var query = new SqlLambda <Employee>(p => !(p.City == "Seattle" || p.City == "Redmond") || p.Title != "Sales Representative")
.OrderByDescending(p => p.FirstName);
var results = Connection.Query <Employee>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedResultCount, results.Count);
}
public void SelectSum()
{
const decimal sum = 2222.71M;
var query = new SqlLambda <Product>()
.SelectSum(p => p.UnitPrice);
var results = Connection.Query <decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(sum, results);
}
public void SelectAverageValue()
{
const decimal averageValue = 28.8663M;
var query = new SqlLambda <Product>()
.SelectAverage(p => p.UnitPrice);
var results = Connection.Query <decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(averageValue, results);
}
public void SelectMinimumValue()
{
const decimal minimumValue = 2.5M;
var query = new SqlLambda <Product>()
.SelectMin(p => p.UnitPrice);
var results = Connection.Query <decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(minimumValue, results);
}
public void FindByFieldValue()
{
const string productName = "Tofu";
var query = new SqlLambda <Product>(p => p.ProductName == productName);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual(productName, results.First().ProductName);
}
public void FindByListOfValuesNegated()
{
var reorderLevels = new object[] { 5, 15, 25 };
var query = new SqlLambda <Product>()
.WhereNotIn(p => p.ReorderLevel, reorderLevels);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(47, results.Count);
Assert.IsTrue(results.All(p => !reorderLevels.Contains(p.ReorderLevel)));
}
public void SelectField()
{
const int productId = 14;
var query = new SqlLambda <Product>(p => p.ProductId == productId)
.Select(p => p.UnitPrice);
var results = Connection.Query <decimal>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual(23.25, results.First());
}
public void OrderEntitiesByFieldDescending()
{
var query = new SqlLambda <Category>()
.OrderByDescending(p => p.CategoryName);
var results = Connection.Query <Category>(query.QueryString, query.QueryParameters).ToList();
for (int i = 1; i < results.Count; ++i)
{
Assert.IsTrue(String.CompareOrdinal(results[i - 1].CategoryName, results[i].CategoryName) >= 0);
}
}
public void FindByMethodCall()
{
var product = new Product()
{
ProductId = 17
};
var query = new SqlLambda <Product>(p => p.ProductId == product.GetProductId());
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(product.ProductId, result.ProductId);
}
public void FindByNullableField()
{
var product = new Product()
{
ReorderLevel = 5
};
var query = new SqlLambda <Product>(p => p.NullableReorderLevel == product.NullableReorderLevel.Value);
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(8, result.Count);
}
public void FindByNotNull()
{
var query = new SqlLambda <Order>(o => o.ShipRegion != null);
var result = Connection.Query <Order>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(407, result.Count);
foreach (var order in result)
{
Assert.IsTrue(order.ShipRegion != null);
}
}
public void TopTenResults()
{
const int reorderLevel = 0;
const int pageSize = 10;
var query = new SqlLambda <Product>(p => p.ReorderLevel == reorderLevel)
.OrderBy(p => p.ProductName);
var results = Connection.Query <Product>(query.QueryStringPage(pageSize), query.QueryParameters).ToList();
Assert.AreEqual(pageSize, results.Count);
Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel));
}
public void FindByJoinedEntityValue()
{
const string categoryName = "Beverages";
const int categoryId = 1;
var query = new SqlLambda <Product>()
.Join <Category>((p, c) => p.CategoryId == c.CategoryId)
.Where(c => c.CategoryName == categoryName);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(12, results.Count);
Assert.IsTrue(results.All(p => p.CategoryId == categoryId));
}
public void FindByDouble()
{
const double minUnitPrice = 10.5d;
var query = new SqlLambda <Product>(p => p.UnitPrice >= minUnitPrice);
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(63, result.Count);
foreach (var product in result)
{
}
}
public void FindByJoinedEntityListOfValues()
{
var categoryNames = new object[] { "Beverages", "Condiments", "Seafood" };
var categoryIds = new[] { 1, 2, 8 };
var query = new SqlLambda <Product>()
.Join <Category>((p, c) => p.CategoryId == c.CategoryId)
.WhereIsIn(c => c.CategoryName, categoryNames);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(36, results.Count);
Assert.IsTrue(results.All(p => categoryIds.Contains(p.CategoryId)));
}
public void FindByMemberComparison()
{
const int expectedNumberOfResults = 55;
var query = new SqlLambda <Order>(o => o.RequiredDate < o.ShippedDate);
var result = Connection.Query <Order>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedNumberOfResults, result.Count);
foreach (var order in result)
{
}
}
public void FindByStringPart()
{
const string part = "ge";
var query = new SqlLambda <Product>(p => p.ProductName.Contains(part));
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(9, result.Count);
foreach (var product in result)
{
Assert.IsTrue(product.ProductName.ToLower().Contains(part));
}
}
public void FindByStringSuffix()
{
const string suffix = "ld";
var query = new SqlLambda <Product>(p => p.ProductName.EndsWith(suffix));
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(2, result.Count);
foreach (var product in result)
{
Assert.IsTrue(product.ProductName.EndsWith(suffix));
}
}
public void FindByBoolean()
{
const int expectedNumberOfResults = 8;
var query = new SqlLambda <Product>(p => p.Discontinued);
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedNumberOfResults, result.Count);
foreach (var product in result)
{
Assert.AreEqual(true, product.Discontinued);
}
}
public void FindByDateTime()
{
var minBirthDate = new DateTime(1960, 1, 1);
const int expectedNumberOfResults = 5;
var query = new SqlLambda <Employee>(p => p.BirthDate < minBirthDate);
var result = Connection.Query <Employee>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedNumberOfResults, result.Count);
foreach (var employee in result)
{
}
}
public void FindBySubQueryNegated()
{
var productNames = new object[] { "Konbu", "Tofu", "Pavlova" };
var subQuery = new SqlLambda <Product>()
.WhereIsIn(p => p.ProductName, productNames)
.Select(p => p.ProductId);
var query = new SqlLambda <Product>()
.WhereNotIn(p => p.ProductId, subQuery);
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(74, results.Count);
Assert.IsTrue(results.All(p => !productNames.Contains(p.ProductName)));
}
public void SelectWithNew()
{
const string productName = "Tofu";
var query = new SqlLambda <Product>()
.Where(p => p.ProductName == productName)
.Select(p => new {
p.ProductId,
p.ProductName,
p.CategoryId,
p.ReorderLevel,
p.UnitPrice
});
var results = Connection.Query <Product>(query.QueryString, query.QueryParameters).Single();
}
public void FindByComplexQuery2Flipped()
{
const int expectedResultCount = 2;
var expectedNames = new[] { "Nancy", "Margaret" };
var query = new SqlLambda <Employee>(p => "Sales Representative" == p.Title && ("Seattle" == p.City || "Redmond" == p.City))
.OrderByDescending(p => p.FirstName);
var results = Connection.Query <Employee>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedResultCount, results.Count);
for (int i = 0; i < expectedResultCount; ++i)
{
Assert.AreEqual(expectedNames[i], results[i].FirstName);
}
}