Efcore: EF Core does not generate where parameter when I explicitly search by it in my linq query

[agrebenyuk]

Hi guys,

I encountered an issue where I use EF Core for a pretty simple query but the corresponding generated sql statement does not look right.

So, my linq query:

var searchParameter = "paramValue1";
var userID = "2d8fe378-2a11-40b1-ada2-7ec145fe395e";

var uriQuery =   from u in _dbcontext.Users.AsNoTracking()
                        join c in _dbcontext.UserDetails.AsNoTracking() on u.SomeKey equals c.SomeKey
                        where u.Id.Equals(userID ) && c.SearchParameterField.Equals(searchParameter, StringComparison.InvariantCultureIgnoreCase)
                        select c.Uri;

car uri = uriQuery .FirstOrDefault();

And the SQL version intercepted bu SQLProfiler is:

exec sp_executesql N'SELECT [c].[SearchParameterField], [c].[Uri]
FROM [AspNetUsers] AS [u]
INNER JOIN [UserDetails] AS [c] ON [u].[SomeKey ] = [c].[SomeKey ]
WHERE [u].[Id] = @__userID _0',N'@__userID _0 nvarchar(450)',@__userID_0=N'2d8fe378-2a11-40b1-ada2-7ec145fe395e'

So I have 2 questions here:
1) Why do I have [c].[SearchParameterField] in the sql result set?
2) Why doesn't my sql query have the where clause with the search by searchParameter ?

Regards,
Andrey

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10
IDE: Visual Studio 2017 Enterprise

[smitpatel]

c.SearchParameterField.Equals(searchParameter, StringComparison.InvariantCultureIgnoreCase)
When you pass in StringComparison option, we don't know how to translate it to Server. Hence equality on searchParameter is computed on client rather than sent to server in SQL. Therefore we also need to project that column in SelectExpression.
If you remove StringComparison option and just overload with 1 parameter, it will translate just like userID.

[agrebenyuk]

thanks @smitpatel , the collation of my DB is case insensitive , so I'll get rid of StringComparison in the where clause ,

What about question 1 ? or it will get resolved once I make my changes with StringComparison?

UPDATE, ohhh, I think I know wh you mean (re: client side equality evaluation) , that is basically why second column is returned.

Issue closed.

[agrebenyuk]

just another point - just as a suggestion, it would be more appropriate if in this case (with String Comparison) devs received some sort of warning/error , as it is not obvious and I just came across the fact that my search is incorrect during query performance optimization (which is not something that always happens during development).

[smitpatel]

We already generate a warning when we evaluate parts of query on client. Check your logs.