public void SimpleOrderByTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.SaveChanges();
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var r = from d in ctx.Dinners
orderby d.Address
select d;
string last = "aaaa";
foreach(var d in r)
{
Assert.That(last.CompareTo(d.Address) <= 0);
last = d.Address;
}
}
public void SimpleUpdateTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
var i = ctx.SaveChanges();
ctx.Dispose();
Assert.That(i, Is.EqualTo(5));
ctx = new NerdDinners(new SQLiteConnection(_db));
var items = from d in ctx.Dinners where d.Address == "test2" select d;
var update = items.First();
int id = update.DinnerId;
update.Address = "New Address";
update.EventDate = DateTime.Today.AddDays(3);
var oldId = Guid.NewGuid();
update.Identifier = Guid.NewGuid();
update.DoubleValue = 54.4;
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var actuals = from d in ctx.Dinners where d.DinnerId == id select d;
var actual = actuals.First();
Assert.That(actual.Address, Is.EqualTo("New Address"));
Assert.That(actual.DoubleValue, Is.EqualTo(54.4));
Assert.That(actual.Identifier, Is.Not.EqualTo(oldId));
}
public void SimpleGroupTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
Assert.That(dinner.DinnerId, Is.Not.EqualTo(0));
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var g = from d in ctx.Dinners
group d by d.Address into gd
select gd.Key;
var set = new HashSet<string>(g);
Assert.That(set.Contains("test1"));
Assert.That(set.Contains("test2"));
}
public void HydrateForeignEntitySetTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
Assert.That(dinner.DinnerId, Is.Not.EqualTo(0));
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var test1 = from d in ctx.Dinners.Include(x => x.Rsvps)
where d.Address == "test1"
select d;
var actual = test1.First();
Assert.That(actual.Rsvps, Is.Not.Null);
foreach(var r in actual.Rsvps)
{
Assert.That(r.DinnerId, Is.EqualTo(actual.DinnerId));
}
}
public void SimpleInsertGuidTest ()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var ids = new List<Guid>();
ids.Add(Guid.NewGuid());
ids.Add(Guid.NewGuid());
ids.Add(Guid.NewGuid());
ids.Add(Guid.NewGuid());
ids.Add(Guid.NewGuid());
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = ids[0], DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = ids[1], DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = ids[2], DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = ids[3], DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = ids[4], DoubleValue = 1.5 });
var i = ctx.SaveChanges();
ctx.Dispose();
Assert.That(i, Is.EqualTo(5));
ctx = new NerdDinners(new SQLiteConnection(_db));
var items = from d in ctx.Dinners where d.DinnerId > 0 select d.Identifier;
var set = new HashSet<Guid>();
foreach(var item in items) {
set.Add(item);
}
foreach(var id in ids)
{
Assert.That(set.Contains(id));
}
}
public void InsertFailsWithNoForeignKeyTest()
{
var rsvp = new Rsvp();
rsvp.Email = "test";
var ctx = new NerdDinners(new SQLiteConnection(_db));
ctx.Rsvps.Add(rsvp);
ctx.SaveChanges();
Assert.Fail();
}
public void ConditionalAnyTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = ctx.Dinners.Any(d => d.DoubleValue > 1);
Assert.That(res, Is.True);
}
public void SimpleFirstOrDefaultTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = from d in ctx.Dinners
where d.DoubleValue == 55
select d;
Assert.That(res.FirstOrDefault(), Is.Null);
}
public void SimpleDistinctTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.SaveChanges();
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var r = (from d in ctx.Dinners
select d.Address).Distinct();
Assert.That(r.Count() == 2);
}
public void SimpleCountTest()
{
using (var ctx = new NerdDinners(new SQLiteConnection(_db)))
{
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.AddRange(
new [] { dinner,
new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 },
new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 },
new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 },
new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 }
});
ctx.SaveChanges();
}
using (var ctx = new NerdDinners(new SQLiteConnection(_db)))
{
var res = ctx.Dinners.Count();
Assert.That(res, Is.EqualTo(5));
}
}
public void SimpleWhereTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var g = from d in ctx.Dinners
where d.Address == "test1"
select d;
Assert.That(g.Count(), Is.EqualTo(3));
}
public void DateTimeYearTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(0), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(0), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(0), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(0), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.SaveChanges();
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var q = from d in ctx.Dinners
where d.EventDate.Year == DateTime.Today.Year
select d;
var list = q.ToList();
Assert.That(list.Count, Is.EqualTo(5));
}
public void ToLowerTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1 });
ctx.SaveChanges();
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var r = from d in ctx.Dinners
select d.Title.ToLower();
foreach(var d in r)
{
Assert.That(d, Is.EqualTo("john's dinner"));
}
}
public void SimpleDeleteTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
var i = ctx.SaveChanges();
ctx.Dispose();
var id = dinner.DinnerId;
ctx = new NerdDinners(new SQLiteConnection(_db));
var actuals = from d in ctx.Dinners where d.DinnerId == id select d;
var actual = actuals.First();
ctx.Dinners.Remove(actual);
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
actual = (from d in ctx.Dinners where d.DinnerId == id select d).FirstOrDefault();
Assert.That(actual, Is.Null);
}
public void InnerJoinTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "test1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = from d in ctx.Dinners
join r in ctx.Rsvps on d.Address equals r.Email
select d.Identifier;
//Console.WriteLine (res.ToString());
var list = res.ToList();
Assert.That(list.Count, Is.EqualTo(1));
Assert.That(list[0], Is.EqualTo(dinner.Identifier));
}
public void SimpleInsertAutoIncrementPrimaryKeyTest ()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
var i = ctx.SaveChanges();
ctx.Dispose();
Assert.That(i, Is.EqualTo(5));
ctx = new NerdDinners(new SQLiteConnection(_db));
var items = from d in ctx.Dinners where d.DinnerId > 0 select d.DinnerId;
var set = new HashSet<int>();
foreach(var item in items) {
set.Add(item);
}
Assert.That(set.Contains(1));
Assert.That(set.Contains(2));
Assert.That(set.Contains(3));
Assert.That(set.Contains(4));
Assert.That(set.Contains(5));
}
public void GroupedAvgTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = (from d in ctx.Dinners group d by d.Title into g select new { g.Key, Value = g.Average(x => x.DoubleValue) }).ToList();
Assert.That(res.First().Value, Is.EqualTo(1.3));
}
public void SimpleAvgTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = ctx.Dinners.Select(d => d.DoubleValue).Average();
Assert.That(res, Is.EqualTo(1.3));
}
public void CountNestedTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = (from d in ctx.Dinners select new { Title = d.Title, Ct = d.Rsvps.Count() }).ToList();
Assert.That(res.Count, Is.EqualTo(5));
Assert.That(res.Count(r => r.Ct > 0), Is.EqualTo(1));
}
public void InsertFillsInForeignKeyTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
Assert.That(dinner.DinnerId, Is.Not.EqualTo(0));
var i = dinner.DinnerId;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
foreach (var r in ctx.Rsvps)
{
Assert.That(r.DinnerId, Is.EqualTo(i));
}
}
public void UpdateFailsWithBadForeignKeyTest()
{
var rsvp = new Rsvp();
rsvp.Email = "test";
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
rsvp.Dinner = dinner;
ctx.Dinners.Add(dinner);
ctx.Rsvps.Add(rsvp);
ctx.SaveChanges();
rsvp.DinnerId = 19099;
ctx.SaveChanges();
Assert.Fail();
}
public void StringComparisonTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.SaveChanges();
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var res = ctx.Dinners.Where(d => d.Address.CompareTo("t") > 0).ToList();
Assert.That(res.Any(), Is.True);
}
public void GroupSelectCountTest()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(2), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test1", EventDate = DateTime.Today.AddDays(1), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail1" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail2" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail3" });
ctx.Rsvps.Add(new Rsvp { Dinner = dinner, Email = "testemail4" });
ctx.SaveChanges();
Assert.That(dinner.DinnerId, Is.Not.EqualTo(0));
var i = dinner.Identifier;
ctx.Dispose();
ctx = new NerdDinners(new SQLiteConnection(_db));
var g = from d in ctx.Dinners
group d by d.Address into gd
select new { gd.Key, Ct = gd.Count() };
var map = new Dictionary<string, int>();
g.ToList().ForEach(x => map.Add(x.Key, x.Ct));
Assert.That(map["test1"], Is.EqualTo(3));
Assert.That(map["test2"], Is.EqualTo(2));
}
public void SimpleInsertDateTimesTest ()
{
var ctx = new NerdDinners(new SQLiteConnection(_db));
var dinner = new Dinner { Address = "test1", EventDate = DateTime.Today, Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.1 };
ctx.Dinners.Add(dinner);
ctx.Dinners.Add(new Dinner { Address = "test2", EventDate = DateTime.Today.AddDays(-.134), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.2 });
ctx.Dinners.Add(new Dinner { Address = "test3", EventDate = DateTime.Today.AddDays(-.623), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.3 });
ctx.Dinners.Add(new Dinner { Address = "test4", EventDate = DateTime.Today.AddDays(-.234), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.4 });
ctx.Dinners.Add(new Dinner { Address = "test5", EventDate = DateTime.Today.AddDays(-.3426), Title = "John's dinner", Identifier = Guid.NewGuid(), DoubleValue = 1.5 });
var i = ctx.SaveChanges();
ctx.Dispose();
Assert.That(i, Is.EqualTo(5));
ctx = new NerdDinners(new SQLiteConnection(_db));
var items = from d in ctx.Dinners where d.DinnerId > 0 select d.EventDate;
var set = new HashSet<DateTime>();
foreach(var item in items) {
set.Add(item);
}
Assert.That(set.Contains(DateTime.Today));
Assert.That(set.Contains(DateTime.Today.AddDays(-.134)));
Assert.That(set.Contains(DateTime.Today.AddDays(-.623)));
Assert.That(set.Contains(DateTime.Today.AddDays(-.234)));
Assert.That(set.Contains(DateTime.Today.AddDays(-.3426)));
}