public void Concat90()
{
using (var context = new TestDbManager())
{
var join1 =
from t1 in context.GetTable <TestEntity1>()
join t2 in context.GetTable <TestEntity2>()
on t1.Id equals t2.Id
into tmp
from t2 in tmp.DefaultIfEmpty()
select new { t1, t2 };
var join1Sql = join1.ToString();
Assert.IsNotNull(join1Sql);
var join2 =
from t2 in context.GetTable <TestEntity2>()
join t1 in context.GetTable <TestEntity1>()
on t2.Id equals t1.Id
into tmp
from t1 in tmp.DefaultIfEmpty()
where t1 == null
select new { t1, t2 };
var join2Sql = join2.ToString();
Assert.IsNotNull(join2Sql);
var fullJoin = join1.Concat(join2);
var fullJoinSql = fullJoin.ToString(); // BLToolkit.Data.Linq.LinqException : Types in Concat are constructed incompatibly.
Assert.IsNotNull(fullJoinSql);
}
}
public void ConcurentTest1()
{
var query = CompiledQuery.Compile((ITestDataContext db, int n) => db.GetTable<Parent>().Where(p => p.ParentID == n).First().ParentID);
const int count = 100;
var threads = new Thread[count];
var results = new int [count, 2];
for (var i = 0; i < count; i++)
{
var n = i;
threads[i] = new Thread(() =>
{
using (var db = new TestDbManager("Sql2008"))
{
var id = (n % 6) + 1;
results[n,0] = id;
results[n,1] = query(db, id);
}
});
}
for (var i = 0; i < count; i++)
threads[i].Start();
for (var i = 0; i < count; i++)
threads[i].Join();
for (var i = 0; i < count; i++)
Assert.AreEqual(results[i,0], results[i,1]);
}
public void GuidTest()
{
using (var db = new TestDbManager())
{
var list = db.GetTable <InheritanceA>().Where(a => a.Bs.Any()).ToList();
}
}
//[Test]
public void Test()
{
using (var db = new TestDbManager(ProviderName.Firebird))
{
var p = db
.SetCommand(@"
SELECT
t1.ParentID,
t1.Value1
FROM
Parent t1
LEFT JOIN (
SELECT
t3.ParentID as ParentID1,
Coalesce(t3.ParentID, 1) as c1
FROM
Child t3
) t2 ON t1.ParentID = t2.ParentID1
WHERE
t2.c1 IS NULL" )
.ExecuteList <Parent>();
var p1 = p.First();
Assert.AreEqual(1, p1.ParentID);
var da = new SqlQuery();
var pr = (Person)da.SelectByKey(typeof(Person), 1);
Assert.AreEqual("Pupkin", pr.LastName);
//Assert.AreEqual(1, p.ID);
}
}
public void ConvertToIndexJoin1()
{
using (var db = new TestDbManager())
{
var q2 =
from gc1 in db.GrandChild
join max in
from gch in db.GrandChild
group gch by gch.ChildID into g
select g.Max(c => c.GrandChildID)
on gc1.GrandChildID equals max
select gc1;
var result =
from ch in db.Child
join p in db.Parent on ch.ParentID equals p.ParentID
join gc2 in q2 on p.ParentID equals gc2.ParentID into g
from gc3 in g.DefaultIfEmpty()
select gc3;
var ctx = result.GetContext();
var idx = ctx.ConvertToIndex(null, 0, ConvertFlags.Key);
Assert.AreEqual(new[] { 0, 1, 2 }, idx.Select(_ => _.Index).ToArray());
}
}
public void ExplicitInterface3()
{
using (var db = new TestDbManager())
{
var result = SelectNoDate2(db.GetTable <TestTable3>()).ToList();
}
}
public void GroupByExtraFieldBugTest()
{
// https://github.com/igor-tkachev/bltoolkit/issues/42
// extra field is generated in the GROUP BY clause, for example:
// GROUP BY p.LastName, p.LastName <--- the second one is redundant
using (var db = new TestDbManager("MySql"))
{
var q =
from d in db.Doctor
join p in db.Person on d.PersonID equals p.ID
group d by p.LastName into g
select g.Key;
q.ToList();
const string fieldName = "LastName";
var lastQuery = db.LastQuery;
var groupByPos = lastQuery.IndexOf("GROUP BY");
var fieldPos = lastQuery.IndexOf(fieldName, groupByPos);
// check that our field does not present in the GROUP BY clause second time
Assert.AreEqual(-1, lastQuery.IndexOf(fieldName, fieldPos + 1));
}
}
public void Transaction()
{
foreach (var provider in Providers)
{
using (var db = new TestDbManager(provider.Name))
{
var list = new[]
{
new Parent { ParentID = 1111, Value1 = 1111 },
new Parent { ParentID = 2111, Value1 = 2111 },
new Parent { ParentID = 3111, Value1 = 3111 },
new Parent { ParentID = 4111, Value1 = 4111 },
};
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
db.BeginTransaction();
db.InsertBatch(list);
db.CommitTransaction();
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
}
}
}
public void Join6()
{
using (var db = new TestDbManager())
{
var q =
from g in db.GrandChild
join p in db.Parent4 on g.Child.ParentID equals p.ParentID
select g;
var ctx = q.GetContext();
ctx.BuildExpression(null, 0);
var sql = db.GetSqlText(ctx.SqlQuery);
CompareSql(sql, @"
SELECT
[g].[ParentID],
[g].[ChildID],
[g].[GrandChildID]
FROM
[GrandChild] [g]
LEFT JOIN [Child] [t1] ON [g].[ParentID] = [t1].[ParentID] AND [g].[ChildID] = [t1].[ChildID]
INNER JOIN [Parent] [p] ON [t1].[ParentID] = [p].[ParentID]" );
}
}
public void ConcurentTest2()
{
var threads = new Thread[100];
var results = new int [100, 2];
for (var i = 0; i < 100; i++)
{
var n = i;
threads[i] = new Thread(() =>
{
using (var db = new TestDbManager())
{
var id = (n % 6) + 1;
results[n, 0] = id;
results[n, 1] = db.Parent.Where(p => p.ParentID == id).First().ParentID;
}
});
}
for (var i = 0; i < 100; i++)
{
threads[i].Start();
}
for (var i = 0; i < 100; i++)
{
threads[i].Join();
}
for (var i = 0; i < 100; i++)
{
Assert.AreEqual(results[i, 0], results[i, 1]);
}
}
public void Join4()
{
using (var db = new TestDbManager())
{
var q =
from p in db.Parent
select new { ID = new MyClass {
ID = p.ParentID
} }
into p
join j in
from c in db.Child
select new { ID = new MyClass {
ID = c.ParentID
} }
on p.ID.ID equals j.ID.ID
where p.ID.ID == 1
select p;
var ctx = q.GetContext();
ctx.BuildExpression(null, 0);
Assert.AreEqual(1, ctx.SqlQuery.Select.Columns.Count);
}
}
public void UserLinqInsertWithIdentity([IncludeDataContexts("Oracle")] string context)
{
using (var db = new TestDbManager(context))
{
db.BeginTransaction();
db.GetTable<User>().InsertWithIdentity(() => new User { Name = "user" });
}
}
public void UserInsert([IncludeDataContexts("Oracle")] string context)
{
using (var db = new TestDbManager(context))
{
db.BeginTransaction();
db.Insert(new User { Name = "user" });
}
}
public void CompiledFunc2([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q = _f2(db, 1);
q.ToList();
}
}
public void UserLinqInsertWithIdentity()
{
using (var db = new TestDbManager("Oracle"))
{
db.BeginTransaction();
db.GetTable<User>().InsertWithIdentity(() => new User { Name = "user" });
}
}
public void CompiledFunc2()
{
using (var db = new TestDbManager())
{
var q = _f2(db, 1);
q.ToList();
}
}
public void CompiledFunc2()
{
using (var db = new TestDbManager())
{
var q = _f2(db, 1);
q.ToList();
}
}
public void UserInsertWithIdentity()
{
using (var db = new TestDbManager("Oracle"))
{
db.BeginTransaction();
db.InsertWithIdentity(new User { Name = "user" });
}
}
public void CompiledFunc2([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q = _f2(db, 1);
q.ToList();
}
}
public void MyType1()
{
using (var db = new TestDbManager {
MappingSchema = _myMappingSchema
})
{
var list = db.GetTable <MyParent>().ToList();
}
}
public void AllNames()
{
using (var db = new TestDbManager("Sql2008"))
db.GetTable<ParenTable>()
.DatabaseName("BLToolkitData")
.OwnerName("dbo")
.TableName("Parent")
.ToList();
}
public void AllNames([IncludeDataContexts("Sql2008")] string context)
{
using (var db = new TestDbManager(context))
db.GetTable<ParenTable>()
.DatabaseName("TestData")
.OwnerName("dbo")
.TableName("Parent")
.ToList();
}
public void AllNames()
{
using (var db = new TestDbManager("Sql2008"))
db.GetTable <ParenTable>()
.DatabaseName("BLToolkitData")
.OwnerName("dbo")
.TableName("Parent")
.ToList();
}
public void AllNames([IncludeDataContexts("Sql2008")] string context)
{
using (var db = new TestDbManager(context))
db.GetTable<ParenTable>()
.DatabaseName("BLToolkitData")
.OwnerName("dbo")
.TableName("Parent")
.ToList();
}
public void UserLinqInsert()
{
using (var db = new TestDbManager("Oracle"))
{
db.BeginTransaction();
db.GetTable <User>().Insert(() => new User {
Name = "user"
});
}
}
public void UserLinqInsert([IncludeDataContexts("Oracle")] string context)
{
using (var db = new TestDbManager(context))
{
db.BeginTransaction();
db.GetTable <User>().Insert(() => new User {
Name = "user"
});
}
}
public void UserInsertWithIdentity([IncludeDataContexts("Oracle")] string context)
{
using (var db = new TestDbManager(context))
{
db.BeginTransaction();
db.InsertWithIdentity(new User {
Name = "user"
});
}
}
public void TestMethod()
{
using (var db = new TestDbManager())
{
IQueryable <IDocument> query = db.GetTable <Document>();
var idsQuery = query.Select(s => s.Id);
var str = idsQuery.ToString(); // Exception
Assert.IsNotNull(str);
}
}
public void UserInsertWithIdentity()
{
using (var db = new TestDbManager("Oracle"))
{
db.BeginTransaction();
db.InsertWithIdentity(new User {
Name = "user"
});
}
}
public void MapAbstract()
{
using (var db = new TestDbManager())
{
var q = from a in db.GetTable <AbsChild>()
select new { a.ChildID, a.Parent.Value1 };
var ql = q.ToList();
}
}
public void WithTabLock([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q =
from p in new Model.Functions(db).WithTabLock<Parent>()
select p;
q.ToList();
}
}
public void ConvertToIndexTable4()
{
using (var db = new TestDbManager())
{
var ctx = db.Parent
.Select(t => t.Value1)
.GetContext();
Assert.AreEqual(new[] { 0 }, ctx.ConvertToIndex(null, 0, ConvertFlags.Field).Select(_ => _.Index).ToArray());
}
}
public void Func1()
{
using (var db = new TestDbManager())
{
var q =
from p in new Model.Functions(db).GetParentByID(1)
select p;
q.ToList();
}
}
public void WithTabLock()
{
using (var db = new TestDbManager())
{
var q =
from p in new Model.Functions(db).WithTabLock<Parent>()
select p;
q.ToList();
}
}
public void WithTabLock()
{
using (var db = new TestDbManager())
{
var q =
from p in new Model.Functions(db).WithTabLock <Parent>()
select p;
q.ToList();
}
}
public void Func1()
{
using (var db = new TestDbManager())
{
var q =
from p in new Model.Functions(db).GetParentByID(1)
select p;
q.ToList();
}
}
public void Func1([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q =
from p in new Model.Functions(db).GetParentByID(1)
select p;
q.ToList();
}
}
public void InheritanceMappingIssueTest()
{
using (var db = new TestDbManager())
{
var q1 = db.GetTable <Parent222>();
var q = q1.Where(_ => _.Value.ID == 1);
var sql = ((Table <Parent222>)q).SqlText;
Assert.IsNotEmpty(sql);
}
}
public void CharTest2()
{
List <PersonCharTest> list;
using (var db = new TestDbManager())
list = db.GetTable <PersonCharTest>().ToList();
ForEachProvider(db => AreEqual(
from p in list where 'M' == p.Gender select p.PersonID,
from p in db.GetTable <PersonCharTest>() where 'M' == p.Gender select p.PersonID));
}
//[Test]
public void BoolTest2()
{
List <PersonBoolTest> list;
using (var db = new TestDbManager())
list = db.GetTable <PersonBoolTest>().ToList();
ForEachProvider(db => AreEqual(
from p in list where p.IsMale == true select p.PersonID,
from p in db.GetTable <PersonBoolTest>() where p.IsMale == true select p.PersonID));
}
public void WithTabLock([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q =
from p in new Model.Functions(db).WithTabLock <Parent>()
select p;
q.ToList();
}
}
public void Func1([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q =
from p in new Model.Functions(db).GetParentByID(1)
select p;
q.ToList();
}
}
public void TestGenerics3()
{
using (var db = new TestDbManager())
{
var q =
from ch in db.Child
where TestClass <int> .GetBool3(ch.Parent)
select ch;
q.ToList();
}
}
static void Main()
{
BLToolkit.Data.DbManager.AddDataProvider(typeof(BLToolkit.Data.DataProvider.MySqlDataProvider));
using (var db = new TestDbManager("MySql"))
{
var list = new GenericConcatQuery(db, new object[] { "A", 1 }).Query().ToList();
foreach (var i in list)
Console.WriteLine(i.ToString());
}
}
public void Func3()
{
using (var db = new TestDbManager())
{
var q =
from c in db.Child
from p in db.GetParentByID(c.ParentID)
select p;
q.ToList();
}
}
public void TestGenerics2()
{
using (var db = new TestDbManager())
{
var q =
from ch in db.Child
where GetBool2(ch.Parent)
select ch;
q.ToList();
}
}
public void Func3([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
var q =
from c in db.Child
from p in db.GetParentByID(c.ParentID)
select p;
q.ToList();
}
}
public void SubQueryCount([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
AreEqual(
from p in Parent
select Parent.Where(t => t.ParentID == p.ParentID).Count()
,
from p in db.Parent
//select Sql.AsSql(db.Parent.Count()));
select Sql.AsSql(db.GetParentByID(p.ParentID).Count()));
}
}
public void SubQueryCount()
{
using (var db = new TestDbManager())
{
AreEqual(
from p in Parent
select Parent.Where(t => t.ParentID == p.ParentID).Count()
,
from p in db.Parent
//select Sql.AsSql(db.Parent.Count()));
select Sql.AsSql(db.GetParentByID(p.ParentID).Count()));
}
}
public void IsDbGeneratedTest()
{
using (var db = new TestDbManager("Sql2008"))
{
var id = db.InsertWithIdentity(new L2SPersons
{
FirstName = "Test",
LastName = "Test",
Gender = "M"
});
db.GetTable<L2SPersons>().Delete(p => p.PersonID == ConvertTo<int>.From(id));
}
}
public void GeneratePredicate()
{
Expression<Func<Person,bool>> a = x => x.FirstName == "John";
Expression<Func<Person,bool>> b = x => x.LastName == "Pupkin";
var bBody = b.Body.Convert(e => e == b.Parameters[0] ? a.Parameters[0] : e);
var predicate = Expression.Lambda<Func<Person,bool>>(Expression.AndAlso(a.Body, bBody), a.Parameters[0]);
using (var db = new TestDbManager())
{
var q = db.Person.Where(predicate);
var p = q.First();
}
}
public void ContainsParameter3()
{
var str = "o";
using (var db = new TestDbManager())
{
var q =
from d in db.Doctor
join p in db.Person.Where(p => p.FirstName.Contains(str))
on d.PersonID equals p.ID
select p;
Assert.AreEqual(1, q.ToList().First().ID);
}
}
public void IsDbGeneratedTest([IncludeDataContexts("Sql2008", "Sql2012")] string context)
{
using (var db = new TestDbManager(context))
{
db.BeginTransaction();
var id = db.InsertWithIdentity(new L2SPersons
{
FirstName = "Test",
LastName = "Test",
Gender = "M"
});
db.GetTable<L2SPersons>().Delete(p => p.PersonID == ConvertTo<int>.From(id));
}
}
public void NoTransaction([DataContexts(ExcludeLinqService = true)] string context, [Values(Int32.MaxValue, 1)]int batchSize)
{
using (var db = new TestDbManager(context))
{
var list = new[]
{
new Parent { ParentID = 1111, Value1 = 1111 },
new Parent { ParentID = 2111, Value1 = 2111 },
new Parent { ParentID = 3111, Value1 = 3111 },
new Parent { ParentID = 4111, Value1 = 4111 },
};
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
db.InsertBatch(batchSize, list);
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
}
}
public void Transaction([DataContexts(ExcludeLinqService = true)] string context)
{
using (var db = new TestDbManager(context))
{
var list = new[]
{
new Parent { ParentID = 1111, Value1 = 1111 },
new Parent { ParentID = 2111, Value1 = 2111 },
new Parent { ParentID = 3111, Value1 = 3111 },
new Parent { ParentID = 4111, Value1 = 4111 },
};
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
db.BeginTransaction();
db.InsertBatch(list);
db.CommitTransaction();
foreach (var parent in list)
db.Parent.Delete(p => p.ParentID == parent.ParentID);
}
}
static void Main()
{
using (var db = new TestDbManager("MySql"))
{
var src = new TestDataSource(db);
var allParents = src.Parent;
var allPersons = src.Person;
var @p1 = "a";
var query = from e in allParents
join c in allPersons on e.Id equals c.Id
where c.Name.StartsWith(@p1)
orderby c.Name
select e;
Console.WriteLine(query.Expression.ToString());
Console.WriteLine();
Console.WriteLine(query);
var result = query.ToArray();
Console.WriteLine(result.Length);
}
}
public void GroupByExtraFieldBugTest()
{
// https://github.com/igor-tkachev/bltoolkit/issues/42
// extra field is generated in the GROUP BY clause, for example:
// GROUP BY p.LastName, p.LastName <--- the second one is redundant
using (var db = new TestDbManager("MySql"))
{
var q =
from d in db.Doctor
join p in db.Person on d.PersonID equals p.ID
group d by p.LastName into g
select g.Key;
q.ToList();
const string fieldName = "LastName";
var lastQuery = db.LastQuery;
var groupByPos = lastQuery.IndexOf("GROUP BY");
var fieldPos = lastQuery.IndexOf(fieldName, groupByPos);
// check that our field does not present in the GROUP BY clause second time
Assert.AreEqual(-1, lastQuery.IndexOf(fieldName, fieldPos + 1));
}
}
public void TestMethod()
{
using (var db = new TestDbManager())
{
IQueryable<IDocument> query = db.GetTable<Document>();
var idsQuery = query.Select(s => s.Id);
var str = idsQuery.ToString(); // Exception
Assert.IsNotNull(str);
}
}
public void MapAbstract()
{
using (var db = new TestDbManager())
{
var q = from a in db.GetTable<AbsChild>()
select new { a.ChildID, a.Parent.Value1 };
var ql = q.ToList();
}
}
public void MyType3()
{
using (var db = new TestDbManager { MappingSchema = _myMappingSchema })
{
db.BeginTransaction();
db.Insert(new MyParent { ParentID = new MyInt { MyValue = 1001 }, Value1 = 1001 });
db.Parent.Delete(p => p.ParentID >= 1000);
}
}
