public void DeleteWithSubquery()
{
// setup the test data...
ISession s = OpenSession();
s.BeginTransaction();
var owner = new SimpleEntityWithAssociation {
Name = "myEntity-1"
};
owner.AddAssociation("assoc-1");
owner.AddAssociation("assoc-2");
owner.AddAssociation("assoc-3");
s.Save(owner);
var owner2 = new SimpleEntityWithAssociation {
Name = "myEntity-2"
};
owner2.AddAssociation("assoc-1");
owner2.AddAssociation("assoc-2");
owner2.AddAssociation("assoc-3");
owner2.AddAssociation("assoc-4");
s.Save(owner2);
var owner3 = new SimpleEntityWithAssociation {
Name = "myEntity-3"
};
s.Save(owner3);
s.Transaction.Commit();
s.Close();
// now try the bulk delete
s = OpenSession();
s.BeginTransaction();
int count =
s.CreateQuery("delete SimpleEntityWithAssociation e where size(e.AssociatedEntities ) = 0 and e.Name like '%'").
ExecuteUpdate();
Assert.That(count, Is.EqualTo(1), "Incorrect delete count");
s.Transaction.Commit();
s.Close();
// finally, clean up
s = OpenSession();
s.BeginTransaction();
s.CreateQuery("delete SimpleAssociatedEntity").ExecuteUpdate();
s.CreateQuery("delete SimpleEntityWithAssociation").ExecuteUpdate();
s.Transaction.Commit();
s.Close();
}
public void DeleteWithSubquery()
{
// setup the test data...
ISession s = OpenSession();
s.BeginTransaction();
var owner = new SimpleEntityWithAssociation {Name = "myEntity-1"};
owner.AddAssociation("assoc-1");
owner.AddAssociation("assoc-2");
owner.AddAssociation("assoc-3");
s.Save(owner);
var owner2 = new SimpleEntityWithAssociation {Name = "myEntity-2"};
owner2.AddAssociation("assoc-1");
owner2.AddAssociation("assoc-2");
owner2.AddAssociation("assoc-3");
owner2.AddAssociation("assoc-4");
s.Save(owner2);
var owner3 = new SimpleEntityWithAssociation {Name = "myEntity-3"};
s.Save(owner3);
s.Transaction.Commit();
s.Close();
// now try the bulk delete
s = OpenSession();
s.BeginTransaction();
int count =
s.CreateQuery("delete SimpleEntityWithAssociation e where size(e.AssociatedEntities ) = 0 and e.Name like '%'").
ExecuteUpdate();
Assert.That(count, Is.EqualTo(1), "Incorrect delete count");
s.Transaction.Commit();
s.Close();
// finally, clean up
s = OpenSession();
s.BeginTransaction();
s.CreateQuery("delete SimpleAssociatedEntity").ExecuteUpdate();
s.CreateQuery("delete SimpleEntityWithAssociation").ExecuteUpdate();
s.Transaction.Commit();
s.Close();
}
public void UpdateWithWhereExistsSubquery()
{
// multi-table ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
var joe = new Human {Name = new Name {First = "Joe", Initial = 'Q', Last = "Public"}};
s.Save(joe);
var doll = new Human {Name = new Name {First = "Kyu", Initial = 'P', Last = "Doll"}, Friends = new[] {joe}};
s.Save(doll);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
string updateQryString = "update Human h " + "set h.description = 'updated' " + "where exists ("
+ " select f.id " + " from h.friends f " + " where f.name.last = 'Public' "
+ ")";
int count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
s.Delete(doll);
s.Delete(joe);
t.Commit();
s.Close();
// single-table (one-to-many & many-to-many) ~~~~~~~~~~~~~~~~~~~~~~~~~~
s = OpenSession();
t = s.BeginTransaction();
var entity = new SimpleEntityWithAssociation();
var other = new SimpleEntityWithAssociation();
entity.Name = "main";
other.Name = "many-to-many-association";
entity.ManyToManyAssociatedEntities.Add(other);
entity.AddAssociation("one-to-many-association");
s.Save(entity);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
// one-to-many test
updateQryString = "update SimpleEntityWithAssociation e set e.Name = 'updated' where "
+ "exists(select a.id from e.AssociatedEntities a " + "where a.Name = 'one-to-many-association')";
count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
// many-to-many test
if (Dialect.SupportsSubqueryOnMutatingTable)
{
updateQryString = "update SimpleEntityWithAssociation e set e.Name = 'updated' where "
+ "exists(select a.id from e.ManyToManyAssociatedEntities a "
+ "where a.Name = 'many-to-many-association')";
count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
}
IEnumerator mtm = entity.ManyToManyAssociatedEntities.GetEnumerator();
mtm.MoveNext();
s.Delete(mtm.Current);
s.Delete(entity);
t.Commit();
s.Close();
}
public void UpdateWithWhereExistsSubquery()
{
// multi-table ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ISession s = OpenSession();
ITransaction t = s.BeginTransaction();
var joe = new Human {
Name = new Name {
First = "Joe", Initial = 'Q', Last = "Public"
}
};
s.Save(joe);
var doll = new Human {
Name = new Name {
First = "Kyu", Initial = 'P', Last = "Doll"
}, Friends = new[] { joe }
};
s.Save(doll);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
string updateQryString = "update Human h " + "set h.description = 'updated' " + "where exists ("
+ " select f.id " + " from h.friends f " + " where f.name.last = 'Public' "
+ ")";
int count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
s.Delete(doll);
s.Delete(joe);
t.Commit();
s.Close();
// single-table (one-to-many & many-to-many) ~~~~~~~~~~~~~~~~~~~~~~~~~~
s = OpenSession();
t = s.BeginTransaction();
var entity = new SimpleEntityWithAssociation();
var other = new SimpleEntityWithAssociation();
entity.Name = "main";
other.Name = "many-to-many-association";
entity.ManyToManyAssociatedEntities.Add(other);
entity.AddAssociation("one-to-many-association");
s.Save(entity);
t.Commit();
s.Close();
s = OpenSession();
t = s.BeginTransaction();
// one-to-many test
updateQryString = "update SimpleEntityWithAssociation e set e.Name = 'updated' where "
+ "exists(select a.id from e.AssociatedEntities a " + "where a.Name = 'one-to-many-association')";
count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
// many-to-many test
if (Dialect.SupportsSubqueryOnMutatingTable)
{
updateQryString = "update SimpleEntityWithAssociation e set e.Name = 'updated' where "
+ "exists(select a.id from e.ManyToManyAssociatedEntities a "
+ "where a.Name = 'many-to-many-association')";
count = s.CreateQuery(updateQryString).ExecuteUpdate();
Assert.That(count, Is.EqualTo(1));
}
IEnumerator mtm = entity.ManyToManyAssociatedEntities.GetEnumerator();
mtm.MoveNext();
s.Delete(mtm.Current);
s.Delete(entity);
t.Commit();
s.Close();
}