We have moved to GitHub Issues
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();
if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias))
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.
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?
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.
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
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
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?
You forgot to add what's different in the result?
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').
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?
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
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.
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.
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.
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.
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 ;)
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.
I fixed it by changing the code to this: var typeValue = type.ToString();
var sql = new Sql();
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 :-).
Backwards Compatible: False
Affected versions: 7.1.7, 7.1.8
Due in version: 7.1.7, 7.1.8