I'm trying to do a union over 2 different tables but I started getting this error since updating to EF Core 8. The full error message is:
InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.
Here is my code:
var query = dbContext.Servicing
.Include(s => s.Customer)
.Include(s => s.Pictures)
.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,
InstallPictures = new List<Picture>(),
})
.Union(dbContext.Installs
.Include(i => i.Order)
.Include(i => i.Pictures)
.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,
ServicingPictures = new List<Picture>()
}))
.OrderByDescending(v => v.Date)
.AsNoTracking();
var queryResults = await query.ToListAsync();
I've tried moving the 'Where' clauses but I keep getting the error. Can anyone suggest other things I can try?
Thanks to everyone for their input. Apologies for not clarifying the SiteVisit class is a POCO. It seems EF Core isn't able to handle projections that involve more than just simple property assignments. I can only assume earlier versions of EF did the UNION entirely in memory. In the end I used CONCAT (the data will be unique anyway) with separate queries (on the advice from some of the contributors) to retrieve the pictures. It doesn't read as elegantly as the original but it works. Thanks again
There are a few issues that EF is not going to be able to resolve through a union, while it might seem nice to be able to get all of the information out in a single query, your best options will most likely be to extract the items in separate queries, combining the results after in memory (since you are loading the data anyways) or potentially unioning the base data and fetching the images separately. One detail I would strongly recommend when using projection to this site visit DTO/ViewModel is to avoid mixing DTOs and entities. In this case, storing references to Picture entities within the SiteVisit. Ideally project these out to POCO DTO classes as well with just the fields from Picture you need. Even if the majority of fields are needed it saves confusion down the road as to whether when working with a Picture, is it a tracked entity or some detached and potentially deserialized representation that originated from an entity.
Option 1: In-memory union:
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();
In your SiteVisit ViewModel, (and entities) have collection references self-initialize to avoid having code in projections/populations being responsible for initialization. Entities for instance should never expose public setters for collections as misuse of these can mess up change tracking. For example:
public ICollection<PictureDto> ServicingPictures { get; protected set; } = new List<PictureDto>();
Option 2: Union double-projection with Pictures loaded separately.
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();
Honestly after writing all that, it still may not even work so I'd recommend sticking to the simpler option 1. :) Double-projection can be used to help get out of sticky situations where there are properties or such that EF cannot translate down to SQL. Project the raw values first into anonymous types then perform a second, in-memory projection that performs the step(s) that cannot translate to get your final results.