1
Vote

Invalid LIKE query (substringof) generated via ApplysTo, yet works OK with EnableQuery attribute

description

Noticed a difference in the way that LINQ to SQL encodes a substringof (contains) query, depending on whether EnableQueryAttribute or ODataQueryOptions.ApplyTo is used.

For example, I send a query like the following:
$filter=substringof('phone'%2CTargetBranchText)%20eq%20true&$top=100&$expand=SourceFile%2CUser%2CProject%2CBranch%2CState&$inlinecount=allpages
And the controller looks like this:
    [EnableQuery]
    public IQueryable<ResourceString> GetResourceString()
    {
        return db.ResourceStrings;
    }
Then the LIKE query is parameterized correctly, and looks like this:
@p__linq__0=N'%phone%'
However, if I use a similar query:
$filter=substringof('''phone'''%2CTargetBranchText)%20eq%20true&$expand=SourceFile%2CUser%2CProject%2CBranch%2CState
And pass this to a controller like the following:
    [HttpPost]
    public void BulkExportAsCsv(
        ODataQueryOptions<ResourceString> opts
        )
    {
        Exporter.ValidateQueryOptionsOrThrow(
            opts, AllowedQueryOptions.Filter | AllowedQueryOptions.Expand);

        var query = opts.ApplyTo(db.ResourceStrings);
        Exporter.ExportAsCsv<ResourceString>(query, 
                typeof(ResourceStringMap), "strings.csv");
    }
Then the value passed to the LINQ parameter is malformed.
@p__linq__0=N'%''phone''%'
Notice the additional string delimiters.

This malformed query fails and returns no records on SQL Server 2014. The call to ApplyTo succeeds, but returns a non-null but empty IQueryable. I can verify that the SQL Server returns no rows. If I capture the query, remove the extra single quotes, and resubmit to SQL, then the query succeeds.

The same issue affects startswith and endswith queries, respectively:
@p__linq__0=N'''phone''%'

@p__linq__0=N'%''phone'''
This may be a LINQ to SQL issue, but I am wondering what EnableQueryAttribute is doing that does not trigger the bug. Is there a workaround?

comments

jsudds wrote Jan 29, 2015 at 9:54 PM

PS: I am using System.Web.Http.OData v4.0.30319.

jsudds wrote Feb 23, 2015 at 9:11 PM

PING?