VB expression tree differences

[lorcQc]

VB generates differences in expression trees which can cause annoying issues when using EFCore.

If you are experiencing any of these issues, you can report it here. I will sort them and then open the tickets on the main repository.

They can also help us in creating functional tests later. dotnet/efcore#14572

[lorcQc]

Dim query = From Post In db.Posts
            Group Post By Key = Post.AuthorId Into Group
            Select Key, Group.Count

System.InvalidOperationException: 'The LINQ expression '$VB$ItAnonymous' could not be translated.

DbSet<Post>()
    .GroupBy(
        keySelector: Post => Post.AuthorId, 
        elementSelector: Post => Post, 
        resultSelector: (Key, $VB$ItAnonymous) => new VB$AnonymousType_0<int, IEnumerable<Post>>(
            Key = Key, 
            Group = $VB$ItAnonymous
        ))
    .Select($VB$It => new VB$AnonymousType_1<int, int>(
        Key = $VB$It.Key, 
        Count = $VB$It.Group
            .Count()
    ))

Reported as dotnet/efcore#30773

[lorcQc]

Dim query2 = From B In db.Blogs
             From P In B.Posts
             Group P By B.Rating Into G = Group
             Select Rating, G.Count

System.InvalidOperationException: 'The LINQ expression 'B => (IEnumerable<Post>)B.Posts' could not be translated.

DbSet<Blog>()
    .SelectMany(
        collectionSelector: B => (IEnumerable<Post>)B.Posts, 
        resultSelector: (B, P) => new VB$AnonymousType_2<Blog, Post>(
            B = B, 
            P = P
        ))
    .GroupBy(
        keySelector: $VB$It => $VB$It.B.Rating, 
        elementSelector: $VB$It => $VB$It.P, 
        resultSelector: (Rating, $VB$ItAnonymous) => new VB$AnonymousType_3<int?, IEnumerable<Post>>(
            Rating = Rating, 
            G = $VB$ItAnonymous
        ))
    .Select($VB$It => new VB$AnonymousType_4<int?, int>(
        Rating = $VB$It.Rating, 
        Count = $VB$It.G
            .Count()
    ))

Reported as dotnet/efcore#29185

[lorcQc]

Note : VB use three-valued logic when comparing nullable value types

Here, Rating is a Nullable<int>

Dim query = From B In db.Blogs
            Where B.Rating <> 4

Generate this query which can be optimized...
With UseRelationalNulls (True)

SELECT [b].[Id], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE COALESCE(CASE
    WHEN [b].[Rating] <> 4 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, CAST(0 AS bit)) = CAST(1 AS bit)

with UseRelationalNulls(False)

SELECT [b].[Id], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE COALESCE(CASE
    WHEN ([b].[Rating] <> 4) OR [b].[Rating] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, CAST(0 AS bit)) = CAST(1 AS bit)

DbSet<Blog>()
    .Where(B => B.Rating != ConvertChecked(4, Nullable`1)
     ?? False)

Reported as dotnet/efcore#27202

[lorcQc]

Note, there is an issue on the main repo for the comparison issue with nullable types :
dotnet/efcore#27202

[bricelam]

/cc @smitpatel @maumar

[lorcQc]

I added some simple functional tests here