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?

Grandiloquence

And how is that related to sql server? Check the petapoco documentation. – Stifle

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.

Moccasin

Is there any way to disable the methods which work by getting the entire dataset and then filtering client-side? This is so dangerous (from a performance perspective) that I don't even want these methods available to me. – Hindermost

@Hindermost - i doubt that's what's being suggested here. I am quite sure all the filtering is done server side. It's just a question of whether or not the results are yielded in an IEnumerable<T>, or loaded into a List<T> – Sankhya

@Sankhya I have confirmed via a Trace that the filtering is NOT done server side and hands the entire table to .NET to be filtered client-side. As such, Fetch should be avoided wherever possible and Query used in preference. See my own question here: #38129488 – Hindermost

I agree. I almost wish I could enable a compiler warning for these methods (or disable them via config). I checked some older code and found I'd used Fetch in several places. I can't really see the point of this method - Query will surely always be better? – Hindermost

Fetch definitely has its uses. Since it returns a List, not an IEnumerable, you get some benefits. The primary benefit is that you can do multiple iterations over the list (e.g. calls to .Count() or foreach etc) without executing the SQL multiple times. – Ungley

@Hindermost Fetch vs Query does not impact the SQL that is executed on the server (i.e. swapping from one to the other will still result in the database executing the same SQL). See https://mcmap.net/q/905737/-is-it-normal-for-npoco-petapoco-fetch-to-get-all-data-and-then-filter-client-side . – Ungley

The original danger of Fetch, was that it used to have an overload with no arguments (therefore no SQL). This meant it always fetched the entire table. If you then used LINQ on it, all the filtering was done client-side resulting in very poor performance. So I disagree that Fetch vs Query has no impact on the SQL - on the contrary - Fetch requires you to write all of the SQL yourself, otherwise it always fetches the entire table - no matter what LINQ you use (try it!). – Hindermost

And checking your link (which is an answer to my question), your answer to that question is wrong. – Hindermost

@Hindermost I suspect there is some confusion here. schotime's twitter post mentioned at https://mcmap.net/q/905737/-is-it-normal-for-npoco-petapoco-fetch-to-get-all-data-and-then-filter-client-side is re: NPoco, while this specific question is re: PetaPoco. For PetaPoco, https://mcmap.net/q/865745/-what-is-the-difference-between-fetch-and-query is 100% correct (you can check the source code to verify that, or test it yourself using SQL Profiler). Saying (as you do in your earlier comment) that Query should be used in preference to Fetch since they do filtering differently (i.e. server vs client-side) is incorrect, in the context of PetaPoco. – Ungley

@Hindermost Just in case I am wrong, are you able to point us to some sample code and associated SQL trace showing that Fetch and Query result in different SQL? I just setup dropbox.com/s/ekvbfyg0854j9vq/SQLRunner.zip?dl=0 and dropbox.com/s/r65qsnp5jyp8jdv/… and they seem to indicate that both Fetch and Query (on PetaPoco) are executing the same SQL. – Ungley

@Ungley I assumed that because NPoco was a fork of PetaPoco they would work similarly. I can see from your example that PetaPoco doesnt' seem to be a Linq to SQL method and both Fetch and Query are equally useless in that both fetch the entire table and then filter client side. If you do the same thing in NPoco, you'll see that the where clause comparing EntryType and EntryID is executed by SQL Server which is dramatically faster if there are more than a few rows in your tables. – Hindermost

* well, not "useless" but "dangerous and inefficient" if you don't realise how they work.... – Hindermost

Yep, PetaPoco and NPoco are quite different in terms of how they work. Thanks for checking @Hindermost . – Ungley

@Ungley Seems to me that NPoco has made a lot of very useful and substantial improvements over PetaPoco. It's worth checking out now NPoco's Query() method behaves for the example you used. – Hindermost

Let us continue this discussion in chat. – Ungley

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

Remedial