I've got an MVC app using Entity Framework that has some complex relationships in a notifications module. I've got different queries to get parameters related to existing vs. new subscriptions to events that potentially produce notifications.

For the existing subscriptions, I created a view, and then did a SQL to EF to create an entity based on that view. Beautiful, straightforward, no problems.

For the new ones, just for fun, I decided that I'd do it all in EF. What a freaking mess. Here's the flowerbox from the repository method, reformatted a touch for width:

// NOTE: I wanted to see how difficult this would be outside of creating a
// view like I did for GetParameterSubscriptionMatrixesBySubscriptionId. I
// think the lesson is that creating a view is smarter.
//
// "LINQ LEFT JOIN on Nullable<int>"
// http://stackoverflow.com/a/28949184/1028230
//
// Populating an entity from a custom join
// http://stackoverflow.com/questions/5325797/
//
// And, tangentially, Overriding Equals and GetHashCode for entity equality.
// http://stackoverflow.com/a/508157/1028230 

That's right, three SO questions plus an insanely nasty query, where I'm still, admittedly, embarrassingly, also appending a Distinct on the collection before sending the entities back as a List. There's some strange left joining going on as a result of the workaround to allo a join on a nullable int that I haven't been able to quickly remove.

Let's list those SO questions again, just to linkify them more easily.

• "LINQ LEFT JOIN on Nullable<int>"
• Populating an entity from a custom join
• Overriding Equals and GetHashCode for entity equality.

Nasty. EF is broken. It's enough different from SQL that it's a new, often inferior, (& never, that I recall, superior) paradigm. Better not to support this, and to force you to use SQL for these tasks.

Do you hire for SQL competency? Then put as much logic as you can in your code's database.

Labels: ef, orm, SQL