Filter Record Base on child Record

Posts   
 
    
Posts: 4
Joined: 11-Mar-2022
# Posted on: 11-Mar-2022 14:56:10   

Hello every one i want to filter the data based on child table, here Is my linq query

var query = from mr in _metadata.TableA
            join mrl in _metadata.TableB on mr.Id equals mrl.MarketPlaceRuleId
            select new ABCMod
            {
                Name = mr.Name,
                GroupCode = from mrlv in _metadata.TableD
                            join grp in _metadata.TableE on mrlv.RuleValue equals grp.Id
                            where mrlv.MarketplaceRuleListId == mrl.Id && mrlv.RuleTypeId == (int)RuleTypeListEnum.GroupCode
                            select new GrpModel { GroupCode = grp.GroupCode },
            };

if (!string.IsNullOrEmpty(groupcode))
{
    query = query.Where(x => x.GroupCode.Any(y => y.GroupCode == groupcode));
}

totalrecordcount = query.Count();

I got an object refrence exeption when i pass value on groupcode. What is the best possible solution for this issue. Thanks in advance

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Mar-2022 09:49:03   

https://www.llblgen.com/tinyforum/Thread/7717/1 Version, stacktrace etc. would be nice.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 4
Joined: 11-Mar-2022
# Posted on: 14-Mar-2022 05:30:48   

Hey Otis, i am using v5.1

Here is my stack trace

 at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendResultsetFields(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRelationCollection relationsToWalk, DelimitedStringList projection, Boolean sortClausesSpecified, Boolean allowDuplicates, Boolean allowAliasesInSubQuery, UniqueList`1 distinctViolatingTypes, IRetrievalQuery query, UniqueList`1& fieldNamesInSelectList, Boolean& distinctViolatingTypesFound, Boolean& pkFieldSeen)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates)
   at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates)
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText()
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendWhereClause(IPredicate filter, QueryFragments destination, IQuery query)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.CreateSelectDQ(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
   at Altig.Impact.Repository.Repositories.MarketplaceRuleRepository.GetIncomingLeads(Int32 take, Int32 skip, String sortColumn, String sortDirection, Int32& totalrecordcount, String groupcode, Int32 leadTypId, String countryCode, String languageCode) in C:\VSO\MRRepository.cs:line 68
   at Altig.Impact.Business.Services.MarketplaceRuleService.GetIncomingLeads(Int32 take, Int32 skip, String sortColumn, String sortDirection, Int32& totalrecordcount, String groupcode, Int32 leadTypId, String countryCode, String languageCode) in C:\VSO\MRService.cs:line 32

Here is my model

public partial class MarketplaceRule 
    {
        public string Name { get; set; }
        public IQueryable<Group> GroupCode { get; set; }
    }
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Mar-2022 09:59:04   

The exception might be due to an old bug in the linq provider, 5.1 is 5 years old, not sure. Also, the nested query for GroupCode results in a queryable but that isn't supported (I don't even know how that query should be materialized). This is the literal query you use?

Regardless, the query projects a nested query to a set which you filter on in the where clause. That's not going to work as the nested query in our framework is fetched as a separate fetch. If you want to filter on the nested set, you have to append the where to the nested set

Frans Bouma | Lead developer LLBLGen Pro
Posts: 4
Joined: 11-Mar-2022
# Posted on: 14-Mar-2022 11:06:18   

Hey Otis can you please give me some example for need to implement this type of list

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Mar-2022 11:31:57   

arsalanqaiser wrote:

Hey Otis can you please give me some example for need to implement this type of list

What's the SQL query look like you're after? As that's often a good start

Frans Bouma | Lead developer LLBLGen Pro
Posts: 4
Joined: 11-Mar-2022
# Posted on: 14-Mar-2022 11:45:47   

Otis wrote:

arsalanqaiser wrote:

Hey Otis can you please give me some example for need to implement this type of list

What's the SQL query look like you're after? As that's often a good start

Otis my requirement is I want to select the list based on child records

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Mar-2022 15:57:18   

arsalanqaiser wrote:

Otis wrote:

arsalanqaiser wrote:

Hey Otis can you please give me some example for need to implement this type of list

What's the SQL query look like you're after? As that's often a good start

Otis my requirement is I want to select the list based on child records

Yes, but your query as you've given it doesn't compile so we can't suggest you something if we don't know what you want.

If you want to fetch a set, say, all customers, and per customer their Orders but only the customers which have an order filed at a given date, then say so, so we know what to suggest to you as we can build the query here and will know it works.

Frans Bouma | Lead developer LLBLGen Pro