Configured DbFunctionParameter doesn't impact query parameter mapping

[roji]

When configuring a function's parameter's mapping in the model:

modelBuilder.HasDbFunction(...).HasParameter("startDate").Metadata.TypeMapping
    = typeMappingSource.GetMapping("...");

My expectation was that this would impact the mapping used in the query pipeline (i.e. when a constant/parameter is given), but that does not seem to be the case.

Repro

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

await ctx.Database.ExecuteSqlRawAsync(@"
CREATE FUNCTION Foo(@p bit)
RETURNS int
AS
BEGIN
    RETURN 0;
END");

_ = await ctx.Blogs.Where(b => ctx.Foo("true") == 0).ToListAsync();

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

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    public int Foo(object p) => throw new NotSupportedException();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var typeMappingSource = this.GetService<IRelationalTypeMappingSource>();

        modelBuilder.HasDbFunction(typeof(BlogContext).GetMethod(nameof(Foo))!)
                .HasParameter("p")
                .Metadata.TypeMapping = typeMappingSource.GetMapping(typeof(bool));
    }
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

The above generates the SQL [dbo].[Foo](N'true'), whereas I'd expect it to generate [dbo].[Foo](CAST(1 AS bit)) (or possibly throw).

[AndriySvyryd]

Also try HasStoreType

[gokhanabatay]

Hi @roji we are storing date properties as integer in database, in this case we have valueconverters. When we want to call database function that concats date and time i need to set TypeMapping as I understand?

But how to get RelationalTypeMapping from "DateConverter"s

modelBuilder.HasDbFunction(...).HasParameter("startDate").Metadata.TypeMapping = ?

With NHibernate its easy as MappedAs(SqlNumberDateTimeyyyyMMddHHmmss.Type)

 return (from online in ISession.Query<Transaction>().Where(predicate)
                    where online.TxnDefinition.IsFinancial &&
                          online.TxnResponseCodeDefinition.IsApproved &&
                          online.TxnSettle == TxnSettle.Settled.GetKey() &&
                          online.TxnStt == TxnStat.Normal.GetKey() &&
                          online.RequestDateYYYYMMDD.ToDateTime(online.F12, 6) <= DateTime.Now.AddSeconds(-30).MappedAs(SqlNumberDateTimeyyyyMMddHHmmss.Type)
                    select online.Guid)
                    .AsEnumerable();

 public static DateTime ToDateTime(this DateTime currentDate, int currentTime, int paddingLength = 6) => throw new NotSupportedException();

 public class DateConverter : ValueConverter<DateTime, int>
    {
        protected static readonly string Format = "yyyyMMdd";
        public static readonly DateTime DefaultDate = new(1900, 1, 1, 0, 0, 0, 0);

        public DateConverter() : 
            base(v => int.Parse(v.ToString(Format)),
                 v => DateTime.ParseExact(v.ToString(), Format, CultureInfo.InvariantCulture, DateTimeStyles.None))
        {
        }
    }

CREATE OR REPLACE FUNCTION  to_date_time(currentdate numeric, currenttime numeric, paddinglength integer)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare 
begin
	
	return CAST(CAST(currentdate AS text)||LPAD(CAST(currenttime AS text),paddinglength,'0') as numeric);
end;
$function$
;

[gokhanabatay]

    [Projectable]
    public long BirthDateTime => (long)(object)(BirthDate + BirthTime.ToString().PadLeft(6, '0'));

I use EntityFrameworkCore.Projectables library BirthDate in database stored as YYYYMMDD, BirthTime storead as hhmmss projectable succesfully concats these two properties with unnecessary coalesce operator, at least its working.

CAST((a.birth_date::text || COALESCE(lpad(a.birth_time::text, 6, '0'), '')) AS bigint) >= @__ToLongDateTime_0