locked
LINQ Joins in Lightswitch? Combination of Tables? RRS feed

  • Question

  • Hi all,

    first 3 day I was deeply impressed from this easy-to-go Lightswitch (thanks to the excellent videos of Beth Massi it's very easy to start with LS).

    The last couple of hours I spend trying to find out, how to apply a query with "join" command.

    What I found suggests: It's simply not possible to apply JOIN commands in queries? Can this be true, is this correct?

    ok, there are some kind of workarounds like

    - indirect way of using collections, but I want to do screens with multiple informations over multiple collections/tables, there is not only "the one" collection on the screen

    - the WCF RIA Service option, much to complex as i think (lightswitch loose its advantage of fast and easy software production

     

    But, is there no way to use an ordinary JOIN statement?

    -------

    Well, what I wanted to do is

    Private Sub ProdukteInPageTreeZuProjekt_PreprocessQuery(ProjektID As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Pagetreefunctions))
    
    query = From ptf In query
                        Join pt In PagetreeSet
                        On ptf.PagetreeID.Id Equals pt.Id
                        Where pt.ProjektID.Id = ProjektID
                        Select ptf.Zuordnungsbezeichnung
    
    End Sub
    

     

    Error Message:

    (Translated by myself from german) -> "The definition of JOIN-method can not be accessed within this context."

    I get a similar error message about SELECT-MANY-method, if I use from var1 in tab1, from var2 in tab2 

     

    to explain the code above:

    ptf.Zurodnungsbezeichnung is a string that is computed on database level (computed property in lightswitch table, from linked table products)

    the database modell behind this query: Projekte - 1:n - Pagetree - 1:n - Pagetreefunctions - n:1 - Produkte

    "Pagetree" and "Produkte" are an m:n relation on behalf of Pagetreefunctions

    What i want to achieve with this query is:

    - a list of all products (ptf.Zuordnungsbeziehung) that are listed at least once in a "Pagetree" (on behalf of this m:n relation) while that Pagetree need to belong to a certain project (ProjekteID), for what i want to get the product list

     

    My guess:

    - the result of this query would be a list of strings

    - Lightswitch isn't able to handle this variable data types of querie that are the result of joined queries

    - thus, applying joins to queries is basically switched off in lightswitch

    - thus, there is no way to work around that (except of reorganising the database)?

    Right?

    If so, I need to use the visual basic full version instead of lightswitch?

    Also right?

     

    Thanks for answers => its a fundamental choice of the IDE, that I'm going to use

     




    • Edited by Antha_TM Sunday, December 18, 2011 7:06 PM
    Sunday, December 18, 2011 4:49 PM

Answers

  • Joins are not allowed in queries because they change the shape of the data that is return from the server to the client. Since LightSwitch uses a fixed model for entities, and queries can only be based on entity types, you cannot perform any operation that transforms the shape. Basically, if you have a query over Customers, it must return Customers.

    "a list of all products (ptf.Zuordnungsbeziehung) that are listed at least once in a "Pagetree" (on behalf of this m:n relation) while that Pagetree need to belong to a certain project (ProjekteID), for what i want to get the product list"

    So this really depends on the layout of the screen in which you are using this information. Would the user select a project and a pagetree, and then expect the list of applicable products to show?

    I think a query over of the Produkte table with an integer parameter that represents a Pagetree would do the trick:

     

    Private Sub ProduktesInPagetree_PreprocessQuery(PagetreeId As Nullable(Of Integer), ByRef query As IQueryable(Of Produkte))
        ' Return all Produkte instances where each has at least one Pagetree with ID of PagetreeId
        query = query.Where(Function(p) p.Pagetreefunctions.Any(Function(ptf) ptf.Pagetree.Id = PagetreeId))
    End Sub
    


     


    Justin Anderson, LightSwitch Development Team
    Sunday, December 18, 2011 8:05 PM
    Moderator

All replies

  • Joins are not allowed in queries because they change the shape of the data that is return from the server to the client. Since LightSwitch uses a fixed model for entities, and queries can only be based on entity types, you cannot perform any operation that transforms the shape. Basically, if you have a query over Customers, it must return Customers.

    "a list of all products (ptf.Zuordnungsbeziehung) that are listed at least once in a "Pagetree" (on behalf of this m:n relation) while that Pagetree need to belong to a certain project (ProjekteID), for what i want to get the product list"

    So this really depends on the layout of the screen in which you are using this information. Would the user select a project and a pagetree, and then expect the list of applicable products to show?

    I think a query over of the Produkte table with an integer parameter that represents a Pagetree would do the trick:

     

    Private Sub ProduktesInPagetree_PreprocessQuery(PagetreeId As Nullable(Of Integer), ByRef query As IQueryable(Of Produkte))
        ' Return all Produkte instances where each has at least one Pagetree with ID of PagetreeId
        query = query.Where(Function(p) p.Pagetreefunctions.Any(Function(ptf) ptf.Pagetree.Id = PagetreeId))
    End Sub
    


     


    Justin Anderson, LightSwitch Development Team
    Sunday, December 18, 2011 8:05 PM
    Moderator
  • I had a similar problem. Check out http://blog.geekwithspecks.net/2012/01/lightswitch-join-in-preprocess-query.html

    I basically created a list of allowed id's from the joined table, then in the preprocess linq statement checked if the join id was in the list that i wanted to include. 

    Monday, January 9, 2012 2:21 PM
  • Hi Kevin,

    I replied to your blog post, but I'll repeat the answer here:

    First off, if you have your tables set up "correctly", with all of the required relationships, you shouldn't *need* any manual joins, you would just use the navigation properties.

    Having said that, you *can* have a manual join in a PreProcessQuery method as long as

    a) it's helping to filter the records provided in the "query" parameter

    b) doesn't attempt to alter the shape of the query (as I saw Justin nicely explain, if your query is based on a Customer entity, it must return Customers)


    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.
    Tuesday, January 10, 2012 12:18 AM
    Moderator