Efcore: The whole related entity gets loaded when doing a null-check on an optionally projected member
How to reproduce?
Say you have: Book and Author domain classes + BookDto and AuthorDto classes which are a subselection of their respective domain classes.
Somewhere in the app, you have to expose/work with an IQueryable<BookDto> rather than an IQueryable<Book>, So, you write the following code to do the conversion, nothing complicated:
IQueryable<BookDto> queryableDto = dbContext.Books.Select(book => new BookDto
{
Name = book.Name,
...
Author = book.Author == null ? null : new AuthorDto
{
Id = book.Author.Id,
Name = book.Author.Name,
Age = book.Author.Age
...
}
});
And you try to do something like this:
List<BookDto> dtos = queryableDto.Select(bDto => new BookDto
{
Name = bDto.Name,
Author = bDto.Author == null ? null : new AuthorDto() { Name = bDto.Author.Name }
}).ToList();
However, there is a big problem with the null-check in the second query (where it says Author = bDto.Author == null ? null : new...). Because in the bDto.Author == null part what happens is that Entity Framework Core retrieves the whole Author object with all of its columns from the database, but obviously that wasn't what the query was intending to do.
These null-checks get translated to SQL when you're working with an IQueryable of domain classes, of course (like in the first code block), but not when you're doing a custom projection prior to your actual querying, like what we've here.
You also can't get rid of the null-check since you have to find out whether the book has an author or not. (I know that in real life there can't be a book without an author but this is just an example)
Not to mention that I know WHY this problem occurs, it's due to the fact that when EF looks at the expression bDto.Author == null there, at this point it can't translate that to SQL and it simply assumes that you want the whole author, instead. So, I'm not really looking for the why.
So, currently, is there a solution for this? Even if it's a hack or weird trick I'm fine with it! And if there isn't, is there a chance that this will be fixed in future versions?
The SQL that got generated:
For the above piece of code, EF Core generates the following SQL:
SELECT [b].[Name], CASE
WHEN [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Id], [a].[Name], [a].[Age]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
Instead, it should ideally be the following:
SELECT [b].[Name], CASE
WHEN [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
Since I haven't requested any column on the author other than Name.
Other information:
EF Core version: I've tried it with both 3.1.8 (latest stable) + 5.0.0 RC
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.7
Thanks in advance.
AradAral
All 16 comments
I've tried the following code on current bits:
[ConditionalFact]
public virtual void Repro22792()
{
using (var ctx = new MyContext())
{
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
var queryableDto = ctx.Books.Select(book => new BookDto
{
Name = book.Name,
Author = book.Author == null ? null : new AuthorDto
{
Id = book.Author.Id,
Name = book.Author.Name,
}
});
var dtos = queryableDto.Select(bDto => new BookDto
{
Name = bDto.Name,
Author = bDto.Author == null ? null : new AuthorDto() { Name = bDto.Author.Name }
}).ToList();
}
}
public class Book
{
public int Id { get; set; }
public string Name { get; set; }
public string Genre { get; set; }
public Author Author { get; set; }
}
public class Author
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public List<Book> Books { get; set; }
}
public class BookDto
{
public string Name { get; set; }
public AuthorDto Author { get; set; }
}
public class AuthorDto
{
public int Id { get; set; }
public string Name { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Author> Authors { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
}
}
and the sql i'm getting is:
SELECT [b].[Name], CASE
WHEN [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Id], [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
Which is what we want - null check is translated and no extra columns (age/genre) are pulled from the database.
@AradAral is the code above missing some important detail? Could you modify it to match your case or provide a full code sample instead? I'm curious that ef is producing INNER JOIN for you. That would indicate the relationship between author and book is required, so the author is always expected to be there. Since null check is what the issue is all about, this assumption is wrong (either by EF for some reason or in the model configuration)
maumar
on 27 Sep 2020
@maumar With 5.0 RC1 bits I'm getting the same translation as yours, but with additional [a].[Age] in the root SELECT. According to your post, we can safely consider it already fixed I guess?
powermetal63
on 27 Sep 2020
@maumar There is actually something missing in your repro: There is nothing other than Id and Name in your Author DTO so those are basically "ALL the fields", which are getting retrieved, plus the null check is getting translated from your first projection from Book to BookDto, not from the second projection.
Try adding another property, likeAge, for example, to your AuthorDto, and then project it in your initial projection from Author to AuthorDto, but then don't request it in the final projection before the ToList() (just like in my example in my original comment), and you'll see that the generated SQL will be the following:
SELECT [b].[Name], CASE
WHEN [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Id], [a].[Name], [a].[Age]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
which is retrieving all the columns (including Age) unnecessarily.
Edit: Also, I just noticed that even in your own repro, the field Id of the author is getting fetched unnecessarily as you can see, without you requesting it before the ToList(). I think you probably missed that!
PS: I updated the SQL I posted in my original comment, you were right about the inner join, I made a mistake in copying it, sorry. But the problem is still there, as I just explained.
AradAral
on 27 Sep 2020
@powermetal63 Please see my comment above ☝
AradAral
on 27 Sep 2020
Initially we get the following expression:
DbSet<Book>()
.Select(book => new BookDto{
Name = book.Name,
Author = book.Author == null ? null : new AuthorDto{
Id = book.Author.Id,
Name = book.Author.Name,
Age = book.Author.Age
}
})
.Select(bDto => new BookDto{
Name = bDto.Name,
Author = bDto.Author == null ? null : new AuthorDto{ Name = bDto.Author.Name }
})
during nav expansion we combine both selects into:
DbSet<Book>()
.LeftJoin(
inner: DbSet<Author>(),
outerKeySelector: b => EF.Property<Nullable<int>>(b, "AuthorId"),
innerKeySelector: a => EF.Property<Nullable<int>>(a, "Id"),
resultSelector: (o, i) => new TransparentIdentifier<Book, Author>(
Outer = o,
Inner = i
))
.Select(b => new BookDto{
Name = b.Outer.Name,
Author = b.Inner == null ? null : new AuthorDto{
Id = b.Inner.Id,
Name = b.Inner.Name,
Age = b.Inner.Age
} == null ? null : new AuthorDto{ Name = b.Inner == null ? null : new AuthorDto{
Id = b.Inner.Id,
Name = b.Inner.Name,
Age = b.Inner.Age
}
.Name }
})
and further optimize to:
DbSet<Book>()
.LeftJoin(
inner: DbSet<Author>(),
outerKeySelector: b => EF.Property<Nullable<int>>(b, "AuthorId"),
innerKeySelector: a => EF.Property<Nullable<int>>(a, "Id"),
resultSelector: (o, i) => new TransparentIdentifier<Book, Author>(
Outer = o,
Inner = i
))
.Select(b => new BookDto{
Name = b.Outer.Name,
Author = b.Inner == null || new AuthorDto{
Id = b.Inner.Id,
Name = b.Inner.Name,
Age = b.Inner.Age
}
== null ? null : new AuthorDto{ Name = b.Inner.Name }
})
However, we can't do optimized entity equality (i.e. just compare the key to null) on Author anymore, because that information is lost when it gets projected as a dto.
@AradAral as a workaround you can add additional property to BookDto that represents the result of entity equality on the Author (when it's still an entity)
public class BookDto
{
public string Name { get; set; }
public bool AuthorIsNull { get; set; } // <---- added this line
public AuthorDto Author { get; set; }
}
query:
var queryableDto = ctx.Books.Select(book => new BookDto
{
Name = book.Name,
AuthorIsNull = book.Author == null, // <---- added this line
Author = book.Author == null ? null : new AuthorDto
{
Id = book.Author.Id,
Name = book.Author.Name,
Age = book.Author.Age
}
});
var dtos = queryableDto.Select(bDto => new BookDto
{
Name = bDto.Name,
Author = bDto.AuthorIsNull // <---- using new property rather than null check on dto
? null
: new AuthorDto() { Name = bDto.Author.Name }
}).ToList();
should produce:
SELECT [b].[Name], CASE
WHEN [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
@maumar Thank you for your help.
I actually thought of the exact same solution as you, but didn't like the idea of adding an extra ugly property to the DTO just to compensate for this problem, then after beating my head against the wall for quite a few hours, I finally found out that changing bDto.Author == null to bDto.Author.Id > 0 would actually kinda solve the problem. It would work without retrieving the Id of the author. This solution wouldn't require an extra property on the DTO. The generated SQL would be:
SELECT [b].[Name], CASE
WHEN [a].[Id] > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
Pretty much the perfect SQL we wanted, except that IS NULL is instead > 0, which obviously wouldn't really make a difference in terms of performance.
I should perhaps also mention that this is while bDto.Author.Id == 0 or bDto.Author.Id != 0 didn't work. (I couldn't exactly figure out why so I'd appreciate any explanation on this)
But I still think that the EF Core team should really address this issue and "solve" it, it's not an ideal situation.
Update: I also found out that bDto.Author.Id != null would also work, and it's perhaps better than the previous bDto.Author.Id > 0 expression that I suggested since it would generate WHEN [a].[Id] IS NULL instead of WHEN [a].[Id] > 0, which is exactly what we ideally wanted.
Note that the C# compiler would complain about this if you use it as a literal expression in your code. Although EF would understand exactly what you mean and would generate the desired SQL.
AradAral
on 28 Sep 2020
But I still think that the EF Core team should really address this issue and "solve" it, it's not an ideal situation.
For any type which is not in model (like AuthorDto), we don't have a way to compare it to null without creating instance of it on client side. If you have a solution (which works for all scenario and not just this query), we would be happy to implement it.
smitpatel
on 28 Sep 2020
@smitpatel I understand that.
Could you please explain why bDto.Author.Id != 0 wouldn't result in the desired SQL, while bDto.Author.Id > 0 would? This is confusing to me.
The SQL that bDto.Author.Id != 0 would generate:
SELECT [b].[Name], CASE
WHEN ([a].[Id] <> 0) OR [a].[Id] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
The OR [a].[Id] IS NULL part is the problem, where does it come from? That would make the condition true for all books, with or without author.
This is while when you use bDto.Author.Id > 0 it would generate the following SQL:
SELECT [b].[Name], CASE
WHEN [a].[Id] > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
which doesn't have that part that would cause problems. Why is that?
AradAral
on 28 Sep 2020
The OR [a].[Id] IS NULL part is the problem, where does it come from? That would make the condition true for all books, with or without author.
[a].[Id] is nullable in database, even though the type is non-nullable in client. So when you compare it to != 0 you are also asserting that it being null is true result, so it gets added. More accurate check would be bDto.Author.Id != null. Writing that in LINQ will complain that you cannot compare int to null so you can add cast to int?. All that confusion comes from the point that if bDto.Author is null then bDto.Author.Id is a null ref exception. So bDto.Author.Id is essentially bDto.Author != null ? b.Dto.Author.Id : (int?)null
smitpatel
on 29 Sep 2020
@smitpatel FYI, in 5.0 RC1 (with or w/o UseRelationalNulls), trying to use any of these
bDto.Author.Id == null
(int?)bDto.Author.Id == null
(int?)(object)bDto.Author.Id == null
(same for != null) leads to
System.InvalidOperationException
HResult=0x80131509
Message=Rewriting child expression from type 'System.Int32' to type 'System.Nullable`1[System.Int32]' is not allowed, because it would change the meaning of the operation. If this is intentional, override 'VisitUnary' and change it to allow this rewrite.
Source=System.Linq.Expressions
StackTrace:
at System.Linq.Expressions.ExpressionVisitor.ValidateChildType(Type before, Type after, String methodName)
at System.Linq.Expressions.ExpressionVisitor.ValidateUnary(UnaryExpression before, UnaryExpression after)
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
At the same time tryibg to use any int comparison like
bDto.Author.Id == 0
bDto.Author.Id != 0
bDto.Author.Id > 0
bDto.Author.Id <= 0
etc. leads to
System.InvalidOperationException
HResult=0x80131509
Message=The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'.
Source=System.Linq.Expressions
StackTrace:
at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
at System.Linq.Expressions.BinaryExpression.Update(Expression left, LambdaExpression conversion, Expression right)
at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
@powermetal63 Well, I'm using 5.0 RC1 too but I don't have any of the problems you described. I don't exactly know what you're doing, have you written an expression visitor, and if yes, what are you doing in there?
AradAral
on 2 Oct 2020
@AradAral The stack trace is bottom up with the the top being the source of exception. As you can see, all I'm doing is calling ToList() (on your second query with the aforementioned null check replacements). The exception is thrown by EF Core infrastructure.
powermetal63
on 2 Oct 2020
@powermetal63 Could you share the projection code as well?
If your projections are the same as me, then it would make no sense, because as I said I'm using the same version, and I don't get those exceptions.
Also, at least your int comparisons shouldn't cause the exception:
The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'
Since obviously both bDto.Author.Id and 0 are System.Int32, neither of them is System.Nullable[System.Int32]. It's strange.
AradAral
on 2 Oct 2020
@AradAral Looks like at some point of the query processing pipeline EF takes into account the nullability.
In my tests I use the model from @maumar with basically the same queries
```c#
var queryableDto = db.Books.Select(book => new BookDto
{
Id = book.Id,
Name = book.Name,
Genre = book.Genre,
Author = book.Author == null ? null : new AuthorDto
{
Id = book.Author.Id,
Name = book.Author.Name,
Age = book.Author.Age,
},
});
var dtos = queryableDto.Select(bDto => new BookDto
{
Name = bDto.Name,
Author = bDto.Author.Name == null ? null : new AuthorDto { Name = bDto.Author.Name }
}).ToList();
and just replacing `bDto.Author == null` with aforementioned conditions.
Actually there is one trick which works - using some property with "natural null" support (from CLR types it is `string`) which normally is required in the model. For instance, if `Author.Name` is required, using `bDto.Author.Name == null` eliminates the exceptions and produces the closer to desired SQL
```sql
SELECT [b].[Name], CASE
WHEN [a].[Name] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, [a].[Name]
FROM [Books] AS [b]
LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
@powermetal63 There is a problem in your first projection (from Book to BookDto):
Remove the part book.Author == null ? null :, so it would be just Author = new AuthorDto { ..., and then in your second projection change bDto.Author.Name == null back to bDto.Author.Id == null, now try it again, that would solve the problem and you would no longer get that exception.
AradAral
on 2 Oct 2020
Please feel free to continue the discussion, but for tracking purposes we're closing this issue based on the analysis above.
ajcvickers
on 2 Oct 2020
Related issues
ryanwinter
·
3Comments
leak
·
3Comments
miguelhrocha
·
3Comments
HappyNomad
·
3Comments
pachecosoftware
·
3Comments
Most helpful comment
@maumar There is actually something missing in your repro: There is nothing other than
IdandNamein your Author DTO so those are basically "ALL the fields", which are getting retrieved, plus the null check is getting translated from your first projection fromBooktoBookDto, not from the second projection.Try adding another property, like
Age, for example, to yourAuthorDto, and then project it in your initial projection fromAuthortoAuthorDto, but then don't request it in the final projection before theToList()(just like in my example in my original comment), and you'll see that the generated SQL will be the following:which is retrieving all the columns (including
Age) unnecessarily.Edit: Also, I just noticed that even in your own repro, the field
Idof the author is getting fetched unnecessarily as you can see, without you requesting it before theToList(). I think you probably missed that!PS: I updated the SQL I posted in my original comment, you were right about the inner join, I made a mistake in copying it, sorry. But the problem is still there, as I just explained.