2

I can define in my SQL Server a column with the following definition:

[Barcode] AS ([Prefix] + CONCAT(REPLICATE('0', (4)-LEN([ContainerId])), [ContainerId])) PERSISTED,

This basically allows me to store a value on the database row with a given prefix like this:

ContainerId     Prefix    Barcode
----------------------------------
23              TUB       TUB0023

This works great but when I try to describe this for Entity Framework in the OnModelCreating:

entity.Property(f => f.Barcode).HasComputedColumnSql("[Prefix]+CONCAT(REPLICATE('0',(4)-LEN([ContainerId])),[ContainerId])");

and use it in a unit test, I get the following error:

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such function: replicate'.

Is there a limitation on using in-memory Entity Framework computed columns such that we can't have this call to HasComputedColumnSql in the OnModelBuilding event?

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Josh
  • 10,352
  • 12
  • 58
  • 109
  • 2
    As discussed here (https://learn.microsoft.com/en-us/ef/core/testing/choosing-a-testing-strategy#inmemory-as-a-database-fake), using the in-memory provider for testing is strongly discouraged. I see the error is coming from SQLite so you my be using SQLite in-memory. If so you may consider editing the question – MD Zand Jan 09 '23 at 20:23
  • 1
    @MDZand Correct, in the unit tests, we are using Sqlite and for production, it is against a real SQL server. Is this a bad practice? – Josh Jan 09 '23 at 20:28
  • No and yes. Every provider has its pros and cons, with the in-memory provider being the lightest but most restricted. There's no need for complex setup or rolling back changes, but many database features just don't exist. SQLite is better, especially in in-memory mode, but you're trying to test product-specific syntax. You can use LocalDB in this case or a test database. – Panagiotis Kanavos Jan 10 '23 at 15:47
  • Do you *really* need to define the computed column in the EF model? This is used only if you use EF migrations which in turn is only meant for development and simple applications. In production you need far more control of the database schema and more importantly, the data. You can use `HasComputedColumnSql()` without the formula to tell EF that this column is computed and read-only. After that you can configure your test provider (SQLite or even in-memory) to return the data you want – Panagiotis Kanavos Jan 10 '23 at 15:55

1 Answers1

1

Assuming that you are using SQLLite in-memory, I should say SQLLite does not have REPLICATE function. There is a workaround here for SQLLite to use instead:

-- X = string
-- Y = number of repetitions

replace(hex(zeroblob(Y)), '00', X)
MD Zand
  • 2,366
  • 3
  • 14
  • 25
  • I tried [Prefix]+replace(hex(zeroblob(2)), '00', [ContainerID]) and the resulting value is '11'. But more importantly, if I have to change the OnModelBuilding just for unit tests, I must be doing something wrong. I'll have to accept that during unit testing, this property will not be set. – Josh Jan 10 '23 at 14:05
  • 1
    Implementing `REPLICATE` would result in a test testing that implementation, not the application. SQLite simply can't be used to test product-specific syntax or product-specific features. Even many ANSI SQL features. It has its place in lightweight unit testing but can't cover everything. – Panagiotis Kanavos Jan 10 '23 at 15:50