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

          Attachments

            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?