Is it possible to extend QueryBuilder?

Oct 31, 2009 at 12:05 PM

Hello,

I have three tests failed for my OracleClient provider.

Table failed to execute:
SELECT CASE WHEN (EXISTS(
  SELECT NULL
  FROM "Customers" t0
  )) THEN 1 ELSE 0 END AS "value"
Test 169: TestAnyTopLevel - FAILED
Reason: ORA-00923: FROM keyword not found where expected

Table failed to execute:
SELECT CASE WHEN (NOT EXISTS(
  SELECT NULL
  FROM "Customers" t0
  WHERE NOT (t0."ContactName" LIKE :p0 || '%')
  )) THEN 1 ELSE 0 END AS "value"
Test 172: TestAllTopLevel - FAILED
Reason: ORA-00923: FROM keyword not found where expected

Table failed to execute:
SELECT CASE WHEN (:p0 IN (
  SELECT t0."CustomerID"
  FROM "Customers" t0
  )) THEN 1 ELSE 0 END AS "value"
Test 175: TestContainsTopLevel - FAILED
Reason: ORA-00923: FROM keyword not found where expected

Obviously I need to append " FROM DUAL" to let them pass in Oracle.
After some study, I found QueryBinder.cs might be what I need. I need to handle "All" and "Any" in VisitMethodCall.
But it seems current QueryBinder class is not extendable.

I can copy and amend it.  But I want to keep IQToolkit core untouched if possible.
So my question is: is there another better way? Or may be my finding is wrong?

Thanks
Wicky

 

Coordinator
Nov 8, 2009 at 7:13 AM

A provider for Oracle has not been done yet.  All the changes necessary to make IQToolkit work for Oracle should happen within an Oracle specific provider.  It is unlikely that QueryBinder needs to change or be extended.  Most likely everything that needs to be done can be done in an Oracle specific query formatter.

Nov 12, 2009 at 2:16 PM
Edited Nov 12, 2009 at 2:25 PM

Hello, mattwar,

I know a provider for Oracle has not been done.   I'm trying to make one. 
I have passed most of cases and logged all problems in Issue Tracker and here.
If you interest in it, I would like to send my project to you.

Thanks
Wicky

 

Dec 17, 2009 at 4:18 AM

Hello, mattwar,

I found the cause in QueryBinder.GetSingletonSequence

 private Expression GetSingletonSequence(Expression expr, string aggregator)
        {
            ParameterExpression p = Expression.Parameter(typeof(IEnumerable<>).MakeGenericType(expr.Type), "p");
            LambdaExpression gator = null;
            if (aggregator != null)
            {
                gator = Expression.Lambda(Expression.Call(typeof(Enumerable), aggregator, new Type[] { expr.Type }, p), p);
            }
            var alias = this.GetNextAlias();

            // original version: "select ..."
            //SelectExpression select = new SelectExpression(alias, new[] { new ColumnDeclaration("value", expr) }, null, null); 

            // for oracle, need to be "select ... from dual", so if I change this line as below, three tests will pass
            SelectExpression select = new SelectExpression(alias, new[] { new ColumnDeclaration("value", expr) },new TableExpression(this.GetNextAlias(), null, "DUAL"),null)

            return new ProjectionExpression(select, new ColumnExpression(expr.Type, null, alias, "value"), gator);
        }

As I checked, this function is used in BindAnyAll and BindContains, both of BindAnyAll and BindContains are used in virtual VisitMethodCall.
So I can change this by create a OracleQueryBinder in which overwrite VisitMethodCall.

But if GetSingletonSequence could be changed to protected virtual, will save me a lot of works to only overwrite this very function.

Or do you have any suggestion to do this job in formatter?

Thanks
Wicky

Dec 17, 2009 at 4:57 PM

I ran into the same problem of not being able to override some methods when I started writing an IQToolkit Provider for VFP data (LINQ to VFP).  I handled this issue by copying some of the IQToolkit source code into my project.  I wasn’t thrilled with the idea of copy and pasting so much code… but it got the job done.

One notable change that I needed to make was an override for BasicMapping.GetGeneratedIdCommand.  This one little change forced me to copy over all the other mapping classes so that they could derive from my version.

 

Feb 23, 2010 at 3:45 AM

Hi,

The issue can be fixed by setting AllowSubqueryInSelectWithoutFrom to false.

Regards
Wicky