LightSpeedContext.QuoteIdentifers doesn't work with PostgreSql8

I a running a linq query against a postgres database and the identifiers in the lightspeed generated query are quoted.  I did NOT set QuotedIdentifers to true in the config file or in code and verified that the default value is false. Logging from lightspeed shows that all queries have the identifiers quoted.

But the following query causes an NpgsqlException exception:

Npgsql.NpgsqlException:
column g1.gamingterminalid does not exist
Severity: ERROR
Code: 42703
   at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResult()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at ...()
   at ..(IUnitOfWork , IDbCommand ,
)
   at ..(IUnitOfWork , IDbCommand )
   at Mindscape.LightSpeed.Data.DataProviderAdapter.
(IUnitOfWork ,  , ProviderOptions )
   at ..(Query )
   at Mindscape.LightSpeed.UnitOfWork.Project(Query query)
   at Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()
   at BlueYonder.Server.BusinessProcesses.MeterBP.MeterEndOfDay(DateTime eod) in C:\Source\Trunk\TerminalManager\TerminalManager\BusinessProcesses\MeterBP.cs:line 330
   at BlueYonder.Server.BusinessProcesses.MeterBP.CheckEndOfDayActivity() in C:\Source\Trunk\TerminalManager\TerminalManager\BusinessProcesses\MeterBP.cs:line 295
   at BlueYonder.Server.TerminalManager..ctor() in C:\Source\Trunk\TerminalManager\TerminalManager\TerminalManager.cs:line 107
   at BlueYonder.Server.ServerController..ctor(Boolean runAsService) in C:\Source\Trunk\TerminalManager\TerminalManager\ServerController.cs:line 99

Generated Query:

SELECT
  g1.GamingTerminalId
FROM
  (
    SELECT
      Meters.GamingTerminalId AS "GamingTerminalId"
    FROM
      Meters Meters
    GROUP BY
      Meters.GamingTerminalId
  )
  g1

--> Time:   0 ms

linq query:

            var currentMetersByTerminalIdByMeterDefId =
                (from meters in Repository.UnitOfWork.MeterEntities
                 group meters by meters.GamingTerminalId into tg
                 select new
                 {
                     TerminalId = tg.Key,
                     MetersDefinitionGroups =
                         from terminalMeters in tg
                         group terminalMeters by terminalMeters.MeterDefinitionId into mdg
                         select new
                         {
                             MeterDefinitionId = mdg.Key,
                             Meters = mdg,
                         }
                 }).IncludeDeleted();

            foreach (var currentTerminalMeters in currentMetersByTerminalIdByMeterDefId)
            {

-----> Exception occurs on previous foreach

We are using the LightSpeed nightly build from 9/19.

Is there a work around to fix this?  Or do we need to update to a different version of LightSpeed?

Thanks for your help.

Nick

Hi Nick,

This isnt actually a case of identifier quoting, rather there is an alias involved in the query and alias specifiers need to be quoted because the provider (PostgreSql in this case) needs quoting to distinguish casing.

Because you have a mixed case column name you should be using quoting to avoid these types of issues so I would recommend you turn on quoted identifiers to cover this :)

Jeremy

I set quoteIdentifiers to true but now it fails on the very first query we execute where before this query worked fine with quoteIdentifiers set to false.

SELECT
  "MeterDefinitions"."Id" AS "MeterDefinitions.Id",
  "MeterDefinitions"."ApplicationConfigurationId" AS "MeterDefinitions.ApplicationConfigurationId",
  "MeterDefinitions"."CreatedOn" AS "MeterDefinitions.CreatedOn",
  "MeterDefinitions"."DeletedOn" AS "MeterDefinitions.DeletedOn",
  "MeterDefinitions"."LockVersion" AS "MeterDefinitions.LockVersion",
  "MeterDefinitions"."MeterName" AS "MeterDefinitions.MeterName",
  "MeterDefinitions"."Type" AS "MeterDefinitions.Type",
  "MeterDefinitions"."UpdatedOn" AS "MeterDefinitions.UpdatedOn",
  "MeterDefinitions"."WagerCategoryId" AS "MeterDefinitions.WagerCategoryId"
FROM
  "MeterDefinitions"
WHERE
  (((("MeterDefinitions"."ApplicationConfigurationId" IS NULL AND "MeterDefinitions"."WagerCategoryId" IS NULL) AND "MeterDefinitions"."MeterName" = 'NoteAcceptorCurrencyInCount') AND "MeterDefinitions"."Type" = 3) AND "MeterDefinitions"."DeletedOn" IS NULL)
ORDER BY
  "MeterDefinitions"."Id"
LIMIT 2

--> Parameter  Direction  DbType      Value
--> ---------  ---------  ----------  -----
--> :p0        Input      String      NoteAcceptorCurrencyInCount
--> :p1        Input      Int32       3

--> Time:   0 ms


A first chance exception of type 'System.TypeInitializationException' occurred in BlueYonder.Server.TerminalManager.dll
Server.ServerController: 2010-10-29 09:40:33,369 Server.ServerController[9] FATAL: Exception during ServerController ctor
System.TypeInitializationException: The type initializer for 'BlueYonder.Server.BusinessProcesses.TerminalMeterBP' threw an exception. ---> Npgsql.NpgsqlException:
relation "MeterDefinitions" does not exist
Severity: ERROR
Code: 42P01
   at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResult()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at ...()
   at ..(IUnitOfWork , IDbCommand ,
)
   at ..(IUnitOfWork , IDbCommand )
   at ...ctor(IUnitOfWork , IEnumerable`1 )
   at Mindscape.LightSpeed.Data.DataProviderAdapter.
(IUnitOfWork , IEnumerable`1 , ProviderOptions )
   at ..(TypeModel , Query , IList )
   at ..(UnitOfWorkBase , TypeModel , Query , IList )
   at ..(Query , TypeModel , IList )
   at ..(Query , IList )
   at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results)
   at Mindscape.LightSpeed.UnitOfWorkBase.Find(Query query)
   at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)

In my previous post the Lightspeed generated query works if the GamingTerminalId alias and GamingTerminalId identifier are NOT quoted like this:

SELECT
  g1.GamingTerminalId
FROM
  (
    SELECT
      Meters.GamingTerminalId AS GamingTerminalId
    FROM
      Meters Meters
    GROUP BY
      Meters.GamingTerminalId
  )
  g1

Or it works if the alias and identifier are both quoted like this:

SELECT
  g1."GamingTerminalId"
FROM
  (
    SELECT
      Meters.GamingTerminalId AS "GamingTerminalId"
    FROM
      Meters Meters
    GROUP BY
      Meters.GamingTerminalId
  )
  g1

Turning on quoting causes lots of issues but turning it off seems to work well except for the above case.  I am not an expert on db case sensitivity, but I know that was is currently being generated does not work.

Is this a bug or is there some other configuration I need?

Thanks,

Nick

Hi Nick,

Is your table called MeterDefinitions or meterdefinitions?

If it is the latter (lower cased form) then you should set the table names accordingly in the designer so that it generates Table attributes for the generated classes with the lower cased name set on them - you will need quoted identifiers on to satisfy the original query as previously mentioned.

Jeremy

In the designer the entity attributes are:

TableName = MeterDefinitions

Name = MeterDefinitionsEntity

We are using migrations to create it and we are currently targeting SqlServer and Postgres databases.  After the migration runs the table in the database is lower case meterdefinitions.  I assume that is the problem.  How do we get the migration  to create the table using mixed case?

The class the designer generates look like this:

 [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("MeterDefinitions")]
  public partial class MeterDefinitionEntity : Entity<long>
  {

...

The Migration that creates the table looks like this:

this.AddTable("MeterDefinitions", null, new Field("Id", ModelDataType.Int64, false), new Field[] {
            new Field("MeterName", ModelDataType.String, true).WithSize(256),
            new Field("Type", ModelDataType.Int32, false),
            new Field("CreatedOn", ModelDataType.DateTime, false),
            new Field("UpdatedOn", ModelDataType.DateTime, false),
            new Field("DeletedOn", ModelDataType.DateTime, true),
            new Field("LockVersion", ModelDataType.Int32, false),
            new ForeignKeyField("ApplicationConfigurationId", ModelDataType.Int64, true, "ApplicationConfigurations", null, "Id"),
            new ForeignKeyField("WagerCategoryId", ModelDataType.Int64, true, "WagerCategories", null, "Id")});

Hi Nick,

Thanks for the clarification. We have discussed this internally and while it would be ideal for us to not require the quoting around the grouping specifier as the ideal solution, we would need to add in some more smarts to the querying engine to allow this which isnt something we can do in the short term (but I have added a ticket to our backlog to have a look at this!).

For now, the suggested workaround is to have a look at using a custom naming strategy to force the columns into lower case when accessing the database. This will mean you dont have to muck around changing anything in your model.

So 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();

}

After that you can also turn quoted identifiers off and you should find the query runs happily as the grouping specifier will be sent quoted in lower case.

Jeremy

Thanks Jeremy.  That worked.

How do I know if/when the ticket to fix the quoting around the grouping specifier is fixed so I can remove the custom naming stategy?

Nick

Hi Nick,

We will post an update in this thread with any details of a change. We also aggregate a change log for the week on our blog in the "Weekly Development" posts.

Jeremy