What is the difference between Fetch and Query?

Question

To me, PetaPoco's Database.Fetch and Database.Query seem to be doing the same thing.

For example,

var db = new PetaPoco.Database("myDB");
ProductList products = db.Fetch<ProductList>("SELECT * FROM ProductList");
ProductList products = db.Query<ProductList>("SELECT * FROM ProductList");

Is there any significant difference between them?

Answer 1

According to the PetaPoco documentation, this is the answer:

Query vs Fetch

The Database class has two methods for retrieving records Query and Fetch. These are pretty much identical except Fetch returns a List<> of POCO's whereas Query uses yield return to iterate over the results without loading the whole set into memory.

Answer 2

Fetch and query behave differently if you use them inside a transaction. I had a use case where I needed to update several tables in one transaction but I needed to retrieve some data from a reference table in the middle of the sequence.

When I retrieved the data with Query, and subsequent Inserts or Updates failed with an InvalidOperationException: "There is already an open DataReader associated with this Command which must be closed first"

The solution was to replace the Query with a Fetch and I was able to complete the sequence.

The pseudocode for this is:

BeginTransaction Update Tbl1 Update Tbl2 Query RefTblA ## Changed Query to Fetch to avoid: '...already an open DataReader..." exception Update Tbl3 using data from RefTblA CompleteTransaction