ParameterExtractingExpressionVisitor::GetValue

[dmitry-lipetsk]

EFCore: master

Could you change

private object GetValue(Expression expression, out string parameterName)

to

protected virtual object GetValue(Expression expression, out string parameterName)

?

My Data Provider for EF needs in overriding of this method for preparing (transforming) expression before local evaluation.

Thanks.

[smitpatel]

Can you describe in detail why you need to override that method?

[dmitry-lipetsk]

I already wrote here about this :)

For equal calculation of expression at server and at client side.

For example, string.Substring not allows startIndex>=length_of_sting, but DBMS allows and returns empty string.

DBMS allows calculation the length of NULL-string, string.Length - no.

And other changes.

I remap "problem" nodes of expression (in overrided GetValue) to my functions, which execute by DBMS-rules.

 protected override object GetValue(Expression expression,
                                    out string parameterName)
 {
#if TRACE
  Core.Core_Trace.Method
   ("LcpiOleDb__ParameterExtractingExpressionVisitor::GetValue\n"
   +"  expression: {0}",
    expression);
#endif

  if(!Object.ReferenceEquals(expression,null))
  {
   var prepareSvc
    =Core.Engines.EngineSvcUtils.QuerySvc<Core.Engines.EngineSvc__ExpressionVisitor>
      (m_CnOptions,
       Core.Engines.EngineSvcID.EngineSvc__ExpressionTree__PrepareForLocalEvaluation);

   expression
    =prepareSvc.Visit(expression);

   Debug.Assert(!Object.ReferenceEquals(expression,null));
  }//if

  return base.GetValue
          (expression,
           out parameterName);
 }//GetValue

[smitpatel]

In general there are discrepancy in how C# works vs how DB works. Major difference being string comparison case sensitivity. Further, only DB has concept of null calculations giving back null rather than exception. Though, I am not able to understand how above differences requires interaction from provider side in ParameterExtraction. Can you write query and what is current parameterization and what is parameterization you are trying to get and why?

[dmitry-lipetsk]

Hello,

When I write the linq-expression, I expect that all it parts will be executed with equal rules.

For example:

//r.VarCharCol1 (string) contains "qwerty"
//r.VarCharCol2 (string) contains null
string vv1="qwerty";
string vv2=null;
var recs=db.testTable.Where(r => (vv1+vv2).Length==null && (r.VarCharCol1+r.VarCharCol2).Length==null);

For me, "vv1+vv2" and "r.VarCharCol1+r.VarCharCol2" - must returns equal result: NULL.

Though, I am not able to understand how above differences requires interaction from provider side in ParameterExtraction. Can you write query and what is current parameterization and what is parameterization you are trying to get and why?

string vv1="qwerty";
string vv2=null;
var recs=db.testTable.Where(r => (vv1+vv2).Length==null && r.TEST_ID==testID);

By default, EFCore calculates "(vv1+vv2)==null" as FALSE. As result, this query always returns zero number of records.

By DBMS rules "(vv1+vv2)==null" is TRUE. As result, this query returns records.

Evaluation (vv1+vv2) performed in ParameterExtractingExpressionVisitor::GetValue.

---

I may copy code of ParameterExtractingExpressionVisitor to my provider. But I try to minimize similar imports.

[smitpatel]

Based on definition string.Length returns integer type so comparing SomeString.Length == null is always going to be false. It is true that in DBMS vv1 + vv2 will give null as result because one of the argument is null, but at the same time vv1 + vv2 = null is also null result. We need to generate (vv1 + vv2) is NULL to actually get true result. That is what relational null semantics do in EF Core. Database would generate NULL result for any null argument which C# wouldn't hence we generate special IS NULL statements to compensate for that. With having whole pipeline to deal with that, our philosophy has been that we would actually try to match C# semantics rather than database. And compensate for that when generating SQL. Trying to dictate that parameterization should work like database is counter-intuitive to that philosophy, confusing to users and creates more mismatch in server eval vs client eval.

[dmitry-lipetsk]

@smitpatel

Ok, I will continue to use modified sources of EFCore.

At finish (if i get to him) I will return to this problem :)

[ajcvickers]

Triage: no decision made about behavior or implementation at this stage, but leaving this open to consider in the context of query guiding principals for 3.0 as tracked by #12795