This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
We are using the following LINQ expression to gather grouped data from a Postgres 9.0 database: from i in Incidents
join le in Logentries on i.Activelogentryid equals le.Id
join pe in Personneleffects on le.Id equals pe.LogentryId
where le.Reportedbyunit != null
orderby le.Reportdate descending
group pe by pe.LogentryId into g
select new { logentryId = g.Key, personnel = g }
which causes the Exception: NpgsqlException: ERROR: 42703: column g1.logentryid does not exist In LINQPad, the generated SQL is as follows:
SELECT g1.LogentryId FROM ( SELECT t2.LogentryId AS "LogentryId" FROM jocwatch.tblincidents t0 INNER JOIN jocwatch.tbllogentries t1 ON t0.Activelogentryid = t1.logentryid INNER JOIN jocwatch.tblpersonneleffects t2 ON t1.logentryid = t2.LogentryId WHERE t1.reportedbyunitid IS NOT NULL GROUP BY t2.LogentryId ) g1 After some poking around it appears that the generated SQL is encapsulating the AS fieldname in quotes - hence preserving case, but not applying the same to the top SELECT query. Hence, the inner SELECT statement is producing a field LogentryId but the outer is looking for logentryid and we get the above exception. This is using Lightspeed version 3.1.1891.14711 Is this an issue with Lightspeed's SQL generation or something elsewhere? Thank you in advance,
|
|
|
|
|
Postgres is expecting a lower cased name to be used so you will need to use a custom naming strategy to ensure this is the case when any quoting is required. What you will want to look at doing is to implement a custom naming strategy (have a look at this thread for an example: http://www.mindscape.co.nz/forums/Thread.aspx?PostID=8667) You will want to force lower cased column names like so: public string GetColumnName(string defaultName, string fieldName) { return defaultName.ToLower(); }
Jeremy |
|
|
|
|
The issue appears to be the inconsistent use of quotes, not whether those column names are or should be upper or lower case. If both column names had been unquoted, it would have worked (ie. Postgres would just use the lower case). If both had been quoted, it would have worked as Postgres would obey the mixed case names. However, because one is quoted and one is not, the two column names are effectively different and we end up with the error. While I understand that the custom naming strategy will hide this issue, it doesn't fix the underlying fault. The error is arising because the generated SQL is inconsistently created. Therefore this would appear to be a bug?
|
|
|
|
|
Its something we would like to improve on and we have an item on our backlog to have a look at this in the future, but for now we have a blanket rule that the grouping specifier must be quoted to cover the scenarios where this is needed. Unfortunately on Postgres because it defaults unquoted column names to lower case you get this issue. For now you will need to use the custom naming strategy to deal with this.
Jeremy
|
|
|
|
|
Hi, I am wondering if by chance you have made further changes related to this topic of PostgreSQL subqueries in LINQPad? I am able to handle the lower casing issues in LightSpeed proper using a custom INamingStrategy as described above. LINQPad is such a valuable tool for exercising these queries. So I'm just wondering if any improvements have been made on the case sensitivity issue (which would fix the problem altogether) or on use of a custom naming strategy in LINQPad (which would be a nice workaround). thanks, |
|
|
|
|
Hi Derek, No, there have not been any changes to this - you will still need to use a custom naming strategy to handle this situation.
|
|
|
|
