Using Contains() on Included entity

Question

I have the following code:

    public PaginatedList<PdModel> PdModel { get; set; }
    public async Task OnGetAsync(int id, int? pageIndex, string searchString)
    {
        IQueryable<PdModel> PdModelsQuer = _context.PdModel.Where(x => x.Id == id)
                                .Include(x => x.PdTables)
                                .Include(x => x.pdFolderTree)
                                .Include(x => x.PdReferences.Where(y=>y.ReferenceName.Contains(searchString)))
                                .Include(x => x.pdViews)
                                .Include(x => x.pdDomains)
                                .Include(x => x.PdModelSources)
                                .Include(x => x.pdModelExtendeds)
                                .Include(x => x.pdRules);






        PdModel = await PaginatedList<PdModel>.CreateAsync(PdModelsQuer, 1, 10);
    }

On code execution I am getting this error:

InvalidOperationException: The property expression 'x => {from PdReference y in x.PdReferences where [y].ReferenceName.Contains(__searchString_1) select [y]}' is not valid. The expression should represent a property access: 't => t.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

I guess I have to use Contains() on included property in another way. I tried a lot of things, but no reasonable code seems to be working.

Anybody can help me on this?

Thanks a lot in advance

My Domain models:

public class PdModel
    {
        [Key]
        public int Id { get; set; }
        public string ModelCode { get; set; }
        public string ModelName { get; set; }
        public string ModelComment { get; set; }
        public string ModelDescription { get; set; }
        public string ModelAnnotation { get; set; }
        public string ModelDatabase { get; set; }
        public DateTime? ModelCreationDate { get; set; }
        public string ModelCreationUser { get; set; }
        public DateTime? ModelModificationDate { get; set; }
        public string ModelModificationUser { get; set; }
        public string ModelGarantExtendedFlag { get; set; }
        public string ModelColumnExtendedFlag { get; set; }
        public string ModelTableExtendedFlag { get; set; }
        public DateTime PdInsertedDate { get; set; }

        public ICollection<PdRule> pdRules { get; set; }
        public ICollection<PdModelExtended> pdModelExtendeds {get;set;}
        public ICollection<PdTable> PdTables { get; set; }
        public ICollection<PdReference> PdReferences { get; set; }
        public ICollection<PdModelSource> PdModelSources { get; set; }
        public ICollection<PdDomain> pdDomains { get; set; }
        public ICollection<PdView> pdViews { get; set; }
        [ForeignKey("Id")]
        public virtual PdFolderTree pdFolderTree { get; set; }


    }

    public class PdReference
    {
        public int Id { get; set; }
        public int ModelId { get; set; }
        public string ModelCode { get; set; }
        public string ReferenceCode { get; set; }
        public string ReferenceName { get; set; }
        public string ReferenceComment { get; set; }
        public string ReferenceDescription { get; set; }
        public string ReferenceAnnotation { get; set; }
        public string ReferenceStereotype { get; set; }
        public int ParentModelId { get; set; }
        public string ParentModelCode { get; set; }
        public string ParentTableCode { get; set; }
        public int ParentTableId { get; set; }
        public int ChildTableId { get; set; }
        public string ChildTableCode { get; set; }
        public string Cardinality { get; set; }
        public DateTime PdInsertedDate { get; set; }


        [ForeignKey("ModelId")]
        public PdModel PdModels { get; set; }

        public ICollection<PdJoin> pdJoins { get; set; }
        [ForeignKey("ChildTableId")]
        public virtual PdTable pdChildTable { get; set; }

Answer 1

You cannot filter an eagerly loaded relationship. The error you're getting is due to Include needing to be passed a valid property expression, which a Where clause is not.

If you only want to load a subset of this particular relationship, you'll need to explicitly load it. For example:

IQueryable<PdModel> PdModelsQuer = _context.PdModel.Where(x => x.Id == id)
                            .Include(x => x.PdTables)
                            .Include(x => x.pdFolderTree)
                            // remove this .Include(x => x.PdReferences.Where(y=>y.ReferenceName.Contains(searchString)))
                            .Include(x => x.pdViews)
                            .Include(x => x.pdDomains)
                            .Include(x => x.PdModelSources)
                            .Include(x => x.pdModelExtendeds)
                            .Include(x => x.pdRules);

foreach (var pdModel in PdModelsQuer)
{
    var pdReferences = await _context.Entry(pdModel).Collection(x => x.PdReferences).Query()
        .Where(x = x.ReferenceName.Contains(searchString)).ToListAsync();
}

If it's not obvious, this means issuing N+1 queries, where N is the count of your PdModels. In other words, the filtered collection has to be fetched for each instance individually.

However, based on querying by id, it appears that you should only have one matching PdModel. As such, you really shouldn't be using a Where here. Instead. Just add all your includes and then use SingleOrDefaultAsync:

var pdModel = await _context.PdModel
    .Include(x => x.PdTables)
    .Include(x => x.pdFolderTree)
    .Include(x => x.pdViews)
    .Include(x => x.pdDomains)
    .Include(x => x.PdModelSources)
    .Include(x => x.pdModelExtendeds)
    .Include(x => x.pdRules)
    .SingleOrDefaultAsync(x => x.Id == id);

Then, you can fetch the PdReferences for just this one instance:

var pdReferences = await _context.Entry(pdModel).Collection(x => x.PdReferences).Query()
    .Where(x = x.ReferenceName.Contains(searchString)).ToListAsync();

It's important to note that this is being stored in another variable. Setting the filtered collection directly to your PdReferences property can cause side-effects, particularly if you end up trying to save this entity later, namely removing anything not in the filtered list from the database. In a situation like this, it's best to employ a view model and map over the data accordingly.