Database.From on Annotated Types Supported?

[mitchelllee222]

I have a class that I annotated the table name using the TableAttribute on the class and column names with the ColumnAttribute on the properties. I noticed when I used the Database.Table method, it honors the attribute tags and loads my objects correctly. When I use the Database.From method, it loads my object only if my property name matches my column names in my tables and doesn't seem to care about my ColumnAttributes defined on my properties. Is this correct behavior or is there another piece I'm missing to the puzzle?

I like how you feed the query to the Database.From method, and there are times we would only want a partial object load, such as just returning the keys. It's not the end of the world if our property names have to match the column names, I was just curious if this is just the way it works.
Thanks,
Mitch

[maxtoroq]

Can you show me some code? What you say makes sense, but it should work, so I want to make sure your code is correct.

[mitchelllee222]

Class declaration:

`Imports DbExtensions

<Table(Name:="acu_forms")> Public Class ACU_FORM
#Region "Properties"
<Column(Name:="co_id")> Public Property ID As Integer
#End Region
End Class`

This works with attribute tags and you get right number of records with property set:

`Imports DbExtensions

Module Module1

Sub Main()
    DatabaseConfiguration.DefaultConnectionString = "Server=gss2k16dlayer;port=5432;Database=globalsrc;User Id=postgres;password=postgres"
    DatabaseConfiguration.DefaultProviderInvariantName = "Npgsql"
    Dim db As New Database

    Dim o = db.Table(Of ACU_FORM)

    For Each i In o
        MsgBox(i.ID)
    Next

End Sub

End Module`

This returns the right number of records but Nothing in the property name:

`Imports DbExtensions

Module Module1

Sub Main()
    DatabaseConfiguration.DefaultConnectionString = "Server=gss2k16dlayer;port=5432;Database=globalsrc;User Id=postgres;password=postgres"
    DatabaseConfiguration.DefaultProviderInvariantName = "Npgsql"
    Dim db As New Database

    Dim query As New SqlBuilder
    query.SELECT("*")
    query.FROM("acu_forms")

    Dim o = db.From(Of ACU_FORM)(query)

    For Each i In o
        MsgBox(i.ID)
    Next
End Sub

End Module`

[maxtoroq]

The issue is that you are using SqlBuilder, so you are responsible for mapping columns to properties.

A hibrid approach would be something like this (C#):

// use a sub-query that has the proper mapping
var query = SQL
   .SELECT("*")
   .FROM(db.Table<ACU_FORM>().GetDefiningQuery(), "t0");

// here you can mutate query

// now, back to SqlSet
var o = db.From<ACU_FORM>(query);

foreach (var i in o) {
   MsgBox(i.ID)
}

[maxtoroq]

An even simpler solution, using SqlTable.CommandBuilder:

var query = db.Table<ACU_FORM>()
   .CommandBuilder
   .BuildSelectStatement();

[mitchelllee222]

First method definitely works, seems like it's loading specifically what properties I want to the object and honoring the attributes. On the select I use the property name and I'm having to wrap it in double quotes otherwise Postgres doesn't like it. So the query looks like:
SELECT "ID" FROM (SELECT "co_id" AS "ID" FROM "acu_forms") t0
If I don't wrap it in double quotes I get:
SELECT ID FROM (SELECT "co_id" AS "ID" FROM "acu_forms") t0
Postgres will say "ID" is an invalid column.

Here's the code that loads only the ID property:

` DatabaseConfiguration.DefaultConnectionString = "Server=gss2k16dlayer;port=5432;Database=globalsrc;User Id=postgres;password=postgres"
DatabaseConfiguration.DefaultProviderInvariantName = "Npgsql"
Dim db As New Database

    Dim query = SQL.SELECT("""ID""").FROM(db.Table(Of ACU_FORM).GetDefiningQuery, "t0")

    Dim o = db.From(Of ACU_FORM)(query)

    For Each i In o
        MsgBox(i.ID)
    Next`

On the second solution, how would I tell it specifically what fields to select in that, cause when I try that it appears it returns me a full select of all the fields?

Thanks for helping on this, this is so much lighter and cleaner than using EF.

[maxtoroq]

On the select I use the property name and I'm having to wrap it in double quotes otherwise Postgres doesn't like it.

Yes. Always remember when using SqlBuilder you are responsible of writing valid SQL.

As an alternative, you can use Database.QuoteIdentifier (although it can make your code harder to read):

var query = SQL.SELECT(db.QuoteIdentifier("ID"));

On the second solution, how would I tell it specifically what fields to select in that, cause when I try that it appears it returns me a full select of all the fields?

You can't. Stick to the first method.