Is it normal for NPoco/PetaPoco Fetch() to get all data and then filter client side?

Question

I've noticed a huge difference in how NPoco (or PetaPoco) works depending on which function you call when you are using LINQ.

For instance compare Fetch() which Query() which both appear to do the same thing:

A: Fetch<EntryImage>().Where(t => t.EntryType == type && t.EntryID == entryID);

B: Query<EntryImage>().Where(t => t.EntryType == type && t.EntryID == entryID);

A returns every row in the table (10,000+) and then filters client side.

B returns just the one row I'm expecting.

I find this behavior is quite dangerous - it would be very easy to write very badly performing code without really evening noticing. Is this expected behavior? If this is normal behavior, is there any way to get a list of methods which work this way, so I can avoid using them where possible?

Dollydolman

Answer 1

This is expected behavior for NPoco.

As per source:

Fetch returns a list.

    /// <summary>
    /// Fetch all objects of type T from the database using the conventions or configuration on the type T. 
    /// Caution: This will retrieve ALL objects in the table
    /// </summary>
    List<T> Fetch<T>();

Query returns IQueryProviderWithIncludes (similar to IQueryable)

    /// <summary>
    /// Entry point for LINQ queries
    /// </summary>
    IQueryProviderWithIncludes<T> Query<T>();

Kinchinjunga

This is so dangerous. I wish there was an NPoco configuration option to throw an exception if you call any of these methods so you can avoid using them accidentally. – Dollydolman

@Dollydolman You could write a Roslyn analyzer to watch for Fetch in your codebase, and set the warning level to Error so it fails compilation. – Deer

Great answer for NPoco - I love the source code links too! – Rabassa

Answer 2

If you are using PetaPoco (*), neither of the initial code samples are great - but the issue is not Fetch vs Query.

In both instances, the SQL being submitted to the server is basically "SELECT * FROM EntryImage" (run a sql trace and confirm that if you aren't sure).

Fetch vs Query doesn't alter the SQL being sent to the server - it just alters how that data is served up client side (i.e. as a List or a deferred execution IEnumerable via yield).

To do what you want, check out PetaPoco's documentation :

var sql=PetaPoco.Sql.Builder()
            .Select("*")
            .From("articles")
            .Where("date_created < @0", DateTime.UtcNow) // fluent Where clause
            .OrderBy("date_created DESC");

(*) If you are using NPoco, see above for the answer.

Rabassa

OK - it seems PetaPoco behaves totally differently to NPoco now (see Albert's answer). I shouldn't have referred to both in my question :( – Dollydolman