Efcore: The LINQ expression 'CompareString' could not be translated VB.NET

[estyfen]

Problem with LINQ Query, fails when compare string. The problem occurs only in project language VB.NET.

Steps to reproduce

Example in VB.NET:

Dim DC As Sup.DAL.SupContext = New DAL.SupContext()
Dim filter As String = $"AR4888"
Dim supplierProdResults = (From pa In DC.supplierproduct Where pa.supplierID = filter Select pa).ToList()

The exception:

System.InvalidOperationException: 'The LINQ expression 'DbSet
.Where(s => Operators.CompareString(
Left: s.supplierID,
Right: __$VB$Local_filter_0,
TextCompare: False) == 0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

The same query in C# project works perfectly:

Sup.DAL.SupContext DC = new DAL.SupContext();
string filter = "AR4888";
var supplierProdResults = (from pa in DC.supplierproduct where pa.supplierID == filter select pa).ToList();

I attach a simplified project, Download that includes projects and an SQL file to create the database and 1 table with 3 rows.

Please change connectionstring for UseSqlServer("...") in OnConfiguring Context

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Framework 4.6.1
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.4)

[ajcvickers]

See #10707. Without Re-linq we will have to translate these explicitly.

[estyfen]

See #10707. Without Re-linq we will have to translate these explicitly.

Hi @ajcvickers thank you very much for your help.

Could you give me an example of how to introduce this translate CompareString in EFCore?. Please!!
I have this reference, but in EF Core 3.1 I don't find this class: Here

[ajcvickers]

@maumar @roji Is there an easy way to add this translation from outside?

[roji]

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

It should be possible to add a visitor at the end of preprocessing that identifies CompareString with (a) third parameter equal to False, and (b) compared to 0 - this seems like what the VB compiler emits for the VB equality operator (docs). This visitor would simply convert that to an equality node. I can knock it up tomorrow.

[estyfen]

Hi @roji , don't forget us please :)

[ajcvickers]

Note from team discussion: when doing this in the product consider using name matching rather than relying on the exact type, since Rosyln emits some VB types into the VB assembly.

@bricelam I haven't been able to find any info on having the compiler spit out C#-like IL. Any ideas where this is documented?

[bricelam]

I was thinking of <OptionCompare>, but it just changes the argument passed--there's no way to remove the call entirely. <RemoveIntegerChecks> will remove the overflow checking...

[estyfen]

@bricelam @ajcvickers, we were looking at the RelationalQueryableMethodTranslatingExpressionVisitor class, to see the possibility of adjusting the Lambda expression:

s => Operators.CompareString (
Left: s.supplierID,
Right: __ $ VB $ Local_filter_0,TextCompare: False) == 0)

for the correct one:

t => t.supplierID == _ filter_0,

and apply a:

DbContext.ReplaceService<IRelationalQueryableMethodTranslatingExpressionVisitor, MyCustomQueryTranslatingExpressionVisitor>()

but this class does not implement a contract.

Is there any way? Can you suggest something please!

[roji]

I've written a visitor that takes care of normalizing VB.NET string comparison, see #19681.

It's possible to make this work right now, by copying that visitor and inserting it at the beginning of preprocessing in the query pipeline, something like the following:

```c#
public class VisualBasicRelationalQueryTranslationPreprocessorFactory : IQueryTranslationPreprocessorFactory
{
private readonly QueryTranslationPreprocessorDependencies _dependencies;
private readonly RelationalQueryTranslationPreprocessorDependencies _relationalDependencies;

public VisualBasicRelationalQueryTranslationPreprocessorFactory(
    [NotNull] QueryTranslationPreprocessorDependencies dependencies,
    [NotNull] RelationalQueryTranslationPreprocessorDependencies relationalDependencies)
{
    _dependencies = dependencies;
    _relationalDependencies = relationalDependencies;
}

public virtual QueryTranslationPreprocessor Create(QueryCompilationContext queryCompilationContext)
    => new VisualBasicRelationalQueryTranslationPreprocessor(_dependencies, _relationalDependencies, queryCompilationContext);

}

public class VisualBasicRelationalQueryTranslationPreprocessor : RelationalQueryTranslationPreprocessor
{
public VisualBasicRelationalQueryTranslationPreprocessor(
[NotNull] QueryTranslationPreprocessorDependencies dependencies,
[NotNull] RelationalQueryTranslationPreprocessorDependencies relationalDependencies,
[NotNull] QueryCompilationContext queryCompilationContext)
: base(dependencies, relationalDependencies, queryCompilationContext)
{
}

public override Expression Process([NotNull] Expression query)
{
    query = new LanguageNormalizingExpressionVisitor().Visit(query);
    return base.Process(query);
}

}

public class BlogContext : DbContext
{
public DbSet Blogs { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(@"...");
    optionsBuilder.ReplaceService<IQueryTranslationPreprocessorFactory, VisualBasicRelationalQueryTranslationPreprocessorFactory>();
}

}
```

Of course, that's all C# :) Can @bricelam help us translate all that to VB.NET?

[estyfen]

I've written a visitor that takes care of normalizing VB.NET string comparison, see #19681.

It's possible to make this work right now, by copying that visitor and inserting it at the beginning of preprocessing in the query pipeline, something like the following:

public class VisualBasicRelationalQueryTranslationPreprocessorFactory : IQueryTranslationPreprocessorFactory
{
    private readonly QueryTranslationPreprocessorDependencies _dependencies;
    private readonly RelationalQueryTranslationPreprocessorDependencies _relationalDependencies;

    public VisualBasicRelationalQueryTranslationPreprocessorFactory(
        [NotNull] QueryTranslationPreprocessorDependencies dependencies,
        [NotNull] RelationalQueryTranslationPreprocessorDependencies relationalDependencies)
    {
        _dependencies = dependencies;
        _relationalDependencies = relationalDependencies;
    }

    public virtual QueryTranslationPreprocessor Create(QueryCompilationContext queryCompilationContext)
        => new VisualBasicRelationalQueryTranslationPreprocessor(_dependencies, _relationalDependencies, queryCompilationContext);
}

public class VisualBasicRelationalQueryTranslationPreprocessor : RelationalQueryTranslationPreprocessor
{
    public VisualBasicRelationalQueryTranslationPreprocessor(
        [NotNull] QueryTranslationPreprocessorDependencies dependencies,
        [NotNull] RelationalQueryTranslationPreprocessorDependencies relationalDependencies,
        [NotNull] QueryCompilationContext queryCompilationContext)
        : base(dependencies, relationalDependencies, queryCompilationContext)
    {
    }

    public override Expression Process([NotNull] Expression query)
    {
        query = new LanguageNormalizingExpressionVisitor().Visit(query);
        return base.Process(query);
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"...");
        optionsBuilder.ReplaceService<IQueryTranslationPreprocessorFactory, VisualBasicRelationalQueryTranslationPreprocessorFactory>();
    }
}

Of course, that's all C# :) Can @bricelam help us translate all that to VB.NET?

Hi @roji @ajcvickers @bricelam
Thanks a lot for your help. One more improvement for EF Core :).

However, a similar problem also occurs when an expression such as the following is made:

In VB.NET
Dim q = (from t in DC.Customer WHERE t.telephone Is Nothing)
The translation to SQL fails

In C#
var q = (from t in DC.Customer WHERE t.telephone == null)
The translation to SQL:
SELECT * FROM customer where telephone IS NULL
Passed

We could consider this as a new issue.

Regards!! You are a great team

[roji]

@estyfen yeah, can you please open a new issue for that?

[estyfen]

@estyfen yeah, can you please open a new issue for that?

Of course!!!

[roji]

Reopening as the PR hasn't been merged yet.

[estyfen]

Hi, @roji , I have created a new issue for the other error: #19688

[estyfen]

Reopening as the PR hasn't been merged yet.

Do you have an estimated date to release these improvements?

[roji]

@estyfen all new development such as this is going into 5.0, except for critical bugs with no workarounds (which this probably won't qualify for). However, we will be regular previews for 5.0 starting soon that you can test and work on, and as a workaround you can also add the visitor yourself as described above.