Recently, I've uncovered this rather annoying problem with our ASP.NET
website which uses NH (3.3.1.4000) and FluentNH to map against a PostgreSQL
db (v. 8.4)
One of our tables, entitytext, contains a char(2) column "lang". This is
how it looks in pgAdmin: lang character(2) NOT NULL
This column is used in an NH IFilter, to determine a text when rendering an
entity with such a translation associated. The filter looks like this:
public class LanguageFilter : FilterDefinition
{
private const string ColumnName = "lang";
public const string ParameterName = "langCode";
public LanguageFilter()
{
WithName(RepositoryFilterName.ContextLocale.ToString())
.AddParameter(ParameterName, NHibernateUtil.String)
.WithCondition(string.Format("{0} = :{1}", ColumnName, ParameterName));
}
}
This works well (FilterDefinition comes from FluentHN). An example snippet
of generated SQL looks like this:
inner join schemaname.entitytext taskstatus12_
on taskstatus11_.id=taskstatus12_.textentityid
and taskstatus12_.lang = ((E'da')::text)
However, the type cast expression causes some issues with PostgreSQL. I'm
not sure of the exact issue (I suspect the postgresql type resolver goes a
bit nutty), but if the expressions contains more then 4 entities
(TaskStatus being one in the above SQL) that need to filter on the lang
column, the query simply doesn't return, testing it in a pgAdmins editor
causes the same issue, so easily reproducible. Take this SQL:
SELECT
this_.id as y0_,
'OP-'||this_.friendlyid as y1_,
this_.description as y2_,
this_.title as y3_,
this_.locationnear as y4_,
this_.plannedstart as y5_,
this_.plannedend as y6_,
this_.startdate as y7_,
this_.enddate as y8_,
'SRID=' || ST_SRID(locational1_.geometry) || ';' ||
AsText(locational1_.geometry) as y9_,
workspacea9_.id as y10_,
AsText(locational1_.geometry) as y11_,
useralias10_.name as y12_,
useralias10_.id as y13_,
casealias6_.id as y14_,
taskcatego4_.id as y15_,
taskstatus11_.id as y16_,
taskpriori2_.id as y17_,
taskstatus12_.value as y18_,
taskpriori3_.value as y19_,
taskcatego5_.value as y20_,
casetypete8_.value as y21_
FROM
driftweb3.task this_
left outer join driftweb3.workspace workspacea9_
on this_.workspaceid=workspacea9_.id
left outer join driftweb3.location locational1_
on this_.locationid=locational1_.id
left outer join driftweb3.dkaddress locational1_1_
on locational1_.id=locational1_1_.locationid
left outer join driftweb3.taskstatus taskstatus11_
on this_.statusid=taskstatus11_.id
inner join driftweb3.entitytext taskstatus12_
on taskstatus11_.id=taskstatus12_.textentityid
and taskstatus12_.lang = ((E'da')::text)
left outer join driftweb3."case" casealias6_
on this_.caseid=casealias6_.id
left outer join driftweb3.casetype casetypeal7_
on casealias6_.casetypeid=casetypeal7_.id
inner join driftweb3.entitytext casetypete8_
on casetypeal7_.id=casetypete8_.textentityid
and casetypete8_.lang = ((E'da')::text)
left outer join driftweb3.taskcategory taskcatego4_
on this_.categoryid=taskcatego4_.id
inner join driftweb3.entitytext taskcatego5_
on taskcatego4_.id=taskcatego5_.textentityid
and taskcatego5_.lang = ((E'da')::text)
left outer join driftweb3."user" useralias10_
on this_.assignedtoid=useralias10_.id
left outer join driftweb3.taskpriority taskpriori2_
on this_.priorityid=taskpriori2_.id
inner join driftweb3.entitytext taskpriori3_
on taskpriori2_.id=taskpriori3_.textentityid
and taskpriori3_.lang = ((E'da')::text)
WHERE
this_.workspaceid in (((E'f5b0f763-2d6c-4cd8-a922-c01f07aeae0f')::uuid),
((E'64d5f915-75c5-4e6b-b2ca-640f16152408')::uuid))
and this_.deleted = ((FALSE)::bool)
AND this_.statusid = ((E'0ca858a6-61ac-4065-b758-5692b1c17e91')::uuid)
This SQL hangs PostgreSQL (tested on 8.4), if the ((E'da')::text) is
changed to ((E'da')::char(2)), or alternatively simple remove all cast
expressions, PostgreSQL seems to have no problems. So I *think* what I want
to know is how to tell NH (or possibly npgsql) that this string MUST be
rendered as a char(2). I tried with a custom IType impl, but can't get it
to work, and I'm not even sure it's the right way forward anyway.
-Svend
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/9Nbafm6vHp8J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
