Uploaded image for project: 'NHibernate'
  1. NHibernate
  2. NH-2254

The new Linq provider generates LIKE statements in a form that is not supported by some databases

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: External Issue
    • Affects Version/s: 3.0.0.Alpha1
    • Fix Version/s: None
    • Component/s: Linq Provider
    • Labels:
      None

      Description

      For a query like db.Users.Where(user => user.Name.StartsWith("A")), the old provider generates:
      SELECT
      this_.id as id0_0_,
      this_.Name as Name0_0_
      FROM
      User this_
      WHERE
      this_.Name like @p0;
      @p0 = 'X%' Type: String (4000)

      ...which is consistent with
      session.CreateCriteria<User>().Add(Restrictions.Like("Name", "A", MatchMode.Start));

      The new provider generates the following:
      select
      user0_.id as id0_,
      user0_.Name as Name0_
      from
      User user0_
      where
      user0_.Name like (@p0+'%');
      @p0 = 'X' Type: String (4000)

      (note the parameter concatenation)

      This breaks in DBs like Informix, which do not support string concatenation with a parameter.

        Gliffy Diagrams

          Activity

          Hide
          fabiomaulo Fabio Maulo added a comment -

          But the problem is not in NH.
          In LINQ we are supporting more complicated scenarios than done by criteria.
          For example: StartWith(myPrefix + toFind)
          When the parameter of StartWith is an expression, including or not a fetched property (a column in the select) we can't simply transform it to a constant.
          Please add the issue to the Informix issue tracker.
          Thanks.

          Show
          fabiomaulo Fabio Maulo added a comment - But the problem is not in NH. In LINQ we are supporting more complicated scenarios than done by criteria. For example: StartWith(myPrefix + toFind) When the parameter of StartWith is an expression, including or not a fetched property (a column in the select) we can't simply transform it to a constant. Please add the issue to the Informix issue tracker. Thanks.
          Hide
          diegose Diego Mijelshon added a comment -

          I understand that, when using other projections, the new construct is useful (and I'm fine with that being unsupported in some DBs)

          However, I don't see why we can't change this:

          return treeBuilder.Like(
          visitor.Visit(targetObject).AsExpression(),
          treeBuilder.Concat(
          visitor.Visit(arguments0).AsExpression(),
          treeBuilder.Constant("%")));

          ...to check if a client-side expression is being used, and generate it as a single argument.

          If you reopen this issue, I'll try to provide a patch.

          Show
          diegose Diego Mijelshon added a comment - I understand that, when using other projections, the new construct is useful (and I'm fine with that being unsupported in some DBs) However, I don't see why we can't change this: return treeBuilder.Like( visitor.Visit(targetObject).AsExpression(), treeBuilder.Concat( visitor.Visit(arguments 0 ).AsExpression(), treeBuilder.Constant("%"))); ...to check if a client-side expression is being used, and generate it as a single argument. If you reopen this issue, I'll try to provide a patch.

            People

            • Assignee:
              Unassigned
              Reporter:
              diegose Diego Mijelshon
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Who's Looking?