c# - 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; }

标签： c#asp.net-mvcrazorasp.net-core

Answer 1

您无法过滤急切加载的关系。您得到的错误是由于Include需要传递一个有效的属性表达式，而Where子句不是。

如果您只想加载此特定关系的子集，则需要显式加载它。例如：

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();
}

如果不是很明显，这意味着发出 N+1 个查询，其中 N 是您的 PdModel 的计数。换句话说，必须为每个实例单独获取过滤后的集合。

但是，根据 id 查询，您似乎应该只有一个匹配的 PdModel。因此，您真的不应该在Where这里使用 a 。反而。只需添加所有包含，然后使用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);

然后，您可以只为这个实例获取 PdReferences：

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

重要的是要注意这是存储在另一个变量中的。将过滤后的集合直接设置为您的PdReferences属性可能会产生副作用，特别是如果您稍后最终尝试保存此实体，即从数据库中删除过滤列表中没有的任何内容。在这种情况下，最好使用视图模型并相应地映射数据。