CompareString in VB Project will be evaluated locally

[NoFear23m]

Hy at all. Sorry for my bad english ;-(

Today i was wondering about the logoutput from EF.
I have the following code:

Public Function GetAllViewModelAndEventDefaultValueValues(viewModelName As String, onEvent As String) As List(Of DefaultValueManager)
        Dim q As IQueryable(Of DefaultValueManager) = ContextInternal.Set(Of DefaultValueManager)() 
        q = q.Where(Function(i) i.ViewModel = viewModelName)
        q = q.Where(Function(i) i.OnEvent = onEvent)
        q = q.Where(Function(i) i.IsActive)
        q = q.Select(Function(s) New DefaultValueManager() With {.ClientId = s.ClientId, .WorkspaceId = s.WorkspaceId, .UserId = s.UserId, .DefaultValueManagerId = s.DefaultValueManagerId,
                .DefaultValue = s.DefaultValue, .OnEvent = s.OnEvent, .PropertyName = s.PropertyName, .Title = s.Title, .ViewModel = s.ViewModel}).AsQueryable
        Return q.ToList
    End Function

EF Generates the following log:

Compiling query model:
'from DefaultValueManager i in DbSet
where int CompareString(
Left: [i].ViewModel,
Right: __$VB$Local_viewModelName_0,
TextCompare: False) == 0
where int CompareString(
Left: [i].OnEvent,
Right: __$VB$Local_onEvent_1,
TextCompare: False) == 0
where [i].IsActive
select new DefaultValueManager{
ClientId = [i].ClientId,
WorkspaceId = [i].WorkspaceId,
UserId = [i].UserId,
DefaultValueManagerId = [i].DefaultValueManagerId,
DefaultValue = [i].DefaultValue,
OnEvent = [i].OnEvent,
PropertyName = [i].PropertyName,
Title = [i].Title,
ViewModel = [i].ViewModel
}
'
Optimized query model:
'from DefaultValueManager i in DbSet
where int CompareString(
Left: [i].ViewModel,
Right: __$VB$Local_viewModelName_0,
TextCompare: False) == 0
where int CompareString(
Left: [i].OnEvent,
Right: __$VB$Local_onEvent_1,
TextCompare: False) == 0
where [i].IsActive
select new DefaultValueManager{
ClientId = [i].ClientId,
WorkspaceId = [i].WorkspaceId,
UserId = [i].UserId,
DefaultValueManagerId = [i].DefaultValueManagerId,
DefaultValue = [i].DefaultValue,
OnEvent = [i].OnEvent,
PropertyName = [i].PropertyName,
Title = [i].Title,
ViewModel = [i].ViewModel
}
'
The LINQ expression 'where (CompareString([i].ViewModel, $VB$Local_viewModelName_0, False) == 0)' could not be translated and will be evaluated locally.
The LINQ expression 'where (CompareString([i].OnEvent, $VB$Local_onEvent_1, False) == 0)' could not be translated and will be evaluated locally.
The LINQ expression 'where [i].IsActive' could not be translated and will be evaluated locally.
(QueryContext queryContext) => IEnumerable _InterceptExceptions(
source: IEnumerable _Select(
source: IEnumerable _Where(
source: IEnumerable _Where(
source: IEnumerable _Where(
source: IEnumerable _ShapedQuery(
queryContext: queryContext,
shaperCommandContext: SelectExpression:
SELECT [i].[ViewModel], [i].[OnEvent], [i].[IsActive], [i].[ClientId], [i].[WorkspaceId], [i].[UserId], [i].[DefaultValueManagerId], [i].[DefaultValue], [i].[PropertyName], [i].[Title]
FROM [DefaultValueManagers] AS [i]
WHERE [i].[IsActive] = 1,
shaper: ValueBufferShaper),
predicate: (ValueBuffer i) => int CompareString(
Left: string TryReadValue(i, 0, DefaultValueManager.ViewModel),
Right: string GetParameterValue(
queryContext: queryContext,
parameterName: "$VB$Local_viewModelName_0"),
TextCompare: False) == 0),
predicate: (ValueBuffer i) => int CompareString(
Left: string TryReadValue(i, 1, DefaultValueManager.OnEvent),
Right: string GetParameterValue(
queryContext: queryContext,
parameterName: "$VB$Local_onEvent_1"),
TextCompare: False) == 0),
predicate: (ValueBuffer i) => bool TryReadValue(i, 2, DefaultValueManager.IsActive)),
selector: (ValueBuffer i) => new DefaultValueManager{
ClientId = Nullable TryReadValue(i, 3, DefaultValueManager.ClientId),
WorkspaceId = Nullable TryReadValue(i, 4, DefaultValueManager.WorkspaceId),
UserId = Nullable TryReadValue(i, 5, DefaultValueManager.UserId),
DefaultValueManagerId = int TryReadValue(i, 6, DefaultValueManager.DefaultValueManagerId),
DefaultValue = string TryReadValue(i, 7, DefaultValueManager.DefaultValue),
OnEvent = string TryReadValue(i, 1, DefaultValueManager.OnEvent),
PropertyName = string TryReadValue(i, 8, DefaultValueManager.PropertyName),
Title = string TryReadValue(i, 9, DefaultValueManager.Title),
ViewModel = string TryReadValue(i, 0, DefaultValueManager.ViewModel)
}
),
contextType: SPS.DMS.Repository.BusinessContext.BusinessContext,
logger: DiagnosticsLogger,
queryContext: queryContext)
Opening connection to database 'SPS.DMS.DB' on server '192.168.0.50,1433'.
Opened connection to database 'SPS.DMS.DB' on server '192.168.0.50,1433'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[ViewModel], [i].[OnEvent], [i].[IsActive], [i].[ClientId], [i].[WorkspaceId], [i].[UserId], [i].[DefaultValueManagerId], [i].[DefaultValue], [i].[PropertyName], [i].[Title]
FROM [DefaultValueManagers] AS [i]
WHERE [i].[IsActive] = 1
Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[ViewModel], [i].[OnEvent], [i].[IsActive], [i].[ClientId], [i].[WorkspaceId], [i].[UserId], [i].[DefaultValueManagerId], [i].[DefaultValue], [i].[PropertyName], [i].[Title]
FROM [DefaultValueManagers] AS [i]
WHERE [i].[IsActive] = 1
A data reader was disposed.
Closing connection to database 'SPS.DMS.DB' on server '192.168.0.50,1433'.
Closed connection to database 'SPS.DMS.DB' on server '192.168.0.50,1433'.[2018-01-14 15:09:52,414] [ValuesManagerBl] [1 ] [DEBUG] - The DataManager returns 2 defaultvalues for viewmodelname SearchBusinesspartnerVm and eventname Loaded

It the log i can see the following line for example:
The LINQ expression 'where (CompareString([i].ViewModel, __$VB$Local_viewModelName_0, False) == 0)' could not be translated and will be evaluated locally.

Is that a mistake from my side? If yes, what can i do?

EF Version: 2.0.1
Visual Studio 2017 (15.5.3)
Language: VB.Net (!!) on .Net Standard 2.0
EF Core version: (found in project.csproj or packages.config)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Pro x64 (1709) Build 16299.19

Many Thanks in advance
Sascha

[NoFear23m]

@divega Any news? Is that a mistake from me or an issue?

[smitpatel]

CompareString([i].ViewModel, __$VB$Local_viewModelName_0, False) == 0)

The function is taking 3 parameters. That is probably the cause of client evaluation. I am not sure about functions in VB. But in C#, string.Equals(str1, str2) would get translated to server. But if you use the overload with 3rd parameter of StringComparison then it is client evaluated.

[NoFear23m]

Thanks for your answere.
For example this in VB:
q = q.Where(Function(u) u.Token = "test")
is this in C#
q = q.Where(u => u.Token = "test")

This is a normal string comparisation. In EF 6 it works!
And if i make it with Contains oder StartWith it will work also:
q.Where(Function(u) u.Token.Contains("test")) will evaluated at the server.

And also with String.Equals is EF will evaluate it locally.
This:

Dim q As IQueryable(Of AuthToken) = ContextInternal.AuthTokens.AsQueryable
        If Not includeDeleted Then q = q.Where(Function(u) u.DeletedFlag = False)
        q = q.Where(Function(u) String.Equals(u.Token, token))
        q = q.Where(Function(u) String.Equals(u.PluginGuid, pluginGuid))
        Return q.Any

will generate the following output:

Compiling query model:
'(from AuthToken u in DbSet
where [u].DeletedFlag == False
where bool Equals(
a: [u].Token,
b: __$VB$Local_token_0)
where bool Equals(
a: [u].PluginGuid,
b: __$VB$Local_pluginGuid_1)
select [u]).Any()'
Optimized query model:
'(from AuthToken u in DbSet
where [u].DeletedFlag == False
where bool Equals(
a: [u].Token,
b: __$VB$Local_token_0)
where bool Equals(
a: [u].PluginGuid,
b: __$VB$Local_pluginGuid_1)
select [u]).Any()'
The LINQ expression 'where Equals([u].Token, $VB$Local_token_0)' could not be translated and will be evaluated locally.
The LINQ expression 'where Equals([u].PluginGuid, $VB$Local_pluginGuid_1)' could not be translated and will be evaluated locally.
The LINQ expression 'Any()' could not be translated and will be evaluated locally.
(QueryContext queryContext) => IEnumerable _InterceptExceptions(
source: IEnumerable _ToSequence(bool Any(IEnumerable _Where(
source: IEnumerable _Where(
source: IEnumerable _ShapedQuery(
queryContext: queryContext,
shaperCommandContext: SelectExpression:
SELECT [u].[Token], [u].[PluginGuid]
FROM [AuthTokens] AS [u]
WHERE [u].[DeletedFlag] = 0,
shaper: ValueBufferShaper),
predicate: (ValueBuffer u) => bool Equals(
a: string TryReadValue(u, 0, AuthToken.Token),
b: string GetParameterValue(
queryContext: queryContext,
parameterName: "$VB$Local_token_0"))),
predicate: (ValueBuffer u) => bool Equals(
a: string TryReadValue(u, 1, AuthToken.PluginGuid),
b: string GetParameterValue(
queryContext: queryContext,
parameterName: "$VB$Local_pluginGuid_1"))))),
contextType: CallCenter.DbContext.CallCenterContext,
logger: DiagnosticsLogger,
queryContext: queryContext)
Opening connection to database 'SPS.DMS.CallCenter' on server 'localhost,1433'.
Opened connection to database 'SPS.DMS.CallCenter' on server 'localhost,1433'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [u].[Token], [u].[PluginGuid]
FROM [AuthTokens] AS [u]
WHERE [u].[DeletedFlag] = 0
Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [u].[Token], [u].[PluginGuid]
FROM [AuthTokens] AS [u]
WHERE [u].[DeletedFlag] = 0
A data reader was disposed.

Do you need more informations or tests?

Thanks in advance.
Greetings
Sascha

[smitpatel]

@NoFear23m - Thanks for additional info. The query model generated using string.Equals is different from the First one. First one has 3rd parameter TextCompare. Not sure reason for this.
string.Equals is not translated to server atm. It is easy translation. It seems it just oversight. Filed #10807

[NoFear23m]

@smitpatel @divega
During the last days i have tested my scenario. Now i have the reason.

I have the Context in C# on .Net Standard -> then a DAL in VB on .Net Standard -> then a TestConsole.
This wont work. It is run into "Client Validation".

But if i have the Context in C# on .Net Standard -> then a DAL in VB in .Net 4.6.1 -> then a TestConsole.
All works fine!!

Is that a known scenario???
If it is necessary i can upload my Testproject.
I have attached a PDF that represents my scenario that includes also the generated SQL (screenshot from EF Profiler (HybernatingRhinos).

Please give me feedback. Thanks in advance ;-)
Ef-Tests_Diagram.pdf

[smitpatel]

@NoFear23m - i suspect this is because of method resolution in different tfms. We ran into similar issue in past see #6803

In brief summary, when your DAL is targetting netstandard2.0 then the code in DAL will resolve to method resolution available in netstandard only even though there are more suitable overloads present in runtime (.net 461). I suspect that is the reason when you convert your DAL to target net461 the method resolves to something which EF can translate and it works fine.

If on netstandard string.Equals is being resolved to the method with TextCompare parameter, EF cannot translate it unless EF start understanding that specific overload (which could be difficult being C# vs F#)

[NoFear23m]

Many thanks for the answer. Now that I know where the problem is I can rebuild my project. However, I would note in the "Docs" that it can lead to problems when using .NEt standard. Most people do not use a profiler, watch the output, or turn on the log. This is where a huge performance problem arises.

As I said, if you know it you can prevent it.

Best regards
Sascha

[bricelam]

This was fixed by #10923.

Re-linq transforms expressions containing CompareString() into nodes that reduce to string.Equals(). The third parameter is ignored.