Lightswitch - allow data relationships to be defined within the same "container" - by pjw

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


90
0
Sign in
to vote
ID 588113 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 8/21/2010 4:50:19 PM
Access Restriction Public

Description

Apparently, if two tables are implicitly related but there is no declarative RI actually defined in the database, LS limits me from defining that relationship when I define my data sources. In other words, I have two tables from the same MSSQL database in my project under Data Sources, but I can't set up the relationship between them because I get an error stating, "Cannot add a relationship between 'A' and 'B' because they are in the same container."

There may be many reasons why a FK won't actually exist in the DB - the provider doesn't support it (SQL Lite), the schema is CRAP, I've even heard people say for "performance reasons" (they think their database is faster with no FK's). In any case, it would be very convenient to allow this.
Sign in to post a comment.
Posted by Brian [MSFT] on 3/14/2012 at 1:33 PM
pjw - please try out the Dev11 beta and give us feedback on this feature...
Posted by Dan [MSFT] on 2/23/2012 at 12:33 PM
Hi all,

Thanks again for your feedback. Indeed, we are considering this for a future version.

Dan Leeaphon
Visual Studio LightSwitch
Posted by juanjromero on 1/9/2012 at 6:34 PM
I spent 6 hours struggling with sql express issues, sp1, etc.etc. just to get stuck on this issue? this product is not ready to be released.
Posted by Gus Beare on 12/29/2011 at 11:18 AM
Hello, how far have you got with actively reviewing this issue?
Posted by Dan [MSFT] on 11/9/2011 at 9:03 AM
Hi folks,

Thank you very much for your collective voice on this issue. We are actively considering addressing this for a future release of LightSwitch so all of your feedback is particularly important in proritizing this issue.

Best regards,
Dan Leeaphon
Visual Studio LightSwitch
Posted by Buzzcarter on 10/13/2011 at 5:36 PM
I will wait on my LightSwitch purchase until MS has a fix for the, "Cannot add a relationsheip between tables because they are in the same container". Using a SQL database that are ready has the relation. Isn't LightSwitch suppose to be all about your data? Not many data tools here.
Posted by Ed Nicholson on 8/13/2011 at 12:51 PM
Tried to use Lightswitch with SQL Azure and the combination fails as there is no referential integrity between tables in an Azure schema. I would have thought that with the expected growth in use of SQL Azure, there is a very strong reason for addressing this problem in Lightswitch.

Ed Nicholson
Posted by BertG LocaSoft on 8/12/2011 at 3:13 AM
I had the same issue (same container). Adding a db diagram didn't help, so even before reading this I found the "add datasource twice" workaround. But I wonder whether this has an impact on performance, and even more important: I need dozens of relationships between tables, sometimes a long chain (e.g. projects client - contact - contact or client language, langage of measurement units, etc.). Not only does this mean I had to add dozens of datasources (and I have to carefully think for each which tables to include), but if Microsoft will improve the relationship feature one day (SOON please!) I had to start my app almost from scratch. So I will probably stop using LS (which I already purchased unfortunately because I never thought that there was such a huge issue) and wait for the next release. Additional note: I think LS should not require manual or even any relationship changes in the database itself; everything should be handled by LS.
Posted by a_ludden on 8/4/2011 at 2:00 PM
This is important to me too. I have a database of legacy data that gets imported from another system every month. I can't guarantee the integrity of that data, but need to be able to relate the records that match, which is 99% of them. Thanks! Alex
Posted by fotis12 on 7/28/2011 at 2:39 AM
For example there could be a generic addresses table in the db, related to all the other entities that need an address. For example both persons and real estate properties can have an address. Real estate properties can also have multiple addresses like a person (for example if the property is located on the corner of two roads). You cannot describe this using classic foreign key relations in the database. I should be able to do this in the business layer that lightswitch provides. I should also be able to describe conditional relations like "relate to that table if this property has that value" (in the above example, an address record should be considered as a person's address, only if the address type field states its actually a person's address)
Posted by HoosierDaddyTim on 6/30/2011 at 7:12 AM
I had the same problem, and to solve it I had to go back to the SQL server database where my connection was p;ointing and actually add a db diagram. Then setup the relationships in the DB wih the diagram, refresh the data source in LS beta 2 and KaChing! It worked like a charm. But, I wish that it would have recognized that I had the FK's in the child tables, etc,..
Thanks
Tim
Posted by GaryW99 on 6/28/2011 at 8:15 AM
No fix for this in Beta 2 then?
My plan to use Lightswitch for a project lasted approximately 10 minutes and fell at the first hurdle because of this.
Posted by Ken in Alberta on 6/1/2011 at 1:35 PM
Arghh! Then the work arouond doesn't work because I have a table that refers to itself.
Posted by Ken in Alberta on 6/1/2011 at 1:24 PM
Looking at Silverlight for my organization and when it stubbles out of the gate on something like this it makes me pretty skeptical
Posted by Andreas Schneider,GER on 5/23/2011 at 11:00 PM
for whatever reasons, the database designer has not included the FKs in the database and i'am only the application guy with no rights to change the database. So please add this very imortant feature and let the developer define FK on datasource level in LS. It is a big show stopper! Thanks!
Posted by Pazzer 9000 on 1/2/2011 at 1:29 PM
Yep, absolutely agree with all comments about lack of FKs in existing database. Unless LS is modified to allow relationships between standalone tables in the parent database, it's a complete showstopper. Was getting quite excited at the possibilities, but this throws a bucket of cold water over everything.
Posted by LaTiNo156 on 12/22/2010 at 2:04 AM
I would like to be able to add a relationship to a VIEW from within LightSwitch.

I have a parent table A and a one to many child table B which has a child table of his own C, again one to many.

So I made a view which should show the data from the C in a grid right under the list of table A.
But I can't make the necessary relationship in LS. And offcourse this is impossible to do in SQL Server.

Or is there another easier way to accomplish this?
Posted by Jonathan Manley on 10/20/2010 at 7:09 AM
I agree! I also have a legacy system that does not have some Foreign Keys defined in the Database, but need to add them in this specific application, without touching multiple other production systems.
Posted by bsstahl on 10/7/2010 at 12:38 PM
I have to agree that adding the ability to establish a relationship in the designer that doesn't exist in the DB is a necessary feature. The very 1st app I tried to build with Lightswitch was on an older database without modern RI. I was trying to create a new UI into that DB using the Lightswitch product. I was unable to do so because the needed relationships don't exist. There is no way I am going to touch the DB in production and risk breaking multiple production systems.
Posted by jprhamburg on 9/6/2010 at 1:52 AM
This is a Bug! The Foreign-Key exists in the Database... But the same Error!
Posted by Felyjos on 8/31/2010 at 5:03 PM
Same for me all Tables are in the same Database but VSLS will not allow me to create Relationship. Is it a settings to change this ?
I read that somebody created the relationship in the database and just refresh the datasource but I am trying and it still failing...
Thanks,
DOm
Posted by Paul Tissue on 8/31/2010 at 2:47 PM
Yes please include the ability to establish a relationship even if the underlying relationship in the external database has not been explicity defined in the database itself.

This make creating a useful UX from an existing database very difficult.

Thank you.

Paul Tissue
Senior Software Design Engineer
Wizards of the Coast
Posted by Wocketfast2 on 8/26/2010 at 8:53 AM
I second that, really dont like having to change the database schema as this would require re-testing other systems.
Posted by Tim [MSFT] on 8/26/2010 at 7:44 AM
Thank you for providing this feedback. I will pass on your comments and suggestions to the development team.

Thank you,
Tim Atkins
Microsoft Developer Support - LightSwitch Beta
Posted by Microsoft on 8/21/2010 at 5:03 PM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)