none
Left Outer Join Query RRS feed

  • Question

  • I want to convert this sql statement to a lightswitch query. No sure how that's accomplished.

     

    	SELECT    Members.MemberID, Members.FirstName, Members.LastName, Members.MemberType, Members.Address, Members.Address1, Members.City, Members.State, 
                 Members.Zip, Members.Email, Members.Phone, [Order].OrderID
    FROM      Members LEFT OUTER JOIN
                 [Order] ON Members.MemberID = [Order].MemberID
    WHERE    ([Order].OrderID IS NULL)
    


    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    Tuesday, August 2, 2011 5:18 AM

Answers

  • Are you using VB? Or C#?

    The C# syntax would be:

    query = query.Where((a) => a.Orders.Any() == false);
    

     


    Yann

    (plus ça change, plus c'est la même chose!)

    • Marked as answer by Rollo1002 Wednesday, August 3, 2011 3:09 PM
    Wednesday, August 3, 2011 6:54 AM
    Moderator

All replies

  • Hi

    You can create a query over Members table and in method preprocessquery write the following code:

     

    query = query.Where(Function(a) a.Orders.Count() = 0)
    

     

    then you can create a Editable Grid Screen on the query. In Lightswitch can not change the selection of fields in the query, but if the design of the screen.

    Jaime


    Tuesday, August 2, 2011 9:31 PM
  • I am getting errors on Function (a) and a.Orders. I guess I don't know how to do the link statement properly. I've tried a few ways with no luck.
    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    Wednesday, August 3, 2011 6:20 AM
  • Or even better:

    query = query.Where(Function(a) a.Orders.Any() = false)

    I *think* this is more efficient. Happy to be proved wrong if anyone thinks it's not.


    Yann

    (plus ça change, plus c'est la même chose!)



    Wednesday, August 3, 2011 6:29 AM
    Moderator
  • I keep getting a Function A does not exist in the IDE of Visual Studio and then "Only assignment, call, increment, decrement, and new object expressions can be used as a statement" for the false in the above statement.

     


    ---------------------------------------------- Help the community and mark posts as Answered if it helps you out ----------------------------------------------
    Wednesday, August 3, 2011 6:37 AM
  • Are you using VB? Or C#?

    The C# syntax would be:

    query = query.Where((a) => a.Orders.Any() == false);
    

     


    Yann

    (plus ça change, plus c'est la même chose!)

    • Marked as answer by Rollo1002 Wednesday, August 3, 2011 3:09 PM
    Wednesday, August 3, 2011 6:54 AM
    Moderator
  • Hi

    You can see this code running in the query "CustomersWithoutBook" and the screen "EditableCustomersWithoutBookGrid" following example:

    A way to: handle a simple customer registration system books by category

    Jaime

    Wednesday, August 3, 2011 1:36 PM
  • Can i know what is meant by Query?? in the answer ???

    query = query.Where((a) => a.Orders.Any() == false);

    and if i have three tables to left outer join then how i can do it???


    rohit ab

    Friday, August 10, 2012 12:45 PM
  • When you write code for a query's PreProcessQuery method, there's a parameter called "query".

    You can't do outer joins in just plain LightSwitch. You'd have to create a RIA service that performs the joins. The service presents the result to LS as a new "entity".


    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.

    Sunday, August 12, 2012 2:40 PM
    Moderator
  • i get this error 

    query = query.Where((a) => a.Release.Any() == false);

    lightswitchApplication does not contain any definition for Any and no extension method.why its showing this error pls help me

    Friday, July 26, 2013 3:43 PM