Xamarin Linq insert/update Sqlite table record with generated column

Question

For a table defined

public class Tbl 
{

    [PrimaryKey]
    public int Id { get; set; }
    public string Col2 { get; set; }
    public string Gen { get; set; }

}

Where Gen is a Sqlite stored generated always column

on _conn.Insert(instanceOfTbl)

I get 'cannot UPDATE generated column'.

Fair enough, but other than directly executing an equivalent sql statement which ignores Gen is there a way of flagging Gen as generated (or other method) to allow the insert/update? I don't think the [Ignore] column attribute is appropriate as Gen needs to be accessible for reading. Removing the Set accessor does prevent the behaviour but results in the property always being null when read.

I'm using VS, Xamarin, Nuget sqlite-net-pcl 1.7.335 targeting Android, if relevant.

Thanks Jason. I forgot to mention I tried that. I've amended the question adding 'Removing the Set accessor does prevent the behaviour but results in the property always being null when read.' — Antony Fox, Mar 16 '21 at 19:46
you might have to resort to two classes for that Table, one for read (with Gen) and one for update (without) — Jason, Mar 16 '21 at 19:53
Thanks again Jason. The solution I didn't want to hear but may well be right :) — Antony Fox, Mar 18 '21 at 12:23

Antony Fox · Accepted Answer · 2021-03-20T12:03:11.507

I've decided to adopt this approach for now. Credit to Jason for pointing me towards it.

Split Tbl into base class Tbl and derived class TblEx. Move the generated column property to TblEx. Hardwire TblEx to map to Sqlite table Tbl. Provide TblEx method to retrieve only properties from base class Tbl.

public class Tbl 
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Col2 { get; set; }
}

[SQLite.Table("Tbl")]
public class TblEx : Tbl
{
    public string Gen { get; set; }
    public Tbl GetTbl() {Tbl t = new Tbl(); t.Id = Id; t.Col2 = Col2; return t;}
}

Update record with primary key id

    TblEx tEx = _conn.Table<TblEx>().First(t => t.Id == id);

    // Generated field is available as class is TblEx
    tEx.Col2 = tEx.Gen + "something";  

    //Update now works as only base class Tbl without reference to generated column is passed
    _conn.Update(tEx.GetTbl());

An alternative way of retrieving the base class uses JSON serialize/deserialize which has the advantage of not having to explicitly assign property values but may come with a performance cost or type compatibility limitations (I'm not sure that the latter's true as I don't know much about JSON).

using System.Text.Json;

[SQLite.Table("Tbl")]
public class TblEx : Tbl
{
    public string Gen { get; set; }
    public Tbl GetTbl()
    {return JsonSerializer.Deserialize<Tbl>(JsonSerializer.Serialize(this));}
}

Xamarin Linq insert/update Sqlite table record with generated column

1 Answers1