none
Query execution failure RRS feed

  • Question

  • Hello,

    if have this code in a Lightswitch application:

            partial void CountriesNotMemberOfGroup_PreprocessQuery(int? CountryGroupID, ref IQueryable<Country> query)
            {
                if (CountryGroupID!=null) {
                    query = query.Where(country => this.UniversalGroupMembers.Where(ugm => ugm.UniversalGroup.UniversalGroupsID == CountryGroupID && ugm.ReferencedObjectUID == country.CountriesUID).FirstOrDefault()==null);
                }
            }

    This a query based on the entity Countries. CountryGroupID is a parameter (being set elsewhere).

    UniversalgroupMembers is a entity that is used for grouping. Objective: get the countries not member of that group!

    If I excute the query an error is being returned.If think it is an issue with the .FirstOrDefault()==null, but the Any() operator is not available here and I do not know how to cast this to get access to the .Any() operator.

    Any support is very much appreciated.

    Regards

    Hansjörg

    Monday, September 2, 2013 3:56 PM

Answers

  • Hello,

    I solved the issues using the following code:

    // get the list of members of the group and store their Guid References into an array

            Guid[] GetGroupMembers(int GroupID)
            {
               return (from UniversalGroupMember ugm in this.DataWorkspace.CSRData.UniversalGroupMembers
                        where ugm.UniversalGroup.UniversalGroupsID == GroupID
                        select ugm.ReferencedObjectUID).ToArray<Guid>();

            }

    // set the query for the list of countries not member of a group

            partial void CountriesNotMemberOfGroup_PreprocessQuery(int? CountryGroupID, ref IQueryable<Country> query)
            {
                
                if (CountryGroupID != null)
                {

    // no comment - if you directly pass the result of GetGroupMembers into the LINQ expression --> does not work

                    Guid[] members = GetGroupMembers((int)CountryGroupID); // THIS VARIABLE MUST BE ALLOCATED HERE!!

    // now use the !Contains operator (aka SQL NOT IN)                 query = query.Where(c => !members.Contains(c.CountriesUID));
                }
                else
                {
                    query = query.Where(c => false);
                }
            }

    This approach is a little less efficient as trying to put it all into one query as there will be two DB accesses (1.) get the list of members, 2.) get the filtered list of object), but I just couln't figure out wyh the above approach that should yield a not exists in Lightswitch as well doesn't work.

    For now i'll not further investigate this (not getting paid for this stuff) but it would be greate if someone who can debug the the whole thing has a look into this. the above approach is SUPPOSED to be working OK.

    Saturday, September 7, 2013 4:45 PM

All replies

  • Hi,

    Would you please share the details of the error returned? That may give better clues on what happened.

    the Any() operator is not available here and I do not know how to cast this to get access to the .Any() operator

    this.UniversalGroupMembers is an Entity Set. The number of LINQ-like operators you can apply to an Entity Set is less, because you have to load an Entity Set from the data source at some point (versus an in-memory IEnumerable, where you can apply the full set of LINQ operators).

    Also, your current code above will execute a query against UniversalGroupMembers entity set for each country - so if you have 100 countries, there will be 100 queries executed.

    I'm wondering if it would be more efficient to do this

    if (CountryGroupID != null)
    {
        var ugms = this.UniversalGroupMembers
            .GetQuery()
            .Include("UniversalGroup")
            .Execute().ToArray();
    
        query = query
            .Where(country =>
                !ugms.Any(ugm =>
                    ugm.UniversalGroup.UniversalGroupsID == CountryGroupID &&
                    ugm.ReferencedObjectUID == country.CountriesUID)
            );
    }

    We first execute a query to loads the Universal Group Members, including their Universal Group in memory, then the filter can be done in memory, and Any operator is availabe.

    Best regards,
    Huy

     
    Friday, September 6, 2013 5:15 PM
  • Hello,

    thanks for your reply

    the error message is none, there is no exception being thrown!

    Just the result is wrong! If i force the execution of my query by adding a query.ToList() and using the data in a loop the the result is empty. Lightswitch will show a red cross and a nonsense error message "Unable to load data. ... "

    Using the same DB but LINQPad this works perfectly well:

    var a = Countries.Where (c => !UniversalGroupMembers.Where(ugm => ugm.ReferencedObjectUID==c.CountriesUID && ugm.UniversalGroupsID==12).Any());
    a.Dump();
    var b= Countries.Where (c => UniversalGroupMembers.Where(ugm => ugm.ReferencedObjectUID==c.CountriesUID && ugm.UniversalGroupsID==12).FirstOrDefault()==null);
    b.Dump();

    The generated SQL is:

    -- Region Parameters
    DECLARE @p0 Int = 12
    -- EndRegion
    SELECT [t0].[CountryID], [t0].[CountryName], [t0].[IncludeInAnafee], [t0].[ISO3166A3_Code], [t0].[CountriesUID], [t0].[ISO31662_Code], [t0].[CountryDescription], [t0].[ListPriority]
    FROM [Countries] AS [t0]
    WHERE NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [UniversalGroupMembers] AS [t1]
        WHERE ([t1].[ReferencedObjectUID] = [t0].[CountriesUID]) AND ([t1].[UniversalGroupsID] = @p0)
        ))
    GO

    -- Region Parameters
    DECLARE @p0 Int = 12
    -- EndRegion
    SELECT [t0].[CountryID], [t0].[CountryName], [t0].[IncludeInAnafee], [t0].[ISO3166A3_Code], [t0].[CountriesUID], [t0].[ISO31662_Code], [t0].[CountryDescription], [t0].[ListPriority]
    FROM [Countries] AS [t0]
    WHERE NOT (EXISTS(
        SELECT TOP (1) NULL AS [EMPTY]
        FROM [UniversalGroupMembers] AS [t1]
        WHERE ([t1].[ReferencedObjectUID] = [t0].[CountriesUID]) AND ([t1].[UniversalGroupsID] = @p0)
        ))

    (the FirstOrDefault introduces the obsolete TOP(1) into the exists clause which is a "bug" in LINQ to entities that SQL server would optimize away)

    I tried your code. Gives me the same behaviour just with the problem that you really have to get all groups members, which reall wouldn't be such a good idea.

    Any other ideas?

    Friday, September 6, 2013 7:49 PM
  • Hi,

    If i force the execution of my query by adding a query.ToList() and using the data in a loop the the result is empty. Lightswitch will show a red cross and a nonsense error message "Unable to load data. ... "

    Inside PreProcessQuery, you cannot execute the query being passed in as a reference. The server data stack will call PreProcessQuery, then apply the result to a different query, out side of PreProcessQuery scope, and then execute the other query.

    the error message is none, there is no exception being thrown! Just the result is wrong!

    So your current custom query is executing fine, but returning the wrong results? Without debugging the code my only suggestion is to also use SQL tracing tool and see if the right queries are being sent to the server. There is a difference between LightSwitch EntitySet and a direct LINQ to SQL mapping as I mentioned above, so the end query may not be correct.

    Best regards,
    Huy

    Friday, September 6, 2013 9:10 PM
  • Hello,

    I solved the issues using the following code:

    // get the list of members of the group and store their Guid References into an array

            Guid[] GetGroupMembers(int GroupID)
            {
               return (from UniversalGroupMember ugm in this.DataWorkspace.CSRData.UniversalGroupMembers
                        where ugm.UniversalGroup.UniversalGroupsID == GroupID
                        select ugm.ReferencedObjectUID).ToArray<Guid>();

            }

    // set the query for the list of countries not member of a group

            partial void CountriesNotMemberOfGroup_PreprocessQuery(int? CountryGroupID, ref IQueryable<Country> query)
            {
                
                if (CountryGroupID != null)
                {

    // no comment - if you directly pass the result of GetGroupMembers into the LINQ expression --> does not work

                    Guid[] members = GetGroupMembers((int)CountryGroupID); // THIS VARIABLE MUST BE ALLOCATED HERE!!

    // now use the !Contains operator (aka SQL NOT IN)                 query = query.Where(c => !members.Contains(c.CountriesUID));
                }
                else
                {
                    query = query.Where(c => false);
                }
            }

    This approach is a little less efficient as trying to put it all into one query as there will be two DB accesses (1.) get the list of members, 2.) get the filtered list of object), but I just couln't figure out wyh the above approach that should yield a not exists in Lightswitch as well doesn't work.

    For now i'll not further investigate this (not getting paid for this stuff) but it would be greate if someone who can debug the the whole thing has a look into this. the above approach is SUPPOSED to be working OK.

    Saturday, September 7, 2013 4:45 PM
  • Sorry. forgot to say: thank you for your continued support and Lightswitch ROCKS!! keep up the good work. I'm curious about the upcoming releases
    Sunday, September 8, 2013 7:02 PM