CODEWITHSUNDEEP
c#postgresqlpetapoco
Carter
Carter

Reputation: 2870

How do I make capitalization for table/column names not matter with PetaPoco and PostgreSQL?

My model looks like this...

[PrimaryKey("TaskId")]
public class Task
{
    public int TaskId { get; set; }

    [StringLength(1000)]
    [DisplayName("What do you want to do?")]
    public string Description { get; set; }

    [DisplayName("When do you want it done?")]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = false)]
    [DataType(DataType.Date)]
    public DateTime CompleteByDate { get; set; }

    public bool IsCompleted { get; set; }

    public DateTime InsertDate { get; set; }
    public DateTime UpdateDate { get; set; }
}

If I try to fetch data from the Task table, one of the first errors I'll see is this...

ERROR: 42P01: relation "Task" does not exist

The problem, as far as I can tell is that PetaPoco generates sql like this...

SELECT "Task"."TaskId", "Task"."Description", "Task"."CompleteByDate", "Task"."IsCompleted", "Task"."InsertDate", "Task"."UpdateDate" FROM "Task"

So, with the quotes, the capitalization matters and in the DB the column names are only recognized in all lower case if they are put in quotes. I'm wondering if there is a way to change that. The only fix I currently see is to make my model look like this (note capitalization changes)...

[PrimaryKey("taskid")]
[TableName("task")]
public class Task
{
    [Column("taskid")]
    public int taskid { get; set; }

    [StringLength(1000)]
    [DisplayName("What do you want to do?")]
    public string description { get; set; }

    [DisplayName("When do you want it done?")]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = false)]
    [DataType(DataType.Date)]
    public DateTime completebydate { get; set; }
    public bool iscompleted { get; set; }
    public DateTime insertdate { get; set; }
    public DateTime updatedate { get; set; }
}

I don't want my properties to be all lower case. Why do I have to do this, is this the only fix?

Upvotes: 3

Views: 1300

Answers (2)

Eduardo Molteni
Eduardo Molteni

Reputation: 39413

Petapoco needs the exact (case sensitive) name to match the column, but if you specify the Column attribute, you are free the name the property the way you want (including changing the name)

[Column("taskid")]
public int TaskID { get; set; }

Upvotes: 4

Richard Huxton
Richard Huxton

Reputation: 22893

If PetaPoco (whatever that is) generates quoted identifiers all the time you'll be fine. If it never generates quoted identifiers you'll be fine. If it does it sometimes and not others, you'll need to file a bug-report.

While you're waiting for a bugfix, a quick bit of editor macros/Perl should help you rename the column-names etc. It looks like selects do use quoted identifiers, so just renaming the table definitions should do it for you.

Oh, and you'll find CamelCased identifiers to be a complete pain when you need to do any manual SQL or interoperate with other systems.

Upvotes: 0

Related Questions