locked
Is there anyway to search on table values that do not have FKs in PreprocessQuery? RRS feed

  • Question

  • We use External Data Sources.   Is there anyway to search on table values that do not have FKs in PreprocessQuery?

    We have requirements to add in search values to tables that have key values without FKs in the database - therefore there are no relationships between the tables.   There are good reasons why there aren't FKs on these tables.

    A RIA Service is not the answer because it would be a separate data source without all of the requried relationships.  

    We have continuous business opportunities so the database and LS apps are constantly changing - therefore converting every table to a RIA Service would be a nightmare.  Also, we would have to rewrite all of our apps if we were to switch to a RIA Service instead of using External Data Sources. 

    It is my understanding that we can ONLY use Entity objects that have LS Relationships within our LS LINQ technology.  

    The purpose of this post is to see if VS 2012 or P2 now have the functionality to support LINQ statements that allow us to use tables that do not have existing FKs.   Doing this has always been a very simple thing with SQL where statements or join statements.

    The only workaround that I can think of is to create a View.  

    However, a View will (still) not allow a Grid row column to be edited, correct?

    Thanks in advance for your help with this.


    Garth Henderson - Vanguard Business Technology

    Thursday, December 27, 2012 6:22 PM

Answers

  • I think I found what was wrong!

    We need to be dealing with a primitive type in the Any clause, not an entity.

    So a fairly simple change in my previous test code, so we end up with a list of strings, instead of a list of entities, now works as expected:

    var vRc =
        (
            from x in collectionName
            where (your contraints here)
            select x.RentalContractNumber;
        ).ToList()
        .Cast<string>();
    
    query = query.Where(
        (x) => vRc.Any(y) => (y == x.RentalContractNumber))
        ) == true);

    The "== true" isn't really necessary, but it helps visually to see what's going on.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.


    Saturday, January 5, 2013 6:42 AM
    Moderator

All replies

  • Hi Garth,

    You can use the Join statement in LINQ queries, but I don't understand what you're going to be joining on.

    In a PreProcessQuery method, you use manually joined tables to assist with filtering the returned results, but you can't project a new entity as the query's result (but I think you know this, so am I missing something here?).

    The ONLY way to project a new entity is with a RIA Service, but that doesn't require all other tables to be converted to RIA services as well, just the projected entity.

    View are still read-only in V2, as far as I know.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Saturday, December 29, 2012 6:23 AM
    Moderator
  • Thanks, Yann.

    I am trying to join on a table that DOES not have a relationship (no FK in the database, so no subsequent Entity relationship in LS).

    It has been my understanding that LS doesn't support join statements with LINQ.   From a pro ERP developer's standpoint, this is severely limiting. 

    Here's a post by Justin that says:

    • 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.

    LINQ Joins in Lightswitch? Combination of Tables?

    This post is to ask, again, if it is possible for LS to support joins in a preprocess query method.   There have been many improvements with V2 and I am hoping that joins are now supported.

    What I am trying to acheive would be a simple inclusion in a sql select that included a table in the from section and a where clause with appropriate column value matching.

    Please let me know what you think and if you have a work around.


    Garth Henderson - Vanguard Business Technology


    • Edited by Yann DuranModerator Sunday, December 30, 2012 11:12 AM Fixed link text (please use Insert Hyperlink button)
    Saturday, December 29, 2012 5:26 PM
  • Sorry Garth, but I'm just going to be mostly repeating what I said in my previous reply.

    1. You can use joins in a PreProcessQuery method, only if you're using the join to assist with filtering the records being returned.
    2. You can't change the shape of the entity in the PreProcessQuery method, so you can't join tables for the purpose of returning a new (projected) entity. Justin's comment is saying the same thing as I did (just explained a bit better - "if the query is based on a Customer entity, you can only return Customer entities, not a modified version of a Customer"). This hasn't changed in V2, & I don't see that it will in the near future. LightSwitch only deals with persisted entities, such as tables in a database, or views (read-only).
    3. The only way to create an entity that isn't a table/view in the database, is with a RIA Service. Unless you're only displaying data from "related" tables, in which case you'd use a "virtual relationship" between the two tables, like you would if you were connecting to say SharePoint data.

    If you haven't created a RIA Service for any of your applications yet, I suggest that you check it out. It's far easier to create a RIA Service in LightSwitch than it is for use in other technologies. Once you get over the initial resistance that many people have to them, the technique will be a very handy addition to your LightSwitch development.

    Using a RIA Service will allow you to create an entity from two joined tables (even without standard FK's, as you say with matched properties). If you're only displaying the new entity, it's much easier than you think. If you need to add/edit these entities, you need to write 3 extra methods to handle adding, editing & deleting.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    • Proposed as answer by Angie Xu Friday, January 4, 2013 5:19 AM
    • Unproposed as answer by Yann DuranModerator Saturday, January 5, 2013 4:32 AM
    Sunday, December 30, 2012 11:31 AM
    Moderator
  • At this point we have about 1/2 the solution.

    Yann helped me out yesterday to get the LINQ syntax that supports joins between LS entities that do NOT have relationships.  This is very helpful.  Many thanks, Yann!

    Here's the section of code within the PreprocessQuery method using a parm value:

    However, I'm stumped as to how to use the vRc in the query.

    FYI:  The PMRentalContract is the primary Entity for the PreprocessQuery Entity.

    The only solution I've found (which does work just fine) is to build a string with unique contract numbers and using the Contains() syntax.

    With everything that LINQ can do, I'm thinking there has to be a better solution.

    However, this solution is certainly better (in this particular case - because the number of returned rows is less than 200) than using a View or RIA Services.

    Note:  I did try to get the PredicateBuilder to work - no luck.  I created a separate post to see if anyone has gotten the PredicateBuilder to work:

    Has anyone been able to get the PredicateBuilder to work with PreprocessQuery?

    Thoughts?


    Garth Henderson - Vanguard Business Technology



    Saturday, January 5, 2013 12:50 AM
  • Garth,

    As we discussed, with an Any statement (I thought you were going to research how t use it), it should be as simple as:

    query = query.Where(
        x => vRc.Any(
            y => y.RentalContractNumber == x.RentalContractNumber
            )
        ) == true);


    What you might have to do is add a .ToList to the local query (to force population of the in-memory query):

    var vRc =
        (
            from x in collectionName
            where (your contraints here)
            select x;
        ).ToList();

    The for each that you used in your code was populating the query, without it you need .ToList (or similar).

    Let me know if those two things don't get the result you want. The Any statement is basically doing the same things as you did with the string construction, but in a single statement.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Saturday, January 5, 2013 4:50 AM
    Moderator
  • I think I found what was wrong!

    We need to be dealing with a primitive type in the Any clause, not an entity.

    So a fairly simple change in my previous test code, so we end up with a list of strings, instead of a list of entities, now works as expected:

    var vRc =
        (
            from x in collectionName
            where (your contraints here)
            select x.RentalContractNumber;
        ).ToList()
        .Cast<string>();
    
    query = query.Where(
        (x) => vRc.Any(y) => (y == x.RentalContractNumber))
        ) == true);

    The "== true" isn't really necessary, but it helps visually to see what's going on.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.


    Saturday, January 5, 2013 6:42 AM
    Moderator
  • Yann's final C# syntax was:

                   if ( pEntityStateGuid != null )
                   {
                        var vRc = (from PMRentalContract oRc in DataWorkspace.VGDBData.PMRentalContracts
                            join CMEntityStatusData oCesd in DataWorkspace.VGDBData.CMEntityStatusDatas
                            on oRc.RentalContractGuid equals oCesd.EnityStatusParentGuid
                            where oCesd.CMEntityState.EntityStateGuid == pEntityStateGuid
                            select oRc.RentalContractNumber ).ToList().Cast<string>();
     
                            query = query.Where((x) => vRc.Any((y) => y == x.RentalContractNumber) == true);
                   }

    The trick in using vRc.Any() was that it had to represent a primary (or scalar) value.

    This solution is extremely valuable to our LS ERP methodology.

    Many thanks to Yann!


    Garth Henderson - Vanguard Business Technology

    Tuesday, January 8, 2013 5:16 AM
  • You're welcome Garth!

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Tuesday, January 8, 2013 6:05 AM
    Moderator