0

I'm using the NHibernate(version: 3.3.1.4000) and I'm trying execute an HQL query, with a subquery as a SELECT value. It works fine, the problem is when there is no data in the subquery, then I'm having the following error:

  • "No row with the given identifier exists[namespace.Table2#-2147483648]"

The code example:

IQuery query = session.CreateQuery(@"SELECT tb1, 
        (SELECT tb2 
           FROM Table2 tb2
          WHERE tb2.IdVal = tb1.IdVal
            AND tb2.Id2 = :id2)
    FROM Table1 tb1
   WHERE tb1.Cod = :cod1");

IList<object[]> dataResult = query.SetParameter("id2", "value")
    .SetParameter("cod1", 1).List<object[]>(); // The error is trown in this Line

I want to know how can I avoid this error, I just want the subquery return a "null" value when there is no data.

I'll be grateful for any help. Thanks in advance.

Jacobi
  • 1,508
  • 15
  • 29
  • Does this question/answer help you at all? http://stackoverflow.com/q/695931/497356 – Andrew Whitaker Jul 07 '14 at 15:51
  • HI @AndrewWhitaker, thanks, but unfortunately, I already find over the stackoverflow, and couldn't found an answer. – Jacobi Jul 07 '14 at 16:30
  • What exactly are you passing as values to the parameters? The code looks a little strange - what is id2 for... another entity? – cbp Aug 01 '14 at 14:08
  • Hi @cbp, the "id2" is only a string field, and it's a filter to Table2. – Jacobi Aug 01 '14 at 14:16
  • Hi @cbp, thi code works fine, when I have data in the Table2 with the where clause, the problem only occurs when there is no data according to the where clause, but in this case I think that the query should return NULL. – Jacobi Aug 01 '14 at 14:21
  • Are you sure the error is coming from the execution of the query, and not from something you are passing to the parameters. For example, you may get that exception in a case like this: .SetParameter("cod1", anotherEntity.Cod1). If anotherEntity is a proxy, then it may be lazily loaded at this point... and if it doesn't exist in the db, you might get that exception. – cbp Aug 01 '14 at 14:21
  • Hi @cbp, unfortunately I'm sure that error is coming from execution of the query. In my case the parameter "cod1", is an "int" variable, and in the "id2", is a "string" variable. I made a test removing a register from the "Table2", and the error thrown as I said. – Jacobi Aug 01 '14 at 17:09
  • Is there any reason you couldn't just use a native sql query? It doesn't look like anything you're doing would really be dialect-specific, if that's your reason for avoiding it. – DanP Aug 08 '14 at 13:06
  • Hi @DanP, Thanks for your suggestion, I'll think about it, and I'll make a test to see if it work as expected. – Jacobi Aug 11 '14 at 11:37

3 Answers3

0

It looks like what you are trying to do is execute a left join. Try this instead of using a sub query:

IQuery query = session.CreateQuery(@"
    SELECT tb1, tb2
    FROM Table1 tb1
    LEFT JOIN Table2 tb2 ON tb1.IdVal = tb2.IdVal
    WHERE tb1.Cod = :cod1 AND tb2.Id2 = :id2;
");

IList<object[]> dataResult = query
                            .SetParameter("id2", "value")
                            .SetParameter("cod1", 1)
                            .List<object[]>();
Hamilton Lucas
  • 419
  • 2
  • 5
  • Hi @Hamilton, unfortunately this is not allowed by the NHibernate, because to use the JOIN's he wants it to be mapped. Thanks. – Jacobi Aug 07 '14 at 20:02
0

Can you try this:

IQuery query = session.CreateQuery(@"SELECT tb1, 
        (SELECT MAX(tb2)
           FROM Table2 tb2
          WHERE tb2.IdVal = tb1.IdVal
            AND tb2.Id2 = :id2)
    FROM Table1 tb1
   WHERE tb1.Cod = :cod1");

Th e use of MAX should make it return the value when there is one, and NULL when there isn't one.

João Mendes
  • 1,719
  • 15
  • 32
  • Hi @JoãoMendes, I can't use the MAX in a table, it' must be used in a field. The sub query will always return 1 or nothing. Unfortunately in the "nothing" it's throwing an Exception. – Jacobi Aug 08 '14 at 14:28
0

Can you try this:

 IList<object[]> dataResult = query.SetParameter("id2", "value")   
     .SetParameter("cod1", 1)    
     .List<object[]>()
     .NotFound   
     .Ignore();

Look this answer: Fluent nHibernate: No row with the given identifier exists. Error occurs when 2 users delete some item

Community
  • 1
  • 1
Andre Mesquita
  • 91
  • 1
  • 4