Arman Arman - 1 month ago 16
C# Question

Why does this LINQ query fail to work?

I am running into a problem that I like to understand why it is happening. Some help would be appreciated.

I am using entity framework core and this is the query I have in my code:

var user = await _db.User
.Where(x => x.Email == username & x.Suspended == null)
.Select(x => new
{
x.UserId,
x.Password,
x.Salt,
Role = x.Role.Name
})
.SingleOrDefaultAsync();


enter image description here


This query does not select the user but it doesn't throw any error ether. If I remove "x.Suspended" from Where clause than it will return the user.
The "Suspended" is a table that is related to the user table by the user ID. The user ID is PKs in both tables, so the relation is 1 to 1.
I think this query does not return the user because the x.Suspended is null. Can some one help me understand why this is happening and what I can do to make it work.

UPDATE:
Ok, I used SQL server profiler and this is the converted sql by entity framework:

exec sp_executesql N'SELECT [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [x].[UserId], [x].[Password], [x].[Salt], [x.Role].[Name]
FROM [User] AS [x]
INNER JOIN [Role] AS [x.Role] ON [x].[RoleId] = [x.Role].[RoleId]
INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='some-username'


the problem here is that all one-to-one relations are converted to INNER JOINs. How can I handle this problem? Thanks in advance!

UPDATE 2:
I am starting to think that this is a bug in EF Core. For example when I have this:

var user = await _db.User
.Include(x => x.Role)
.Include(x => x.Suspended)
.Where(x => x.Email == username)
.SingleOrDefaultAsync();


Under profiler the sql output is this:

exec sp_executesql N'SELECT TOP(2) [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
FROM [User] AS [x]
LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]
WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='user-email'


Clearly the "Suspended" table is left join not inner. But when I have this:

var user1 = await _db.User
.Include(x => x.Role)
.Include(x => x.Suspended)
.Where(x => x.Email == username & x.Suspended == null)
.SingleOrDefaultAsync();


The sql under the sql profiler is:

SELECT [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
FROM [User] AS [x]
INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]


what a mess :-D how the hell do we have "Suspended" table joined twice, INNER and LEFT? And where is the WHERE clause?

All I want is this in LINQ to work:



SELECT U.[UserId], U.[Password], U.[Salt], R.[Name] AS [Role]
FROM [ag].[dbo].[User] AS U
INNER JOIN [ag].[dbo].[Role] AS R ON U.[RoleId] = R.[RoleId]
LEFT JOIN [ag].[dbo].[Suspended] AS S ON U.[UserId] = S.[UserId]
WHERE U.[Email] = 'john.smith@gmail.com' AND S.[UserId] IS NULL

Answer

I was right on this, it is a bug and will be fixed in the future EF core releases. Here is the link to the issue on github.

Comments