public void Test()
{
using (var db = new TestDataConnection())
{
var q = (
from t1 in db.GetTable<Table1>()
where t1.Field3 != null
select new
{
t1.Ref1.Ref4.Field6, t1.Ref3.Field4,
Field1 = t1.Ref2.Ref5.Field8 ?? string.Empty
}
).Distinct();
var sql1 = q.ToString();
var q2 =
from t3 in q
group t3 by new { t3.Field6, t3.Field4 }
into g
where g.Count() > 1
select new { g.Key.Field6, EngineeringCircuitNumber = g.Key.Field4, Count = g.Count() };
var sql2 = q2.ToString();
var idx = sql2.IndexOf("DISTINCT");
Assert.That(idx, Is.GreaterThanOrEqualTo(0));
idx = sql2.IndexOf("Field8", idx);
Assert.That(idx, Is.GreaterThanOrEqualTo(0));
}
}
public void AliasTest1()
{
using (var db = new TestDataConnection())
{
var count = db.GetTable<TestParent>().Count(t => t.ID > 0);
}
}
public void Test()
{
using (var db = new TestDataConnection())
{
/*
var query =
from t3 in db.Parent
//let t1 = t3.Children.SelectMany(x => x.GrandChildren)
//let t2 = t3.Table2s.SelectMany(x => x.Table1s)
select new
{
//c2 = t1.Count(),
c1 = t3.Children.SelectMany(x => x.GrandChildren),
};
*/
var query =
from t3 in db.GetTable<Table3>()
let t1 = t3.Children.SelectMany(x => x.GrandChildren)
//let t2 = t3.Children.SelectMany(x => x.GrandChildren)
select new
{
c2 = t1.Count(),
c1 = t3.Children.SelectMany(x => x.GrandChildren).Count(),
};
query.FirstOrDefault(p => p.c2 > 1);
query.FirstOrDefault();
}
}
public void MergeChar1(string context)
{
using (var db = new TestDataConnection(context))
{
var id = ConvertTo<int>.From(db.GetTable<AllType>().InsertWithIdentity(() => new AllType
{
charDataType = '\x0',
ncharDataType = "\x0"
}));
try
{
db.Merge(db.GetTable<AllType>().Where(t => t.ID == id));
}
finally
{
db.GetTable<AllType>().Delete(t => t.ID == id);
}
}
}
public void AliasTest2()
{
using (var db = new TestDataConnection())
{
db.GetTable<TestParent>()
.Where(t => t.ID < 0 && t.ID > 0)
.Update(t => new TestParent
{
ID = t.ID - 1
});
}
}
public void IsDbGeneratedTest()
{
using (var db = new TestDataConnection())
{
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 Test()
{
using (var db = new TestDataConnection())
{
var q = db.GetTable<CustomTableColumn>()
.Select(
x => new
{
DataType = Sql.AsSql(ColumnDataType.Unknown),
});
var sql = q.ToString();
Assert.That(sql, Is.Not.Contains("Unknown"));
}
}
public void LetTest2()
{
using (var repository = new TestDataConnection())
{
var q =
from t1 in repository.GetTable<Table2>()
from t2 in
from t5 in t1.Ref3.Ref4.Ref1.Ref2
let t3 = t1.Ref3
where t3.Ref5 == t5.Ref5
from t4 in t5.Ref9
select t4
select t1;
var linqResult = q.ToString();
}
}
public void MergeChar2(string context)
{
using (var db = new TestDataConnection(context))
{
try
{
db.Merge(new[]
{
new AllType
{
ID = 10,
charDataType = '\x0',
ncharDataType = "\x0"
}
});
}
finally
{
db.GetTable<AllType>().Delete(t => t.ID == 10);
}
}
}
public void InsertFromSelectManySourceQuery([MergeDataContextSource(false)] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
// prepare test data
db.GetTable<CrossJoinLeft>().Delete();
db.GetTable<CrossJoinRight>().Delete();
db.GetTable<CrossJoinResult>().Delete();
db.Insert(new CrossJoinLeft() { Id = 1 });
db.Insert(new CrossJoinLeft() { Id = 2 });
db.Insert(new CrossJoinRight() { Id = 10 });
db.Insert(new CrossJoinRight() { Id = 20 });
db.Insert(new CrossJoinResult() { Id = 11, LeftId = 100, RightId = 200 });
var source = db.GetTable<CrossJoinLeft>()
.SelectMany(
r => db.GetTable<CrossJoinRight>(),
(t1, t2) =>
new
{
LeftId = t1.Id,
RightId = t2.Id,
ResultId = t1.Id + t2.Id
});
var rows = db.GetTable<CrossJoinResult>()
.Merge()
.Using(source)
.On((t, s) => t.Id == s.ResultId)
.InsertWhenNotMatched(s => new CrossJoinResult()
{
Id = s.ResultId,
LeftId = s.LeftId,
RightId = s.RightId
})
.Merge();
var result = db.GetTable<CrossJoinResult>().OrderBy(_ => _.Id).ToList();
AssertRowCount(3, rows, context);
Assert.AreEqual(4, result.Count);
Assert.AreEqual(11, result[0].Id);
Assert.AreEqual(100, result[0].LeftId);
Assert.AreEqual(200, result[0].RightId);
Assert.AreEqual(12, result[1].Id);
Assert.AreEqual(2, result[1].LeftId);
Assert.AreEqual(10, result[1].RightId);
Assert.AreEqual(21, result[2].Id);
Assert.AreEqual(1, result[2].LeftId);
Assert.AreEqual(20, result[2].RightId);
Assert.AreEqual(22, result[3].Id);
Assert.AreEqual(2, result[3].LeftId);
Assert.AreEqual(20, result[3].RightId);
}
}
public void TestMethod()
{
using (var db = new TestDataConnection())
{
IQueryable<IDocument> query = db.GetTable<Document>();
var idsQuery = query.Select(s => s.Id);
var str = idsQuery.ToString(); // Exception
Assert.IsNotNull(str);
}
}
public void MyType2()
{
using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema))
{
var list = db.GetTable<MyParent>()
.Select(t => new MyParent { ParentID = t.ParentID, Value1 = t.Value1 })
.ToList();
}
}
public void MyType1()
{
using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema))
{
var list = db.GetTable<MyParent>().ToList();
}
}
public ITable<Person> People2(TestDataConnection db)
{
return db.GetTable<Person>();
}
public void CharTest2(string context)
{
List<PersonCharTest> list;
using (var db = new TestDataConnection())
list = db.GetTable<PersonCharTest>().ToList();
using (var db = GetDataContext(context))
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);
}
public void Concat90()
{
using(var context = new TestDataConnection())
{
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 ITable <Person> People2(TestDataConnection db)
{
return(db.GetTable <Person>());
}
public void CrossJoinedSourceWithSingleFieldSelection([MergeDataContextSource(false)] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
// prepare test data
db.GetTable<CrossJoinLeft>().Delete();
db.GetTable<CrossJoinRight>().Delete();
db.GetTable<CrossJoinResult>().Delete();
db.Insert(new CrossJoinLeft() { Id = 1 });
db.Insert(new CrossJoinLeft() { Id = 2 });
db.Insert(new CrossJoinRight() { Id = 10 });
db.Insert(new CrossJoinRight() { Id = 20 });
db.Insert(new CrossJoinResult() { Id = 11, LeftId = 100, RightId = 200 });
var source = from t1 in db.GetTable<CrossJoinLeft>()
from t2 in db.GetTable<CrossJoinRight>()
select new
{
RightId = t2.Id
};
var rows = db.GetTable<CrossJoinResult>()
.Merge()
.Using(source)
.On((t, s) => t.Id == s.RightId)
.InsertWhenNotMatched(s => new CrossJoinResult()
{
RightId = s.RightId
})
.Merge();
// sort on client, see SortedMergeResultsIssue test for details
var result = db.GetTable<CrossJoinResult>().AsEnumerable().OrderBy(_ => _.Id).ThenBy(_ => _.RightId).ToList();
AssertRowCount(4, rows, context);
Assert.AreEqual(5, result.Count);
Assert.AreEqual(0, result[0].Id);
Assert.AreEqual(0, result[0].LeftId);
Assert.AreEqual(10, result[0].RightId);
Assert.AreEqual(0, result[1].Id);
Assert.AreEqual(0, result[1].LeftId);
Assert.AreEqual(10, result[1].RightId);
Assert.AreEqual(0, result[2].Id);
Assert.AreEqual(0, result[2].LeftId);
Assert.AreEqual(20, result[2].RightId);
Assert.AreEqual(0, result[3].Id);
Assert.AreEqual(0, result[3].LeftId);
Assert.AreEqual(20, result[3].RightId);
Assert.AreEqual(11, result[4].Id);
Assert.AreEqual(100, result[4].LeftId);
Assert.AreEqual(200, result[4].RightId);
}
}
public void SelectField()
{
using (var db = new TestDataConnection())
{
var q =
from p in db.GetTable<TestParent>()
select p.Value1_;
var sql = q.ToString();
Assert.That(sql.IndexOf("ParentID_"), Is.LessThan(0));
}
}
public void MyType5()
{
using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema) as TestDataConnection)
{
try
{
db.GetTable<MyParent>().Insert(() => new MyParent { ParentID = new MyInt { MyValue = 1001 }, Value1 = 1001 });
}
finally
{
db.Parent.Delete(p => p.ParentID >= 1000);
}
}
}
public void SimplTest()
{
using (var db = new TestDataConnection())
Assert.AreEqual(1, db.GetTable <PersonEx>().Where(_ => _.FirstName == "John").Select(_ => _.ID).Single());
}
public void KeepIdentity_SkipOnInsertTrue(
[DataSources(false)] string context,
[Values(null, true, false)] bool?keepIdentity,
[Values] BulkCopyType copyType)
{
if ((context == ProviderName.OracleNative || context == TestProvName.Oracle11Native) && copyType == BulkCopyType.ProviderSpecific)
{
Assert.Inconclusive("Oracle BulkCopy doesn't support identity triggers");
}
// don't use transactions as some providers will fallback to non-provider-specific implementation then
using (var db = new TestDataConnection(context))
{
var lastId = db.InsertWithInt32Identity(new TestTable2());
try
{
var options = new BulkCopyOptions()
{
KeepIdentity = keepIdentity,
BulkCopyType = copyType
};
if (!Execute(db, context, perform, keepIdentity, copyType))
{
return;
}
var data = db.GetTable <TestTable2>().Where(_ => _.ID > lastId).OrderBy(_ => _.ID).ToArray();
Assert.AreEqual(2, data.Length);
// oracle supports identity insert only starting from version 12c, which is not used yet for tests
var useGenerated = keepIdentity != true ||
context.Contains("Oracle");
Assert.AreEqual(lastId + (!useGenerated ? 10 : 1), data[0].ID);
Assert.AreEqual(200, data[0].Value);
Assert.AreEqual(lastId + (!useGenerated ? 20 : 2), data[1].ID);
Assert.AreEqual(300, data[1].Value);
void perform()
{
db.BulkCopy(
options,
new[]
{
new TestTable2()
{
ID = lastId + 10,
Value = 200
},
new TestTable2()
{
ID = lastId + 20,
Value = 300
}
});
}
}
finally
{
// cleanup
db.GetTable <TestTable2>().Delete(_ => _.ID >= lastId);
}
}
}
public void SelectComplexField()
{
using (var db = new TestDataConnection())
{
var q =
from p in db.GetTable<ComplexPerson>()
select p.Name.LastName;
var sql = q.ToString();
Assert.That(sql.IndexOf("First"), Is.LessThan(0));
Assert.That(sql.IndexOf("LastName"), Is.GreaterThan(0));
}
}
public void KeepIdentity_SkipOnInsertFalse(
[DataSources(false)] string context,
[Values(null, true, false)] bool?keepIdentity,
[Values] BulkCopyType copyType)
{
List <TestTable1> list = null;
// don't use transactions as some providers will fallback to non-provider-specific implementation then
using (var db = new TestDataConnection(context))
//using (db.BeginTransaction())
{
var lastId = db.InsertWithInt32Identity(new TestTable1());
try
{
list = db.GetTable <TestTable1>().ToList();
db.GetTable <TestTable1>().Delete();
var options = new BulkCopyOptions()
{
KeepIdentity = keepIdentity,
BulkCopyType = copyType
};
if (!Execute(context, perform, keepIdentity, copyType))
{
return;
}
var data = db.GetTable <TestTable1>().Where(_ => _.ID > lastId).OrderBy(_ => _.ID).ToArray();
Assert.AreEqual(2, data.Length);
// oracle supports identity insert only starting from version 12c, which is not used yet for tests
var useGenerated = keepIdentity != true ||
context == ProviderName.Oracle ||
context == ProviderName.OracleNative ||
context == ProviderName.OracleManaged;
Assert.AreEqual(lastId + (!useGenerated ? 10 : 1), data[0].ID);
Assert.AreEqual(200, data[0].Value);
Assert.AreEqual(lastId + (!useGenerated ? 20 : 2), data[1].ID);
Assert.AreEqual(300, data[1].Value);
void perform()
{
db.BulkCopy(
options,
new[]
{
new TestTable1()
{
ID = lastId + 10,
Value = 200
},
new TestTable1()
{
ID = lastId + 20,
Value = 300
}
});
}
}
finally
{
// cleanup
db.GetTable <TestTable2>().Delete(_ => _.ID >= lastId);
if (list != null)
{
foreach (var item in list)
{
db.Insert(item);
}
}
}
}
}