A short notice about object relational mapping framework generated queries…

Publish date:

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 […]

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))

Related Posts

Cybersecurity

The Cambrian Explosion of Data

Sicco Maathuis
Date icon April 15, 2019

What we need and increasingly want is privacy, cybersecurity, data protection, and...

cookies

The cookie monster is dead: Long live the cookie monster

Patricia Evans
Date icon March 20, 2019

Why does good practice with cookies matter? Read further to find out.

AI

Your Own Private Magic Quadrant

Ron Tolido
Date icon March 6, 2019

Dear client, if you had to rank yourself in a Magic Quadrant for data and analytics where...

cookies.

By continuing to navigate on this website, you accept the use of cookies.

For more information and to change the setting of cookies on your computer, please read our Privacy Policy.

Close

Close cookie information