A quick discovery of something that probably shouldn’t have come as a surprise, but Entity Framework v4 seems capable of turning

obj.ColumnOne ?? obj.ColumnTwo

into something akin to

CASE WHEN ColumnOne IS NOT NULL THEN ColumnOne ELSE ColumnTwo END

without much difficulty, at least against Oracle and using DotConnect for Oracle as the interface. Somewhat disappointingly it won’t currently turn that ?? into an equivalent NVL upon which one might have previously created a function-based index, but at least the outcome is equivalent making the LINQ query on the .NET end substantially tidier.

It’s one of many things that I’ve struggled with when getting into Entity Framework as previously I’ve had to write all of the database queries myself, either as SQL or PL/SQL table functions. It can sometimes put you at a disadvantage, as you more readily have in the back of your mind the question ‘How’s it going to turn this into a query?’ which, at least at the initial development stages is pretty much an irrelevance.

What I should be doing is trusting that EF will figure out how my LINQ expression maps to equivalent SQL, and then test that the outputs match my expectations. However, when you’ve spent so long with your head at the database level, letting go can be difficult to do and I still find myself feeling apprehensive that once EF’s mediating my database accesses I’m just a little less in control than I was before. That leads to situations like the above – where one can sometimes slip back just a half step and, while acknowledging that the black box between you and the database probably knows what it’s doing, you wrongly think that you should help it on its way by keeping queries through it nice and straightforward.

The power, of course, is that things don’t have to be straightforward, and that you get to worry about the logic of your query rather than its implementation.