none
SIMPLE LINQ/LIGHTSWITCH/SQL QUERY - HELP.

    Question

  • Hello, thanks for helping.

    I've been pulling my hair out for 2 days trying to work LINQ out now and failing. 

    I'll start simple, even though I can create this query with the GUI i'd like to understand it in code so I can edit when needed.

    Lets say I have two tables Calls and Accounts. And account can have many calls.

    I create a query Query1 attached to Calls. I edit query code and use this: 

     

        
    
    partial void Query1_PreprocessQuery(ref IQueryable<MiniCRM> query)
        {
          query = from call__in query
               where call__.AccNo.Contains('A') 
               orderby call__.callDate descending
               select call__;          
        }
    

     

    Now I assume that call__is just a variable that I use to specify my dataset/list/ table Calls.

    It compiles ok, however when I try and run a screen based upon the code I get the following error 

    So some things going wrong.. What?

    As a note the screen works fine when i use the GUI to create the query.

     

     

    Now. Second question. 

     

    I want to perform a Left Outer Join on the two tables.

     

    in SQL I would use 

    SELECT CUCODE, CUNAME, CUPHONE, CUPHONE2, CUCONTACT, CUSORT, CUTURNOVERYTD, CU_NOTES, CU_ON_STOP, CU_EMAIL, AccNo, CallNo, Comments, callDate, Code
    
    FROM SL_ACCOUNTS 
    
    LEFT OUTER JOIN MiniCRM on SL_ACCOUNTS.CUCODE = MiniCRM.AccNo
    

     

    Where SL_ACOUNTS is the Account table and MiniCRM is the Call table.
    I've tried creating a view in SQL and importing that into a dataset in LS but it is not viewable. (Why?)
    I've tried all the walk-through, forums, websites I can find but am unable to find a solution to my problem.
    As you can see if i'm getting stuck on the first question i'm going to struggle with this one. 
    What I want is a screen that displays all my accounts that have a callDate (which is on the Call/MiniCRM table) that is over 3 weeks old usable as a query in LightSwitch.
    Can anyone help me with these two questions? 
            partial void Query1_PreprocessQuery(ref IQueryable<MiniCRM> query)
            {
             
    //SOME CODE HERE
            }

    Thankyou. 

    Thursday, September 01, 2011 11:21 AM

Answers

  • Hi

    Since I have no access to Ares, recreates a small model of the three databases and see that even Lightswitch effectively establish the correct relationship generates the error you reported.

    So as an alternative I can think of creating SLACCOUNT base, a pair of view, then link to the project and create the DetailsGrid finally the result can be seen below.

     

     


    Jaime. If you found this post helpful, please: Vote As Helpful. If it answered your question, remember to: Mark As Answer.
    • Marked as answer by HoltDan Tuesday, September 20, 2011 9:43 AM
    Monday, September 19, 2011 5:16 PM

All replies

  • I can't see what's wrong with your LINQ query, off the top of my head.

    Your SQL view is probably not viewable in LS, due to the fact that there's no primary key, & LS (well Entity Framework really), is unable to infer one from the fields in your view. Do you have any non-null colums included in it?

    I know there's an entity helper library that has thing like DateDiff in it, but I can't for the life of me think of the name, or find it anywhere.


    Yann

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

    Thursday, September 01, 2011 12:35 PM
  • Thanks Yann, I'll look into creating a view with a primary key on it. - Let you know if i get  anywhere.

    However i'm still stuck with the LINQ syntax to create a Join on two tables within the query editor in LS.

    Can anyone help me?

    Thursday, September 01, 2011 1:55 PM
  • Hi

    Maybe if you spread call__in how call__ in

    partial void Query1_PreprocessQuery(ref IQueryable<MiniCRM> query)
      {
       query = from call__ in query
          where call__.AccNo.Contains('A') 
          orderby call__.callDate descending
          select call__;     
      }


    Jaime
    Thursday, September 01, 2011 1:59 PM
  • Thanks for the reply Jamie, I don't understand your comment, Can you elaborate?

     

     

    Do you mean put a space between 'Call_' and 'in query'

     

    "Call_*space*in query"

     

     

    If so, this was already there. The formatter must have missed it out some how. 

     

    Thursday, September 01, 2011 2:27 PM
  • Call__in is diferent of Call__  in.

    I use vb

                query = From call__ In query
                        Where call__.Name.Contains("A")
                        Order By call__.Name
                        Select call__


    Jaime
    • Edited by JaimeH Thursday, September 01, 2011 2:59 PM
    Thursday, September 01, 2011 2:37 PM
  • Agreed, this syntax is what is in place. 

     

    I'm currently still trying to consider how to Join two tables in LINQ or in LS. 

    Can someone help with this problem?

    Thursday, September 01, 2011 2:59 PM
  • Hi Dave,

     

    Yes, but that shows a Union, where I need a Join, I couldn't get the syntax to work for a Join. Can anyone help me with changing the code for the link above into a Join? A left outer join to be specific.

     

     

    I'm still at a brick wall here. I guess with a bit more knowledge this should be easy. Reminds me when I first learnt Java! =)

     

    Still stuck! 

    Thursday, September 01, 2011 3:53 PM
  • Try This over SL_ACCOUNTS  new query with:

       query=query.Where(a => a.MiniCRM.Any());
    


     

     


    Jaime
    Thursday, September 01, 2011 4:04 PM
  • Thanks for the reply Jamie,

     

    Could you possibly take 5 minutes out and explain the syntax to me please? 

    Really want to get this down and understand it fully.

     

    Thanks!

    Thursday, September 01, 2011 4:06 PM
  • Yes
    If you have a relationship One - Many, you can create a query on the table One with Linq or Lambda coded as in this case. Here are saying that the result will always have the same structure (fields) of the collection on which they work, but filtering to select only the records (Any) in the table Many also have related record.
    Now, over that query you can create a EditableGrid Screen to see the results.

    Jaime
    Thursday, September 01, 2011 4:18 PM
  • Hi Jamie, i've been on different projects for a while. Coming back to the LINQ queries:

    I'm still stuck. This is my query. 

    It does not come back with an error while compiling. However at runtime I get a red X and no data. 

     

     

    query = query.Where(a => a.MiniCRMs.Any());

    So for my understanding, the code work off a base query ( for example SELECT * SL_ACCOUNTS) so this base query is

     

    query is the base query where ('a' is more than or equal to 'a' which is 'MiniCRMs.Any'()

     

     

    What does a mean? it has not been specified anywhere. What is MiniCRMs.Any? Does this mean any record in MiniCRMs?

    Why more than or equal to? 

    Sorry to ask but can someone walk me through this step by step? I cannot understand the code, I know it will be simple once explained however cant get over this simple block. 

    Thankyou all.

     

     

    Friday, September 16, 2011 2:34 PM
  • Another point is that I dont see where in the query we are specifying which field the two tables have in common? I.E which table to join on. 

     

    Any help is gratfully received. 

     

    Friday, September 16, 2011 2:51 PM
  • Hi Holt

    As you say we go step by step, but communicating once every 16 days we will not do much.

     

    query = query.Where(a => a.MiniCRMs.Any());

     

    It is the way in a statement that Lambda is filtered query of the current entity records that have at least one record in relation to the other entity, in this case MiniCRMs.

    Obviously, the relationship must have been previously established. With SL_ACCOUNTS – One (or Zero – One), MiniCRMs - Many. And code does not require field indicate the relationship because Lightswitch infers the relationship. For starters check this relationship.


    Jaime. If you found this post helpful, please: VoteAsHelpful. If it answered your question, remember to: MarkAsAnswer.
    Friday, September 16, 2011 3:29 PM
  • Thankyou.

     

    Relationship is as below : 

     

    I'm still unable to get either of the below queries to run (separately of course)

     

        public partial class AccountsDSService
        {
            partial void Query2_PreprocessQuery(ref IQueryable<SL_ACCOUNTS> query)
            {
                query = query.Where(AccountsDS => AccountsDS.MiniCRMs.Any());
                //query = query.Where(a => a.MiniCRMs.Any());
            }
    
           
        }
    

     

    I just get the following > 

     

    Any idea what is causing this error?

    Friday, September 16, 2011 3:57 PM
  • If you want will be happy review that happens in your project. Put a copy in SkyDrive and enter the link to access it.
    Jaime. If you found this post helpful, please: VoteAsHelpful. If it answered your question, remember to: MarkAsAnswer.
    Friday, September 16, 2011 4:01 PM
  • Certainly, I will do this for you on Monday. Thankyou very much Jamie. 

     

    Friday, September 16, 2011 4:16 PM
  • I've just seen this again. Why are you trying to create a join between two tables? When you set up relationships between tables in LS, all the "joining" is done for you in the form of "navigation" properties.

    So fi you create a relationship between your two tables so you end up with:

    Accounts Table

        - ID

        - Name

        - Calls (navigation property created by the relationship)

    Calls Table

        - ID

        - CallDate

        - CallDetails

        - Account (navigation property created by the relationship

    Then you can:

    1. create a query, based on the Calls table (CallsByAccount)
    2. in the query designer

      • add an integer parameter (accountID)

      • add a filter based on AccountID (Account.ID = accountID
    3. Add a screen based on CallsByAccount

    4. Done


    Yann

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

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Friday, September 16, 2011 10:46 PM
  • Thanks for your time Yann, 

    However I do not understand. I have a relationship between my tables as such

     

    Calls : 

    CallNo : Primary Key 

    AccNo: (String)

    Commants

    CallDate

     

    Accounts:

    CUCODE (string)

    CUNAME

    CUADDRESS

    CUPHONE

    CU_PRIMARY - Primary Key

     

    The tables are related on  CUCODE and AccNo.

    One account can have many calls. 

     

    So, creating a query on calls - with a string parameter of Code.

    When I create a search screen on this query naturally all i'm doing is entering a string to search for the paremeter. WHich brings back all my calls with that code. 

    Can you confirm how I bring back data from both tables joined on the Accno/CUCODE?

     

    All the options when specifying a filter are the field names within the Calls table so I assume you are asking me to specify a field in the accounts table?   "add a filter based on AccountID (Account.ID = accountID" I'm missing something. Please advise.

    Thankyou.

     

     



    • Edited by HoltDan Monday, September 19, 2011 11:33 AM
    Monday, September 19, 2011 10:59 AM
  • Essentially, what I want to do is display data from two tables on one screen joined by account number. 

     

    Can this be done? 

    Monday, September 19, 2011 11:27 AM
  • https://skydrive.live.com/redir.aspx?cid=3c9adb47d2a31232&resid=3C9ADB47D2A31232!103

     

    Here you go Jamie. Thanks for your time.

    The query in question is LambdaQuery on the SLACCOUNTS table. Screen is SearchLambdaQuery which just returns a red X.

     

    Thanks for your time. 

    Monday, September 19, 2011 11:45 AM
  • I'm downloading the file, give me some time to look.
    Jaime. If you found this post helpful, please: Vote As Helpful. If it answered your question, remember to: Mark As Answer.
    Monday, September 19, 2011 1:25 PM
  • Yes, if you have a relationship between the two tables.

    In the screen that you want to display the "related" field, when you click "Add", select "Other Screen Data".


    Yann

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

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Monday, September 19, 2011 1:57 PM
  • Yann, I am confused by your instructions. 

    When I try and add 'Other screen Data' I am presented with all the field names of the accounts table. Rather than any option to add any of the call data.

     

    Could you please clarify from the beginning. 

     

    If we have two tables 

    Calls : 

    CallNo : Primary Key 

    AccNo: (String)

    Commants

    CallDate

     

    Accounts:

    CUCODE (string)

    CUNAME

    CUADDRESS

    CUPHONE

    CU_PRIMARY - Primary Key

     

    The tables are related on  CUCODE and AccNo.

     

     

    And I want one search screen, that will display a join of all the call data to all the account data - diaplayed on one table. 

     

    How is this done?

     

    Monday, September 19, 2011 2:27 PM
  • Hi

    Since I have no access to Ares, recreates a small model of the three databases and see that even Lightswitch effectively establish the correct relationship generates the error you reported.

    So as an alternative I can think of creating SLACCOUNT base, a pair of view, then link to the project and create the DetailsGrid finally the result can be seen below.

     

     


    Jaime. If you found this post helpful, please: Vote As Helpful. If it answered your question, remember to: Mark As Answer.
    • Marked as answer by HoltDan Tuesday, September 20, 2011 9:43 AM
    Monday, September 19, 2011 5:16 PM
  •  

     

     


    Jaime. If you found this post helpful, please: Vote As Helpful. If it answered your question, remember to: Mark As Answer.
    Monday, September 19, 2011 5:19 PM
  •  

     Obviously you should organize the contents of the screen according to your needs.


    Jaime. If you found this post helpful, please: Vote As Helpful. If it answered your question, remember to: Mark As Answer.
    Monday, September 19, 2011 5:20 PM
  • Hi Jamie, in your example you have created a sql view right? Is this for presentation purposes only or you can use its data to insert/update a table? For example if I will want to edit CUADDRESS, will table Account be updated? 

    Thanks in Advance,

    Jeff 

    Saturday, May 19, 2012 8:07 AM