NHibernate.Linq Pitfalls: Casting

[ERR!]
added by notherdev
10/31/2011 10:31:32 AM

382 Views

One thing that needs to be always remembered when writing NHibernate.Linq queries is that it is going to be translated into SQL eventually. What this means is that we can't do everything in our Select or Where conditions - we are restricted by the capabilities of underlying database and SQL language itself.

Read the full story
NHIbernate LINQ
5 comments
dpeterson
10/31/2011 10:30:52 AM
This is one of those spots where I feel Linq isn't as good as other solutions I've seen. In Sybase's PowerBuilder product, you can pass an array directly to a SQL IN statement and it will automatically populate the IN statement. It would be much cleaner if Linq could do something similar here.
notherdev
10/31/2011 10:33:59 AM
You can do that in NHibernate.Linq too - my second query does that. But you need to ensure that the types are converted correctly and at C#-side.
dpeterson
10/31/2011 10:35:29 AM
Ah, it didn't seem like that was happening based on your code. Does the SQL generated by that code use an IN statement?
notherdev
10/31/2011 10:37:23 AM
Yes, it does.
dpeterson
10/31/2011 10:43:21 AM
My mistake then, looking at the code it seemed like Linq would do the filtering after the results were returned. Seems it's smarter than I guessed!
bradygaster
10/31/2011 8:54:57 PM
Using NHProfiler for things debugging your NH code makes things like this a little easier to find. Everyone gets to learn these gems of the art of NHibernate through experiencing the pain first-hand. It's nice to see example articles like this pop up to explain those ins and outs.
Noldorin
11/2/2011 10:18:28 PM
This is an important pitfall to point out, so well done for that.

However, I believe I'm right in saying that an even easier way, and certainly more efficient (it doesn't require the conversion to a list and the corresponding allocation of memory) is to call `.AsEnumerable()` on the enumerable *after* the local operations but *before* the SQL operations.