Hi,

basically I had a WHERE clause which had a .STDistance based filtering.

 _geoLocationTemplate = string.Format( "{0}.STDistance(geography::Point({{0}}, {{1}}, {1}))", YYYFields.GeoLocation.Name, CommonConstants.Misc.SqlGeographyPoint );

 public static DbFunctionCall GetFavorFieldsGeoLocation(double latitude, double longitude )
            {
                return new DbFunctionCall(
                    _geoLocationTemplate,
                    new object[] { latitude.ToString(CultureInfo.InvariantCulture), longitude.ToString(CultureInfo.InvariantCulture) });
            }

(also tried removing the ToString() conversion)

that worked just fine. Then I needed to have additional fields, including the calculated distance, plus additional left/inner joins - everything with "group by". This is when it got crazy. I was specifying that column in the group by clause, but statement was failing with the SqlException saying that this column is not in GROUP BY clause. I copied it from the OrmProfiler with inline parameters (had to tweak, more on that below) - it worked in SQL Management Studio. To actually replicate the problem I ran SQL Profiler and extracted the actual exec sp_executesql statement, tried to run it and got that error.

exec sp_executesql N'SELECT [LPA_f2].[UserId], [LPA_f2].[Name], [LPA_f1].[Description], [LPA_f1].[MediaFilename], [LPA_f2].[Location],
           GeoLocation.STDistance(geography::Point(@p2, @p4, 4326)) AS [d], [LPA_f2].[TimeZoneId], [LPA_f2].[DateRequestFrom], [LPA_f2].[DateRequestUntil] FROM (( [XXX].[dbo].[Entities] [LPA_f1]  INNER JOIN [XXX].[dbo].[YYY] [LPA_f2]  ON  [LPA_f1].[Id]=[LPA_f2].[Id]) LEFT JOIN [XXX].[dbo].[ZZZ] [LPA_e3]  ON  [LPA_f2].[Id]=[LPA_e3].[FId]) 
           GROUP BY [LPA_f2].[UserId], [LPA_f2].[Name], [LPA_f1].[Description], [LPA_f1].[MediaFilename], [LPA_f2].[Location], [LPA_f2].[TimeZoneId], [LPA_f2].[DateRequestFrom], [LPA_f2].[DateRequestUntil], [LPA_f1].[DateCreated], 
           GeoLocation.STDistance(geography::Point(@p6, @p8, 4326))',N'@p2 float,@p4 float,@p6 float,@p8 float',@p2=12.17,@p4=13.94,@p6=12.17,@p8=13.94

(please note that I renamed tables and some columns in the query for obvious reasons)

and it fails with:

Column 'XXX.dbo.YYY.GeoLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried removing the parameters and simply putting the values right into the query - it worked. So my assumption was that SQL Server fails (by specs?) to provide parameters to UDT in ORDER BY clause. So I needed a way to inline them in query. I tried fighting the DbFunctionCall and got very close, the only problem was extra "()" parenthesis. So I decided to check out its internals and came up with a stripped down version:

public class DirectExpression : IExpression
    {
        #region Class Member Declarations

        private readonly string _directExpression;

        #endregion

        public DirectExpression( string directExpression )
        {
            _directExpression = directExpression;
        }

        public string ToQueryText()
        {
            return ToQueryText( false );
        }

        public virtual string ToQueryText( bool inHavingClause )
        {
            DatabaseParameters = new List<DbParameter>();
            return _directExpression;
        }

        string IExpression.ToQueryText()
        {
            return ToQueryText();
        }

        string IExpression.ToQueryText( bool inHavingClause )
        {
            return ToQueryText( inHavingClause );
        }

        List<DbParameter> IExpression.Parameters { get { return DatabaseParameters; } }

        IExpressionElement IExpression.LeftOperand { get { return new ExpressionElement<object>( ExpressionElementType.Value, this ); } }

        IExpressionElement IExpression.RightOperand { get { return null; } }

        ExOp IExpression.Operator { get { return ExOp.None; } }

        void IExpression.WriteXml( XmlWriter writer )
        {
        }

        void IExpression.ReadXml( XmlReader reader )
        {
        }

        public List<DbParameter> DatabaseParameters { get; private set; }

        public IDbSpecificCreator DatabaseSpecificCreator { get; set; }
    }

now I can actually just string.Format the value I need:

public static DirectExpression GetFavorFieldsGeoLocation(double latitude, double longitude)
            {
                return new DirectExpression(string.Format( _geoLocationTemplate, latitude, longitude));
            }

same for calling additional properties for geography type like Lat and Long:

private static readonly string _geoLocationLat = string.Format( "[{0}].Lat", YYYFields.GeoLocation.Name );
private static readonly string _geoLocationLong = string.Format( "[{0}].Long", YYYFields.GeoLocation.Name );

var geoLocationLat = YYYFields.GeoLocation.Source( fAlias ).SetExpression( new DirectExpression( _geoLocationLat ) ).As( "Lat" );
                    var geoLocationLong = YYYFields.GeoLocation.Source( fAlias ).SetExpression( new DirectExpression( _geoLocationLong ) ).As( "Long" );

Question is: did I go too far? Is there a simpler solution to this? I know that this approach is subpar: I removed catalog and schema, it doesn't get a JOIN alias like other fields do in the final query, doesn't handle the 'HAVING' etc. I was also thinking if I could have extracted the resulting query from the LLBLGen engine, did string substitutions and ran it directly, but couldn't find how to extract it.

New query (copied from ORM Profiler):

SELECT [LPA_f2].[Id]                                  AS [FId],
       [LPA_f2].[UserId],
       [LPA_f2].[Name],
       [LPA_f1].[Description],
       [LPA_f1].[MediaFilename],
       [LPA_f2].[Location],
       GeoLocation.STDistance(geography::Point(12.17,
                                               13.94,
                                               4326)) AS [GeoLocation],
       [GeoLocation].Lat                              AS [Lat],
       [GeoLocation].Long                            AS [Long],
       [LPA_f2].[TimeZoneId],
       [LPA_f2].[DateRequestFrom],
       [LPA_f2].[DateRequestUntil],
       COUNT([LPA_e3].[Id])                        AS [Id]
FROM   (( [XXX].[dbo].[Entities] [LPA_f1]
          INNER JOIN [XXX].[dbo].[YYY] [LPA_f2]
              ON [LPA_f1].[Id] = [LPA_f2].[Id])
        LEFT JOIN [XXX].[dbo].[ZZZ] [LPA_e3]
            ON [LPA_f2].[Id] = [LPA_e3].[FId])
WHERE  (((((([LPA_f2].[IsDeleted] = @p1
             AND [LPA_f2].[Status] = @p2)
            AND [LPA_f2].[DateRequestUntil] >= @p3)
       AND GeoLocation.STDistance(geography::Point(12.17,
                                                   13.94,
                                                   4326)) <= @p5))))
GROUP  BY [LPA_f2].[Id],
          [LPA_f2].[UserId],
          [LPA_f2].[Name],
          [LPA_f1].[Description],
          [LPA_f1].[MediaFilename],
          [LPA_f2].[Location],
          [GeoLocation].Lat,
          [GeoLocation].Long,
          [LPA_f2].[TimeZoneId],
          [LPA_f2].[DateRequestFrom],
          [LPA_f2].[DateRequestUntil],
          [LPA_f1].[DateCreated],
          GeoLocation.STDistance(geography::Point(12.17,
                                                  13.94,
                                                  4326))
ORDER  BY [LPA_f1].[DateCreated] DESC,
          GeoLocation.STDistance(geography::Point(12.17,
                                                  13.94,
                                                  4326)) ASC 

(also modified)

You can see that UDT values are not parameterized anymore. And yes, this works.

BTW, it is impossible to copy the query with inline parameters in ORM Profiler, it throws:

Specified cast is not valid.

ORM Profiler version 1.5. Build March 20th, 2014
-----[Core exception]--------------------
   at SD.Tools.OrmProfiler.Client.Core.CoreUtils.ConvertParameterValueToString(Parameter parameter, String quoteChar) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client.Core\CoreUtils.cs:line 373
   at SD.Tools.OrmProfiler.Client.Core.CoreUtils.InlineParameters(String sqlQuery, Command command, DatabaseType typeOfDatabase) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client.Core\CoreUtils.cs:line 295
   at SD.Tools.OrmProfiler.Client.Controls.CommandViewer.HandleCopyToClipBoard() in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client\Controls\CommandViewer.cs:line 318
   at SD.Tools.OrmProfiler.Client.Controls.CommandViewer._copyToClipBoardButton_Click(Object sender, EventArgs e) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client\Controls\CommandViewer.cs:line 331
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Thank you!

P.S.: context is QuerySpec + LLBLGen Pro 4.2 build from March.

Findev wrote:

Question is: did I go too far? Is there a simpler solution to this? I know that this approach is subpar: I removed catalog and schema, it doesn't get a JOIN alias like other fields do in the final query, doesn't handle the 'HAVING' etc.

Indeed the problem is that the parameters are re-added in further expressions (SELECT, ORDER BY, GROUP, HAVING, etc). Your solution looks good. A simpler approach could be just to put the parameters inline in the original DBFunctionCall instead of creating a new expression, but your approach is ok.

We'll look into the ormprofiler crash though

daelmo wrote:

Findev wrote:

Question is: did I go too far? Is there a simpler solution to this? I know that this approach is subpar: I removed catalog and schema, it doesn't get a JOIN alias like other fields do in the final query, doesn't handle the 'HAVING' etc.

Indeed the problem is that the parameters are re-added in further expressions (SELECT, ORDER BY, GROUP, HAVING, etc). Your solution looks good. A simpler approach could be just to put the parameters inline in the original DBFunctionCall instead of creating a new expression, but your approach is ok.

Well, then I will have to keep it like that Also as I mentioned previously the ""()" parenthesis" part. I don't think it is doable through DbFunctionCall. I inlined everything and was getting extra "()" after the expression. If you check ToQueryText for DbFunctionCall.cs it adds "()" anyway when not preformatted. Can't be certain, but I think I also tried the preformatted version with no luck. Nevertheless, thank you!

About the profiler crash, it dies on:

toReturn = "/* Type: OBJECT. */";

Not really the right line, but the file hasn't been revisioned since 2012, so it might be an outdated pdb. I don't really know what the parameter values were that couldn't be inlined (it looks like they were floats) so I cant setup a test. Do you recall the parameter values (e.g. was it an UDT?) which caused this so we can have a test setup to see where and if it crashes in the latest build? THanks!