SQLite: Not existing column in table, but may be generated/added from some queries

Question

I have a table. Let say:

TableA

Id
ColumnA

The C# entity is defined with the properties Id, ColumnA, int ColumnB.

I would like that in normal cases, the ColumnB is set to -1 and when executing a RawSQL I can have this column in the SELECT clause that would fill the property.

Raw SQL

SELECT *, 123 AS ColumnB FROM TableA

Attempts

I tried to used NotMapped attribute on ColumnB property ==> No issue for the normal case loading, but using the RawSQL having this column added/generated, the value is not in the property.

I tried to used DatabaseGenerated(DatabaseGeneratedOption.Computed) on ColumnB property ==> The RawSQL works, but now trying to load an entity without RawSQL crashes.

I tried using:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<TableA>().Property(m => m.ColumnB).HasComputedColumnSql("-1");
}

But same result as previous attempt.

Without creating a new class that overrides the TableA class and that includes the ColumnB property, would there be a way to obtain this behavior?

You can try to define `KeyLess` entity with all properties you need https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations. And then just use this entity for raw sql queries like `context.MyKeyLessEntities.FromSqlQuery()`. Where `MyKeyLessEntities` is `DbSet` of your `KeyLess` entity — E. Shcherbo, Feb 22 '22 at 23:01
To define it, just create a class to represent your query (columnA, id and columnB), annotate the class with `KeyLess` attribute and add the `DbSet` for it to your context — E. Shcherbo, Feb 22 '22 at 23:03
@E.Shcherbo Would it be possible without creating a new class? Because I would like to continue to use `TableA` class in my normal usages, but in a particular circumstance, I would like to have this property populated using my raw sql. — Master DJon, Feb 22 '22 at 23:05
I'm not sure, probably this might help https://stackoverflow.com/a/55884524/8715436, but anyway I wouldn't recommend doing this, because this option uses the wrong things to do the job and it seems to cause problems on updates, inserts and deletes — E. Shcherbo, Feb 22 '22 at 23:10
`123` is not constant, but a value gotten from the SQLite extension `spellfix1`. In fact, it is the column `distance` that is my `ColumnB`. I wrote it like this because I don't think it was relevant and it was adding complexity to the question. — Master DJon, Feb 22 '22 at 23:10
Note that you always can map the result of query for `keyless` entity to your class `TableA` with a simple `Select` call — E. Shcherbo, Feb 22 '22 at 23:14
@E.Shcherbo Unfortanetly, I am getting `SQLite Error 1: 'error during initialization` when trying to load an entity with normal usage (with the other question page you suggested): `var one = db.TableA.Where(m => m.Id == 2718064).ToList();` — Master DJon, Feb 22 '22 at 23:14
Why do not use `Select`? You can construct any entity with any properties. — Svyatoslav Danyliv, Feb 22 '22 at 23:46
@SvyatoslavDanyliv Using `Select`, would there be a way to add all the entity properties + the ColumnB one? (Then I would remove the property from the entity). — Master DJon, Feb 23 '22 at 00:24
Up to now, the only way I can figure out is to have a subclass containing this new property `ColumnB`. — Master DJon, Feb 23 '22 at 01:05
@E.Shcherbo Finally, the error I am getting is related to calling a second query. If I disable the spellfix1 extension, I can execute two queries, but for sure no more the one using spellfix1. I will create a new question. That said, I opted for the subclass as my requirement seems really too much an exception. — Master DJon, Feb 23 '22 at 20:42
@E.Shcherbo If you want to add an answer, I'll accept it. Otherwise, just for sake, I'll add one myself. — Master DJon, Feb 23 '22 at 20:53
@MasterDJon, interesting, could you please elaborate a bit on what error you are saying about and what the second query is? — E. Shcherbo, Feb 23 '22 at 22:08
@E.Shcherbo I just added a new question: https://stackoverflow.com/questions/71244785/ — Master DJon, Feb 23 '22 at 22:30

score 0 · Accepted Answer · edited Apr 05 '22 at 14:42

0

I finally opted to create a new subclass. But, it brought me another issue, that I also fixed. See Using Include on FromRawSql query.

edited Apr 05 '22 at 14:42

Eric Aya

69,473
35
181
253

answered Apr 05 '22 at 14:24

Master DJon

1,899
2
19
30

SQLite: Not existing column in table, but may be generated/added from some queries

1 Answers1

Linked