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.
Chris is awesome.
