locked
PreProcessQuery - using Join to filter data ? RRS feed

  • Question

  • I'm new to LS and trying to learn linq.  I can write a stored proc in SQL and get the data I need but strugling to figure it out in my PreProcessQuery.

    Here are the tables I'm working with.

    Loads & LoadCarriers - There's a one to many relationship (one load with many load carriers)

    ClientUser & FacilityAccessList - There's a one to many relationship (one client user with many facilities access list)

    There's an OrgFacilities table with a one to many relationship to both the LoadCarriers and FacilityAccessList

    FacilityAccessList is a Many to Many between ClientUsers and OrgFacilities

    LoadCarriers are "facilities" and if the Current User has the load carrier("facility") in their FacilityAccessList they should be able to see the load data.  The user should only see loads where LoadCarrier "facility" is in their FacilityAccessList

    I created a query under the loads table called 'LoadsByCarrierUser' and trying to filter the loads based on the access list of the current user name.

    I've tried several variations of the following code but still gettin error 'Can not convert type string to bool'.

    query = query.Where(lc => lc.LoadCarriers.Where(of => of.OrgFacility.FacilityAccessList.Where(cual => cual.ClientUser.FacilityAccessList.Any(cu => cu.ClientUser.UserName == Application.Current.User.Name))).Any();

    Any help would be greatly appreciated!

    Thank you,

    Chris


    • Edited by canderson70 Monday, February 13, 2012 6:29 AM
    Monday, February 13, 2012 6:27 AM

Answers

  • Yeah, it seems that every platform seems to have its own implementation of LINQ.   More documentation is definately required for LS LINQ.

    RE: 

    query = query.Where(lc => lc.LoadCarriers.Where(of => of.OrgFacility.FacilityAccessList.Where(cual => cual.ClientUser.FacilityAccessList.Any(cu => cu.ClientUser.UserName == Application.Current.User.Name))).Any();

    I have not been able to use nested Where clauses in LS.  They don't give me errors, they just don't seem to work.

    However, the use of the Any syntax always seems to work:

    if (pPetName != null) { query = query.Where(p => p.PMRentalContracts .Any( q => q.PMPets .Any( r => r.PetName.Contains(pPetName)) ) ) ; } if (pLateChargeGE != null) { query = query.Where(p => p.PMRentalContracts .Any(q => q.IsContractCurrentYorN == true && q.LateChargeCount >= pLateChargeGE)); }

    This should work for you.

    I'd appreciate it if someone could explain more about LS and LINQ.


    Garth Henderson - Vanguard Business Technology

    Tuesday, February 14, 2012 10:24 PM

All replies

  • Hopefully someone can answer this...I have a post with a very similar question!
    Monday, February 13, 2012 6:08 PM
  • The following seems to be the solution to my problem. A little more testing is required but it appears to be working correctly.   

                string cuser = Application.Current.User.Name;

                query = query.Where(lc => lc.LoadCarriers.Where(ofs => ofs.OrgFacility.FacilityAccessList.Where
                    (cumtm => cumtm.ClientUser.FacilityAccessList.Any(cu => cu.ClientUser.UserName == cuser)).Any()).Any());
                  

    Chris


    Hope this is helpful!
    • Edited by canderson70 Tuesday, February 14, 2012 1:33 AM
    Tuesday, February 14, 2012 1:32 AM
  • Yeah, it seems that every platform seems to have its own implementation of LINQ.   More documentation is definately required for LS LINQ.

    RE: 

    query = query.Where(lc => lc.LoadCarriers.Where(of => of.OrgFacility.FacilityAccessList.Where(cual => cual.ClientUser.FacilityAccessList.Any(cu => cu.ClientUser.UserName == Application.Current.User.Name))).Any();

    I have not been able to use nested Where clauses in LS.  They don't give me errors, they just don't seem to work.

    However, the use of the Any syntax always seems to work:

    if (pPetName != null) { query = query.Where(p => p.PMRentalContracts .Any( q => q.PMPets .Any( r => r.PetName.Contains(pPetName)) ) ) ; } if (pLateChargeGE != null) { query = query.Where(p => p.PMRentalContracts .Any(q => q.IsContractCurrentYorN == true && q.LateChargeCount >= pLateChargeGE)); }

    This should work for you.

    I'd appreciate it if someone could explain more about LS and LINQ.


    Garth Henderson - Vanguard Business Technology

    Tuesday, February 14, 2012 10:24 PM
  • I don't believe there's any different "implementation" of LINQ in LS, there are just some objects that don't support *all* LINQ methods, but perhaps that's what you meant?

    The most common problem people have is that they include things in their queries that EF can't pass down to the data provider (usually SQL Server), things like computed properties, for example). I know there are more examples, but I can't think of them off the top of my head at the moment.


    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.

    Wednesday, February 15, 2012 1:38 AM
    Moderator
  • Thank you Garth and Yann for jumping in on this. 

    Garth, There's a full round of testing of this code scheduled for this weekend, I'll let you know how the nested 'Where' works or if we have to shift over to 'Any'.

    Chris

    Thursday, February 16, 2012 8:51 PM