how to delete using nhibernate using where with joins

I have the following method. Which returns "Could not execute update query". If i query another entities pr开发者_如何学运维operty ie "Order.Campaign.Id" do i need include a join? How would this work. ?

public void RemoveOrderItems(int companyId, int campaignId, int productId, int orderStatus)
{
    using (ITransaction transaction = _session.BeginTransaction())
    {
        _session.CreateQuery("delete from OrderItem where Product.Id = '" + productId + "' and Order.Company.Id = '" + companyId + "' and Order.Campaign.Id = '" + campaignId + "' and Order.OrderStatus = '" + orderStatus + "'").ExecuteUpdate();
        transaction.Commit();
    }
}

** EDIT **

Here is the sql statement.

DELETE oi
FROM OrderItems oi inner JOIN  Orders o On oi.OrderId = o.Id
Where oi.ProductId = '13077' and o.CompanyId = '32' and o.CampaignId = '2' and o.OrderStatus = 3

_session.CreateQuery(@"
    delete OrderItem oi
    where oi in
         (select i
          from OrderItem i
          where i.Product.Id = :productId
          and i.Order.Company.Id = :companyId
          and i.Order.Campaign.Id :campaignId
          and i.Order.OrderStatus = :orderStatus)
    ")
    .SetParameter("productId", productId)
    .SetParameter("companyId", companyId)
    .SetParameter("campaignId", campaignId)
    .SetParameter("orderStatus", orderStatus)
    .ExecuteUpdate();

The generated SQL is not exactly the same that you created manually, but it's semantically equivalent.

The sql statement is invalid, your where statement is incorrect. Try to write the query in the Microsoft SQL Server Management Studio and then rewrite it with your params to the CreateQuery.

Find the right query in SQL Server Management studio and after do something like my sample (the query can but more dificult of course with join, subquery)

ISession session = SessionFactory.GetCurrentSession();
using (var tx = session.BeginTransaction())
{
    try
    {
        IQuery myQuery = session.CreateQuery("DELETE FROM TableA c WHERE c.Field1 = :Filed1Value and c.Field2 = :Field2 ")
            .SetParameter("Field1", 25)
            .SetParameter("Field2", "Test")
        myQuery.ExecuteUpdate();
        tx.Commit();
    }
    catch (Exception)
    {
        tx.Rollback();
    }
}