Translate SQL to NHibernate Query
- by Thad
I have a SQL query that I would like to translate to nhibernate criteria, but I have not found a way to generate the MatchCount field. I tried adding it using a sqlprojection but I could not find a place to set the parameters.
SELECT
(CASE WHEN LEFT([FirstName], LEN(@Text0)) = @Text0 OR LEFT([FirstName], LEN(@Text1)) = @Text1 OR LEFT([FirstName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END
+ CASE WHEN LEFT([LastName], LEN(@Text0)) = @Text0 OR LEFT([LastName], LEN(@Text1)) = @Text1 OR LEFT([LastName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END
+ CASE WHEN LEFT([PreferredName], LEN(@Text0)) = @Text0 OR LEFT([PreferredName], LEN(@Text1)) = @Text1 OR LEFT([PreferredName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END) AS MatchCount
, *
FROM [client].[Individual]
WHERE
(
[FirstName] LIKE @Text0 + '%'
OR [FirstName] LIKE @Text1 + '%'
OR [FirstName] LIKE @Text2 + '%'
OR [LastName] LIKE @Text0 + '%'
OR [LastName] LIKE @Text1 + '%'
OR [LastName] LIKE @Text2 + '%'
OR [PreferredName] LIKE @Text0 + '%'
OR [PreferredName] LIKE @Text1 + '%'
OR [PreferredName] LIKE @Text2 + '%'
)
ORDER BY
(CASE WHEN LEFT([FirstName], LEN(@Text0)) = @Text0 OR LEFT([FirstName], LEN(@Text1)) = @Text1 OR LEFT([FirstName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END
+ CASE WHEN LEFT([LastName], LEN(@Text0)) = @Text0 OR LEFT([LastName], LEN(@Text1)) = @Text1 OR LEFT([LastName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END
+ CASE WHEN LEFT([PreferredName], LEN(@Text0)) = @Text0 OR LEFT([PreferredName], LEN(@Text1)) = @Text1 OR LEFT([PreferredName], LEN(@Text2)) = @Text2 THEN 1 ELSE 0 END) DESC
And yes, this is a ugly statement. Hate having a sql statement in the middle of everthing.
Note: There is paging involved and I would prefer not returning all the data to the app server before cutting it down.
