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);
}
}
