Beispiel #1
0
        /// <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();

        }
Beispiel #2
0
        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));
            }
        }
Beispiel #3
0
        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();
                }
            }
        }
Beispiel #4
0
        public SqlLam <TReturn> GetSqlLam <TReturn>(string aliasName = "a", bool clearSql = true)
        {
            var sql = new SqlLam <TReturn>(aliasName, ProviderType.SQLServer2005);

            sql.ClearSql = clearSql;
            return(sql);
        }
Beispiel #5
0
        /// <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);
        }
Beispiel #6
0
        /// <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);
        }
Beispiel #7
0
        /// <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);
        }
Beispiel #8
0
        /// <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);
        }
Beispiel #9
0
        /// <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);
        }
Beispiel #10
0
        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));
        }
Beispiel #12
0
        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);
                }
            }
        }
Beispiel #13
0
        /// <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);
        }
Beispiel #14
0
        /// <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);
        }
Beispiel #15
0
        /// <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);
        }
Beispiel #16
0
        public OrderByFinder(SqlLam <TDto> sqlQuery)
        {
            if (sqlQuery == null)
            {
                throw new ArgumentNullException(nameof(sqlQuery));
            }

            _sqlQuery = sqlQuery;
        }
Beispiel #17
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);
        }
Beispiel #18
0
        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);
        }
Beispiel #19
0
        /// <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);
        }
Beispiel #20
0
        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);
        }
Beispiel #21
0
        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);
        }
Beispiel #22
0
        /// <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));
        }
Beispiel #25
0
        /// <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);
        }
Beispiel #26
0
        /// <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);
        }
Beispiel #27
0
        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);
        }
Beispiel #29
0
        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);
        }
Beispiel #30
0
        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);
        }
Beispiel #33
0
        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);
            }
        }