How to create criteria query for given sql query

Question

I am creating an ICriteria query for this equivalent sql query.

SELECT fCustomerID,
       ISNULL(
                (SELECT SUM(payinv.fAmount) AS Expr1
                 FROM dbo.tARPayment AS pay
                 INNER JOIN dbo.tARPaymentInvoice AS payinv ON pay.fPaymentID = payinv.fPaymentID
                 INNER JOIN dbo.tARInvoice AS inv ON payinv.fInvoiceID = inv.fARInvoiceID
                 WHERE (pay.fIsPosted = CASE pay.fPaymentType WHEN 'CM' THEN 0 WHEN 'EPD' THEN 0 ELSE 1 END)
                   AND (inv.fCustomerID <> dbo.tARCustomer.fCustomerID)
                   AND (pay.fCustomerID = dbo.tARCustomer.fCustomerID)), 0)
FROM dbo.tARCustomer
GROUP BY fCustomerID

But I am not getting anyway that how can I generate equivalent nhibernate ICriteria query.

This is payment class

public partial class tARPayment 
{
    #region Constructor

    /// <summary>
    /// Initializes a new instance of the <see cref="tARPayment"/> class.
    /// </summary>
    public tARPayment()
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="tARPayment"/> class.
    /// </summary>
    /// <param name="fPaymentID">The fPaymentID of guid type.</param>
    public tARPayment(System.Guid fPaymentID)
    {
        this.ID = fPaymentID;
    }
    #endregion

    #region Properties

    /// <summary>
    /// Gets or sets payment id.
    /// </summary> 
    public virtual System.Guid fPaymentID { get; set; }

    /// <summary>
    /// Gets or sets fCustomerID.
    /// </summary> 
    public virtual System.Guid fCustomerID { get; set; }

    /// <summary>
    /// Gets or sets check number.
    /// </summary> 
    public virtual string fCheckNumber { get; set; }

    /// <summary>
    /// Gets or sets amount.
    /// </summary> 
    public virtual decimal fAmount { get; set; }       

    /// <summary>
    /// Gets or sets customer detail.
    /// </summary> 
    public virtual tARCustomer Customer { get; set; }

    public virtual IList<tARPaymentInvoice> PaymentInvoices { get; set; }        

    #endregion

    #region Methods
    /// <summary>
    /// partial class for payment.
    /// </summary>
    /// <returns>The method get code.</returns>
    public override int GetHashCode()
    {
        return ID.GetHashCode();
    }
    #endregion
}

This is a invoice class

public partial class tARInvoice 
{
    #region Constructor
    /// <summary>
    /// Initializes a new instance of the <see cref="tARInvoice"/> class.
    /// </summary>
    public tARInvoice()
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="tARInvoice"/> class.
    /// </summary>
    /// <param name="fARInvoiceID">The fARInvoiceID.</param>
    public tARInvoice(System.Guid fARInvoiceID)
    {
        this.ID = fARInvoiceID;
    }

    #endregion

    #region Properties
    /// <summary>
    /// Gets or sets fARInvoiceID.
    /// </summary> 
    public virtual Guid fARInvoiceID { get; set; }

    /// <summary>
    /// Gets or sets fCustomerID.
    /// </summary> 
    public virtual Guid fCustomerID { get; set; }


    /// <summary>
    /// Gets or sets Delivery Method.
    /// </summary> 
    public virtual string fDeliveryMethod { get; set; }

    /// <summary>
    /// Gets or sets Invoice Number.
    /// </summary> 
    public virtual int? fARInvoiceNumber { get; set; }



    public virtual tARCustomer Customer { get; set; }

    public virtual IList<tARPaymentInvoice> PaymentInvoices { get; set; }        

    #endregion

    #region Methods
    /// <summary>
    /// retrieve Hash Code.
    /// </summary>
    /// <returns>The method get code.</returns>
    public override int GetHashCode()
    {
        return ID.GetHashCode();
    }
    #endregion
}

This is a payment invoice class.

public partial class tARPaymentInvoice 
{
    #region Constructor
    /// <summary>
    /// Initializes a new instance of the <see cref="tARPaymentInvoice"/> class.
    /// </summary>
    public tARPaymentInvoice()
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="tARPaymentInvoice"/> class.
    /// </summary>
    /// <param name="fPaymentInvoiceID">The Invoice ID.</param>   
    public tARPaymentInvoice(System.Guid fPaymentInvoiceID)
    {
        this.ID = fPaymentInvoiceID;
    }
    #endregion

    #region Properties
    /// <summary>
    /// Gets or sets fPaymentInvoiceID.
    /// </summary>
    public virtual System.Guid fPaymentInvoiceID { get; set; }

    /// <summary>
    /// Gets or sets fPaymentID.
    /// </summary>
    public virtual System.Guid fPaymentID { get; set; }

    /// <summary>
    /// Gets or sets fInvoiceID.
    /// </summary>
    public virtual System.Guid fInvoiceID { get; set; }           


    /// <summary>
    /// Gets or sets tARPayment.
    /// </summary>
    public virtual tARPayment Payment { get; set; }

    /// <summary>
    /// Gets or sets tARInvoice.
    /// </summary>
    public virtual tARInvoice Invoice { get; set; }

    #endregion

    #region Methods
    /// <summary>
    /// get hash codes.
    /// </summary>        
    /// <returns>The hash code.</returns>
    public override int GetHashCode()
    {
        return ID.GetHashCode();
    }
    #endregion
}

Answer 1

Not sure about nHibernate, but does this rewritten query help get the same answer and is something you can run with easier?

SELECT T.fCustomerID,
       coalesce( SUM( payinv.fAmount ), 0 ) as SumAmt
   FROM 
      dbo.tARCustomer T
         JOIN dbo.tARPayment AS pay
            ON T.fCustomerID = pay.fCustomerID 
            AND pay.fIsPosted = CASE pay.fPaymentType 
                                WHEN 'CM' THEN 0 
                                WHEN 'EPD' THEN 0 
                                ELSE 1 END
             JOIN dbo.tARPaymentInvoice AS payinv 
               ON pay.fPaymentID = payinv.fPaymentID
               INNER JOIN dbo.tARInvoice AS inv 
                  ON payinv.fInvoiceID = inv.fARInvoiceID
                 AND inv.fCustomerID <> T.fCustomerID
   GROUP BY 
      T.fCustomerID

Answer 2

Rather than converting the above query to LINQ or HQL, I would recommend making the query into a view, and then using NHibernate to query that view.

SQL

CREATE VIEW vCustomerAmount AS
SELECT fCustomerID,
       ISNULL(
                (SELECT SUM(payinv.fAmount) AS Expr1
                 FROM dbo.tARPayment AS pay
                 INNER JOIN dbo.tARPaymentInvoice AS payinv ON pay.fPaymentID = payinv.fPaymentID
                 INNER JOIN dbo.tARInvoice AS inv ON payinv.fInvoiceID = inv.fARInvoiceID
                 WHERE (pay.fIsPosted = CASE pay.fPaymentType WHEN 'CM' THEN 0 WHEN 'EPD' THEN 0 ELSE 1 END)
                   AND (inv.fCustomerID <> dbo.tARCustomer.fCustomerID)
                   AND (pay.fCustomerID = dbo.tARCustomer.fCustomerID)), 0) [Amount]
FROM dbo.tARCustomer
GROUP BY fCustomerID

C# DTO

public class CustomerAmount
{
    public int fCustomerID { get; set; }
    public decimal Amount { get; set; }
}

Query

List<CustomerAmount> customerAmounts = session.Query<CustomerAmount>().ToList();