CODEWITHSUNDEEP
nhibernate
frosty
frosty

Reputation: 5370

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 property 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

Upvotes: 0

Views: 1781

Answers (3)

Diego Mijelshon
Diego Mijelshon

Reputation: 52725

_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.

Upvotes: 3

TheBoubou
TheBoubou

Reputation: 19903

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

Upvotes: 1

Peter
Peter

Reputation: 27944

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.

Upvotes: 1

Related Questions