CODEWITHSUNDEEP
c#nhibernate
Eric Morris
Eric Morris

Reputation: 155

Why are my values of my first query, being returned as values from my second query?

Some background. We created a type called SqlSum. It has 2 properties, Id and SumValue.

I have two queries in the same session:

SqlSum paidTemp = session.CreateSQLQuery(
    "select count(p.id) as Id, Sum(p.PaymentAmount) as SumValue " +
    "FROM StPayments p " +
    "where p.Active = 1 and p.IsVoided = 0 and p.StCustomerFk = :custid")
    .AddEntity(typeof(SqlSum))
    .SetParameter("custid", cust.Id)
    .List<SqlSum>().First();
if (paidTemp != null)
{
    paid = paidTemp.SumValue;
}

SqlSum allocTemp = session.CreateSQLQuery(
    "select count(pA.id) as Id, Sum(pA.Amount) As SumValue " +
    "FROM StPaymentAllocations pA " +
    "INNER JOIN StPayments p on pA.StPaymentFk = p.Id " +
    "where pA.Active = 1 and p.StCustomerFk = :custid")
    .AddEntity(typeof(SqlSum))
    .SetParameter("custid", cust.Id)
    .List<SqlSum>().First();
if (allocTemp != null)
{
    allocated = allocTemp.SumValue;
}

I can clearly see in the profiler that the query for paidtemp is returning a sumvalue of 1575 and the allocTemp query is returning a value of 1500, however, both the paid and allocated variables are assigned the value of 1575. In fact inspection of the allocTemp.SumValue property in the debugger shows a value of 1575.

Now, I made some minor changes and moved each of these queries into their own sessions:

using (var session = factory.OpenSession())
using (var trans = session.BeginTransaction(IsolationLevel.ReadCommitted))
{
    SqlSum paidTemp = session.CreateSQLQuery(
        "select count(p.id) as Id, Sum(p.PaymentAmount) as SumValue " +
        "FROM StPayments p " +
        "where p.Active = 1 and p.IsVoided = 0 and p.StCustomerFk = :custid")
        .AddEntity(typeof(SqlSum))
        .SetParameter("custid", cust.Id)
        .List<SqlSum>().First();
    if (paidTemp != null)
    {
        paid = paidTemp.SumValue;
    }
    trans.Commit();
    session.Flush();
}    

using (var session = factory.OpenSession())
using (var trans = session.BeginTransaction(IsolationLevel.ReadCommitted))
{
    SqlSum allocTemp = session.CreateSQLQuery(
        "select count(pA.id) as Id, Sum(pA.Amount) As SumValue " +
        "FROM StPaymentAllocations pA " +
        "INNER JOIN StPayments p on pA.StPaymentFk = p.Id " +
        "where pA.Active = 1 and p.StCustomerFk = :custid")
        .AddEntity(typeof(SqlSum))
        .SetParameter("custid", cust.Id)
        .List<SqlSum>().First();
    if (allocTemp != null)
    {
        allocated = allocTemp.SumValue;
    }
    trans.Commit();
    session.Flush();
}

upon execution of this code, suddenly the alloctemp.SumValue is 1500 as expected.

What was causing the second query to keep the values from the first query in my first example?

Upvotes: 1

Views: 87

Answers (1)

Rippo
Rippo

Reputation: 22424

If your Id column values can return the same value then the first level cache already thinks this id is in it and will duplicate row.

Therefore:-

  1. If you want to keep the Id as a column then transform into a dto remembering to override Equals and GetHashCode. e.g. see this exmple
  2. Don't use the column name Id (too be honest I am not sure about this one).

Upvotes: 2

Related Questions