예제 #1
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 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);
                }
            }
        }
예제 #3
0
        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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
        }
예제 #9
0
        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);
        }
예제 #10
0
        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);
        }
예제 #11
0
        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);
        }
예제 #12
0
        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);
        }
예제 #13
0
        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)));
        }
예제 #14
0
        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());
        }
예제 #15
0
        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);
            }
        }
예제 #16
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);
        }
예제 #17
0
        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);
        }
예제 #18
0
        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);
            }
        }
예제 #19
0
        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));
        }
예제 #21
0
        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)));
        }
예제 #23
0
        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)
            {
            }
        }
예제 #24
0
        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));
            }
        }
예제 #25
0
        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));
            }
        }
예제 #26
0
        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);
            }
        }
예제 #27
0
        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)
            {
            }
        }
예제 #28
0
        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)));
        }
예제 #29
0
        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();
        }
예제 #30
0
        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);
            }
        }