c# - Semantic differences between FirstOrDefault() and Where() in sqlite-net -

more clarity question actual question have found solution. don't understand reasoning...

using sqllite 3.8.3.1 using sqlite-net 2.1

i see distinct difference between running .where(lambda).firstordefault() running .firstordefault(lambda).

as far experience linq goes, database linq providers treat both of these same (.firstordefault(lambda) may little faster if it's optimized properly, , large, these 2 calls take same time run).

however, in sqlite-net, seeing following results on table ~40,000 records in it:

when running .firstordefault(x => x.id == id), seeing time taking on core-i7 between 2200ms 3700ms. on surface rt (1st gen) takes around 20,000ms-30,000ms..

when running .where(x => x.id == id).firstordefault(), seeing time taking on same core-i7 between 16ms-20ms. on surface rt, takes around 30ms.

my question whether bug, or if that's conscious design decision. if it's design decision - love understand reasoning behind it.

.where directly translated sql clause, while .firstordefault(lambda) reads unfiltered records database , checks whether match.

in theory, possible automatically translate latter former, in practice, not done. neither conscious design decision nor bug; it's theoretically possible optimization has not been implemented.