Calling Convert.ToInt64() gives `signed` instead of `bigint`

[sommmen]

Steps to reproduce

Given the following DTO:

public class WpPostmeta : IMetaKeyValuePair
{
    public long MetaId { get; set; }
    public long PostId { get; set; }
    public string? MetaKey { get; set; }
    public string? MetaValue { get; set; }
}

With the following config:

modelBuilder.Entity<WpPostmeta>(entity =>
{
    entity.HasKey(e => e.MetaId)
        .HasName("PRIMARY");

    entity.ToTable("wp_postmeta");

    entity.HasIndex(e => e.MetaKey, "meta_key");

    entity.HasIndex(e => e.PostId, "post_id");

    entity.Property(e => e.MetaId)
        .HasColumnType("bigint(20) unsigned")
        .HasColumnName("meta_id");

    entity.Property(e => e.MetaKey)
        .HasMaxLength(255)
        .HasColumnName("meta_key")
        .HasDefaultValueSql("'NULL'");

    entity.Property(e => e.MetaValue)
        .HasColumnType("longtext")
        .HasColumnName("meta_value")
        .HasDefaultValueSql("'NULL'");

    entity.Property(e => e.PostId)
        .HasColumnType("bigint(20) unsigned")
        .HasColumnName("post_id");
});

Calling Convert.ToInt64(c.MetaValue) results in the following query (from debug view):

SELECT CAST(`w`.`meta_value` AS signed) AS `c`
FROM `wp_postmeta` AS `w`

The issue

This gives the signed datatype which from my belief is an int - I was however expecting a bigint.

The MSSQL provider converts to bigint by the way: Function Mappings of MSSQL Provider

Convert.ToInt64(value) | CONVERT(bigint, @value)

The problem is that i have a query that gets an id from a string column. I then want to cast this string column to bigint so that i can use it in a Union[^1]

Take a look at this example:

var xUnion2 = postMetaRepository
    .AsQueryable()
    .AsNoTracking()
    .Where(c => c.MetaKey == "_replacement_for")
    .Select(c => Convert.ToInt64(c.MetaValue))
    .Union(postMetaRepository
        .AsQueryable()
        .AsNoTracking()
        .Where(c => c.MetaKey == "_replacement_for")
        .Select(c => c.PostId));

var xUnion = qUnion2.ToList();

Gives the following exception: System.InvalidOperationException: 'Unable to translate set operation when matching columns on both sides have different store types.'

However - if i add a conversion from long to long the datatypes will be the same - and it works:

var qUnion2 = postMetaRepository
    .AsQueryable()
    .AsNoTracking()
    .Where(c => c.MetaKey == "_replacement_for")
    .Select(c => Convert.ToInt64(c.MetaValue))
    .Union(postMetaRepository
        .AsQueryable()
        .AsNoTracking()
        .Where(c => c.MetaKey == "_replacement_for")
        .Select(c => Convert.ToInt64(c.PostId)));

Produced SQL:

SELECT CAST(`w`.`meta_value` AS signed) AS `c`
FROM `wp_postmeta` AS `w`
WHERE `w`.`meta_key` = '_replacement_for'
UNION
SELECT CAST(`w0`.`post_id` AS signed) AS `c`
FROM `wp_postmeta` AS `w0`
WHERE `w0`.`meta_key` = '_replacement_for'

See the cast to signed?

Further technical details

MySQL version: 10.3.34-MariaDB-0ubuntu0.20.04.1-log Operating system: Windows Pomelo.EntityFrameworkCore.MySql version: 6.0.1 Microsoft.AspNetCore.App version: Latest 6.0.7

[^1]: As a sidenote, it seems LINQ's Concat is not supported (yet) by this provider - i had expected that to simply produce a Union or have it throw an NotSupportedException - it gives me an exception from the database however - so may wanna place a quick guard in place to notify users can use Union - but not Concat.

[lauxjpn]

This gives the signed datatype which from my belief is an int - I was however expecting a bigint. [...] See the cast to signed?

@sommmen There is no bigint keyword to cast to in MySQL.

From 12.11 Cast Functions and Operators: CAST(expr AS type [ARRAY])

These type values are permitted:

[...]

• SIGNED [INTEGER] Produces a signed BIGINT value.

[...]

So using signed should be correct here.

Did you encounter any concrete issues with the result of the CAST() call in your case?

---

As a sidenote, it seems LINQ's Concat is not supported (yet) by this provider - i had expected that to simply produce a Union or have it throw an NotSupportedException - it gives me an exception from the database however - so may wanna place a quick guard in place to notify users can use Union - but not Concat.

@sommmen Please post the full exception that is being thrown and the generated SQL, so we can take a look at it, because Concat() is generally supported.