How to split a string field and check if it contains a specified string using an nhibernate queryOver?

Question

I am trying to use NHibernate QueryOver to return results where a column (DoNotDisplay) with a comma separated string does not contain a specific string e.g. "3".

I have tried the following:

var query = session.QueryOver<Host>()
.Where(h => !h.systemsNotToBeShown.Split(',').Contains("3"));

The host class contains the following:

public virtual string systemsNotToBeShown { get; set; }

The mapping is as follows:

<property name="systemsNotToBeShown" column="DoNotDisplay" />

This returns the following exception:

Unrecognised method call: System.Linq.Enumerable:Boolean Any[TSource](System.Collections.Generic.IEnumerable`1[TSource], System.Func`2[TSource,System.Boolean])

I have also tried setting the mapped property as private and creating a list in the class itself and then comparing from that list as follows:

private virtual string _systemsNotToBeShown { get; set; }
public virtual List<int> SystemsNotToBeShown
{
   get
    {
    return string.IsNullOrEmpty(_systemsNotToBeShown) ? new List<int>() : _systemsNotToBeShown.Split(',').Select(Int32.Parse).ToList();
    }
     set { }
     }

The mapping was also changed appropriately.

I then tried changing the query to the following:

var query = session.QueryOver<Host>()
.Where(h => !h.SystemsNotToBeShown.Contains(3));

I then get the following exception:

Unrecognised method call: System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxx]]:Boolean Contains(Int32)

I'm not sure what I'm doing incorrectly. Any help would be great.

Answer 1

I managed to get around this by doing the following:

var queryString = "( ( (select position('" + sourceId + "' in trim(NODISPONSYSTEM) ) > 0 from system.iota) = false) or trim(NODISPONSYSTEM) is null) ";

and then added it to the QueryOver with the following line:

.And(NHibernate.Criterion.Expression.Sql(queryString))