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); } } } } }