First of all, the full scenario can be found here: https://dotnetfiddle.net/a2aIkJ
I am using AutoMapper to enable filtering (LINQ expression) on DTO attributes and map this expressions back to the database entity. In the AutoMapper config I have the following scenario:
cfg.CreateMap<Car, CarDto>()
.ForMember(dest => dest.BrandFks, opt => opt.MapFrom(source => source.Wheels.Select(x => x.FkBrand)));
When I use the following code the LINQ to SQL conversion works as expected:
// This code throws NO error
var working = context.Cars
.UseAsDataSource(mapper.ConfigurationProvider).For<CarDto>()
.Where(x => x.BrandFks.Any(y => y == 1))
.ToList();
However, when I check the same property for null:
// This code throws the error
var notWorking = context.Cars
.UseAsDataSource(mapper.ConfigurationProvider).For<CarDto>()
.Where(x => x.BrandFks == null)
.ToList();
the following error occurs:
Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Car>
.Where(c => DbSet<Wheel>
.Where(w => EF.Property<Nullable<int>>(c, "Id") != null && EF.Property<Nullable<int>>(c, "Id") == EF.Property<Nullable<int>>(w, "Id"))
.Select(w => w.FkBrand) == null)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
Does somebody know why this isn't working and how I could solve this issue?
Edit - Clarification: The filter expression is defined by another component that only knows about the DTO and nothing about the database.
You should use the following query:
var correct = context.Cars
.UseAsDataSource(mapper.ConfigurationProvider).For<CarDto>()
.Where(x => !x.BrandFks.Any())
.ToList();
If you log the generated queries, it gets obvious why the query needs to be rewritten as above:
SELECT [c].[Id], [c].[Name], [w].[FkBrand], [w].[Id]
FROM [Cars] AS [c]
LEFT JOIN [Wheels] AS [w] ON [c].[Id] = [w].[Id]
WHERE NOT (EXISTS (
SELECT 1
FROM [Wheels] AS [w0]
WHERE [c].[Id] = [w0].[Id]))
ORDER BY [c].[Id], [w].[Id]
Automapper translates your opt.MapFrom(source => source.Wheels.Select(x => x.FkBrand)) statement into a LEFT JOIN on Wheels and then uses CarDto.BrandFks as a synonym (kind of) for the Wheels table.
Long story short, you can't check whether a table is NULL, but you can check whether it is empty.
As a suboptimal alternative, you could just switch to client evaluation, but this results in a potentially large result set from the database, that it then being filtered down in memory:
var suboptimal = context.Cars
.UseAsDataSource(mapper.ConfigurationProvider).For<CarDto>()
.AsEnumerable() // <-- switch to client evaluation
.Where(x => x.BrandFks == null)
.ToList();
The generated SQL looks like this (it does not contain any filter, since it is filtered later in memory via LINQ to Objects):
SELECT [c].[Id], [c].[Name], [w].[FkBrand], [w].[Id]
FROM [Cars] AS [c]
LEFT JOIN [Wheels] AS [w] ON [c].[Id] = [w].[Id]
ORDER BY [c].[Id], [w].[Id]
See .NET Fiddle for code in action.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments