EAV schema and Lightswitch

Question

Hi,

Firstly this is related to a previous post (I thought it better to start a new one for clarity).

An EAV (Entity Attribute Value) schema is quite common. The DB purists out there will be shaking their heads in disgust; I'm appreciate the failings of this schema. But like everything there is a time when it makes sense; I’m using a hybrid schema, conventional tables where possible.

Being quite a common schema I’m surprised that I’ve found so little on the web regarding Lightswitch with EAV (actually I found nothing). The question I have is how to manipulate the UI controls to use the desired datatype; all the EAV data is effectively stored in a string column (although I’m playing with a solution using SQL sparse column for each datatype and then using a RIA service to flatten this out for Lightswitch). This means that a) you have to validate the datatype prior to storage and b) you need to set the datatype on the entry controls, so that things like a Boolean show up as a check box rather than a text box.

The validation is comparatively easy. The changing control datatypes is a bit more tricky.

Does anyone have an examples or can anyone point me in the right direction.

If anyone really objects to EAV then I’m more than willing to discuss alternatives (EAV isn’t really my favourite schema). I’ve read lots of articles slamming EAV but no real alternatives given.

Answer 1

What you're trying to do is way out of the scope of what LS was intended to do. I know that's not the answer you were hoping for, but it's kind of just how it is.

You *may* be able to "trick" LS into doing what you want, but IMO you're asking for a boatload of trouble down the road, if not straight away.

If you want to use LS, the two best options are:

• Add tables to LS's intrinsic database (which then produces entites for you & also creates the database for you)
• Attach to an external data source (that only uses simple data types)

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 2

Afternoon Yann,

Referencing the previous post probably caused confusion. In this case I'm using a SQL database using simple types. The data is in lightswitch and I've created a number of the screens; the problem I'm having is to get the UI to to be sufficiently polished. The data grid treates everything as a string, which is particularly user friendly. Below is a screenshot of the schema.

I'm attempting to create a data entry solution for our operations to replace the plethora of spreadsheets and manual logsheets. You can think of a log sheet as a checklist where the operator has to capture certain data about items of equipment.

The easiest solution would be to create a table for each logsheet. This unfortunately becomes completely impractical as we would land up with somewhere in the region of 2,000 tables and the same number of screens across our sites. This would be unmanagable.

What we need is to create a solution that will allow the operations to construct their own logsheets. After a fair amount of searching and investigation I've gone down the EAV route. EAV is a fairly common database design; even Microsoft has used it in a number of their solutions. From a pure database prespective EAV is horrible. If you know of other alternatives I'm all ears.

Regarding the limitation of LS; at the moment I feel that I'm more the limitation than LightSwitch.

 

Answer 3

After a bit of fiddling I found a solution to my problem. So I thought that post it here to close the loop; you never know some else might find this useful.

What I did was alter the DB schema slightly to use a different column per datatype. I used SQL sparse columns to make it slightly more efficient. LightSwitch loads this data with no problems. I then created a custom control for the AttributeValue based on the Telerik RADGridView control (a truly awesome control). This control uses a DataTemplateSelector to control which data template should be used for each data type. This description is extremely brief; if you want more details try the following links:

http://www.telerik.com/help/silverlight/gridview-template-selectors-overview.html

http://blogs.telerik.com/blogs/posts/10-04-01/conditional-styles-and-templates-with-radgridview-for-silverlight-and-wpf.aspx

The data displays correctly in the UI (although I still have a few kinks to cleanup) and updates to the database.

For custom controls I would suggest that you start with Michael Washington's articles on LightSwitchHelp or Jan Van der Haegen's copious articles and blogs.