Dynamic Data: Skeptic Turned Believer

by Admin 9. April 2012 12:35

I have to admit for the most part I loathe code generation tools.  The code generated is, in most cases, unsupportable, messy, uncommented and not optimized.  But that is the nature of code generators (otherwise most of us wouldn’t have jobs).  Recently I wrote an application that requires an administration piece for some of our database administrators.  Being technical people they do not need all of the bells and whistles of your average user.  I normally try to avoid these types of applications; they are not sexy, tedious and consume more time to develop then it appears they should.  

I had heard of Dynamic Data Web sites about 3 years ago and dismissed them as another code generation nightmare, however, in preparing for my Microsoft Web exam I ran across them again. I realized they are not code generated web sites, they are, in fact, a template with data hookups allowing developers to create a quick standard CRUD (Create, Read, Update and Delete) web site with a simple grid/detail page interface.  After looking into the details of how to implement, develop and configure the application I realized Dynamic Data is a perfect candidate for quickly implementing the application development task I had at hand.

There is a simple checklist I came up with to determine if an application is a good candidate for an out of the box (mostly) dynamic data implementation:

1.Are a relatively low number of users going to be accessing the application?

2.Can the application be created with a relatively simple UI

3.Is the application used for mainly administration (user creation/modification, updating type tables, updating contacts, etc.?

4.Is this not a user facing application?

5.There are no business requirements for the application

If the answers to all of those questions are yes, there is no reason not to create a dynamic data web site.  I would go into the details of how to create a Dynamic Data Website but Visual Studio 2010 makes it so easy I would just be regurgitating the steps provided for you.  I will say this, give it a try with one of your existing databases and you will see just how easy it is to create a fully functional application in minutes.

Tags: , ,

ASP.NET | Entity Framework | Dynamic Data

Entity Framework and Table per Type: You’ve been warned!!!

by Admin 7. April 2012 16:23

Recently I was tasked with writing an application that uses a decorator pattern for its data model.  Since the model I worked on is proprietary I will use a simple “Contact” Data model to mimic this pattern.  In my “Contact” model Contact is the main table while Person, Employee, ThirdParty and Customer are the decorator tables.  The best possible choice for the implementation (or so I thought) of this model in Entity Framework (my preferred ORM) is a pattern called Table per Type.  

This seemed like the magic bullet, but being a developer I am always skeptical of magic bullets, so I did some performance testing. I noticed performance degraded exponentially for each decorator table I added to my model.  So I took a look at the generated SQL; that is when the magic bullet went through my conceptual model and shattered my design.  Ok that was a bit over dramatic.

Here is the crux of the problem:

A one table model works just fine.  I started with the Contact table.  The Lambda Expression used to select a list of items from the “Contact” table is simply:

IEnumerable<Contact> contactList = this._context.Contacts.ToList();

Which generates the following SQL:

SELECT 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType]
FROM [dbo].[Contact] AS [Extent1]

Pretty simple, but when I add one table pictured below watch what happens to the SQL:

 

To use table per type you need to adjust some properties.  First, change the properties of the “Person” entity so that its bas type is “Contact”:

Second remove the ContactId property from the “Person” entity.  Now the “Person” decorator table is fully realized in the object model.  The model is now:

 

But look at what happens to the generated SQL (remember I changed nothing in the code):

SELECT 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN '0X' ELSE '0X0X' END AS [C1], 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType], 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN CAST(NULL AS datetime2) ELSE [Project1].[Birthday] END AS [C2], 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[Title] END AS [C3]
FROM  [dbo].[Contact] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
[Extent2].[ContactId] AS [ContactId], 
[Extent2].[Birthday] AS [Birthday], 
[Extent2].[Title] AS [Title], 
cast(1 as bit) AS [C1]
FROM [dbo].[Person] AS [Extent2] ) AS [Project1] ON [Extent1].[ContactId] = [Project1].[ContactId]

Ok a bit more overhead but still not horrible, watch what happens when I add all of the items to the object model:

 

Associated SQL:

SELECT 

CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN '0X' WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN '0X0X' WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN '0X1X' WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN '0X2X' ELSE '0X3X' END AS [C1], 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS datetime2) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN [UnionAll3].[C2] WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS datetime2) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS datetime2) END AS [C2], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN [UnionAll3].[C3] WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C3], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN [UnionAll3].[C4] WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C4], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS tinyint) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS tinyint) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN [UnionAll3].[C5] WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS tinyint) END AS [C5], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN [UnionAll3].[C6] END AS [C6], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN [UnionAll3].[C7] END AS [C7], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll3].[C8] END AS [C8], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll3].[C9] END AS [C9]
FROM  [dbo].[Contact] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
[UnionAll2].[C1] AS [C1], 
[UnionAll2].[C2] AS [C2], 
[UnionAll2].[C3] AS [C3], 
[UnionAll2].[C4] AS [C4], 
[UnionAll2].[C5] AS [C5], 
[UnionAll2].[C6] AS [C6], 
[UnionAll2].[C7] AS [C7], 
[UnionAll2].[C8] AS [C8], 
[UnionAll2].[C9] AS [C9], 
[UnionAll2].[C10] AS [C10], 
[UnionAll2].[C11] AS [C11], 
[UnionAll2].[C12] AS [C12], 
[UnionAll2].[C13] AS [C13]
FROM  (SELECT 
[UnionAll1].[ContactId] AS [C1], 
[UnionAll1].[Birthday] AS [C2], 
[UnionAll1].[Title] AS [C3], 
[UnionAll1].[C1] AS [C4], 
[UnionAll1].[C2] AS [C5], 
[UnionAll1].[C3] AS [C6], 
[UnionAll1].[C4] AS [C7], 
[UnionAll1].[C5] AS [C8], 
[UnionAll1].[C6] AS [C9], 
[UnionAll1].[C7] AS [C10], 
[UnionAll1].[C8] AS [C11], 
[UnionAll1].[C9] AS [C12], 
[UnionAll1].[C10] AS [C13]
FROM  (SELECT 
[Extent2].[ContactId] AS [ContactId], 
[Extent2].[Birthday] AS [Birthday], 
[Extent2].[Title] AS [Title], 
CAST(NULL AS varchar(1)) AS [C1], 
CAST(NULL AS tinyint) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS varchar(1)) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(1 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[Person] AS [Extent2]
UNION ALL
SELECT 
[Extent3].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
[Extent3].[CustomerId] AS [CustomerId], 
[Extent3].[CustomerRating] AS [CustomerRating], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS varchar(1)) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(0 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(1 as bit) AS [C10]
FROM [dbo].[Customer] AS [Extent3]) AS [UnionAll1]
UNION ALL
SELECT 
[Extent4].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS tinyint) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
[Extent4].[CompanyName] AS [CompanyName], 
[Extent4].[Location] AS [Location], 
cast(0 as bit) AS [C7], 
cast(1 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[ThirdParty] AS [Extent4]) AS [UnionAll2]
UNION ALL
SELECT 
[Extent5].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS tinyint) AS [C4], 
[Extent5].[EmployeeId] AS [EmployeeId], 
[Extent5].[SSN] AS [SSN], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(0 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(1 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[Employee] AS [Extent5]) AS [UnionAll3] ON [Extent1].[ContactId] = [UnionAll3].[C1]

Now imagine this on a larger scale decorator implementation.  This issue is supposed to be addressed in the entity framework 5.0.

Hopefully this will prevent others from running into the same refactoring nightmare I ran into.

 

Tags: ,

Entity Framework | SQL Server

Calendar

<<  October 2017  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Page List

RecentComments

None