U4-5633 - Petapoco strongly typed queries don't work anymore

Created by Jeroen Breuer 10 Oct 2014, 19:04:14 Updated by Jeroen Breuer 13 Oct 2014, 16:08:43

I've got the following strongly typed query written in petapoco

var sql = new Sql(); sql.Select("*") .From() .Where(x => x.ContentTypeAlias == contentTypeAlias) .Where(x => x.Type == type.ToString());

if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias)) { sql.Where(x => x.ConfigAlias == configAlias); }

return Database.Fetch(sql);

It's part of the Hybrid Framework: https://github.com/jbreuer/Hybrid-Framework-for-Umbraco-v7-Best-Practises/blob/master/Umbraco.Extensions/BLL/ContentOverrideLogic.cs#L115

In Umbraco 7.1.6 when I debug sql.SQL I get the following query:

SELECT * FROM [hfContentOverride] WHERE ([hfContentOverride].[contentTypeAlias] = 'Home') AND ([hfContentOverride].[type] = 'Config') AND ([hfContentOverride].[configAlias] = 'startNodeId')

When I debug this in Umbraco 7.1.7 and 7.1.8 I get the following query:

SELECT * FROM [hfContentOverride] WHERE ([hfContentOverride].[contentTypeAlias] = @0) AND ([hfContentOverride].[type] = @1) AND ([hfContentOverride].[configAlias] = @2)

I know that 7.1.7 [fixed a memory issue|https://umbraco.com/follow-us/blog-archive/2014/10/2/umbraco-717-and-623-saving-you-loads-of-memory] with petapoco so it's probably related to that. The strongly typed queries are part of Umbraco (Umbraco.Core.Persistence.PetaPocoSqlExtensions) and not petapoco.

For now I downgraded the website to 7.1.6 (easy with nuget) and my strongly typed queries work again.

1 Attachments


Morten Christensen 10 Oct 2014, 19:34:03

When you debug the sql object, don't you have access to the parameters which will be replacing the @0, @1 and @2 pointers? And does it not produce the right result?

Jeroen Breuer 10 Oct 2014, 19:40:10

No I don't have access to those parameters. The only thing the strongly typed queries do is convert the code to a sql query and that returns a different result in 7.1.7.

Morten Christensen 10 Oct 2014, 20:02:34

The before and after sql you listed is exactly what we wanted to do for the 7.1.7 optimization, so not entirely sure I see an issue here. The only difference should be that you could use the output of sql.SQL before because the parameters were inserted into the string (which was the memory issue), whereas now you'd get "pointers" to the right parameters.

We use the strongly typed sql object throughout the code base, and something like this looks very similar to your code: https://github.com/umbraco/Umbraco-CMS/blob/7.2.0/src/Umbraco.Core/Persistence/Repositories/ContentRepository.cs#L75

Morten Christensen 10 Oct 2014, 20:07:15

This is the object to look for on the SQL class for a list of parameter values: https://github.com/umbraco/Umbraco-CMS/blob/7.2.0/src/Umbraco.Core/Persistence/PetaPoco.cs#L2303

Jeroen Breuer 10 Oct 2014, 20:14:00

I'm not really sure what changed in 7.1.7, but how can a strongly typed query now return a different result? Do I need to write my queries different now and if so what would I need to change to get the result which I had in 7.1.6?

Sebastiaan Janssen 11 Oct 2014, 12:32:47

You forgot to add what's different in the result?

Jeroen Breuer 11 Oct 2014, 12:36:46

The queries are diffent so with the 7.1.6 I get the result I expected and in 7.1.7 it always returns no results because it's a different query (with @0 instead of 'Home').

Morten Christensen 11 Oct 2014, 12:54:52

Maybe have a look at this test and how it ensures the arguments for the various parameters are there - and see how it compares to your query: https://github.com/umbraco/Umbraco-CMS/blob/release-7.1.8/src/Umbraco.Tests/Persistence/Querying/ContentRepositorySqlClausesTest.cs

If there are any issues in your specific query I'm not spotting them, and if the argument / parameter part is not working properly I would assume nothing in umbraco would be working, as all the repositories use this same SQL object approach.

Do you have a solution and database you can share to reproduce the issue?

Jeroen Breuer 11 Oct 2014, 13:01:59

If you download the Hybrid Framework and upgrade it to 7.1.7 you can reproduce the issue: https://github.com/jbreuer/Hybrid-Framework-for-Umbraco-v7-Best-Practises

Andy Butland 11 Oct 2014, 16:13:05

Had a quick look at this in my copy of your framework Jeroen. It looks to be happening with your type parameter above. If you put a break in your method referenced above, and examine sql.Arguments, you'll see the second one looks rather odd - and not 'Config' as you would expect - leading to no results. See screenshot attached. With 7.1.6 as you've seen there are no arguments, the parameters are already included in the SQL string. So it's something around this enum being converted into an argument within the lambda expression of the Where clause by the looks of it.

If you do the type.ToString() outside of this, set it to a string variable and use that in the Where lambda, it works as you'd expect with the value 'Config' being passed it.

Jeroen Breuer 13 Oct 2014, 07:34:35

I think Andy is on to something here. I didn't realized that sql.SQL had to look different with @0 and @1 in 7.1.7 because there are now arguments. Looks like that converting the arguments goes wrong with an enum so that's probably the bug/breaking change here.

Morten Christensen 13 Oct 2014, 07:38:51

Guess Andy is better at explaining it then me cause that's what I've been trying to say ;)

From Andy's findings I would say that its a bug in the formatting of your lambdas.

Jeroen Breuer 13 Oct 2014, 08:58:27

I'll do some testing today to see if changing the formatting of my lambdas fixes it. However this should still be marked as a breaking change. The strongly typed query API is public and people are using it. Code that worked in 7.1.6 doesn't work in 7.1.7 so in my opinion it's a breaking change.

Morten Christensen 13 Oct 2014, 09:08:53

I'll leave the breaking change discussion to @sebastiaan because I don't agree. Your code was wrongly formatted before this update, so it only worked by fluke. And whether the fix of that is then breaking might be debatable. So you'll need to convince him ;)

Sebastiaan Janssen 13 Oct 2014, 09:19:20

I'll mark it as a breaking change, unfortunately for you but from what I'm reading you're very likely to be the only person who were able to take advantage of something that wasn't actually working like it should before.

Jeroen Breuer 13 Oct 2014, 16:08:43

I fixed it by changing the code to this: var typeValue = type.ToString();

var sql = new Sql(); sql.Select("*") .From() .Where(x => x.ContentTypeAlias == contentTypeAlias) .Where(x => x.Type == typeValue); So Andy was right. Having the enum being converted into an argument within the lambda expression of the Where clause somehow gives a different result.

I'm probaby the only person so far who had this, but it's part of the Hybrid Framework so other people might also run into it. I'll fix it :-).

Priority: Normal

Type: Bug

State: Closed


Difficulty: Normal


Backwards Compatible: False

Fix Submitted:

Affected versions: 7.1.7, 7.1.8

Due in version: 7.1.7, 7.1.8


Story Points: