Csharp EF Core 8—应用客户端投影后无法转换集操作

有一些问题EF是无法通过联合来解决的，虽然能够在单个查询中获得所有信息似乎很好，但您最好的 Select 将最有可能是在单独的查询中提取项目，在内存中合并结果(因为您无论如何都要加载数据)，或者可能联合基础数据并分别提取图像.当使用投影到这个站点访问DTO/ViewModel时，我强烈推荐的一个细节是避免混合DTO和实体.在这种情况下，在SiteVisit中存储对Picture实体的引用.理想的情况下，将这些投影到POCO DTO类中，以及仅使用您需要的图片字段.即使大多数字段都是需要的，它也可以避免在处理Picture时，它是一个被跟踪的实体，还是来自实体的一些分离的和潜在的虚拟化的表示.

选项1:内存联合:

var serviceSiteVisits = await dbContext.Servicing
    .Where(s => s.CustomerId == customerId)
    .Where(i => i.Pictures.Count > 0)
    .Select(s => new SiteVisit
    {
        Id = s.Id,
        CustomerId = s.CustomerId,
        CustomerName = s.Customer.Name,
        Date = s.ServiceDate,
        Category = SiteVisitCategory.Service,
        ServicingPictures = s.Pictures.Select(p => new PictureDto
        {
            Id = p.Id,
            // ..
        }).ToList()
     }).ToListAsync();

var installSiteVisits = await dbContext.Installs
    .Where(i => i.Order.CustomerId == customerId)
    .Where(i => i.Pictures.Count > 0)
    .Select(i => new SiteVisit
    {
        Id = i.Id,
        CustomerId = i.Order.CustomerId,
        CustomerName = i.Order.CustomerName,
        Date = i.InstallDate,
        Category = SiteVisitCategory.Install,
        InstallPictures = i.Pictures.Selectp => new PictureDto
        {
            Id = p.Id,
            // ..
        }).ToList()
    }).ToListAsync();

var results = serviceSiteVisits.Concat(installSiteVisits)
    .OrderByDescending(v => v.Date)
    .ToList();

在您的SiteVisit ViewModel中，(和实体)有集合引用自初始化，以避免投影/群体中的代码负责初始化.例如，实体永远不应该公开集合的公共setter，因为滥用这些集合会扰乱变更跟踪.例如:

  public ICollection<PictureDto> ServicingPictures { get; protected set; } = new List<PictureDto>();

选项2:联合双投影与图片单独加载.

var initialResults = await dbContext.Servicing
    .Where(s => s.CustomerId == customerId)
    .Where(i => i.Pictures.Count > 0)
    .Select(s => new 
    {
        Id = s.Id,
        CustomerId = s.CustomerId,
        CustomerName = s.Customer.Name,
        Date = s.ServiceDate,
        Category = SiteVisitCategory.Service,
        ServicingPictureIds = s.Pictures.Select(p => p.Id).ToList(),
        InstallPictureIds = new List<int>(),
    })
    .Union(dbContext.Installs
        .Where(i => i.Order.CustomerId == customerId)
        .Where(i => i.Pictures.Count > 0)
        .Select(i => new 
        {
            Id = i.Id,
            CustomerId = i.Order.CustomerId,
            CustomerName = i.Order.CustomerName,
            Date = i.InstallDate,
            Category = SiteVisitCategory.Install,
            InstallPictureIds = i.Pictures.Select(p => p.Id).ToList(),
            ServicingPictureIds = new List<int>()                       
        })).OrderByDescending(v => v.Date)
            .ToListAsync();

var pictureIds = initialResults.SelectMany(x => x.ServicePictureIds)
    .Concat(initialResults.SelectMany(x => x.InstallPictureIds))
    .ToList();
var pictures = await dbContext.Pictures
    .Where(p => pictureIds.Contains(p.Id))
    .Select(p => new PictureDto
    {
        Id = p.Id,
        // ...
    }).ToListAsync();

var results = interimResults
    .Select(x => new SiteVisit
    {
        Id = x.Id,
        CustomerId = x.CustomerId,
        CustomerName = x.CustomerName,
        Date = x.Date,
        Category = x.Category,
        ServicingPictures = x.ServicingPictureIds
            .Select(pId => pictures.FirstOrDefault(p => p.Id == pId))
            .ToList();
        InstallPictures = x.InstallPictureIds
            .Select(pId => pictures.FirstOrDefault(p => p.Id == pId))
            .ToList();
    }).ToList();

老实说，在写完所有这些之后，它仍然可能不工作，所以我建议坚持更简单的选项1.:)双投影可以用来帮助摆脱一些棘手的情况，其中有属性或者EF不能向下转换为SQL.首先将原始值投影为匿名类型，然后执行第二个内存投影，该投影执行无法转换的步骤以获得最终结果.