The generated SQL doesn't use the uuid() function for constant GUIDs
Posted: Wed 20 Jan 2016 15:58
We are using the dotConnect for PostgreSQL together with Entity Framework.
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:
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:
But since we are talking to the DB through dotConnect (using Entity Framework), we should configure dotConnect to use the uuid() function anytime our query refers some constant Guid value. Is there any setting for this? Or is this a bug?
We are using dotConnect for Postgres 7.4.543.
And the Linq query which produced my first SQL sample was something like:
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()
});