Autoincremented ID Columns

[helegrod]

Having a bit of trouble finding information on autoincremented identity columns using this setup. Is there a particular way to go about this? I tested using record types with the Id field set to -1, 0, and None.

Is it just more correct to use guid types for this when using this approach?

[Dzoukr]

I use Guid instead of autoincremented integers, but if I would use them, I will just insert the record without the Id column. So probably anonymous record (or new one without Id property)?

[Dzoukr]

Do you need more help on this or can close this issue? 🙂

[helegrod]

Oh, my apologies! Absolutely. This worked well. Thank you!

[AlanBarber]

Hello @Dzoukr, hope you don't mind me posting on this old one. Been using this lib for a project and it's worked out nicely but I feel we've been struggling specifically with the auto increment Ids using both multiple record types with / without ids and anonymous records.

I feel like there could be a compromise to handle things much nicer. Would you be open to a PR to add support for providing a solution to have inserts ignore ids. I'm thinking it could be something as simple as an new CustomOperation on the InsertBuilder to provide a column or columns to exclude when building the insert query.

Example

let! _ =
    insert {
        table "Persons"
        value r
        excludeCol "Id"
    } |> conn.InsertAsync

let! _ =
    insert {
        table "Persons"
        value r
        excludeCols ["Id","Position"]
    } |> conn.InsertAsync

[Dzoukr]

Hi @AlanBarber,

it's slightly against my original design, but I feel such feature wouldn't do any harm, so please send a PR. I would also consider to introduce excludeCol to update computation expression.

[AlanBarber]

@Dzoukr I can totally understand not wanting to break with your goal of simplicity.

Been thinking about it and just to throw out some other options for discussion...

If you wanted to be more limiting in scope maybe just a flag to indicate a table has auto increment id and make assumption the column name is ("Id") or (table + "Id")

let! _ =
    insert {
        table "Persons"
        autoIncrementId true
        value r
    } |> conn.InsertAsync

another being create a whole different insert builder that follows the same concept as above.

let! _ =
    autoIncrementIdInsert {
        table "Persons"
        value r
    } |> conn.InsertAsync

Obviously, pick your poison for the naming if those sound long and annoying.

[Dzoukr]

I would go for the initial proposal having excludeCol or ignoreCol (maybe ignoreColumn to make it explicit it's column not collection or so) - it will give more power to user.

FYI: I am also thinking about some initial support for aggregate functions, so there will be some tweaks in CE anyway. :)

[crimsonhawk47]

Can we revive this? It seems weird to need a type for Insert queries and a different type for Select queries.