Hibernate Community • View topic - Execute SQL Delete statements

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

Execute SQL Delete statements

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

parus

Post subject: Execute SQL Delete statements

Posted: Thu Apr 24, 2008 8:34 am

Newbie

Joined: Thu Feb 14, 2008 5:34 am
Posts: 8

Is it possible to execute a normal SQL delete statement?
I have 3 tables:
Sheet,
Operation
and OperationSheet which is used for a many-to-many association between the two tables and contains the 2 id's.
The many-to-many association is only visible in the Operation mapping:

Code:

<bag name="Sheetlist" table="OperationSheet" lazy="false" >
      <key column="OperationID"></key>
      <many-to-many class="Plan.Sheet, Plan" column="SheetID"/>
 </bag>

Now I want to delete all the sheets that have no connection to the operation table, in SQL (sql server 2005) my statement would be something like this:

Code:

DELETE From Sheet WHERE Sheet.SheetID IN(
SELECT Sheet.SheetID FROM Sheet LEFT OUTER JOIN
OperationSheet on 
Sheet.SheetID = OperationSheet.SheetID
WHERE OperationSheet.SheetID IS NULL)

Is it possible to execute this statement with NHibernate or are there alternatives which have the same result?

Top

FlukeFan

Post subject: Execute SQL Delete statements

Posted: Thu Apr 24, 2008 1:13 pm

Senior

Joined: Thu Jun 21, 2007 8:03 am
Posts: 127
Location: UK

Hi,

You could try someting like:

Code:

DetachedCriteria allUsedSheets =
    DetachedCriteria.For<Operation>()
        .CreateAlias("Sheetlist", "Sheetlist")
        .SetProjection(Projections.Property("Sheetlist.id"));

IList<Sheet> unusedSheets =
    session
        .CreateCriteria(typeof(Sheet))
        .Add(Subqueries.PropertyNotIn("id", allUsedSheets))
        .List<Sheet>();

foreach (Sheet unusedSheet in unusedSheets)
{
    session.Delete(unusedSheet);
}

Regards,
Richard

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum