locked
inner joins RRS feed

  • Question

  • Hi guys,

    Before I lose my sanity, can someone please help on a beginner question?

    I have the following table setup:

    One application can be hosted on many servers.. and those servers can have multiple roles.

    In the Application screen, I would like to show that it's hosted on a certain server BUT ALSO, on that same page, the roles of that server.

    In SQL:

    select app.KnownAs, srv.ServerName, rls.Description
    from applications app
    inner join app_servers aps on aps.AppId = app.AppId
    inner join [servers] srv on srv.ServerId = aps.ServerId
    inner join Servers_Roles ser on ser.ServerId = srv.ServerId
    inner join Roles rls on rls.RoleId = ser.RoleId

    How to do in Lightswitch?

    Thanks already! :)

    Thursday, April 12, 2012 7:26 PM

Answers

  • I think what you would want to do is create a query over the Roles entity set, add an integer parameter to the query that represents the ID of the Server instance for which you want to get Roles, and then write code for the <QueryName>_PreprocessQuery method to do the actual filtering:

    partial void RolesInServer_PreprocessQuery(int? ServerId, ref IQueryable<Roles> query)
    {
        // Filter to all roles where any Servers_Roles for a particular role is associated with the server that has an ID of ServerId
        query = query.Where(r => r.Servers_Roles.Any(sr => sr.Servers.ServerId == ServerId));
    }
    Use this query as the source of a collection on your screen and bind the ServerId parameter to the selected server's ServerId property. Whenever the user selects a different server, the data grid bound to this collection should show only those roles that are associated with the server.

    Justin Anderson, LightSwitch Development Team

    Friday, April 13, 2012 1:44 AM
    Moderator

All replies

  • The clean coder in me would open VS11 and try it out.

    The dirty coder in me would add a "computed field" to the Server entity, which lists the server's roles, and then show it on the page where you show the Applications.

    Dirty coder going to get some sleep now, I'll come back tomorrow if you're still requiring help on this. :-)


    It's your story - time to switch on the innovation. || About me || LightSwitch blog

    Friday, April 13, 2012 12:54 AM
  • I think what you would want to do is create a query over the Roles entity set, add an integer parameter to the query that represents the ID of the Server instance for which you want to get Roles, and then write code for the <QueryName>_PreprocessQuery method to do the actual filtering:

    partial void RolesInServer_PreprocessQuery(int? ServerId, ref IQueryable<Roles> query)
    {
        // Filter to all roles where any Servers_Roles for a particular role is associated with the server that has an ID of ServerId
        query = query.Where(r => r.Servers_Roles.Any(sr => sr.Servers.ServerId == ServerId));
    }
    Use this query as the source of a collection on your screen and bind the ServerId parameter to the selected server's ServerId property. Whenever the user selects a different server, the data grid bound to this collection should show only those roles that are associated with the server.

    Justin Anderson, LightSwitch Development Team

    Friday, April 13, 2012 1:44 AM
    Moderator
  • I'm running into this kind of situation all the time.

    I've found with read only apps where you just want to show the data you can just create a VIEW in sql for this:

    select app.KnownAs, srv.ServerName, rls.Description
    from applications app
    inner join app_servers aps on aps.AppId = app.AppId
    inner join [servers] srv on srv.ServerId = aps.ServerId
    inner join Servers_Roles ser on ser.ServerId = srv.ServerId
    inner join Roles rls on rls.RoleId = ser.RoleId

    and then use the view as a data source for your screen or filter it with a query.

    It works a treat in the new beta version as it allows you to create an inferred relationship between a table and a view!

    I love this feature!

    Friday, April 13, 2012 10:59 AM
  • sorry for the double post guys. Opera is not what it used to be..

    @Jan,

    I was not aware. I just checked my version and it's v10 (I downloaded from MSDN and assumed it was the last). I don't know what new features you were referring to, in v11, that may apply, but I'll investigate. Bedankt voor de tip :)

    @Justin,

    It makes sense although I was trying to make it simpler. This will have to be maintained, in my department, by people who are a bit technical but not that much :) 

    I'll try later today (with the profiler in the background as I'm intrigued with the method). Many thanks for your reply.

    @Gus,

    I wish I could indeed use it. But some "organizations" have what's called segregation of duties...anything that changes the schema requires a DBA.. And I only intend to call them for the initial setup, no views or sps to maintain. I will try it for another project though, locally. Thanks!

    I'm glad I posted the question here! Thanks All. Now I can proceed.

    Friday, April 13, 2012 5:22 PM
  • This is what I read:

    http://blogs.msdn.com/b/lightswitch/archive/2012/02/29/announcing-lightswitch-in-visual-studio-11-beta.aspx

    "Defining Relationships within External Data Containers

    We also wanted to use this release as an opportunity to address important feedback we heard from you.  One of the most popular requests we received was the ability to allow data relationships to be defined within the same data container (just like you can add relationships across data sources today). It turns out that there are quite a few databases out there that don’t define relationships in their schema – instead, a conceptual relationship is implied via data in the tables.  This was problematic for folks connecting LightSwitch to these databases because while a good number of defaults and app patterns are taken care for you when relationships are detected, LightSwitch was limited to only keying off of relationships pre-defined in the database.  Folks wanted the ability to augment the data model with their own relationships so that LightSwitch can use a richer set of information to generate more app functionality.  Well, problem solved – you can now specify your own user-defined relationships between entities within the same container after importing them into your project. This
    functionality is available for database data sources only."

    I seriously recommend taking a look at the beta version. I spent a day playing with it and was hooked. It's very clever in that it can create relationships for your remote data without changing that schema. The relationships are held in the LS app *somewhere* I believe. The team have done amazing work here and deserve a lot of respect.

    This was a deal sealer for me. I bought the product! 

    Problem is with beta it's a bit premature to be using this for development..

    Friday, April 13, 2012 9:15 PM
  • Hi GusBeare,

    I'm downloading as I type.

    My thoughts before trying it (and specific to my question):

    1) I have relationships defined in schema and LS "imported" it correctly. Have no complaints here...OK, so now I can create (or complement) with LS side relationships. Still don't see how this would help in this case, unless I follow your suggestion to join in a view and query/relate that view from LS (which, I confess, starts looking more attractive by the day. By attractive I mean simple, which was the initial goal of LS anyway). If I'm missing the point, DO let me know! :)

    2) by "somewhere" I hope it's not the dedicated sql express instance or my main instance, for that matter.

    It's risky to model a custom application in the current state. I'm having a lot of doubts at this point.

    I hope Microsoft realizes the amount of money that can be made from organizations that are still strongly relying in user spreadsheets, tiny databases, macro templates, vba, etc, built by the workforce who knows what they need, instead of their clueless superiores. 

    I'm starting to see a lot of "geeky" powerful features which I would love to spend hours on, but have no time. Nothing wrong with it, and very necessary, but first comes the foundation and then the roof. I can see a lack of balance between MustHave and NiceToHave features for what I thought. This would mean I'm wrong  about the targeted users, and that's a shame for me :)

    Well, I'm about to test drive VS11. I hope my next post starts with "WOW..." :)

    Enjoy your weekend.
    Saturday, April 14, 2012 11:06 AM
  • Well...it does have oData...

    I'm going with GusBeare on this one. Because I just want to show the related data, the View is a much simpler solution. I can create a relationship to both tables and reuse the view code whether the user asks info via the Applications or Servers screen.

    After disabling paging and sorting, I'm also satisfied with the queries sent to the database.

    I'm still confident there's another way to do this. For sure my lack of LS knowledge is blurring my vision on multiple joins. If someone has other suggestions not involving code (or corrections to my assumptions!) your help would be much appreciated.

    Very helpful forum. Thanks!

    Saturday, April 14, 2012 3:25 PM
  • Warning:  I have had a lot of trouble with this method in the last couple of days;

    http://social.msdn.microsoft.com/Forums/en-US/LightSwitchDev11Beta/thread/2f4f0ff8-5f92-49fb-9979-c246d797059d

    I would suggest keeping it as simple as possible. Only add views and tables that you will use in the project.

    Avoid adding a view to a table that's already in your project.  You may not run into these problems, I hope you don't because at the start of this testing I had a good head of hair.. now I'm going bald..

    I believe LS should warn you about restrictions or issues with relationships between entities and stop you creating them instead of allowing you to do something and then breaking but I guess this is what beta is for.

    hopefully this will be fixed.

    gus

    Monday, April 16, 2012 2:37 PM
  • GusBeare, thanks for the heads-up.

    So far so good with my view which has the same code as posted before, and even has an additional join.

    I'm using the view with a "many" relation to both the Applications and Servers. I've done some extensive testing BUT with a low set of data. I've had no issue and performance is quite good. In fact I find it a very flexible solution (or workaround, depending on perspective).

    I read your post but there must be some other pattern besides using the view together with tables where it's based. Otherwise I would probably experience the same.

    I'll drop a line if the situation changes..

    Cheerios

    Monday, April 16, 2012 8:35 PM