public void InsertFromCrossJoinedSourceQuery([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
{
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 ContainsJoin1(string context)
{
using (var db = new TestDataConnection(context))
{
db.Child. Delete(c => c.ParentID >= 1000);
db.Parent.Delete(c => c.ParentID >= 1000);
try
{
var id = 1000;
db.Insert(new Parent { ParentID = id });
for (var i = 0; i < 3; i++)
db.Insert(new Child { ParentID = id, ChildID = 1000 + i });
var sql1 = ContainsJoin1Impl(db, new [] { 1000, 1001 });
var sql2 = ContainsJoin1Impl(db, new [] { 1002 });
Assert.That(sql1, Is.Not.EqualTo(sql2));
}
finally
{
db.Child. Delete(c => c.ParentID >= 1000);
db.Parent.Delete(c => c.ParentID >= 1000);
}
}
}
public void MergeWithDeletePredicate3([MergeBySourceDataContextSource] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
db.Insert(new Person()
{
FirstName = "Не ASCII",
Gender = Gender.Unknown,
LastName = "Last Name",
MiddleName = "Mid"
});
var person = db.Person.First();
db.Insert(new Patient()
{
PersonID = person.ID,
Diagnosis = "Negative"
});
var patient = db.Patient.Where(_ => _.PersonID == person.ID).First();
db.GetTable <Person>()
.Merge()
.Using(db.Person.Where(t => t.Patient == patient))
.OnTargetKey()
.UpdateWhenMatched()
.InsertWhenNotMatched()
.DeleteWhenNotMatchedBySourceAnd(t => t.Patient == patient)
.Merge();
}
}
private void PrepareAssociationsData(TestDataConnection db)
{
using (new DisableLogging())
{
db.Patient.Delete();
db.Doctor.Delete();
db.Person.Delete();
var id = 1;
foreach (var person in AssociationPersons)
{
person.ID = id++;
person.ID = Convert.ToInt32(db.InsertWithIdentity(person));
}
AssociationDoctors[0].PersonID = AssociationPersons[4].ID;
AssociationDoctors[1].PersonID = AssociationPersons[5].ID;
foreach (var doctor in AssociationDoctors)
{
db.Insert(doctor);
}
AssociationPatients[0].PersonID = AssociationPersons[2].ID;
AssociationPatients[1].PersonID = AssociationPersons[3].ID;
foreach (var patient in AssociationPatients)
{
db.Insert(patient);
}
}
}
public void ContainsJoin1(string context)
{
using (var db = new TestDataConnection(context))
{
db.Child.Delete(c => c.ParentID >= 1000);
db.Parent.Delete(c => c.ParentID >= 1000);
try
{
var id = 1000;
db.Insert(new Parent {
ParentID = id
});
for (var i = 0; i < 3; i++)
{
db.Insert(new Child {
ParentID = id, ChildID = 1000 + i
});
}
var sql1 = ContainsJoin1Impl(db, new[] { 1000, 1001 });
var sql2 = ContainsJoin1Impl(db, new[] { 1002 });
Assert.That(sql1, Is.Not.EqualTo(sql2));
}
finally
{
db.Child.Delete(c => c.ParentID >= 1000);
db.Parent.Delete(c => c.ParentID >= 1000);
}
}
}
public void MergeWithDeletePredicate4([IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
db.Insert(new Person()
{
FirstName = "Не ASCII",
Gender = Gender.Unknown,
LastName = "Last Name",
MiddleName = "Mid"
});
var person = db.Person.First();
db.InsertOrReplace(new Patient()
{
PersonID = person.ID,
Diagnosis = "Negative"
});
var patient = person.ID;
var merge = db.GetTable <Person>()
.Merge()
.Using(db.Person.Where(t => t.Patient !.PersonID == patient))
.OnTargetKey()
.UpdateWhenMatched()
.InsertWhenNotMatched()
.DeleteWhenNotMatchedBySourceAnd(t => t.Patient !.PersonID == patient);
merge.Merge();
patient++;
merge.Merge();
}
}
public void Issue1007OnOldAPIv1(string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
var table = db.GetTable <TestMappingWithIdentity>();
table.Delete();
db.Insert(new TestMappingWithIdentity());
var lastId = table.Select(_ => _.Id).Max();
var source = new[]
{
new TestMappingWithIdentity()
{
Id = lastId,
Field = 10
}
};
var rows = db.Merge(source);
var result = table.OrderBy(_ => _.Id).ToList();
AssertRowCount(1, rows, context);
Assert.AreEqual(1, result.Count);
var newRecord = new TestMapping1();
Assert.AreEqual(lastId, result[0].Id);
Assert.AreEqual(10, result[0].Field);
}
}
public void CommitTransaction(string context)
{
using (var db = new TestDataConnection(context))
{
db.Insert(new Parent {
ParentID = 1010, Value1 = 1010
});
try
{
using (var tr = db.BeginTransaction())
{
db.Parent.Update(t => t.ParentID == 1010, t => new Parent {
Value1 = 1011
});
tr.Commit();
}
var p = db.Parent.First(t => t.ParentID == 1010);
Assert.That(p.Value1, Is.EqualTo(1011));
}
finally
{
db.Parent.Delete(t => t.ParentID >= 1000);
}
}
}
public void TestBinaryLengthTranslation([IncludeDataSources(TestProvName.AllSQLite)] string context)
{
using (var db = new TestDataConnection(context))
using (var table = db.CreateLocalTable <IssueClass>())
{
db.Insert(new IssueClass()
{
Id = 99,
Data = new byte[] { 1, 2, 3, 4, 5 },
DataB = new Binary(new byte[] { 1, 2, 3, 4, 5, 6 })
});
var qry1 = table.Select(x => Sql.Length(x.Data)).ToList();
Assert.That(db.LastQuery !.ToLower().Contains("length("));
var qry2 = table.Select(x => x.Data !.Length).ToList();
Assert.That(db.LastQuery !.ToLower().Contains("length("));
var qry3 = db.GetTable <IssueClass>().Select(x => Sql.Length(x.DataB)).ToList();
Assert.That(db.LastQuery !.ToLower().Contains("length("));
var qry4 = db.GetTable <IssueClass>().Select(x => x.DataB !.Length).ToList();
Assert.That(db.LastQuery !.ToLower().Contains("length("));
Assert.AreEqual(5, qry1[0]);
Assert.AreEqual(5, qry2[0]);
Assert.AreEqual(6, qry3[0]);
Assert.AreEqual(6, qry4[0]);
}
}
public void AutoRollbackTransaction()
{
using (var db = new TestDataConnection())
{
db.Insert(new Parent {
ParentID = 1010, Value1 = 1010
});
try
{
using (db.BeginTransaction())
{
db.Parent.Update(t => t.ParentID == 1010, t => new Parent {
Value1 = 1012
});
}
var p = db.Parent.First(t => t.ParentID == 1010);
Assert.That(p.Value1, Is.Not.EqualTo(1012));
}
finally
{
db.Parent.Delete(t => t.ParentID >= 1000);
}
}
}
public void RollbackTransaction([DataSources(false)] string context)
{
using (var db = new TestDataConnection(context))
{
db.Insert(new Parent {
ParentID = 1010, Value1 = 1010
});
try
{
using (var tr = db.BeginTransaction())
{
db.Parent.Update(t => t.ParentID == 1010, t => new Parent {
Value1 = 1012
});
tr.Rollback();
}
var p = db.Parent.First(t => t.ParentID == 1010);
Assert.That(p.Value1, Is.Not.EqualTo(1012));
}
finally
{
db.Parent.Delete(t => t.ParentID >= 1000);
}
}
}
private void Update(string context, Data data, int iteration)
{
using (var db = new TestDataConnection(context))
{
foreach (var record in data.Updates)
{
db.Insert(record);
}
db.GetTable <Animal>().Merge()
.Using(db.GetTable <AnimalUpdate>().Where(_ => _.Iteration == iteration))
.On((target, source) => target.Name == source.Name)
.UpdateWhenMatched(
(target, source) => new Animal()
{
Color = data.Color,
Length = source.Length
})
.InsertWhenNotMatched(
source => new Animal()
{
Name = source.Name,
Color = data.Color,
Length = source.Length
})
.Merge();
}
}
public void Test([DataSources(false)] string context)
{
using (var db = new TestDataConnection(context))
using (db.CreateLocalTable <Parent564>())
{
db.Insert(new Child564A()
{
StringValue = "SomeValue"
});
db.Insert(new Child564B()
{
IntValue = 911
});
Assert.AreEqual(2, db.GetTable <Parent564>().Count());
}
}
public void Issue1007OnOldAPIv2([IdentityInsertMergeDataContextSource(
ProviderName.Sybase, ProviderName.SybaseManaged)]
string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
db.Doctor.Delete();
db.Patient.Delete();
var table = db.GetTable <Person1007>();
table.Delete();
db.Insert(new Person1007()
{
FirstName = "first name",
LastName = "last name",
Gender = Gender.Female
});
var lastId = table.Select(_ => _.ID).Max();
var source = new[]
{
new Person1007()
{
FirstName = "first name",
LastName = "updated",
Gender = Gender.Male,
ID = 10
}
};
var rows = db.Merge(source);
var result = table.OrderBy(_ => _.ID).ToList();
AssertRowCount(1, rows, context);
Assert.AreEqual(1, result.Count);
var newRecord = new TestMapping1();
Assert.AreEqual(lastId, result[0].ID);
Assert.AreEqual("first name", result[0].FirstName);
Assert.AreEqual("updated", result[0].LastName);
Assert.AreEqual(Gender.Male, result[0].Gender);
}
}
public void ImplicitInsertIdentityWithSkipOnInsert(
[IdentityInsertMergeDataContextSource] string context)
{
using (var db = new TestDataConnection(context))
{
var table = db.GetTable <TestMappingWithIdentity>();
table.Delete();
db.Insert(new TestMappingWithIdentity());
var lastId = table.Select(_ => _.Id).Max();
var source = new[]
{
new TestMappingWithIdentity()
{
Field = 22,
},
new TestMappingWithIdentity()
{
Field = 23
}
};
var rows = table
.Merge()
.Using(source)
.On((s, t) => s.Field == t.Field)
.InsertWhenNotMatched()
.Merge();
var result = table.OrderBy(_ => _.Id).ToList();
AssertRowCount(2, rows, context);
Assert.AreEqual(3, result.Count);
var newRecord = new TestMapping1();
Assert.AreEqual(lastId, result[0].Id);
Assert.AreEqual(null, result[0].Field);
Assert.AreEqual(lastId + 1, result[1].Id);
Assert.AreEqual(22, result[1].Field);
Assert.AreEqual(lastId + 2, result[2].Id);
Assert.AreEqual(23, result[2].Field);
}
}
public void MyType3()
{
using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema) as TestDataConnection)
{
try
{
db.Insert(new MyParent {
ParentID = new MyInt {
MyValue = 1001
}, Value1 = 1001
});
}
finally
{
db.Parent.Delete(p => p.ParentID >= 1000);
}
}
}
public void SequenceNameTest([IncludeDataSources(false, TestProvName.AllOracle)]
string context)
{
using (var db = new TestDataConnection(context))
{
db.BeginTransaction();
var user = new User {
Name = "user"
};
user.Id = Convert.ToInt64(db.InsertWithIdentity(user));
Assert.True(db.LastQuery?.Contains("\"c##sequence_schema\".\"sq_test_user\".nextval"));
db.Insert(new Contract {
UserId = user.Id, ContractNo = 1, Name = "contract1"
});
Assert.True(db.LastQuery?.Contains("\t\"sq_test_user_contract\".nextval"));
}
}
public void Issue1007OnNewAPI(string context)
{
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
var table = db.GetTable <TestMappingWithIdentity>();
table.Delete();
db.Insert(new TestMappingWithIdentity());
var lastId = table.Select(_ => _.Id).Max();
var source = new[]
{
new TestMappingWithIdentity()
{
Field = 10
}
};
var rows = table
.Merge()
.Using(source)
.On((s, t) => s.Field == null)
.InsertWhenNotMatched()
.UpdateWhenMatched()
.Merge();
var result = table.OrderBy(_ => _.Id).ToList();
AssertRowCount(1, rows, context);
Assert.AreEqual(1, result.Count);
var newRecord = new TestMapping1();
Assert.AreEqual(lastId, result[0].Id);
Assert.AreEqual(10, result[0].Field);
}
}
public void AutoRollbackTransaction(string context)
{
using (var db = new TestDataConnection(context))
{
db.Insert(new Parent { ParentID = 1010, Value1 = 1010 });
try
{
using (db.BeginTransaction())
{
db.Parent.Update(t => t.ParentID == 1010, t => new Parent { Value1 = 1012 });
}
var p = db.Parent.First(t => t.ParentID == 1010);
Assert.That(p.Value1, Is.Not.EqualTo(1012));
}
finally
{
db.Parent.Delete(t => t.ParentID >= 1000);
}
}
}
public void CommitTransaction()
{
using (var db = new TestDataConnection())
{
db.Insert(new Parent { ParentID = 1010, Value1 = 1010 });
try
{
using (var tr = db.BeginTransaction())
{
db.Parent.Update(t => t.ParentID == 1010, t => new Parent { Value1 = 1011 });
tr.Commit();
}
var p = db.Parent.First(t => t.ParentID == 1010);
Assert.That(p.Value1, Is.EqualTo(1011));
}
finally
{
db.Parent.Delete(t => t.ParentID >= 1000);
}
}
}
public void MyType3()
{
using (var db = new TestDataConnection().AddMappingSchema(_myMappingSchema) as TestDataConnection)
{
try
{
db.Insert(new MyParent { ParentID = new MyInt { MyValue = 1001 }, Value1 = 1001 });
}
finally
{
db.Parent.Delete(p => p.ParentID >= 1000);
}
}
}
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);
}
}
}
}
}
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);
}
}
