Closed
Description
openedon May 18, 2021
Hi, I have LINQ query that throws SqlNullValueException exception:
var result = await (
from i in db.Items
from di in db.DashboardItems.Where(x => x.ItemId == i.Id || x.DashboardId == i.Id).DefaultIfEmpty()
select new { Item = i, Filters = i.ItemFilters, DashboardItem = di }
)
.AsNoTracking()
.ToListAsync();Expected result is null in DashboardItem property & empty list or null value in Filters property in case of absence of corresponding records.
You could find example project in attached archive (SqlNullValueExceptionEFDemo.zip).
Error Details
Stack trace:
Unhandled exception. System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
at Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
at Microsoft.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at lambda_method(Closure , QueryContext , DbDataReader )
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.CustomShaperCompilingExpressionVisitor.InitializeCollection[TElement,TCollection](Int32 collectionId, QueryContext queryContext, DbDataReader dbDataReader, ResultCoordinator resultCoordinator, Func`3 parentIdentifier, Func`3 outerIdentifier, IClrCollectionAccessor clrCollectionAccessor)
at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at SqlNullValueExceptionEFDemo.Program.Main(String[] args) in c:\Projects\Instructure-Videri\test\SqlNullValueExceptionEFDemo\SqlNullValueExceptionEFDemo\Program.cs:line 24
at SqlNullValueExceptionEFDemo.Program.<Main>(String[] args)
SQL query generated by Entity Framework:
SELECT [i].[Id], [i].[Config], [i].[CreatedTimeUtc], [i].[Description], [i].[IsObsoleted], [i].[JsonConfig], [i].[Name], [t].[DashboardId], [t].[ItemId], [t].[SortOrder], [t].[DashboardId], [t].[ItemId], [i0].[ItemId], [i0].[FilterId], [i0].[PlacementMode], [i0].[SortOrder]
FROM [Items] AS [i]
OUTER APPLY (
SELECT [d].[DashboardId], [d].[ItemId], [d].[SortOrder]
FROM [DashboardItems] AS [d]
WHERE ([d].[ItemId] = [i].[Id]) OR ([d].[DashboardId] = [i].[Id])
) AS [t]
LEFT JOIN [ItemFilters] AS [i0] ON [i].[Id] = [i0].[ItemId]
ORDER BY [i].[Id], [t].[DashboardId], [t].[ItemId], [i0].[ItemId], [i0].[FilterId]Provider and version information
EF Core version: 3.1.13
Database provider: Microsoft.EntityFrameworkCore.SqlServer (v3.1.13)
Target framework: .NET Core 3.1
Operating system: Windows 10 Pro
IDE: Microsoft Visual Studio Pro 2019 Version 16.9.4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
