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 Bug
    • Status: Closed
    • Priority: Major 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.

        Activity

        Hide
        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
        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
        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
        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:
            Diego Mijelshon
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Who's Looking?