NHibernate any array values are contained in a string does not work?

Question

I'm trying to do this:

    public static IQueryable<MyEntity> WhereLocations(
        this IQueryable<MyEntity> query,
        string[] locations)
    {
        return query.Where(x => locations.Any(t => x.Location.StartsWith(t)));
    }

How ever when I do that, it gives me a method not supported;

[NotSupportedException: Specified method is not supported.]
   NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName(IASTNode querySource) +71
   NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process(IASTNode tree) +136
   NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process() +40
   NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) +89
   NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryIdentifier, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) +42
   NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) +234
   NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) +307
   NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) +294
   NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) +70
   NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +32
   NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +37
   Remotion.Linq.QueryableBase`1.GetEnumerator() +53
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58

The value of string MyEntity.Location could be like: 100-01, 100-02 and so on.

The value of array of string locations could be like: [100, 101].

What I would like to find any of MyEntity that starts with any of the values in locations.

Optionally:

Is there a mapping I could do in fluent-nhibernate that I could map this database column into two properties based on the first value before dash and what ever behind the dash

Appreciate any tips or help on this.

Solution

Here is what I ended up doing:

    public static IQueryable<MyEntity> WhereLocations(
        this IQueryable<MyEntity> query,
        string[] locations)
    {
        if (locations.Length == 0)
        {
            return query;
        }

        if (locations.Length == 1)
        {
            return query.Where(x => x.Location.StartsWith(locations[0]));
        }

        var predicate = PredicateBuilder.False<MyEntity>();
        predicate = locations.Aggregate(
            predicate, (current, temp) => current.Or(x => x.Location.StartsWith(temp)));
        return query.Where(predicate);
    }

I guess it won't make any difference about the error, but from your description, shouldn't it be t=>x.Location.Contains/StartsWith(t) ? — jbl
– jbl, Commented Oct 24, 2013 at 14:05
the point was on more on t=>x.Location.Contains(t) instead of t => t.Contains(x.Location) — jbl
– jbl, Commented Oct 25, 2013 at 7:02
True that, but it got resolved with the OR chain built in expressions :) — Bjarte
– Bjarte, Commented Oct 25, 2013 at 7:03

Oskar Berggren · Accepted Answer · 2013-10-24 18:53:23Z

4

The SQL you need would be something like

   x.Location LIKE 'tLoc1' + '%'
OR x.Location LIKE 'tLoc2' + '%'
OR x.Location LIKE 'tLoc3' + '%'
etc.

If you cannot get it to work with Any(), use PredicateBuilder to construct the dynamic OR-clauses in LINQ.

Regarding the mapping, I'm not aware of any mapping construct that can take two properties and store them as one column. You can use extra unmapped properties in your model that performs the required split/concat, but that doesn't help you with querying them.

answered Oct 24, 2013 at 18:53

Oskar Berggren

5,6561 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bjarte Over a year ago

Thanks, the PredicateBuilder helped make the OR statements dynamically :)

Collectives™ on Stack Overflow

NHibernate any array values are contained in a string does not work?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related