2

I've begun investigating Microsoft Lightswitch 2011 as a possible solution for developing quick "admin" apps for updating various databases - primarily those containing lookup tables or configuration data for internal corporate websites or applications.

I have a website that was developed using ASP.NET MVC. EF Code First was used in building out the data layer. Some of the relationships are many-to-many which EF CF handles by creating a join table with just two fields containing the primary keys of the two tables involved in the relationship. The primary key of the join table is combination of the two fields. For example, a document entity can have many categories and a category can consist of many documents. Three tables get created: Documents, Categories, and DocumentCategories. DocumentCategories only has two columns: DocumentID and CategoryID.

When this database is attached to Lightswitch as an external database, and a master-detail screen is created for the Documents table (and showing the related Categories), data can be deleted from and added to the related table (the join table) but not modified.

Investigation revealed that Lightswitch requires a join table in a many-to-many relationship to have its own primary key that is not a concatenation of the keys of the related tables. In other words the table must be of the format: DocumentCategoryID, DocumentID, CategoryID. If the join table is structured that way, it becomes possible to update the entries in the related table.

I know that I can work-around this by not updating records and simply deleting and re-adding them. That's not a big deal since 1) Lightswitch makes that easy, and 2) there's usually not wholesale changes in the related data. It goes against my sensibilities though of "what's right".

So at the risk of providing fodder for all the Microsoft tool haters, 1) is this just a case of Microsoft making a mistake and not being consistent or is there some other force at work here, and 2) is there a way to "fix" this without having to rework my ASP.NET MVC, EF CF app and changing the database structure?

RWGodfrey
  • 847
  • 1
  • 9
  • 19

1 Answers1

2

1) LightSwitch works. Yes there are 'haters' but then there were haters when nail guns came out ( http://jhurst.blogspot.com/2011/01/nail-gun-or-hand-nail-your-roof-which.html ) The end user who is paying for our services will demand that we use tools that can reduce costs by 90%+

2) Using WCF RIA Services allows you to place a layer between whatever you have going on in your data layer and LightSwitch (see: http://lightswitchhelpwebsite.com/Blog/tabid/61/tagid/21/WCF-RIA-Service.aspx ) Only use the 'data source wizard' when it works for you. If it gives you any problems then a WCF RIA Service, that will only take you 5 minutes to code up, will resolve any situation because you are able to drop into procedural code to handle any transform you need.

Michael Washington
  • 2,744
  • 20
  • 29
  • I think the suggestion to use WCF RIA services is probably the way to handle this, but I shouldn't have to do that. My point behind the consistency comment was that either EF CF should produce a join table that has it's own Primary Key element (instead of the composite key it now produces) or Lightswitch should work "out of the box" with the composite key. I understand these are two different products, with different development timelines and different development teams, but this seems like such a basic thing that I would have expected it to work more seamlessly. – RWGodfrey Mar 12 '12 at 13:56
  • To say "I shouldn't have to do that" is a bit like me saying that I should not have to put my econmy car in 3rd gear when my BMW could acheive the same speed in 2nd. What I hope doesn't get lost is that you CAN get LightSwitch to do what you want... just not always the way you want :) – Michael Washington Mar 13 '12 at 04:23
  • I'm not disagreeing with the point that it CAN do what I want - but I want more :) The promise of Lightswitch as a RAD tool could be realized even more if Microsoft applied some consistency across products. In this particular case, I doubt that there is an underlying technical reason why a join table has to have a different architecture between EF CF and Lightswitch. The fact that there is a difference causes me to have to do more work (i.e. time) than I would otherwise have to do. – RWGodfrey Mar 13 '12 at 11:51
  • The LightSwitch team has made statements in the past that it is something they could do but that it was not a high enough priority (when alternatives exist things fall below the priority of things that have no alternative). – Michael Washington Mar 13 '12 at 15:24