wrong generated query

[vzabavnov]

Using EF 8 with MS SQL provider.
have this query:

FmSpecimenLocations
.Join(FwSampleLocations, z => new
    {
        Barcode = z.CorrectBarcode != null ? z.CorrectBarcode : Convert.ToInt64(z.Barcode),
        SapID = z.ClientID,
    },
    z => new
    {
        z.Barcode,
        SapID = Convert.ToInt32(z.SapID),
    },
    (l, r) => new
    {
        Fm = l,
        Fw = r
    })

it generates this SQL request:

SELECT [f].[id], [f].[barcode], ...(cut for simpliticy)
FROM [FmSpecimenLocations] AS [f]
INNER JOIN [v_FwSampleLocations] AS [v] ON (CASE
    WHEN [f].[correct_barcode] IS NOT NULL THEN [f].[correct_barcode]
    ELSE CONVERT(bigint, [f].[barcode])
END = [v].[Barcode] OR (CASE
    WHEN [f].[correct_barcode] IS NOT NULL THEN [f].[correct_barcode]
    ELSE CONVERT(bigint, [f].[barcode])
END IS NULL AND [v].[Barcode] IS NULL)) AND

there is a AND at the end of query and it creates an error.

when I modify query by re-ording keys like this:

FmSpecimenLocations
.Join(FwSampleLocations, z => new
    {
        SapID = z.ClientID,
        Barcode = z.CorrectBarcode != null ? z.CorrectBarcode : Convert.ToInt64(z.Barcode),
    },
    z => new
    {
        SapID = Convert.ToInt32(z.SapID),
        z.Barcode,
    },
    (l, r) => new
    {
        Fm = l,
        Fw = r
    })

it generates this SQL request:

SELECT [f].[id], [f].[barcode], ...(cut for simpliticy)
FROM [FmSpecimenLocations] AS [f]
INNER JOIN [v_FwSampleLocations] AS [v] ON CASE
    WHEN [f].[correct_barcode] IS NOT NULL THEN [f].[correct_barcode]
    ELSE CONVERT(bigint, [f].[barcode])
END = [v].[Barcode]

and no issue.
another way I found to avoid that additional AND to replace

SapID = Convert.ToInt32(z.SapID),

by

SapID = (int)z.SapID,

like this:

FmSpecimenLocations
.Join(FwSampleLocations, z => new
    {
        Barcode = z.CorrectBarcode != null ? z.CorrectBarcode : Convert.ToInt64(z.Barcode),
        SapID = z.ClientID,
    },
    z => new
    {
        z.Barcode,
        SapID = (int)z.SapID,
    },
    (l, r) => new
    {
        Fm = l,
        Fw = r
    })

it generates workable SQL request:

SELECT [f].[id], [f].[barcode], ...(cut for simpliticy)
FROM [FmSpecimenLocations] AS [f]
INNER JOIN [v_FwSampleLocations] AS [v] ON (CASE
    WHEN [f].[correct_barcode] IS NOT NULL THEN [f].[correct_barcode]
    ELSE CONVERT(bigint, [f].[barcode])
END = [v].[Barcode] OR (CASE
    WHEN [f].[correct_barcode] IS NOT NULL THEN [f].[correct_barcode]
    ELSE CONVERT(bigint, [f].[barcode])
END IS NULL AND [v].[Barcode] IS NULL)) AND [f].[client_id] = [v].[sap_id]

the type of z.SapID, is int?. it may help to find what is wrong

The DB Context model is very big. if will be necessary I may execute any tests to find out what may cause the issue.

EF Core version: 8.0.6. I found this in version 8.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer 8.0.6
Target framework: net8.0
Operating system: Windows 11

[maumar]

I'm able to reproduce this on my end on 8.0.6 as well as latest preview of 9.

Minimal repro:

using var ctx = new MyContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var query = await ctx.FmSpecimenLocations
    .Join(ctx.FwSampleLocations, z => new
    {
        Barcode = z.CorrectBarcode != null ? z.CorrectBarcode : Convert.ToInt64(z.Barcode),
        SapID = z.ClientID,
    },
    z => new
    {
        z.Barcode,
        SapID = Convert.ToInt32(z.SapID),
    },
    (l, r) => new
    {
        Fm = l,
        Fw = r
    }).ToListAsync();


public class FmSpecimenLocation
{
    public int Id { get; set; }

    public long? CorrectBarcode { get; set; }
    public string Barcode { get; set; }
    public int ClientID { get; set; }

}

public class FwSampleLocation
{
    public int Id { get; set; }
    public long? Barcode { get; set; }
    public int? SapID { get; set; }
}


public class MyContext : DbContext
{
    public DbSet<FmSpecimenLocation> FmSpecimenLocations { get; set; }
    public DbSet<FwSampleLocation> FwSampleLocations { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
    }
}

[maumar]

Problem is that Convert.ToInt32(int?) translation is not supported. If we try to produce join where key selectors are not anonymous, we get the following exception:

System.InvalidOperationException : The LINQ expression 'DbSet<Gear>()
    .Join(
        inner: DbSet<Weapon>(), 
        outerKeySelector: g => (object)g.SquadId, 
        innerKeySelector: w => (object)Convert.ToInt32((object)w.SynergyWithId), 
        resultSelector: (g, w) => new TransparentIdentifier<Gear, Weapon>(
            Outer = g, 
            Inner = w
        ))' could not be translated. Additional information: Translation of method 'System.Convert.ToInt32' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

However, anonymous types are processed somewhat differently, and the translation failure is lost on us. We should correctly flow the translation failure information and throw the proper exception.

[maumar]

this is now fixed, most likely as a result of #33891

We now generate the following sql:

SELECT [f].[Id], [f].[Barcode], [f].[ClientID], [f].[CorrectBarcode], [f0].[Id], [f0].[Barcode], [f0].[SapID]
FROM [FmSpecimenLocations] AS [f]
INNER JOIN [FwSampleLocations] AS [f0] ON (CASE
    WHEN [f].[CorrectBarcode] IS NOT NULL THEN [f].[CorrectBarcode]
    ELSE CONVERT(bigint, [f].[Barcode])
END = [f0].[Barcode] OR (CASE
    WHEN [f].[CorrectBarcode] IS NOT NULL THEN [f].[CorrectBarcode]
    ELSE CONVERT(bigint, [f].[Barcode])
END IS NULL AND [f0].[Barcode] IS NULL)) AND [f].[ClientID] = CONVERT(int, [f0].[SapID])