private SQLiteConnection CreateDb()
{
var db = new TestDb();
db.CreateTable <TestTable>();
var items = from i in Enumerable.Range(0, Count)
select new TestTable
{
Datum = 1000 + i, Test = "Hello World"
};
db.InsertAll(items);
Assert.AreEqual(Count, db.Table <TestTable>().Count());
db.CreateTable <TestTableMultiPK>();
var items2 = from i in Enumerable.Range(0, Count)
select new TestTableMultiPK
{
Id = "t" + i,
Id2 = "t" + i,
Datum = 1000 + i
};
db.InsertAll(items2);
Assert.AreEqual(Count, db.Table <TestTableMultiPK>().Count());
return(db);
}
public void Issue96_NullabelIntsInQueries()
{
TestDb db = CreateDb();
db.CreateTable <Issue96_A>();
int id = 42;
db.Insert(new Issue96_A
{
ClassB = id,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
Assert.AreEqual(1, db.Table <Issue96_A>().Where(p => p.ClassB == id).Count());
Assert.AreEqual(3, db.Table <Issue96_A>().Where(p => p.ClassB == null).Count());
}
public void CanCompareAnyField()
{
int n = 20;
IEnumerable <TestObjString> cq = from i in Enumerable.Range(1, n)
select new TestObjString
{
Data = Convert.ToString(i),
Date = new DateTime(2013, 1, i)
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
TableQuery <TestObjString> results = db.Table <TestObjString>().Where(o => o.Data.Equals("10"));
Assert.AreEqual(results.Count(), 1);
Assert.AreEqual(results.FirstOrDefault().Data, "10");
results = db.Table <TestObjString>().Where(o => o.Id.Equals(10));
Assert.AreEqual(results.Count(), 1);
Assert.AreEqual(results.FirstOrDefault().Data, "10");
var date = new DateTime(2013, 1, 10);
results = db.Table <TestObjString>().Where(o => o.Date.Equals(date));
Assert.AreEqual(results.Count(), 1);
Assert.AreEqual(results.FirstOrDefault().Data, "10");
}
public void Issue86()
{
var db = new TestDb();
db.CreateTable <Foo>();
db.Insert(new Foo
{
Bar = 42
});
db.Insert(new Foo
{
Bar = 69
});
Foo found42 = db.Table <Foo>().Where(f => f.Bar == 42).FirstOrDefault();
Assert.IsNotNull(found42);
var ordered = new List <Foo>(db.Table <Foo>().OrderByDescending(f => f.Bar));
Assert.AreEqual(2, ordered.Count);
Assert.AreEqual(69, ordered[0].Bar);
Assert.AreEqual(42, ordered[1].Bar);
}
public void OrderByCast()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
TotalSales = 1,
});
db.Insert(new Product
{
Name = "B",
TotalSales = 100,
});
List <Product> nocast = (from p in db.Table <Product>() orderby p.TotalSales descending select p).ToList();
Assert.AreEqual(2, nocast.Count);
Assert.AreEqual("B", nocast[0].Name);
List <Product> cast = (from p in db.Table <Product>() orderby(int) p.TotalSales descending select p).ToList();
Assert.AreEqual(2, cast.Count);
Assert.AreEqual("B", cast[0].Name);
}
public void ContainsConstantData()
{
int n = 20;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Name = i.ToString()
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
db.TraceListener = DebugTraceListener.Instance;
var tensq = new[] { "0", "10", "20" };
List <TestObj> tens = (from o in db.Table <TestObj>() where tensq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new[] { "0", "x", "99", "10", "20", "234324" };
List <TestObj> more = (from o in db.Table <TestObj>() where moreq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more.Count);
}
public void ContainsQueriedData()
{
int n = 20;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Name = i.ToString()
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
db.TraceListener = DebugTraceListener.Instance;
var tensq = new[] { "0", "10", "20" };
List <TestObj> tens = (from o in db.Table <TestObj>() where tensq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new[] { "0", "x", "99", "10", "20", "234324" };
List <TestObj> more = (from o in db.Table <TestObj>() where moreq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more.Count);
// https://github.com/praeclarum/SQLite.Net/issues/28
List <string> moreq2 = moreq.ToList();
List <TestObj> more2 = (from o in db.Table <TestObj>() where moreq2.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more2.Count);
}
public void GetWithExpression()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
db.Insert(new Product
{
Name = "C",
Price = 5,
});
Assert.AreEqual(3, db.Table <Product>().Count());
var r = db.Get <Product>(x => x.Price == 10);
Assert.IsNotNull(r);
Assert.AreEqual("B", r.Name);
}
public void BulkInsertAndSelect()
{
var db = new TestDb(false, new ContractResolver(t => _container.CanResolve(t), (t, op) => _container.Resolve(t)));
db.CreateTable <IProduct>();
db.CreateTable <IOrder>();
db.CreateTable <IOrderLine>();
db.CreateTable <IOrderHistory>();
var rnd = new Random();
var data = new List <IOrderLine>();
for (var i = 0; i < 100; i++)
{
var l = _container.Resolve <IOrderLine>();
l.OrderId = rnd.Next(1, 100);
l.ProductId = rnd.Next(1, 100);
l.Quantity = rnd.Next(1, 25);
l.Status = OrderLineStatus.Shipped;
l.UnitPrice = rnd.Next(0, 100);
data.Add(l);
}
db.InsertAll(data.ToArray());
var results = db.Table <IOrderLine>();
Assert.AreEqual(data.Count, results.Count());
Assert.AreEqual(data.First().UnitPrice, results.First().UnitPrice);
Assert.AreEqual(data.Last().UnitPrice, results.Last().UnitPrice);
}
public void Skip()
{
int n = 100;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Order = i
};
TestObj[] objs = cq.ToArray();
var db = new TestDb(TestPath.CreateTemporaryDatabase());
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TableQuery <TestObj> q = from o in db.Table <TestObj>()
orderby o.Order
select o;
TableQuery <TestObj> qs1 = q.Skip(1);
List <TestObj> s1 = qs1.ToList();
Assert.AreEqual(n - 1, s1.Count);
Assert.AreEqual(2, s1[0].Order);
TableQuery <TestObj> qs5 = q.Skip(5);
List <TestObj> s5 = qs5.ToList();
Assert.AreEqual(n - 5, s5.Count);
Assert.AreEqual(6, s5[0].Order);
}
public void MultipleSkipsWillSkipTheSumOfTheSkips()
{
int n = 100;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Order = i
};
TestObj[] objs = cq.ToArray();
var db = new TestDb(TestPath.CreateTemporaryDatabase());
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TableQuery <TestObj> q = from o in db.Table <TestObj>()
orderby o.Order
select o;
TableQuery <TestObj> qs1 = q.Skip(1).Skip(5);
List <TestObj> s1 = qs1.ToList();
Assert.AreEqual(n - 6, s1.Count, "Should have skipped 5 + 1 = 6 objects.");
Assert.AreEqual(7, s1[0].Order);
}
public void InsertAllFailureInsideTransaction()
{
List <UniqueObj> testObjects = Enumerable.Range(1, 20).Select(i => new UniqueObj
{
Id = i
}).ToList();
testObjects[testObjects.Count - 1].Id = 1; // causes the insert to fail because of duplicate key
ExceptionAssert.Throws <SQLiteException>(() => _db.RunInTransaction(() => { _db.InsertAll(testObjects); }));
Assert.AreEqual(0, _db.Table <UniqueObj>().Count());
}
public void CreateInsertDrop()
{
var db = new TestDb();
db.CreateTable <Product>();
db.Insert(new Product
{
Name = "Hello",
Price = 16,
});
int n = db.Table <Product>().Count();
Assert.AreEqual(1, n);
db.DropTable <Product>();
Assert.Throws <SQLiteException>(() => db.Table <Product>().Count());
}
public void WhereGreaterThan()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
Assert.AreEqual(2, db.Table <Product>().Count());
List <Product> r = (from p in db.Table <Product>() where p.Price > 15 select p).ToList();
Assert.AreEqual(1, r.Count);
Assert.AreEqual("A", r[0].Name);
}
public void FailNestedSavepointTransaction()
{
try
{
db.RunInTransaction(() =>
{
db.Delete(testObjects[0]);
db.RunInTransaction(() =>
{
db.Delete(testObjects[1]);
throw new TransactionTestException();
});
});
}
catch (TransactionTestException)
{
// ignore
}
Assert.AreEqual(testObjects.Count, db.Table <TestObj>().Count());
}
private SQLiteConnection CreateDb()
{
var db = new TestDb();
db.CreateTable <TestTable>();
IEnumerable <TestTable> items = from i in Enumerable.Range(0, Count)
select new TestTable
{
Two = 2
};
db.InsertAll(items);
Assert.AreEqual(Count, db.Table <TestTable>().Count());
return(db);
}
public void Contains()
{
List <Product> fs = db.Table <Product>().Where(x => x.Name.Contains("o")).ToList();
Assert.AreEqual(2, fs.Count);
List <Product> bs = db.Table <Product>().Where(x => x.Name.Contains("a")).ToList();
Assert.AreEqual(2, bs.Count);
}
private static void VerifyCreations(TestDb db)
{
var orderLine = db.GetMapping(typeof(OrderLine));
Assert.AreEqual(6, orderLine.Columns.Length);
var l = new OrderLine
{
Status = OrderLineStatus.Shipped
};
db.Insert(l);
var lo = db.Table <OrderLine>().First(x => x.Status == OrderLineStatus.Shipped);
Assert.AreEqual(lo.Id, l.Id);
}
public void ToUpper()
{
var db = new TestDb();
db.CreateTable <TestTable>();
var testTable = new TestTable()
{
Name = "test"
};
db.Insert(testTable);
var x = db.Table <TestTable>().Where(t => t.Name.ToUpper() == "TEST");
Assert.AreEqual(1, x.Count());
}
public void SelectWorks()
{
using (var db = new TestDb(TestPath.CreateTemporaryDatabase()))
{
db.Insert(new TestObj()
{
Order = 5
});
try
{
Assert.That(db.Table <TestObj>().Select(obj => obj.Order * 2).First(), Is.EqualTo(10));
}
catch (NotImplementedException)
{
//Allow Not implemented exceptions as the selection may be too complex.
}
}
}
public void Query()
{
var db = new TestDb();
db.CreateTable <UnicodeProduct>();
string testString = "\u2329\u221E\u232A";
db.Insert(new UnicodeProduct
{
Name = testString,
});
var ps = (from p in db.Table <UnicodeProduct>() where p.Name == testString select p).ToList();
Assert.AreEqual(1, ps.Count);
Assert.AreEqual(testString, ps[0].Name);
}
public void AddForStringsMeansConcatenate()
{
int n = 20;
IEnumerable <TestObjString> cq = from i in Enumerable.Range(1, n)
select new TestObjString()
{
Data = i.ToString(),
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
TableQuery <TestObjString> results = db.Table <TestObjString>().Where(o => o.Data + "1" == "11");
Assert.AreEqual(1, results.Count());
Assert.AreEqual("1", results.OrderBy(o => o.Data).FirstOrDefault().Data);
}
public void CanHaveSubtractInWhereClause()
{
int n = 20;
IEnumerable <TestObjInt> cq = from i in Enumerable.Range(1, n)
select new TestObjInt()
{
Data = i,
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
TableQuery <TestObjInt> results = db.Table <TestObjInt>().Where(o => o.Data - 10 >= 0);
Assert.AreEqual(results.Count(), 11);
Assert.AreEqual(results.OrderBy(o => o.Data).FirstOrDefault().Data, 10);
}
public void Issue115_MissingPrimaryKey()
{
using (var conn = new TestDb())
{
conn.CreateTable <Issue115_MyObject>();
conn.InsertAll(from i in Enumerable.Range(0, 10)
select new Issue115_MyObject
{
UniqueId = i.ToString(),
OtherValue = (byte)(i * 10),
});
TableQuery <Issue115_MyObject> query = conn.Table <Issue115_MyObject>();
foreach (Issue115_MyObject itm in query)
{
itm.OtherValue++;
Assert.AreEqual(1, conn.Update(itm, typeof(Issue115_MyObject)));
}
}
}
public void Issue303_WhereNot_A()
{
using (var db = new TestDb())
{
db.CreateTable <Issue303_A>();
db.Insert(new Issue303_A {
Id = 1, Name = "aa"
});
db.Insert(new Issue303_A {
Id = 2, Name = null
});
db.Insert(new Issue303_A {
Id = 3, Name = "test"
});
db.Insert(new Issue303_A {
Id = 4, Name = null
});
var r = (from p in db.Table <Issue303_A>() where !(p.Name == null) select p).ToList();
Assert.AreEqual(2, r.Count);
Assert.AreEqual(1, r[0].Id);
Assert.AreEqual(3, r[1].Id);
}
}
public void Issue303_WhereNot_B()
{
using (var db = new TestDb())
{
db.CreateTable <Issue303_B>();
db.Insert(new Issue303_B {
Id = 1, Flag = true
});
db.Insert(new Issue303_B {
Id = 2, Flag = false
});
db.Insert(new Issue303_B {
Id = 3, Flag = true
});
db.Insert(new Issue303_B {
Id = 4, Flag = false
});
var r = (from p in db.Table <Issue303_B>() where !p.Flag select p).ToList();
Assert.AreEqual(2, r.Count);
Assert.AreEqual(2, r[0].Id);
Assert.AreEqual(4, r[1].Id);
}
}
public void FunctionParameter()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
Func <decimal, List <Product> > GetProductsWithPriceAtLeast =
delegate(decimal val) { return((from p in db.Table <Product>() where p.Price > val select p).ToList()); };
List <Product> r = GetProductsWithPriceAtLeast(15);
Assert.AreEqual(1, r.Count);
Assert.AreEqual("A", r[0].Name);
}
public void ReplaceInWhere()
{
string testElement = "Element";
string alternateElement = "Alternate";
string replacedElement = "ReplacedElement";
int n = 20;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Name = (i % 2 == 0) ? testElement : alternateElement
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.InsertAll(cq);
db.TraceListener = DebugTraceListener.Instance;
List <TestObj> result = (from o in db.Table <TestObj>() where o.Name.Replace(testElement, replacedElement) == replacedElement select o).ToList();
Assert.AreEqual(10, result.Count);
}
public OrderLine[] GetOrderLines(TestDb db)
{
return(db.Table <OrderLine>().Where(o => o.ProductId == Id).ToArray());
}
public void Collate()
{
var obj = new TestObj
{
CollateDefault = "Alpha ",
CollateBinary = "Alpha ",
CollateRTrim = "Alpha ",
CollateNoCase = "Alpha ",
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.Insert(obj);
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA " select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha " select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA" select o).Count());
}
