C#自定义SQLiteFunction：Invoke与Step、Final方法的差异解析

Great question—this confused me when I first started with SQLite custom functions too! The short answer is that these two function types exist to handle completely different kinds of operations in SQL, so their implementations are built to match how SQLite actually uses them.

Let’s break it down:

Scalar Functions (Like Your ToUpper Class) #

Scalar functions are all about row-level, one-off transformations. Think of them as little tools that take a single value (or a set of values from one row) and spit out exactly one value back.

• When you run a query like SELECT ToUpper(Username) FROM Users, SQLite calls the Invoke method once for every row in the Users table. Each call gets the Username value from that specific row, processes it, and returns the uppercase version right away.
• The Invoke method is perfect here because it’s designed for stateless, single-input-single-output operations. There’s no need to track data across rows—each call is independent.

Aggregate Functions (Like Your SUM Class) #

Aggregate functions are the opposite: they’re built to calculate a single result from multiple rows. Operations like summing values, averaging numbers, or counting rows require collecting data across an entire dataset, not just processing one row at a time.

• Here’s how SQLite uses them:
  1. The Step method gets called once per row in the dataset. This is where you accumulate data—for SUM, you’d add the current row’s value to a running total you’re keeping track of in your class.
  2. After SQLite has processed every row, it calls the Final method. This is where you take all the accumulated data (that running total) and return the final result (the total sum).
• You couldn’t use a simple Invoke method here because aggregate functions need to maintain state across rows. The Step/Final split lets you handle the incremental accumulation and then wrap up with the final calculation.

To put it in plain terms:

• Use a scalar function when you need to "tweak" each row individually.
• Use an aggregate function when you need to "roll up" multiple rows into a single answer.

内容的提问来源于stack exchange，提问作者user9198672