Search code examples
sqlnhibernatecasecriteriawhere-clause

Nhibernate Criteria Conditional Where

Im working on a NHibernate criteria wich i graduatly builds upp depending on input parameters.

I got some problem with the postal section of these paramters. Since we got a 5 number digit zipcodes the input parameter is a int, but since we in database also accept foreign zipcodes the database saves it as string.

What im trying to replicate in NHibernate Criteria/Criterion is the following where clause.

WHERE
11182 <=
    (case when this_.SendInformation = 0 AND dbo.IsInteger(this_.Zipcode) = 1 then
        CAST(REPLACE(this_.Zipcode, ' ', '') AS int)
    when this_.SendInformation = 1 AND dbo.IsInteger(this_.WorkZipcode) = 1 then
        CAST(REPLACE(this_.WorkZipcode, ' ', '') AS int)
    when this_.SendInformation = 2 AND dbo.IsInteger(this_.InvoiceZipcode) = 1 then
        CAST(REPLACE(this_.InvoiceZipcode, ' ', '') AS int)
    else
        NULL
    end)

What we do is to check where the member contact (this_) has preferenced to get information sent to, then we check the input zipcode as integer against three different columns depending on if the column is convertable to int (IsInteger(expr) function) if column is not convertable we mark the side as NULL

in this case we just check if the zipcode is >= input parameter (reversed in sql code since paramter is first), the goal is to do a between (2 clauses wrapped with 'AND' statement), >= or <=.

UPDATE

Got a hint of success.

Projections.SqlProjection("(CASE when SendInformation = 0 AND dbo.IsInteger(Zipcode) = 1 then CAST(REPLACE(Zipcode, ' ', '') AS int) when SendInformation = 1 AND dbo.IsInteger(WorkZipcode) = 1 then CAST(REPLACE(WorkZipcode, ' ', '') AS int) when SendInformation = 2 AND dbo.IsInteger(InvoiceZipcode) = 1 then CAST(REPLACE(InvoiceZipcode, ' ', '') AS int) else NULL END)"
                , new[] { "SendInformation", "Zipcode", "WorkZipcode", "InvoiceZipcode" },
                new[] { NHibernateUtil.Int32, NHibernateUtil.String, NHibernateUtil.String, NHibernateUtil.String });

Throw my whole clause in a Projections.SqlProjection, however when i run my code some of my projection is cut (" AS int) else NULL END)" is cut from the end) and makes the sql corrupt. Is there some kind of limit on this ?

Solution

  • Got it working yesterday.

    Projections.SqlProjection worked, however if you don't name the projection as a column it some how cuts some of the TSQL code.

    (Case
    when x = 1 then 'bla'
    when x = 2 then 'bla_bla'
    else NULL
 END) as foo

    when using the last part (as foo) and naming the entire case syntax it works and dont cut anything.

    However i dont know why but i could not manage to use the aliases from the other part of the criteria.