EDIT: I found a link that confirms the nature of MySQL's anomaly with the spatial index not working if equated with true. The other poster found a similar solution too.

See https://stackoverflow.com/questions/3894994/mysql-spatial-index-doesnt-work-when-equating-mbrcontains-to-true

Bottom line: I believe the trick below "MBRContains(GeomFromText('LineString({0} {1}, {2} {3})'), pt) and 1" should work and there is likely a bug with LLBLGen 5.5.5 that is preventing this approach from working. I reconfirmed that the SQL generated is fine but LLBLGen does not see any results even though there are really 3 results in my test case.

For LLBLGen 5.5.5 runtime with Adapter and .NET 4.8 with Devart's 8.13.1437 provider, I am trying to use MySql/MariaDB 10.4.8's MBRContains feature to speed up spatial queries. I am stuck by the "Booleans are not supported in MySql databases" exception. If I use a function type that is not bool, it does not use the spatial index and is 100x slower. Example SQL that works and is fast is:

SELECT * FROM mytable 
WHERE MBRCONTAINS(GEOMFROMTEXT('LineString(44.5 -90.7, 44.8 -90.4)'), pt);

An idiosyncrasy of the MBRCONTAINS function is it is a boolean which does not use the spatial index if queried as an integer. Thus, this query is 100 times slower than the above one.

SELECT * FROM mytable 
WHERE MBRCONTAINS(GEOMFROMTEXT('LineString(44.5 -90.7, 44.8 -90.4)'), pt) = 1;

Where I am stuck relates to the LLBLGen exception "Booleans are not supported in MySql databases" that LLBLGen throws with the code below. If I change SpatialFunctions.MBRContains to return an int and ensure it equals 1 in the whereClause, then I get slow results. I even tried tricking the runtime by changing the function to an int and making its contents "MBRContains(GeomFromText('LineString({0} {1}, {2} {3})'), pt) and 1" so I could add == 1 in the whereClause. That generates valid SQL but LLBLGen does not think there are any results from the query.

      Expression<Func<MyTableEntity, bool>> whereClause = c => SpatialFunctions.MBRContains(inBounds.BottomLine, inBounds.LeftLine, inBounds.TopLine, inBounds.RightLine, c.Latitude);

using (var adapter = new DataAccessAdapter())
{
   var metaData = new LinqMetaData(adapter);
   metaData.CustomFunctionMappings = new SpatialFunctionMappings();
   var q2 = from c in metaData.MyTable.Where(whereClause)
               select c.PostalCode;
   var count = q2.Count();
}

/// <summary>
/// Class which defines the custom mapping for MBRContains on MySQL to do a minimum bounding rectangle search
/// </summary>

public class SpatialFunctionMappings : FunctionMappingStore
{
    public SpatialFunctionMappings() : base()
    {
        // note that the 5th parameter is not actually used, but is needed to trick LLBLGen to not try to evaluate the function at the client
        this.Add(new FunctionMapping(typeof(SpatialFunctions), "MBRContains", 5, "MBRContains(GeomFromText('LineString({0} {1}, {2} {3})'), pt) "));
    }
}

/// <summary>
/// Class which is used to specify the call to the MBRContains construct. 
/// </summary>
public class SpatialFunctions
{
    public static bool MBRContains(double lat1, double lon1, double lat2, double lon2, double lat)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return true;
    }
}

Here is the schema for a test table.

CREATE TABLE `mytable` (
    `postalcode` VARCHAR(50) NULL DEFAULT NULL,
    `latitude` DOUBLE NULL DEFAULT NULL,
    `longitude` DOUBLE NULL DEFAULT NULL
)

-- Code to create the spatial index
ALTER TABLE mytable ADD pt POINT;
UPDATE mytable SET pt = point(ifnull(latitude, -1000), ifnull(longitude, -1000));
ALTER TABLE mytable MODIFY pt POINT NOT NULL;

Please advise if you can think of a workaround or fix. Note that the code is part of a large project so I provided snippets herein. If you find this info is insufficient to reproduce the issue, let me know and I'll see what I can send privately.

Thank you.

Weird thing is: the error you get is in the parameter creation method, which can't create a parameter of type System.Boolean. But your code doesn't create a boolean parameter, so it's weird.

Looking into it.

(edit) reproduced

The problem is mainly that the predicate ends up as a field compare value predicate. Where clauses are always predicates so it tries to compare it with something, in this case 'true'. So it tries to create <functioncall> = true.

This doesn't work on mysql as the 'true' is invalid. Converting it to 1 will not help you as, as you say, the index then isn't used.

A workaround could be to use queryspec for this query and your own implementation of IPredicate, which simply produces the sql of the function call. You could implement the MBRContains directly if you want, so an MBRContainsPredicate class, which produces a sql string equal to MBRCONTAINS(GEOMFROMTEXT('LineString(44.5 -90.7, 44.8 -90.4)'), pt) (but then with parameters)

For linq, the options are rather limited, as it will end up in a method which makes the selection what to use as predicate and it will produce a <function>=true predicate as that's what's logical resulting from the linq expression. I don't see a way to trick the linq provider into doing something else, as it simply has to provide a predicate and there's no way to custom create a custom predicate type or pass it to it.

Regarding the 0 rows returned... did you check with e.g. orm profiler to see what the resultset was from the query as generated by the system? I don't have any testdata so can't reliably test it

After spending most of the day on this, I have some good news...

The code below works fine in Linq mode:

this.Add(new FunctionMapping(typeof(SpatialFunctions), "MBRContains", 5, "MBRContains(GeomFromText(CONCAT('LineString(',{0},' ',{1},',',{2},' ',{3},')')), pt) and 1"));

I'll spare you the play-by-play of how I found this, but suffice it to say, my spatial queries via LLBLGen are fast now

edit: To avoid confusion, here is the full FunctionMapping that works with MySQL/MariaDB:

    /// <summary>
    /// Class which defines the custom mapping for MBRContains on MySQL to do a minimum bounding rectangle search
    /// </summary>

    public class SpatialFunctionMappings : FunctionMappingStore
    {
        public SpatialFunctionMappings() : base()
        {
            this.Add(new FunctionMapping(typeof(SpatialFunctions), "MBRContains", 5, "MBRContains(GeomFromText(CONCAT('LineString(',{0},' ',{1},',',{2},' ',{3},')')), pt) and 1"));
        }

    }

    /// <summary>
    /// Class which is used to specify the call to the MBRContains construct. 
    /// </summary>
    public class SpatialFunctions
    {
        public static int MBRContains(double lat1, double lon1, double lat2, double lon2, double lat)
        {
            // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
            return 0;
        }
    }

/// Usage example
   Expression<Func<MyTableEntity, bool>> whereClause = c => c.PostalCode != null
                && SpatialFunctions.MBRContains(inBounds.BottomLine, inBounds.LeftLine, inBounds.TopLine, inBounds.RightLine, c.Latitude) == 1;

using (var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
metaData.CustomFunctionMappings = new SpatialFunctionMappings();
var q2 = from c in metaData.MyTable.Where(whereClause)
             select c.PostalCode;
var count = q2.Count();
}

Glad you found a workaround! And sorry it took 2 days for you to get this working. With things like this, it might be beneficial next time to write a custom predicate and use a queryspec query to get it working. Linq is rather closed so it's often hard to make a customization...

Thanks. Can you point me to an example of creating such a predicate and using it in QuerySpec? Part of my challenge yesterday was I've been having a hard time wrapping my head around how to do that. (I found it easier to compile and step through the runtime code to see what was being passed to the Devart provider so I could find and fix the Linq approach.)

I've reviewed the documentation, but it seems to focus more on custom functions than custom predicates and I could not find a "cookbook" example. (I reviewed https://www.llblgen.com/documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_functionmappings.htm and https://www.llblgen.com/documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_creatingpredicates.htm)

I'm sure I'm missing something obvious but as I'm gradually moving the codebase toward QuerySpec, further guidance would be much appreciated.

Thank you!

After more thought (and some sleep!), I think I know how to do the Predicate. I'll post again in a day or two with either a more specific question or a suggested solution. Thanks.

Here's a custom predicate for MBRContains. It's not beautiful or complete, but it works. Thanks again for your help!

    /// <summary>
    /// Implementation of the MySql MBRContains spatial search from the region (x1,y2) to (x2,y2) against a specified field of type Point
    /// </summary>
    [Serializable]
    public class MBRContainsPredicate : Predicate
    {
        #region Class Member Declarations
        private IEntityFieldCore _field;
        private IFieldPersistenceInfo _persistenceInfo;
        private double _x1 = 0;
        private double _y1 = 0;
        private double _x2 = 0;
        private double _y2 = 0;
        private string _compareFieldName;
        #endregion

        #region Selfservicing constructors

        /// <summary>
        /// CTor for MBRContains spatial search from the region (x1,y2) to (x2,y2) against a specified field of type Point
        /// SelfServicing specific
        /// </summary>
        /// <param name="field">Field to compare</param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <param name="doubleAlias"></param>
        public MBRContainsPredicate(IEntityField field, double x1, double y1, double x2, double y2, string doubleAlias)
        {
            InitClass(field, field, x1, y1, x2, y2, doubleAlias, null);
        }
        #endregion

        #region Adapter constructors
        /// <summary>
        /// CTor for MBRContains spatial search from the region (x1,y2) to (x2,y2) against a specified field of type Point
        /// Adapter specific
        /// </summary>
        /// <param name="field"></param>
        /// <param name="persistenceInfo"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <param name="doubleAlias"></param>
        public MBRContainsPredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, double x1, double y1, double x2, double y2, string doubleAlias)
        {
            InitClass(field, persistenceInfo, x1, y1, x2, y2, doubleAlias, null);
        }

        /// <summary>
        /// CTor for MBRContains spatial search from the region (x1,y2) to (x2,y2) against a specified field of type Point
        /// Adapter specific
        /// </summary>
        /// <param name="field"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public MBRContainsPredicate(IEntityFieldCore field, double x1, double y1, double x2, double y2)
        {
            InitClass(field, null, x1, y1, x2, y2, null, null);
        }
        #endregion

        /// <summary>
        /// CTor for MBRContains spatial search from the region (x1,y2) to (x2,y2) against a specified field of type Point
        /// </summary>
        /// <param name="compareFieldName"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public MBRContainsPredicate(string compareFieldName, double x1, double y1, double x2, double y2)
        {
            InitClass(null, null, x1, y1, x2, y2, null, compareFieldName);
        }

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

        public override string ToQueryText(bool inHavingClause)
        {
            var locCompareFieldName = _compareFieldName;
            if (string.IsNullOrEmpty(locCompareFieldName))
            {
                locCompareFieldName = _field.Name;
                //has null reference exceptions
                //locCompareFieldName = this.DatabaseSpecificCreator.CreateFieldNameSimple(_persistenceInfo, _field.Name);
                //locCompareFieldName = this.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, this.ObjectAlias, inHavingClause);
            }

            var queryText = StringBuilderCache.Acquire(128);
            queryText.Append("MBRContains(GeomFromText('LineString(")
                .Append(_x1).Append(' ').Append(_y1)
                .Append(',').Append(_x2).Append(' ').Append(_y2)
                .Append(")'), ").Append(locCompareFieldName).Append(')');
            return StringBuilderCache.GetStringAndRelease(queryText);
        }

        /// <summary>
        /// Inits the class
        /// </summary>
        /// <param name="field"></param>
        /// <param name="persistenceInfo"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        /// <param name="doubleAlias"></param>
        /// <param name="compareFieldName"></param>
        private void InitClass(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, double x1, double y1, double x2, double y2, string doubleAlias, string compareFieldName)
        {
            _field = field;
            _persistenceInfo = persistenceInfo;
            _x1 = x1;
            _x2 = x2;
            _y1 = y1;
            _y2 = y2;
            this.InstanceType = (int)PredicateType.Undefined;
            this.ObjectAlias = doubleAlias;
            _compareFieldName = compareFieldName;
        }
    }

Yep that's basically it. The sourcecode zip of the runtime in general is a good start for these kind of classes. (It's available in the extras section on the website in the My Account area)