A short notice about object relational mapping framework generated queries…

I guess object relational mapping is an accepted paradigm for exchanging data between an object oriented domain layer and underlying databases. For most applications object relational mapping is more than sufficient. And if not, perhaps command query responsibility segregation might contribute well to your solution.

Being a more than frequent user, this very short blog post is not meant to question either of these popular paradigms. However, I would like to make it clear that applying object relational mapping might not always lead to optimal solutions, especially from a database perspective. Most object relational mapping framework generate the underlying queries to the database, based on the domain layer and the associations between the domain objects in this layer. The quality of these generated queries is strongly dependent on the quality of the domain model and layer. If the domain model is complex, so will the generated queries be. Rubbish in is rubbish out.

image

So it is noteworthy how the framework you’re using maps associations and properties to SQL statements. Some frameworks may choose remarkable construct for this mapping resulting in hard and unreadable queries, or queries that aren’t very much optimized for the underlying data model. So this short blog post is just to make you notice.

The problem actually comes from the enormous amount of features a full-grown object relational mapping framework needs to implement. Basically, everything and the kitchen sink. Driven by the good but blind ambition to serve as many developers as they possibly can, in any case more than all other competitors, the framework developers implement as many features as the code in the framework can handle. And sometimes this feature bloating may lead to peculiar results, where the framework might still function, but the original goals of providing a lightweight, straightforward, easy-to-use framework to solve most common mapping problems have long been abandoned.

For developers using such frameworks I guess the most decent piece of advice I can give is: don’t go into the dark corners of domain modeling, as the solutions resulting may be hard to maintain and to extend. Use middle-of-the-road constructs, and prefer simpler, more generic out-of-the-box solutions to more specific and elaborate domain models.

I’ll leave with an example of a query which was generated by a frequently used object relational mapping framework in the .Net space. I’m quite sure the database will have a hard time executing this particular query.

And yes, the following is indeed a single select query. Go figure.

SELECT

[Extent1].[ID] AS [ID],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN ‘2X’ WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL)))) THEN ‘2X0X’ WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN ‘2X0X0X’ WHEN(([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN’2X1X’ ELSE ‘2X1X0X’ END AS [C1],

[Extent1].[MAPReportId] AS [MAPReportId],

[Extent1].[PartnerNumber] AS [PartnerNumber],

[Extent1].[CapturedVia] AS [CapturedVia],

[Extent1].[Currency] AS [Currency],

[Extent1].[DataAsOf] AS [DataAsOf],

[Extent1].[DateApproved] AS [DateApproved],

[Extent1].[DateCreated] AS [DateCreated],

[Extent1].[DateProcessed] AS [DateProcessed],

[Extent1].[DateReturned] AS [DateReturned],

[Extent1].[DateSubmittedToRo] AS [DateSubmittedToRo],

[Extent1].[PeriodCovered] AS [PeriodCovered],

[Extent1].[ProcessGeneralComments] AS [ProcessGeneralComments],

[Extent1].[ReasonReturned] AS [ReasonReturned],

[Extent1].[StatusID] AS [StatusID],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[AverageLoanSize] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL))THEN [UnionAll1].[AverageLoanSize] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C2],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[AveragePortfolioOutstanding] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6]IS NOT NULL)) THEN [UnionAll1].[AveragePortfolioOutstanding] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT(([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C3],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[BoYPortfolioOutstanding] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] ISNOT NULL)) THEN [UnionAll1].[BoYPortfolioOutstanding] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT(([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C4],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[DepositsAmount] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL))THEN [UnionAll1].[DepositsAmount] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C5],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS int) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[EoYNrBorrowers] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN[UnionAll1].[EoYNrBorrowers] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS int) END AS [C6],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[EoYPortfolioOutstanding] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] ISNOT NULL)) THEN [UnionAll1].[EoYPortfolioOutstanding] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT(([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C7],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] =1) AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[MFIPortfolioSize] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOTNULL)) THEN [UnionAll1].[MFIPortfolioSize] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] =1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) END AS [C8],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS int) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[NrLoans] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN[UnionAll1].[NrLoans] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS int) END AS [C9],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[InvestmentOutstanding] WHEN (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL)) THEN [UnionAll1].[InvestmentOutstanding] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C10],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[LoanOutstanding] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] ISNOT NULL)) THEN [UnionAll1].[LoanOutstanding] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT(([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C11],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN [UnionAll1].[OutstandingExposure] WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOTNULL)) THEN [UnionAll1].[OutstandingExposure] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT(([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C12],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] =1) AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL))THEN [UnionAll2].[C1] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) END AS [C13],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN[UnionAll2].[C2] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6]IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C14],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN[UnionAll2].[C3] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6]IS NOT NULL)))) THEN CAST(NULL AS float) END AS [C15],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] =1) AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL))THEN [UnionAll2].[C4] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) END AS [C16],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS bit) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS bit) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THEN[UnionAll2].[C5] WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND ([UnionAll1].[C6]IS NOT NULL)))) THEN CAST(NULL AS bit) END AS [C17],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THENCAST(NULL AS float) WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN [UnionAll2].[Exposure] ELSE [UnionAll2].[Exposure] END AS [C18],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THENCAST(NULL AS float) WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN [UnionAll2].[InvestmentOutstanding] ELSE [UnionAll2].[InvestmentOutstanding]END AS [C19],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT (([UnionAll2].[C6] = 1)AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS float) WHEN (([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)) THENCAST(NULL AS float) WHEN (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] IS NOT NULL) AND ( NOT (([UnionAll1].[C6] = 1) AND([UnionAll1].[C6] IS NOT NULL)))) THEN [UnionAll2].[LoanOutstanding] ELSE [UnionAll2].[LoanOutstanding] END AS [C20],

CASE WHEN (( NOT (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL))) AND ( NOT (([UnionAll2].[C7] = 1) AND ([UnionAll2].[C7] ISNOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([UnionAll1].[C5] = 1) AND ([UnionAll1].[C5] IS NOT NULL) AND ( NOT(([UnionAll2].[C6] = 1) AND ([UnionAll2].[C6] IS NOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([UnionAll2].[C6] = 1) AND([UnionAll2].[C6] IS NOT NULL))