public void AuthorUsesCases() { var ev = OrmLiteConfig.DialectProvider.SqlExpression <Author>(); using (var db = OpenDbConnection()) { int year = DateTime.Today.AddYears(-20).Year; var lastDay = new DateTime(year, 12, 31); int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); List <Author> result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay)); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Assert.AreEqual(expected, result.Count); Author a = new Author() { Birthday = lastDay }; result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= a.Birthday); Assert.AreEqual(expected, result.Count); // select authors from London, Berlin and Madrid : 6 expected = 6; //Sql.In can take params object[] var city = "Berlin"; ev.Where().Where(rn => Sql.In(rn.City, "London", "Madrid", city)); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => Sql.In(rn.City, new[] { "London", "Madrid", "Berlin" })); Assert.AreEqual(expected, result.Count); // select authors from Bogota and Cartagena : 7 expected = 7; //... or Sql.In can take List<Object> city = "Bogota"; List <Object> cities = new List <Object>(); cities.Add(city); cities.Add("Cartagena"); ev.Where().Where(rn => Sql.In(rn.City, cities)); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Assert.AreEqual(expected, result.Count); // select authors which name starts with A expected = 3; ev.Where().Where(rn => rn.Name.StartsWith("A")); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Name.StartsWith("A")); Assert.AreEqual(expected, result.Count); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; var name = "GARZON"; ev.Where().Where(rn => rn.Name.ToUpper().EndsWith(name)); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Name.ToUpper().EndsWith(name)); Assert.AreEqual(expected, result.Count); // select authors which name ends with garzon //A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters //in the string. //An underscore ("_") in the LIKE pattern matches any single character in the string. //Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). expected = 3; ev.Where().Where(rn => rn.Name.EndsWith("garzon")); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Name.EndsWith("garzon")); Assert.AreEqual(expected, result.Count); // select authors which name contains Benedict expected = 2; name = "Benedict"; ev.Where().Where(rn => rn.Name.Contains(name)); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Name.Contains("Benedict")); Assert.AreEqual(expected, result.Count); a.Name = name; result = db.Select <Author>(rn => rn.Name.Contains(a.Name)); Assert.AreEqual(expected, result.Count); // select authors with Earnings <= 50 expected = 3; var earnings = 50; ev.Where().Where(rn => rn.Earnings <= earnings); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Earnings <= 50); Assert.AreEqual(expected, result.Count); // select authors with Rate = 10 and city=Mexio expected = 1; city = "Mexico"; ev.Where().Where(rn => rn.Rate == 10 && rn.City == city); result = db.Select(ev); Assert.AreEqual(expected, result.Count); result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == "Mexico"); Assert.AreEqual(expected, result.Count); a.City = city; result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == a.City); Assert.AreEqual(expected, result.Count); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; var rate = 0; ev.Where().Where(rn => rn.Rate == rate).Update(rn => rn.Active); var rows = db.UpdateOnly(new Author() { Active = false }, ev); Assert.AreEqual(expected, rows); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where().Where(rn => !rn.Active); result = db.Select(ev); Assert.AreEqual(expected, result.Count); //update comment for City == null expected = 2; ev.Where().Where(rn => rn.City == null).Update(rn => rn.Comments); rows = db.UpdateOnly(new Author() { Comments = "No comments" }, ev); Assert.AreEqual(expected, rows); // delete where City is null expected = 2; rows = db.Delete(ev); Assert.AreEqual(expected, rows); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new { at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = db.Select(ev); Assert.AreEqual(expected, result.Count); var author = result.FirstOrDefault(); Assert.AreEqual("Claudia Espinel", author.Name); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = db.Select(ev); Assert.AreEqual(expected, result.Count); // and finally lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Assert.AreEqual("Claudia Espinel".ToUpper(), author.Name); ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), rn.Name), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Assert.AreEqual("Claudia Espinel".ToUpper(), author.Name); //paging : ev.Limit(0, 4);// first page, page size=4; result = db.Select(ev); author = result.FirstOrDefault(); Assert.AreEqual("Claudia Espinel".ToUpper(), author.Name); ev.Limit(4, 4);// second page result = db.Select(ev); author = result.FirstOrDefault(); Assert.AreEqual("Jorge Garzon".ToUpper(), author.Name); ev.Limit(8, 4);// third page result = db.Select(ev); author = result.FirstOrDefault(); Assert.AreEqual("Rodger Contreras".ToUpper(), author.Name); // select distinct.. ev.Limit().OrderBy(); // clear limit, clear order for postres ev.SelectDistinct(r => r.City); expected = 6; result = db.Select(ev); Assert.AreEqual(expected, result.Count); ev.Select(r => Sql.As(Sql.Max(r.Birthday), "Birthday")); result = db.Select(ev); var expectedResult = authors.Max(r => r.Birthday); Assert.AreEqual(expectedResult, result[0].Birthday); ev.Select(r => Sql.As(Sql.Max(r.Birthday), r.Birthday)); result = db.Select(ev); expectedResult = authors.Max(r => r.Birthday); Assert.AreEqual(expectedResult, result[0].Birthday); var r1 = db.Single(ev); Assert.AreEqual(expectedResult, r1.Birthday); var r2 = db.Scalar <Author, DateTime>(e => Sql.Max(e.Birthday)); Assert.AreEqual(expectedResult, r2); ev.Select(r => Sql.As(Sql.Min(r.Birthday), "Birthday")); result = db.Select(ev); expectedResult = authors.Min(r => r.Birthday); Assert.AreEqual(expectedResult, result[0].Birthday); ev.Select(r => Sql.As(Sql.Min(r.Birthday), r.Birthday)); result = db.Select(ev); expectedResult = authors.Min(r => r.Birthday); Assert.AreEqual(expectedResult, result[0].Birthday); ev.Select(r => new { r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), "Birthday") }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(ev); var expectedStringResult = "Berlin"; Assert.AreEqual(expectedStringResult, result[0].City); ev.Select(r => new { r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), r.Birthday) }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(ev); expectedStringResult = "Berlin"; Assert.AreEqual(expectedStringResult, result[0].City); r1 = db.Single(ev); Assert.AreEqual(expectedStringResult, r1.City); var expectedDecimal = authors.Max(e => e.Earnings); Decimal?r3 = db.Scalar <Author, Decimal?>(e => Sql.Max(e.Earnings)); Assert.AreEqual(expectedDecimal, r3.Value); var expectedString = authors.Max(e => e.Name); string r4 = db.Scalar <Author, String>(e => Sql.Max(e.Name)); Assert.AreEqual(expectedString, r4); var expectedDate = authors.Max(e => e.LastActivity); DateTime?r5 = db.Scalar <Author, DateTime?>(e => Sql.Max(e.LastActivity)); Assert.AreEqual(expectedDate, r5); var expectedDate51 = authors.Where(e => e.City == "Bogota").Max(e => e.LastActivity); DateTime?r51 = db.Scalar <Author, DateTime?>( e => Sql.Max(e.LastActivity), e => e.City == "Bogota"); Assert.AreEqual(expectedDate51, r51); try { var expectedBool = authors.Max(e => e.Active); bool r6 = db.Scalar <Author, bool>(e => Sql.Max(e.Active)); Assert.AreEqual(expectedBool, r6); } catch (Exception e) { //???? //if (dialect.Name == "PostgreSQL") // Console.WriteLine("OK PostgreSQL: " + e.Message); //else // Console.WriteLine("************** FAILED *************** " + e.Message); } // Tests for predicate overloads that make use of the expression visitor author = db.Single <Author>(q => q.Name == "Jorge Garzon"); try { author = db.Single <Author>(q => q.Name == "Does not exist"); Assert.Fail(); } catch { //"Expected exception thrown, OK? True" } author = db.Single <Author>(q => q.Name == "Does not exist"); Assert.IsNull(author); author = db.Single <Author>(q => q.City == "Bogota"); Assert.AreEqual("Angel Colmenares", author.Name); a.City = "Bogota"; author = db.Single <Author>(q => q.City == a.City); Assert.AreEqual("Angel Colmenares", author.Name); // count test var expectedCount = authors.Count(); long r7 = db.Scalar <Author, long>(e => Sql.Count(e.Id)); Assert.AreEqual(expectedCount, r7); expectedCount = authors.Count(e => e.City == "Bogota"); r7 = db.Scalar <Author, long>( e => Sql.Count(e.Id), e => e.City == "Bogota"); Assert.AreEqual(expectedCount, r7); ev.Update();// all fields will be updated // select and update expected = 1; var rr = db.Single <Author>(rn => rn.Name == "Luis garzon"); rr.City = "Madrid"; rr.Comments = "Updated"; ev.Where().Where(r => r.Id == rr.Id); // if omit, then all records will be updated rows = db.UpdateOnly(rr, ev); // == dbCmd.Update(rr) but it returns void Assert.AreEqual(expected, rows); expected = 0; ev.Where().Where(r => r.City == "Ciudad Gotica"); rows = db.UpdateOnly(rr, ev); Assert.AreEqual(expected, rows); expected = db.Select <Author>(x => x.City == "Madrid").Count; author = new Author() { Active = false }; rows = db.UpdateOnly(author, x => x.Active, x => x.City == "Madrid"); Assert.AreEqual(expected, rows); expected = db.Select <Author>(x => x.Active == false).Count; rows = db.Delete <Author>(x => x.Active == false); Assert.AreEqual(expected, rows); } }
private static void TestDialect(Dialect dialect) { Console.Clear(); Console.WriteLine("Testing expressions for Dialect {0}", dialect.Name); OrmLiteConfig.ClearCache(); OrmLiteConfig.DialectProvider = dialect.DialectProvider; SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>(); using (IDbConnection db = dialect.ConnectionString.OpenDbConnection()) { try { db.DropTable <Author>(); var tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(db, typeof(Author).Name); Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.FalseString, tableExists.ToString(), !tableExists ? "OK" : "************** FAILED ***************"); db.CreateTable <Author>(); tableExists = OrmLiteConfig.DialectProvider.DoesTableExist(db, typeof(Author).Name); Console.WriteLine("Expected:{0} Selected:{1} {2}", bool.TrueString, tableExists.ToString(), tableExists ? "OK" : "************** FAILED ***************"); db.DeleteAll <Author>(); Console.WriteLine("Inserting..."); DateTime t1 = DateTime.Now; db.InsertAll(authors); DateTime t2 = DateTime.Now; Console.WriteLine("Inserted {0} rows in {1}", authors.Count, t2 - t1); Console.WriteLine("Selecting....."); int year = DateTime.Today.AddYears(-20).Year; var lastDay = new DateTime(year, 12, 31); int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Console.WriteLine(ev.ToSelectStatement()); List <Author> result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); Author a = new Author() { Birthday = lastDay }; result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= a.Birthday); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors from London, Berlin and Madrid : 6 expected = 6; //Sql.In can take params object[] var city = "Berlin"; ev.Where(rn => Sql.In(rn.City, "London", "Madrid", city)); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => Sql.In(rn.City, new[] { "London", "Madrid", "Berlin" })); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors from Bogota and Cartagena : 7 expected = 7; //... or Sql.In can take List<Object> city = "Bogota"; List <Object> cities = new List <Object>(); cities.Add(city); cities.Add("Cartagena"); ev.Where(rn => Sql.In(rn.City, cities)); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name starts with A expected = 3; ev.Where(rn => rn.Name.StartsWith("A")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Name.StartsWith("A")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; var name = "GARZON"; ev.Where(rn => rn.Name.ToUpper().EndsWith(name)); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Name.ToUpper().EndsWith(name)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name ends with garzon //A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters //in the string. //An underscore ("_") in the LIKE pattern matches any single character in the string. //Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). expected = 3; ev.Where(rn => rn.Name.EndsWith("garzon")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Name.EndsWith("garzon")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors which name contains Benedict expected = 2; name = "Benedict"; ev.Where(rn => rn.Name.Contains(name)); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Name.Contains("Benedict")); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); a.Name = name; result = db.Select <Author>(rn => rn.Name.Contains(a.Name)); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors with Earnings <= 50 expected = 3; var earnings = 50; ev.Where(rn => rn.Earnings <= earnings); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Earnings <= 50); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors with Rate = 10 and city=Mexio expected = 1; city = "Mexico"; ev.Where(rn => rn.Rate == 10 && rn.City == city); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == "Mexico"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); a.City = city; result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == a.City); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; var rate = 0; ev.Where(rn => rn.Rate == rate).Update(rn => rn.Active); var rows = db.UpdateOnly(new Author() { Active = false }, ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where(rn => !rn.Active); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); //update comment for City == null expected = 2; ev.Where(rn => rn.City == null).Update(rn => rn.Comments); rows = db.UpdateOnly(new Author() { Comments = "No comments" }, ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // delete where City is null expected = 2; rows = db.Delete(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new { at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); Console.WriteLine(ev.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name ? "OK" : "************** FAILED ***************"); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // and finally lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), rn.Name), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); //paging : ev.Limit(0, 4); // first page, page size=4; result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Limit(4, 4); // second page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); ev.Limit(8, 4); // third page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); // select distinct.. ev.Limit().OrderBy(); // clear limit, clear order for postres ev.SelectDistinct(r => r.City); expected = 6; result = db.Select(ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Max(r.Birthday), "Birthday")); result = db.Select(ev); var expectedResult = authors.Max(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Max(r.Birthday), r.Birthday)); result = db.Select(ev); expectedResult = authors.Max(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday ? "OK" : "************** FAILED ***************"); var r1 = db.FirstOrDefault(ev); Console.WriteLine("FOD: Expected:{0} Selected {1} {2}", expectedResult, r1.Birthday, expectedResult == r1.Birthday ? "OK" : "************** FAILED ***************"); var r2 = db.GetScalar <Author, DateTime>(e => Sql.Max(e.Birthday)); Console.WriteLine("GetScalar DateTime: Expected:{0} Selected {1} {2}", expectedResult, r2, expectedResult == r2 ? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Min(r.Birthday), "Birthday")); result = db.Select(ev); expectedResult = authors.Min(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday? "OK" : "************** FAILED ***************"); ev.Select(r => Sql.As(Sql.Min(r.Birthday), r.Birthday)); result = db.Select(ev); expectedResult = authors.Min(r => r.Birthday); Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].Birthday, expectedResult == result[0].Birthday? "OK" : "************** FAILED ***************"); ev.Select(r => new{ r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), "Birthday") }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(ev); var expectedStringResult = "Berlin"; Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); ev.Select(r => new{ r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), r.Birthday) }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(ev); expectedStringResult = "Berlin"; Console.WriteLine("Expected:{0} Selected {1} {2}", expectedResult, result[0].City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); r1 = db.FirstOrDefault(ev); Console.WriteLine("FOD: Expected:{0} Selected {1} {2}", expectedResult, r1.City, expectedStringResult == result[0].City ? "OK" : "************** FAILED ***************"); var expectedDecimal = authors.Max(e => e.Earnings); Decimal?r3 = db.GetScalar <Author, Decimal?>(e => Sql.Max(e.Earnings)); Console.WriteLine("GetScalar decimal?: Expected:{0} Selected {1} {2}", expectedDecimal, r3.Value, expectedDecimal == r3.Value ? "OK" : "************** FAILED ***************"); var expectedString = authors.Max(e => e.Name); string r4 = db.GetScalar <Author, String>(e => Sql.Max(e.Name)); Console.WriteLine("GetScalar string?: Expected:{0} Selected {1} {2}", expectedString, r4, expectedString == r4 ? "OK" : "************** FAILED ***************"); var expectedDate = authors.Max(e => e.LastActivity); DateTime?r5 = db.GetScalar <Author, DateTime?>(e => Sql.Max(e.LastActivity)); Console.WriteLine("GetScalar datetime?: Expected:{0} Selected {1} {2}", expectedDate, r5, expectedDate == r5 ? "OK" : "************** FAILED ***************"); var expectedDate51 = authors.Where(e => e.City == "Bogota").Max(e => e.LastActivity); DateTime?r51 = db.GetScalar <Author, DateTime?>( e => Sql.Max(e.LastActivity), e => e.City == "Bogota"); Console.WriteLine("GetScalar datetime?: Expected:{0} Selected {1} {2}", expectedDate51, r51, expectedDate51 == r51 ? "OK" : "************** FAILED ***************"); try{ var expectedBool = authors.Max(e => e.Active); bool r6 = db.GetScalar <Author, bool>(e => Sql.Max(e.Active)); Console.WriteLine("GetScalar bool: Expected:{0} Selected {1} {2}", expectedBool, r6, expectedBool == r6 ? "OK" : "************** FAILED ***************"); } catch (Exception e) { if (dialect.Name == "PostgreSQL") { Console.WriteLine("OK PostgreSQL: " + e.Message); } else { Console.WriteLine("************** FAILED *************** " + e.Message); } } // Tests for predicate overloads that make use of the expression visitor Console.WriteLine("First author by name (exists)"); author = db.First <Author>(q => q.Name == "Jorge Garzon"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon", author.Name, "Jorge Garzon" == author.Name); try { Console.WriteLine("First author by name (does not exist)"); author = db.First <Author>(q => q.Name == "Does not exist"); Console.WriteLine("Expected exception thrown, OK? False"); } catch { Console.WriteLine("Expected exception thrown, OK? True"); } Console.WriteLine("First author or default (does not exist)"); author = db.FirstOrDefault <Author>(q => q.Name == "Does not exist"); Console.WriteLine("Expected:null ; OK? {0}", author == null); Console.WriteLine("First author or default by city (multiple matches)"); author = db.FirstOrDefault <Author>(q => q.City == "Bogota"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); a.City = "Bogota"; author = db.FirstOrDefault <Author>(q => q.City == a.City); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); // count test var expectedCount = authors.Count(); long r7 = db.GetScalar <Author, long>(e => Sql.Count(e.Id)); Console.WriteLine("GetScalar long: Expected:{0} Selected {1} {2}", expectedCount, r7, expectedCount == r7 ? "OK" : "************** FAILED ***************"); expectedCount = authors.Count(e => e.City == "Bogota"); r7 = db.GetScalar <Author, long>( e => Sql.Count(e.Id), e => e.City == "Bogota"); Console.WriteLine("GetScalar long: Expected:{0} Selected {1} {2}", expectedCount, r7, expectedCount == r7 ? "OK" : "************** FAILED ***************"); // more updates..... Console.WriteLine("more updates....................."); ev.Update(); // all fields will be updated // select and update expected = 1; var rr = db.FirstOrDefault <Author>(rn => rn.Name == "Luis garzon"); rr.City = "Madrid"; rr.Comments = "Updated"; ev.Where(r => r.Id == rr.Id); // if omit, then all records will be updated rows = db.UpdateOnly(rr, ev); // == dbCmd.Update(rr) but it returns void Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = 0; ev.Where(r => r.City == "Ciudad Gotica"); rows = db.UpdateOnly(rr, ev); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = db.Select <Author>(x => x.City == "Madrid").Count; author = new Author() { Active = false }; rows = db.UpdateOnly(author, x => x.Active, x => x.City == "Madrid"); Console.WriteLine("Expected:{0} Updated:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); expected = db.Select <Author>(x => x.Active == false).Count; rows = db.Delete <Author>(x => x.Active == false); Console.WriteLine("Expected:{0} Deleted:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); DateTime t3 = DateTime.Now; Console.WriteLine("Expressions test in: {0}", t3 - t2); Console.WriteLine("All test in : {0}", t3 - t1); } catch (Exception e) { Console.WriteLine(e.Message); } } Console.WriteLine("Press enter to return to main menu"); Console.ReadLine(); PaintMenu(); }
public static void Main(string[] args) { Console.WriteLine("Hello World!"); OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider(); SqlExpression <Author> ev = OrmLiteConfig.DialectProvider.SqlExpression <Author>(); using (IDbConnection db = "User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection()) { db.DropTable <Author>(); db.CreateTable <Author>(); db.DeleteAll <Author>(); List <Author> authors = new List <Author>(); authors.Add(new Author() { Name = "Demis Bellot", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 99.9m, Comments = "CSharp books", Rate = 10, City = "London" }); authors.Add(new Author() { Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 50.0m, Comments = "CSharp books", Rate = 5, City = "Bogota" }); authors.Add(new Author() { Name = "Adam Witco", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 80.0m, Comments = "Math Books", Rate = 9, City = "London" }); authors.Add(new Author() { Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(-23), Active = true, Earnings = 60.0m, Comments = "Cooking books", Rate = 10, City = "Bogota" }); authors.Add(new Author() { Name = "Libardo Pajaro", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 80.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Jorge Garzon", Birthday = DateTime.Today.AddYears(-28), Active = true, Earnings = 70.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Alejandro Isaza", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 70.0m, Comments = "Java books", Rate = 0, City = "Bogota" }); authors.Add(new Author() { Name = "Wilmer Agamez", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 30.0m, Comments = "Java books", Rate = 0, City = "Cartagena" }); authors.Add(new Author() { Name = "Rodger Contreras", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 90.0m, Comments = "CSharp books", Rate = 8, City = "Cartagena" }); authors.Add(new Author() { Name = "Chuck Benedict", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "CSharp books", Rate = 8, City = "London" }); authors.Add(new Author() { Name = "James Benedict II", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "Java books", Rate = 5, City = "Berlin" }); authors.Add(new Author() { Name = "Ethan Brown", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 45.0m, Comments = "CSharp books", Rate = 5, City = "Madrid" }); authors.Add(new Author() { Name = "Xavi Garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 75.0m, Comments = "CSharp books", Rate = 9, City = "Madrid" }); authors.Add(new Author() { Name = "Luis garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.0m, Comments = "CSharp books", Rate = 10, City = "Mexico" }); db.InsertAll(authors); // lets start ! // select authors born 20 year ago int year = DateTime.Today.AddYears(-20).Year; int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); List <Author> result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31))); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from London, Berlin and Madrid : 6 expected = 6; //Sql.In can take params object[] ev.Where(rn => Sql.In(rn.City, new object[] { "London", "Madrid", "Berlin" })); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from Bogota and Cartagena : 7 expected = 7; //... or Sql.In can take IList<Object> List <Object> cities = new List <Object>(); cities.Add("Bogota"); cities.Add("Cartagena"); ev.Where(rn => Sql.In(rn.City, cities)); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name starts with A expected = 3; ev.Where(rn => rn.Name.StartsWith("A")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; ev.Where(rn => rn.Name.ToUpper().EndsWith("GARZON")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with garzon ( no case sensitive ) expected = 3; ev.Where(rn => rn.Name.EndsWith("garzon")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name contains Benedict expected = 2; ev.Where(rn => rn.Name.Contains("Benedict")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Earnings <= 50 expected = 3; ev.Where(rn => rn.Earnings <= 50); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Rate = 10 and city=Mexio expected = 1; ev.Where(rn => rn.Rate == 10 && rn.City == "Mexico"); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; ev.Where(rn => rn.Rate == 0).Update(rn => rn.Active); var rows = db.UpdateOnly(new Author() { Active = false }, ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where(rn => !rn.Active); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); //update comment for City == null expected = 2; ev.Where(rn => rn.City == null).Update(rn => rn.Comments); rows = db.UpdateOnly(new Author() { Comments = "No comments" }, ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // delete where City is null expected = 2; rows = db.Delete(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new{ at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.WriteLine(ev.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); //paging : ev.Limit(0, 4); // first page, page size=4; result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); ev.Limit(4, 4); // second page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name); ev.Limit(8, 4); // third page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name); // select distinct.. ev.Limit(); // clear limit ev.SelectDistinct(r => r.City); expected = 6; result = db.Select(ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.ReadLine(); Console.WriteLine("Press Enter to continue"); } Console.WriteLine("This is The End my friend!"); }
/// <summary>Main entry-point for this application.</summary> /// <param name="args">Array of command-line argument strings.</param> public static void Main(string[] args) { Console.WriteLine("Hello World!"); Console.WriteLine("Join Test"); JoinTest.Test(); Console.WriteLine("Ignored Field Select Test"); IgnoredFieldSelectTest.Test(); Console.WriteLine("Count Test"); CountTest.Test(); OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance; SqlExpressionVisitor <Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <Author>(); using (IDbConnection db = GetFileConnectionString().OpenDbConnection()) { db.DropTable <Author>(); db.CreateTable <Author>(); db.DeleteAll <Author>(); List <Author> authors = new List <Author>(); authors.Add(new Author() { Name = "Demis Bellot", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 99.9m, Comments = "CSharp books", Rate = 10, City = "London" }); authors.Add(new Author() { Name = "Angel Colmenares", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 50.0m, Comments = "CSharp books", Rate = 5, City = "Bogota" }); authors.Add(new Author() { Name = "Adam Witco", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 80.0m, Comments = "Math Books", Rate = 9, City = "London" }); authors.Add(new Author() { Name = "Claudia Espinel", Birthday = DateTime.Today.AddYears(-23), Active = true, Earnings = 60.0m, Comments = "Cooking books", Rate = 10, City = "Bogota" }); authors.Add(new Author() { Name = "Libardo Pajaro", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 80.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Jorge Garzon", Birthday = DateTime.Today.AddYears(-28), Active = true, Earnings = 70.0m, Comments = "CSharp books", Rate = 9, City = "Bogota" }); authors.Add(new Author() { Name = "Alejandro Isaza", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 70.0m, Comments = "Java books", Rate = 0, City = "Bogota" }); authors.Add(new Author() { Name = "Wilmer Agamez", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 30.0m, Comments = "Java books", Rate = 0, City = "Cartagena" }); authors.Add(new Author() { Name = "Rodger Contreras", Birthday = DateTime.Today.AddYears(-25), Active = true, Earnings = 90.0m, Comments = "CSharp books", Rate = 8, City = "Cartagena" }); authors.Add(new Author() { Name = "Chuck Benedict", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "CSharp books", Rate = 8, City = "London" }); authors.Add(new Author() { Name = "James Benedict II", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.5m, Comments = "Java books", Rate = 5, City = "Berlin" }); authors.Add(new Author() { Name = "Ethan Brown", Birthday = DateTime.Today.AddYears(-20), Active = true, Earnings = 45.0m, Comments = "CSharp books", Rate = 5, City = "Madrid" }); authors.Add(new Author() { Name = "Xavi Garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 75.0m, Comments = "CSharp books", Rate = 9, City = "Madrid" }); authors.Add(new Author() { Name = "Luis garzon", Birthday = DateTime.Today.AddYears(-22), Active = true, Earnings = 85.0m, Comments = "CSharp books", Rate = 10, City = "Mexico" }); db.InsertAll(authors); // lets start ! // select authors born 20 year ago int year = DateTime.Today.AddYears(-20).Year; int expected = 5; ev.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); List <Author> result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31))); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from London, Berlin and Madrid : 6 expected = 6; ev.Where(rn => Sql.In(rn.City, new object[] { "London", "Madrid", "Berlin" })); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => Sql.In(rn.City, new[] { "London", "Madrid", "Berlin" })); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors from Bogota and Cartagena : 7 expected = 7; ev.Where(rn => Sql.In(rn.City, new object[] { "Bogota", "Cartagena" })); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name starts with A expected = 3; ev.Where(rn => rn.Name.StartsWith("A")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Name.StartsWith("A")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; ev.Where(rn => rn.Name.ToUpper().EndsWith("GARZON")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Name.ToUpper().EndsWith("GARZON")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name ends with garzon //A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters //in the string. //An underscore ("_") in the LIKE pattern matches any single character in the string. //Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). expected = 3; ev.Where(rn => rn.Name.EndsWith("garzon")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Name.EndsWith("garzon")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors which name contains Benedict expected = 2; ev.Where(rn => rn.Name.Contains("Benedict")); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Name.Contains("Benedict")); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Earnings <= 50 expected = 3; ev.Where(rn => rn.Earnings <= 50); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Earnings <= 50); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // select authors with Rate = 10 and city=Mexio expected = 1; ev.Where(rn => rn.Rate == 10 && rn.City == "Mexico"); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == "Mexico"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; ev.Where(rn => rn.Rate == 0).Update(rn => rn.Active); var rows = db.UpdateOnly(new Author() { Active = false }, ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; ev.Insert(rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, ev); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, ev); ev.Where(rn => !rn.Active); result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); //update comment for City == null expected = 2; ev.Where(rn => rn.City == null).Update(rn => rn.Comments); rows = db.UpdateOnly(new Author() { Comments = "No comments" }, ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // delete where City is null expected = 2; rows = db.Delete(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected == rows); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; ev.Where().OrderBy(rn => new{ at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.WriteLine(ev.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name); // select only first 5 rows .... expected = 5; ev.Limit(5); // note: order is the same as in the last sentence result = db.Select(ev); Console.WriteLine(ev.WhereExpression); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); // and finally lets select only Name and City (name will be "UPPERCASED" ) ev.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); Console.WriteLine(ev.SelectExpression); result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); //paging : ev.Limit(0, 4); // first page, page size=4; result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name); ev.Limit(4, 4); // second page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name); ev.Limit(8, 4); // third page result = db.Select(ev); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name); // select distinct.. ev.Limit(); // clear limit ev.SelectDistinct(r => r.City); expected = 6; result = db.Select(ev); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count); Console.WriteLine(); // Tests for predicate overloads that make use of the expression visitor Console.WriteLine("First author by name (exists)"); author = db.First <Author>(a => a.Name == "Jorge Garzon"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon", author.Name, "Jorge Garzon" == author.Name); try { Console.WriteLine("First author by name (does not exist)"); author = db.First <Author>(a => a.Name == "Does not exist"); Console.WriteLine("Expected exception thrown, OK? False"); } catch { Console.WriteLine("Expected exception thrown, OK? True"); } Console.WriteLine("First author or default (does not exist)"); author = db.FirstOrDefault <Author>(a => a.Name == "Does not exist"); Console.WriteLine("Expected:null ; OK? {0}", author == null); Console.WriteLine("First author or default by city (multiple matches)"); author = db.FirstOrDefault <Author>(a => a.City == "Bogota"); Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Angel Colmenares", author.Name, "Angel Colmenares" == author.Name); Console.ReadLine(); Console.WriteLine("Press Enter to continue"); } Console.WriteLine("This is The End my friend!"); }
public void Run_Expressions_Author_tests() { using (var db = OpenDbConnection()) { var dialect = OrmLiteConfig.DialectProvider; var q = db.From <Author>(); db.DropTable <Author>(); var tableName = dialect.NamingStrategy.GetTableName(typeof(Author).Name); var tableExists = dialect.DoesTableExist(db, tableName); Assert.That(tableExists, Is.False); db.CreateTable <Author>(); tableExists = dialect.DoesTableExist(db, tableName); Assert.That(tableExists); db.DeleteAll <Author>(); "Inserting...".Print(); var t1 = DateTime.Now; var authors = GetAuthors(); db.InsertAll(authors); var t2 = DateTime.Now; "Inserted {0} rows in {1}".Print(authors.Count, t2 - t1); "Selecting.....".Print(); var year = DateTime.Today.AddYears(-20).Year; var lastDay = new DateTime(year, 12, 31); var expected = 5; q.Where().Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); q.ToSelectStatement().Print(); var result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select(db.From <Author>().Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay)); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= lastDay); Assert.That(result.Count, Is.EqualTo(expected)); var a = new Author { Birthday = lastDay }; result = db.Select <Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= a.Birthday); Assert.That(result.Count, Is.EqualTo(expected)); // select authors from London, Berlin and Madrid : 6 expected = 6; //Sql.In can take params object[] var city = "Berlin"; q.Where().Where(rn => Sql.In(rn.City, "London", "Madrid", city)); //clean prev result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => Sql.In(rn.City, "London", "Madrid", "Berlin")); Assert.That(result.Count, Is.EqualTo(expected)); // select authors from Bogota and Cartagena : 7 expected = 7; //... or Sql.In can take List<Object> city = "Bogota"; var cities = new List <object> { city, "Cartagena" }; q.Where().Where(rn => Sql.In(rn.City, cities)); result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => Sql.In(rn.City, "Bogota", "Cartagena")); Assert.That(result.Count, Is.EqualTo(expected)); // select authors which name starts with A expected = 3; q.Where().Where(rn => rn.Name.StartsWith("A")); result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => rn.Name.StartsWith("A")); Assert.That(result.Count, Is.EqualTo(expected)); // select authors which name ends with Garzon o GARZON o garzon ( no case sensitive ) expected = 3; var name = "GARZON"; q.Where().Where(rn => rn.Name.ToUpper().EndsWith(name)); result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => rn.Name.ToUpper().EndsWith(name)); Assert.That(result.Count, Is.EqualTo(expected)); // select authors which name ends with garzon //A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters //in the string. //An underscore ("_") in the LIKE pattern matches any single character in the string. //Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). expected = 3; q.Where().Where(rn => rn.Name.EndsWith("garzon")); result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => rn.Name.EndsWith("garzon")); Assert.That(result.Count, Is.EqualTo(expected)); // select authors which name contains Benedict expected = 2; name = "Benedict"; q.Where().Where(rn => rn.Name.Contains(name)); result = db.Select(q); q.WhereExpression.Print(); Assert.That(result.Count, Is.EqualTo(expected)); result = db.Select <Author>(rn => rn.Name.Contains("Benedict")); Assert.That(result.Count, Is.EqualTo(expected)); a.Name = name; result = db.Select <Author>(rn => rn.Name.Contains(a.Name)); Assert.That(result.Count, Is.EqualTo(expected)); // select authors with Earnings <= 50 expected = 3; var earnings = 50; q.Where().Where(rn => rn.Earnings <= earnings); result = db.Select(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Earnings <= 50); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // select authors with Rate = 10 and city=Mexio expected = 1; city = "Mexico"; q.Where().Where(rn => rn.Rate == 10 && rn.City == city); result = db.Select(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == "Mexico"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); a.City = city; result = db.Select <Author>(rn => rn.Rate == 10 && rn.City == a.City); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // enough selecting, lets update; // set Active=false where rate =0 expected = 2; var rate = 0; q.Where().Where(rn => rn.Rate == rate).Update(rn => rn.Active); var rows = db.UpdateOnly(new Author { Active = false }, q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // insert values only in Id, Name, Birthday, Rate and Active fields expected = 4; db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Victor Grozny", Birthday = DateTime.Today.AddYears(-18) }, rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); db.InsertOnly(new Author() { Active = false, Rate = 0, Name = "Ivan Chorny", Birthday = DateTime.Today.AddYears(-19) }, rn => new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate }); q.Where().Where(rn => !rn.Active); result = db.Select(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); //update comment for City == null expected = 2; q.Where().Where(rn => rn.City == null).Update(rn => rn.Comments); rows = db.UpdateOnly(new Author() { Comments = "No comments" }, q); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // delete where City is null expected = 2; rows = db.Delete(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, rows, expected == rows ? "OK" : "************** FAILED ***************"); // lets select all records ordered by Rate Descending and Name Ascending expected = 14; q.Where().OrderBy(rn => new { at = Sql.Desc(rn.Rate), rn.Name }); // clear where condition result = db.Select(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); Console.WriteLine(q.OrderByExpression); var author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel", author.Name, "Claudia Espinel" == author.Name ? "OK" : "************** FAILED ***************"); // select only first 5 rows .... expected = 5; q.Limit(5); // note: order is the same as in the last sentence result = db.Select(q); q.WhereExpression.Print(); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); // and finally lets select only Name and City (name will be "UPPERCASED" ) q.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), "Name"), rn.City }); q.SelectExpression.Print(); result = db.Select(q); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); q.Select(rn => new { at = Sql.As(rn.Name.ToUpper(), rn.Name), rn.City }); q.SelectExpression.Print(); result = db.Select(q); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); //paging : q.Limit(0, 4);// first page, page size=4; result = db.Select(q); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); q.Limit(4, 4);// second page result = db.Select(q); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); q.Limit(8, 4);// third page result = db.Select(q); author = result.FirstOrDefault(); Console.WriteLine("Expected:{0} Selected:{1} {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper() == author.Name ? "OK" : "************** FAILED ***************"); // select distinct.. q.Limit().OrderBy(); // clear limit, clear order for postres q.SelectDistinct(r => r.City); expected = 6; result = db.Select(q); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, result.Count, expected == result.Count ? "OK" : "************** FAILED ***************"); q.Select(r => Sql.As(Sql.Max(r.Birthday), "Birthday")); result = db.Select(q); var expectedResult = authors.Max(r => r.Birthday); Assert.That(result[0].Birthday, Is.EqualTo(expectedResult)); q.Select(r => Sql.As(Sql.Max(r.Birthday), r.Birthday)); result = db.Select(q); expectedResult = authors.Max(r => r.Birthday); Assert.That(result[0].Birthday, Is.EqualTo(expectedResult)); var r1 = db.Single(q); Assert.That(r1.Birthday, Is.EqualTo(expectedResult)); var r2 = db.Scalar <Author, DateTime>(e => Sql.Max(e.Birthday)); Assert.That(r2, Is.EqualTo(expectedResult)); q.Select(r => Sql.As(Sql.Min(r.Birthday), "Birthday")); result = db.Select(q); expectedResult = authors.Min(r => r.Birthday); Assert.That(result[0].Birthday, Is.EqualTo(expectedResult)); q.Select(r => Sql.As(Sql.Min(r.Birthday), r.Birthday)); result = db.Select(q); expectedResult = authors.Min(r => r.Birthday); Assert.That(result[0].Birthday, Is.EqualTo(expectedResult)); q.Select(r => new { r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), "Birthday") }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(q); var expectedStringResult = "Berlin"; Assert.That(result[0].City, Is.EqualTo(expectedStringResult)); q.Select(r => new { r.City, MaxResult = Sql.As(Sql.Min(r.Birthday), r.Birthday) }) .GroupBy(r => r.City) .OrderBy(r => r.City); result = db.Select(q); expectedStringResult = "Berlin"; Assert.That(result[0].City, Is.EqualTo(expectedStringResult)); r1 = db.Single(q); Assert.That(r1.City, Is.EqualTo(expectedStringResult)); var expectedDecimal = authors.Max(e => e.Earnings); var r3 = db.Scalar <Author, decimal?>(e => Sql.Max(e.Earnings)); Assert.That(r3.Value, Is.EqualTo(expectedDecimal)); var expectedString = authors.Max(e => e.Name); var r4 = db.Scalar <Author, string>(e => Sql.Max(e.Name)); Assert.That(r4, Is.EqualTo(expectedString)); var expectedDate = authors.Max(e => e.LastActivity); var r5 = db.Scalar <Author, DateTime?>(e => Sql.Max(e.LastActivity)); Assert.That(r5, Is.EqualTo(expectedDate)); var expectedDate51 = authors.Where(e => e.City == "Bogota").Max(e => e.LastActivity); var r51 = db.Scalar <Author, DateTime?>( e => Sql.Max(e.LastActivity), e => e.City == "Bogota"); Assert.That(r51, Is.EqualTo(expectedDate51)); try { var expectedBool = authors.Max(e => e.Active); var r6 = db.Scalar <Author, bool>(e => Sql.Max(e.Active)); Assert.That(r6, Is.EqualTo(expectedBool)); } catch (Exception e) { if (Dialect == Dialect.PostgreSql) { Console.WriteLine("OK PostgreSQL: " + e.Message); } else { Console.WriteLine("************** FAILED *************** " + e.Message); } } // Tests for predicate overloads that make use of the expression visitor "First author by name (exists)".Print(); author = db.Single <Author>(x => x.Name == "Jorge Garzon"); Assert.That(author.Name, Is.EqualTo("Jorge Garzon")); "First author by name (does not exist)".Print(); author = db.Single <Author>(x => x.Name == "Does not exist"); Assert.That(author, Is.Null); "First author or default (does not exist)".Print(); author = db.Single <Author>(x => x.Name == "Does not exist"); Assert.That(author, Is.Null); "First author or default by city (multiple matches)".Print(); author = db.Single <Author>(x => x.City == "Bogota"); Assert.That(author.Name, Is.EqualTo("Angel Colmenares")); a.City = "Bogota"; author = db.Single <Author>(x => x.City == a.City); Assert.That(author.Name, Is.EqualTo("Angel Colmenares")); // count test var expectedCount = authors.Count; var r7 = db.Scalar <Author, long>(e => Sql.Count(e.Id)); Assert.That(r7, Is.EqualTo(expectedCount)); expectedCount = authors.Count(e => e.City == "Bogota"); r7 = db.Scalar <Author, long>( e => Sql.Count(e.Id), e => e.City == "Bogota"); Assert.That(r7, Is.EqualTo(expectedCount)); // more updates..... Console.WriteLine("more updates....................."); q.Update(); // all fields will be updated // select and update expected = 1; var rr = db.Single <Author>(rn => rn.Name == "Luis garzon"); rr.City = "Madrid"; rr.Comments = "Updated"; q.Where().Where(r => r.Id == rr.Id); // if omit, then all records will be updated rows = db.UpdateOnly(rr, q); // == dbCmd.Update(rr) but it returns void Assert.That(rows, Is.EqualTo(expected)); expected = 0; q.Where().Where(r => r.City == "Ciudad Gotica"); rows = db.UpdateOnly(rr, q); Assert.That(rows, Is.EqualTo(expected)); expected = db.Select <Author>(x => x.City == "Madrid").Count; author = new Author { Active = false }; rows = db.UpdateOnly(author, x => x.Active, x => x.City == "Madrid"); Assert.That(rows, Is.EqualTo(expected)); expected = db.Select <Author>(x => x.Active == false).Count; rows = db.Delete <Author>(x => x.Active == false); Assert.That(rows, Is.EqualTo(expected)); var t3 = DateTime.Now; "Expressions test in: {0}".Print(t3 - t2); "All test in : {0}".Print(t3 - t1); } }