/// <summary>
/// 查询所有的post，同时填充对应的category和tags
/// </summary>
/// <param name="pageNo">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns></returns>
public IList<Post> Posts(int pageNo, int pageSize)
{
var subQuery = new SqlLam<Post>()
.Where(p => p.Published)
.OrderByDescending(p => p.PostedOn)
.Select(p => p)
.Join<Category>((p, c) => p.Category == c.CategoryId)
.Select(t => t);
var posts = CONN.SQLQuery<Post, Category, Post>(subQuery.QueryStringPage(pageSize, pageNo),
(p, c) => { p.BlogCategory = c; p.Tags = GetTags(p); return p; }, subQuery.QueryParameters, splitOn: "CategoryId");
return posts.ToList();
//var subQuery = new SqlLam<Category>()
// .Select(c => c).Join<Post>((c, p) => c.CategoryId == p.Category)
// .Where(p => p.Published)
// .OrderByDescending(p => p.PostedOn)
// .Select(p => p);
//var posts = CONN.SQLQuery<Category, Post, Post>(subQuery.QueryStringPage(pageSize, pageNo),
// (c, p) =>
// {
// p.BlogCategory = c;
// p.Tags=GetTags(p);
// return p;
// }, subQuery.QueryParameters, splitOn: "Id");
//return posts.ToList();
}
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 SqlLam <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 SubmitForm(RoleEntity roleEntity, List <RoleAuthorizeEntity> roleAuthorizeEntitys, string keyValue)
{
using (var db = base.Connection)
{
var tran = db.BeginTransaction();
try
{
if (!string.IsNullOrEmpty(keyValue))
{
var sql = base.GetSqlLam().Update(roleEntity).Where(m => m.F_Id == keyValue);
db.Execute(sql.SqlString, sql.Parameters, tran);
}
else
{
roleEntity.F_Category = 1;
var sql = base.GetSqlLam().Insert(roleEntity);
db.Execute(sql.SqlString, sql.Parameters, tran);
}
var query = new SqlLam <RoleAuthorizeEntity>().Delete(m => m.F_ObjectId == roleEntity.F_Id);
db.Execute(query.SqlString, query.Parameters, tran);
query = new SqlLam <RoleAuthorizeEntity>().Insert(roleAuthorizeEntitys);
db.Execute(query.SqlString, query.Parameters, tran);
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}
public SqlLam <TReturn> GetSqlLam <TReturn>(string aliasName = "a", bool clearSql = true)
{
var sql = new SqlLam <TReturn>(aliasName, ProviderType.SQLServer2005);
sql.ClearSql = clearSql;
return(sql);
}
/// <summary>
/// 分组
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamGroupBy()
{
Guid g = Guid.NewGuid();
SqlLam <Users> sql = new SqlLam <Users>(adapter).GroupBy(m => new { m.Id, m.Name }).Select(m => m.Id, m => m.Name).Sum(m => m.Money, "AllMoney");
return(sql);
}
/// <summary>
/// 条件查询
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamWhere()
{
Guid g = Guid.NewGuid();
SqlLam <Users> sql = new SqlLam <Users>(adapter).Where(m => m.Id > 100).Select(m => m.Id, m => m.Name);
return(sql);
}
/// <summary>
/// Join
/// </summary>
/// <returns></returns>
public SqlLam <Departments> GetSqlLamJoin()
{
Guid g = Guid.NewGuid();
var sql = new SqlLam <Users>(adapter).Select(m => m.Name, m => m.Money).Where(m => m.Sex == 1).Join <Departments>((u, d) => d.Id == u.DepartmentId).Select(d => d).Where(d => d.Id > 100);
return(sql);
}
/// <summary>
/// 排序
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamOrderBy()
{
Guid g = Guid.NewGuid();
SqlLam <Users> sql = new SqlLam <Users>(adapter).OrderBy(m => new { m.Id }).OrderByDescending(m => m.Money);
return(sql);
}
/// <summary>
/// is null and in not null
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamNull()
{
Guid g = Guid.NewGuid();
var sql = new SqlLam <Users>(adapter).Where(m => m.Enaled != null).And(m => m.DepartmentId == null);
return(sql);
}
public void SubmitForm(UserEntity userEntity, UserLogOnEntity userLogOnEntity, string keyValue)
{
using (var db = base.Connection)
{
var tran = db.BeginTransaction();
try
{
if (!string.IsNullOrEmpty(keyValue))
{
var sql = base.GetSqlLam().Update(userEntity).Where(m => m.F_Id == keyValue);
db.Execute(sql.SqlString, sql.Parameters, tran);
}
else
{
userLogOnEntity.F_Id = userEntity.F_Id;
userLogOnEntity.F_UserId = userEntity.F_Id;
userLogOnEntity.F_UserSecretkey = Md5.md5(Common.CreateNo(), 16).ToLower();
userLogOnEntity.F_UserPassword = Md5.md5(DESEncrypt.Encrypt(Md5.md5(userLogOnEntity.F_UserPassword, 32).ToLower(), userLogOnEntity.F_UserSecretkey).ToLower(), 32).ToLower();
var sql = base.GetSqlLam().Insert(userEntity);
db.Execute(sql.SqlString, sql.Parameters, tran);
var query = new SqlLam <UserLogOnEntity>().Insert(userLogOnEntity);
db.Execute(query.SqlString, query.Parameters, tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}
public static PagedResult <T> PagedQueryWithAction <T>(this IDbConnection db, int pageSize, int pageNumber, Action <SqlLam <T> > action = null) where T : class
{
var sqllam = new SqlLam <T>(db.GetAdapter());
if (action != null)
{
action(sqllam);
}
var countSqlam = Clone(sqllam).Count();
var countRet = db.Query <int>(countSqlam.SqlString, countSqlam.Parameters).FirstOrDefault();
//var sqlString = sqllam.SqlString;
//var param = sqllam.Parameters;
//string parameterString = GetParameterString(sqllam.Parameters);
var sqlstring = sqllam.QueryPage(pageSize, pageNumber);
var retlist = db.Query <T>(sqlstring, sqllam.Parameters);
// return new Tuple<IEnumerable<T>, int>(retlist,countRet);
return(new PagedResult <T>(retlist, countRet, pageSize, pageNumber));
}
public void GetEntitiesWithOneToManyRelationship()
{
var query = new SqlLam <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)
{
Assert.Greater(category.Products.Count, 0);
foreach (var product in category.Products)
{
Assert.AreEqual(category.CategoryId, product.CategoryId);
}
}
}
/// <summary>
/// Join 2
/// </summary>
/// <returns></returns>
public SqlLam <UserDepartment> GetSqlLamJoin2()
{
Guid g = Guid.NewGuid();
var depart = new SqlLam <Departments>(adapter);
var sql = new SqlLam <Users>(adapter).Where(m => m.Money == 1).Join <Departments, int, UserDepartment>(depart, u => u.DepartmentId, d => d.Id, null);
return(sql);
}
/// <summary>
/// Update
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamUpdate()
{
Users u = new Users();
u.Sex = 2;
var sql = new SqlLam <Users>(adapter).Update(u).UseEntityProperty(false).Where(m => m.Name == "李四");
return(sql);
}
/// <summary>
/// Insert 2
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamInsert2()
{
Users u = new Users();
u.Sex = 2;
var sql = new SqlLam <Users>(adapter).Insert(new { Sex = u.Sex, Id = 100 }, true);
return(sql);
}
public OrderByFinder(SqlLam <TDto> sqlQuery)
{
if (sqlQuery == null)
{
throw new ArgumentNullException(nameof(sqlQuery));
}
_sqlQuery = sqlQuery;
}
public void SelectEntityCount()
{
var query = new SqlLam <Product>()
.SelectCount(p => p.ProductId);
var resultCount = Connection.Query <int>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(77, resultCount);
}
public void FindByGuidFake()
{
var fakeGuid = new FakeGuid();
var query = new SqlLam <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);
}
/// <summary>
/// in and not in 2
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamInAndNotIn2()
{
Guid g = Guid.NewGuid();
var names = new SqlLam <Departments>(adapter).Where(m => m.Id > 0).Select(m => m.Name);
var ids = new SqlLam <Departments>(adapter).Where(m => m.Name.StartsWith("A")).Select(m => m.Id);
var query = new SqlLam <Users>(adapter).WhereIsIn(m => m.Name, names).WhereNotIn(m => m.Id, ids).And(m => m.Sex == 1);
return(query);
}
public void Do(ProviderType type)
{
string fileName = "Query";
int count = 1;
SqlLam <Area> sql = new SqlLam <Area>("u", type);
sql.Type = ProviderType.Oracle;
GlobalConfig.UseDb(ProviderType.Oracle);
Log.WriteLog(count, fileName, "生成最简单的SQL", "SqlLam<Area> sql = new SqlLam<Area>();", sql);
count++;
sql = new SqlLam <Area>("u");
Log.WriteLog(count, fileName, "带别名的简单SQL", "SqlLam<Area> sql = new SqlLam<Area>(\"u\");", sql);
count++;
sql.As("u");
Log.WriteLog(count, fileName, "带别名的简单SQL2,和上面效果一样", "SqlLam<Area> sql = new SqlLam<Area>();\r\nsql.As(\"u\");", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100);
Log.WriteLog(count, fileName, "SQL TOP * ", "sql.Top(100);", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100, true);
Log.WriteLog(count, fileName, "SQL TOP * 带 percent ", "sql.Top(100, true);", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => m.AreaCode);
Log.WriteLog(count, fileName, "SQL Select 只查一列 ", "sql.Select(m => m.F_CreatorUserId);", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => new { m.AreaCode, m.AreaId, m.AreaName });
Log.WriteLog(count, fileName, "SQL Select 查多列 ", "sql.Select(m => m.F_CreatorUserId);", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100).Select(m => new { m.AreaCode, m.AreaId, m.AreaName });
Log.WriteLog(count, fileName, "SQL TOP 其他列 先写 TOP 再写 Select", "sql.Top(100).Select(m => new { m.F_CreatorUserId, m.F_DeleteMark, m.F_EnCode });", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => new { m.AreaCode, m.AreaId, m.AreaName }).Top(100);
Log.WriteLog(count, fileName, "SQL TOP 其他列 先写 Select 再写 TOP, 其结果一样", "sql.Top(100).Select(m => new { m.F_CreatorUserId, m.F_DeleteMark, m.F_EnCode });", sql);
count++;
sql.Clear();
sql.As("a");
sql.Where(m => !string.IsNullOrEmpty(m.AreaCode));
Log.WriteLog(count, fileName, "SQL 实现 IsNullOrEmpty 方法", "sql.Where(m => string.IsNullOrEmpty(m.AreaCode));", sql);
}
public void SelectRestrictedEntityCount()
{
var query = new SqlLam <Product>()
.SelectCount(p => p.ProductId)
.Where(p => p.ReorderLevel == 25);
var resultCount = Connection.Query <int>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(12, resultCount);
}
/// <summary>
/// Update 2
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamUpdate2()
{
Users u = new Users();
u.Sex = 3;
var sql = new SqlLam <Users>(adapter).Update(new { Sex = u.Sex, Key = "abcdefg", Name = "李四" }).Where(m => m.Id == 2);
int count = DBHelper.Excute(sql.SqlString, sql.Parameters);
return(sql);
}
public void QueryStringTest()
{
const string productName = "Tofu";
SqlLamBase._defaultAdapter = new UmbracoQueryAdapter();
var query = new SqlLam <Profile>(p => p.ProfileDescription == productName);
var sql = query.QueryString;
Console.WriteLine();
}
public ActionResult List()
{
SqlLam <UserEntity> sql = new SqlLam <UserEntity>().Where(u => u.F_Id == "a" && u.F_NickName == "a");
sql.And(u => u.F_MobilePhone == "123").OrderBy(u => u.F_RoleId);
var data = new { SQL = sql.QueryPage(10, 2), Parameters = sql.Parameters };
return(Json(data, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Delete
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamDelete()
{
Users u = new Users();
u.Sex = 2;
var sql = new SqlLam <Users>(adapter).Delete(m => m.Id == 0);
int count = DBHelper.Excute(sql.SqlString, sql.Parameters);
return(sql);
}
/// <summary>
/// in and not in
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamInAndNotIn()
{
Guid g = Guid.NewGuid();
string[] names = new string[] { "A", "B" };
object[] ids = new object[] { 1, 2, 3 };
var query = new SqlLam <Users>(adapter).WhereIsIn(m => m.Name, names).WhereNotIn(m => m.Id, ids);
return(query);
}
public void FindByStringEquality()
{
const string name = "Tofu";
var query = new SqlLam <Product>(p => p.ProductName.Equals(name));
var result = Connection.Query <Product>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(name, result.ProductName);
}
public void FindByStringEquality()
{
const string name = "Tofu";
var query = new SqlLam<Product>(p => p.ProductName.Equals(name));
var result = Connection.Query<Product>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(name, result.ProductName);
}
public void SelectSum()
{
const decimal sum = 2222.71M;
var query = new SqlLam <Product>()
.SelectSum(p => p.UnitPrice);
var results = Connection.Query <decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(sum, results);
}
public RepositoryBase()
{
client = new DbClient(DbConfig.Default);
sql = this.GetSqlLam <T>();
log = LogFactory.GetLogger();
client.AfterDb = (s, ts) => WriteLog(s, null, ts);
client.Error = (s, e) => WriteLog(s, e, client.GetElapsed());
watch = new Stopwatch();
}
//
// GET: /Test/
public ActionResult Index()
{
//SqlBuilder buider = new SqlBuilder();
//var template = buider.AddTemplate("select age , name,/**select**/ from tablea as a /**leftjoin**/ /**where**/ /**orderby**/");
//buider = buider.Select("abc").Where("age>@age and name=@name", new { age = 100, name = "张三" }).Where(" sex='男' ").LeftJoin(" tableb as b on a.id=b.id ").OrderBy("id, name desc");
SqlLam <UserEntity> sql = new SqlLam <UserEntity>().Where(m => m.F_Id == "1" && m.F_NickName.Contains("roc")).Select(m => new { m.F_ManagerId, m.F_Id, m.F_IsAdministrator });
var data = new { SQL = sql.SqlString, Parameters = sql.Parameters };
return(Json(data, JsonRequestBehavior.AllowGet));
}
public void FindByFieldValue()
{
const string productName = "Tofu";
var query = new SqlLam<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 FindByComplexQuery2Negated()
{
const int expectedResultCount = 13;
var query = new SqlLam <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 FindByListOfValuesNegated()
{
var reorderLevels = new object[] { 5, 15, 25 };
var query = new SqlLam<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 FindByJoinedEntityListOfValues()
{
var categoryNames = new object[] { "Beverages", "Condiments", "Seafood" };
var categoryIds = new[] { 1, 2, 8 };
var query = new SqlLam<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 FindByJoinedEntityValue()
{
const string categoryName = "Beverages";
const int categoryId = 1;
var query = new SqlLam<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 FindByStringSuffix()
{
const string suffix = "ld";
var query = new SqlLam<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 FindByStringPart()
{
const string part = "ge";
var query = new SqlLam<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 FindBySubQuery()
{
var productNames = new object[] { "Konbu", "Tofu", "Pavlova" };
var subQuery = new SqlLam<Product>()
.WhereIsIn(p => p.ProductName, productNames)
.Select(p => p.ProductId);
var query = new SqlLam<Product>()
.WhereIsIn(p => p.ProductId, subQuery);
var results = Connection.Query<Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(3, results.Count);
Assert.IsTrue(results.All(p => productNames.Contains(p.ProductName)));
}
public void OrderEntitiesByJoinedEntityField()
{
var categoryQuery = new SqlLam<Category>();
var categories = Connection.Query<Category>(categoryQuery.QueryString).ToDictionary(k => k.CategoryId);
var query = new SqlLam<Product>()
.Join<Category>((p, c) => p.CategoryId == c.CategoryId)
.OrderBy(c => c.CategoryName);
var results = Connection.Query<Product>(query.QueryString, query.QueryParameters).ToList();
for (int i = 1; i < results.Count; ++i)
{
Assert.IsTrue(String.CompareOrdinal(
categories[results[i - 1].CategoryId].CategoryName,
categories[results[i].CategoryId].CategoryName)
<= 0);
}
}
public void PaginateOverResults()
{
const int reorderLevel = 0;
const int pageSize = 5;
const int numberOfPages = 5;
const int lastPageSize = 4;
var query = new SqlLam<Product>(p => p.ReorderLevel == reorderLevel)
.OrderBy(p => p.ProductName);
for(int page = 1; page < numberOfPages; ++page)
{
var results = Connection.Query<Product>(query.QueryStringPage(pageSize, page), query.QueryParameters).ToList();
Assert.AreEqual(pageSize, results.Count);
Assert.IsTrue(results.All(p => p.ReorderLevel == reorderLevel));
}
var lastResults = Connection.Query<Product>(query.QueryStringPage(pageSize, numberOfPages), query.QueryParameters).ToList();
Assert.AreEqual(lastPageSize, lastResults.Count);
Assert.IsTrue(lastResults.All(p => p.ReorderLevel == reorderLevel));
}
public void SelectSum()
{
const decimal sum = 2222.71M;
var query = new SqlLam<Product>()
.SelectSum(p => p.UnitPrice);
var results = Connection.Query<decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(sum, results);
}
public void SelectWithNew()
{
const string productName = "Tofu";
var query = new SqlLam<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();
Assert.NotNull(results.ProductId);
Assert.NotNull(results.ProductName);
Assert.NotNull(results.CategoryId);
Assert.NotNull(results.ReorderLevel);
Assert.NotNull(results.UnitPrice);
Assert.Null(results.EnglishName);
}
public void SelectMinimumValue()
{
const decimal minimumValue = 2.5M;
var query = new SqlLam<Product>()
.SelectMin(p => p.UnitPrice);
var results = Connection.Query<decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(minimumValue, results);
}
public void SelectAverageValue()
{
const decimal averageValue = 28.8663M;
var query = new SqlLam<Product>()
.SelectAverage(p => p.UnitPrice);
var results = Connection.Query<decimal>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(averageValue, results);
}
public void FindByComplexQuery2Negated()
{
const int expectedResultCount = 13;
var query = new SqlLam<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 FindByMethodCall()
{
var product = new Product()
{
ProductId = 17
};
var query = new SqlLam<Product>(p => p.ProductId == product.GetProductId());
var result = Connection.Query<Product>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(product.ProductId, result.ProductId);
}
public void SelectRestrictedEntityCount()
{
var query = new SqlLam<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 SelectGroupedCounts()
{
var groupSizes = new[] {24, 8, 7, 10, 8, 12, 8};
var query = new SqlLam<Product>()
.SelectCount(p => p.ProductId)
.GroupBy(p => p.ReorderLevel)
.OrderBy(p => p.ReorderLevel);
var results = Connection.Query<int>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(groupSizes.Length, results.Count);
for (int i = 0; i < groupSizes.Length; ++i)
{
Assert.AreEqual(groupSizes[i], results[i]);
}
}
public void OrderEntitiesByFieldDescending()
{
var query = new SqlLam<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 SelectEntityCount()
{
var query = new SqlLam<Product>()
.SelectCount(p => p.ProductId);
var resultCount = Connection.Query<int>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(77, resultCount);
}
public void SelectAllFields()
{
const int productId = 14;
var query = new SqlLam<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 SelectField()
{
const int productId = 14;
var query = new SqlLam<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 FindByMemberAccessAndMethodCall()
{
var category = new Category()
{
CategoryId = 8
};
var product = new Product()
{
Category = category
};
var query = new SqlLam<Category>(c => c.CategoryId == product.Category.GetCategoryId());
var result = Connection.Query<Category>(query.QueryString, query.QueryParameters).Single();
Assert.AreEqual(category.CategoryId, result.CategoryId);
}
public void TopTenResults()
{
const int reorderLevel = 0;
const int pageSize = 10;
var query = new SqlLam<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 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 SqlLam<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 FindByComplexQuery3()
{
const int expectedNumberOfResults = 16;
const int reorderLevel = 0;
var query = new SqlLam<Product>(p => !p.Discontinued && p.ReorderLevel == reorderLevel);
var result = Connection.Query<Product>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(expectedNumberOfResults, result.Count);
foreach (var product in result)
{
Assert.AreEqual(false, product.Discontinued);
Assert.AreEqual(reorderLevel, product.ReorderLevel);
}
}
public void SelectDistinctValues()
{
var allValues = new[] {0, 5, 10, 15, 20, 25, 30};
var query = new SqlLam<Product>()
.SelectDistinct(p => p.ReorderLevel)
.OrderBy(p => p.ReorderLevel);
var results = Connection.Query<short>(query.QueryString, query.QueryParameters).ToList();
Assert.AreEqual(allValues.Length, results.Count);
for (int i = 0; i < allValues.Length; ++i)
{
Assert.AreEqual(allValues[i], results[i]);
}
}
public void FindByComplexQuery2Flipped()
{
const int expectedResultCount = 2;
var expectedNames = new[] { "Nancy", "Margaret" };
var query = new SqlLam<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);
}
}
public void FindByMemberComparison()
{
const int expectedNumberOfResults = 55;
var query = new SqlLam<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)
{
Assert.Less(order.RequiredDate, order.ShippedDate);
}
}