It seems that the generated SQL code doesn't wrap text constants containing Guid values with the uuid() function of Postgres.
This can lead to problems in some complex situations.
For example we've just ran into the error: "CASE types uuid and text cannot be matched" and indeed, Postgres tries to run a statement like:
Code: Select all
CASE WHEN <condition> THEN
CASE WHEN <condition> THEN
'56ad6f60-2448-4d1c-97e6-829fa272ed41'
ELSE
'10a8d873-0bbd-4135-b9b6-cec2f8b165ad'
END
ELSE
"SomeTable"."UUID_Column"
END
Which is not correct. For some reason Postgres (we are using 9.4.5) is not smart enough to recognize that the "inner" CASE statement also have a result of type UUID, not a text.
The following query could be fixed using the uuid() conversion function:
Code: Select all
CASE WHEN <condition> THEN
CASE WHEN <condition> THEN
uuid('56ad6f60-2448-4d1c-97e6-829fa272ed41')
ELSE
uuid('10a8d873-0bbd-4135-b9b6-cec2f8b165ad')
END
ELSE
"SomeTable"."UUID_Column"
END
We are using dotConnect for Postgres 7.4.543.
And the Linq query which produced my first SQL sample was something like:
Code: Select all
Guid g1 = Guid.NewGuid();
Guid g2 = Guid.NewGuid();
var query = baseQuery.SelectMany(a => a.DS, (a, ds) => new {A = a, DS = ds})
.GroupBy(flat => new
{
UB = flat.DS.UBCatalogValueId,
TT = (condition1 ? (condition2 ? g1 : g2) : flat.A.TTCatalogValueId
},
flat => flat.A,
(key, group) => new
{
key.UB,
key.TT,
Count = group.Select(a => a.Id).Distinct().Count()
});