public void Run_Expressions_Author_tests()
{
var hold = OrmLiteConfig.StripUpperInLike;
OrmLiteConfig.StripUpperInLike = false;
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);
}
OrmLiteConfig.StripUpperInLike = hold;
}
public void Run_Expressions_Author_tests()
{
var hold = OrmLiteConfig.StripUpperInLike;
OrmLiteConfig.StripUpperInLike = false;
using (var db = OpenDbConnection())
{
var dialect = 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.HasFlag(Dialect.AnyPostgreSql))
{
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);
}
OrmLiteConfig.StripUpperInLike = hold;
}
