Ok, time to try the linq to nhibernate subquery possibilities.

Let’s say I have a object called TexCase that has a collection of Tags Of Type Tag. And the Class Tag has a property called Tag of type string.

So I have this HQL query.

vbnet _Session = SessionFactory.OpenSession _Query = _Session.CreateQuery("select i from TexCase as i join i.Tags t where t.Tag like :Tag") _Query.SetString("Tag", ToFind) _ReturnList = _Query.List(Of TexCase)() Where ToFind is a String (obviously).

In this case the string can contain wildcards. Not sure if that is a good idea, but anyway.

So,

  • %s
  • s%
  • %s%

should all work.

And NHProf also confirms it.

tsql and x1_.Tag like '%s' tsql and x1_.Tag like 's%' tsql and x1_.Tag like '%s%' The linq query looks something like this.

vbnet _Session = SessionFactory.OpenSession Dim _query = From e In _Session.Linq(Of TexCase)() Where e.Tags.Any(Function(x) x.Tag.Contains(ToFind)) _ReturnList = _Query.ToList But that’s not completely true since the above will give us the following sql.

tsql and x1_.Tag like '%s%' tsql and x1_.Tag like '%s%' tsql and x1_.Tag like '%s%' Yes, the same for all three tests.

To solve that I needed to do.

vbnet _Session = SessionFactory.OpenSession Dim _query As IEnumerable(Of TexCase) If ToFind.StartsWith("%") AndAlso ToFind.EndsWith("%") Then _query = From e In _Session.Linq(Of TexCase)() Where e.Tags.Any(Function(x) x.Tag.Contains(ToFind.Substring(1, ToFind.Length() - 2))) ElseIf ToFind.StartsWith("%") Then _query = From e In _Session.Linq(Of TexCase)() Where e.Tags.Any(Function(x) x.Tag.StartsWith(ToFind.Substring(1))) ElseIf ToFind.EndsWith("%") Then _query = From e In _Session.Linq(Of TexCase)() Where e.Tags.Any(Function(x) x.Tag.EndsWith(ToFind.Substring(0, ToFind.Length() - 1))) Else _query = From e In _Session.Linq(Of TexCase)() Where e.Tags.Any(Function(x) x.Tag.Equals(ToFind)) End If _ReturnList = _query.ToList Eeck. Not pretty. But I think I can make this work better over time.