I’m using sqlite to write unit tests against repository code. I’m running into a more complex query not working with sqlite that works in sql server. It’s possible I have an issue in my seed data but I’m struggling to narrow down what’s not working.
Should I expect this query to work:
var q = db.From<MerchantEntity>()
.Join<MerchantContact>((m, mc) => m.Id == mc.MerchantId && mc.ContactTypeId == 1,
db.JoinAlias("mcd"))
.Join<MerchantContact, AddressEntity>((mc, a) => Sql.JoinAlias(mc.AddressId, "mcd") == a.ID,
db.JoinAlias("ad"))
.Join<AddressEntity, State>((a, s) => Sql.JoinAlias(a.StateId, "ad") == s.Id,
db.JoinAlias("sd"))
.Join<MerchantContact>((m, mc) => m.Id == mc.MerchantId && mc.ContactTypeId == 2,
db.JoinAlias("mcl"))
.Join<MerchantContact, AddressEntity>((mc, a) => Sql.JoinAlias(mc.AddressId, "mcl") == a.ID,
db.JoinAlias("al"))
.Join<AddressEntity, State>((a, s) => Sql.JoinAlias(a.StateId, "al") == s.Id,
db.JoinAlias("sl"))
.Where<AddressEntity>(a =>
Sql.JoinAlias(a.AddressLine1, "ad") == dba.AddressLine1.Trim()
&& Sql.JoinAlias(a.City, "ad") == dba.City.Trim()
&& Sql.JoinAlias(a.Zip, "ad") == dba.Zip.Trim()
&& Sql.JoinAlias(a.AddressLine1, "al") == legal.AddressLine1.Trim()
&& Sql.JoinAlias(a.City, "al") == legal.City.Trim()
&& Sql.JoinAlias(a.Zip, "al") == legal.Zip.Trim())
.And<State>(s => Sql.JoinAlias(s.StateShortName, "sd") == dba.State.Trim()
&& Sql.JoinAlias(s.StateShortName, "sl") == legal.State.Trim())
.Select(Sql.Count("*"));
var merchCount = db.Scalar<int>(q);
When configuring my Set-up as follows:
[OneTimeSetUp]
public void TestFixtureSetUp()
{
this._appHost = new BasicAppHost().Init();
var container = this._appHost.Container;
container.Register<IDbConnectionFactory>(new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider));
using (var db = container.Resolve<IDbConnectionFactory>().Open())
{
db.DropAndCreateTable<State>();
db.DropAndCreateTable<AddressEntity>();
db.DropAndCreateTable<MerchantContact>();
db.DropAndCreateTable<MerchantEntity>();
db.InsertAll(BoardingTests.Data.SeedData.StatesSeedList);
db.InsertAll(BoardingTests.Data.SeedData.AddressSeedList);
db.InsertAll(BoardingTests.Data.SeedData.MerchantSeedList);
db.InsertAll(BoardingTests.Data.SeedData.MerchantContactSeedList);
}
}
Thanks