Click here to Skip to main content
15,881,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
select * from Table
where CAST(FORMAT(column,'00.00') as varchar(20)) LIKE '%03.1%'


Now, I have a IQueryable and want to send the Query through LINQ with the given format

What I have tried:

obj.Number.ToString("{0:0.00}")
Posted
Updated 7-Apr-21 21:23pm
v2

1 solution

Define the function(s) you want to call:
C#
public static class SqlFunctions
{
    public static string Format(decimal? value, string format) => value?.ToString(format) ?? string.Empty;
    
    public static void Register(ModelBuilder modelBuilder)
    {
        var method = typeof(SqlFunctions).GetMethod(nameof(Format));
        modelBuilder.HasDbFunction(method).HasTranslation(TranslateFormat);
    }
    
    private static SqlExpression TranslateFormat(IReadOnlyCollection<SqlExpression> args)
    {
        return SqlFunctionExpression.Create("FORMAT", args, typeof(string), null);
    }
}
Register the function(s) in your DbContext:
C#
public class YourContext : DbContext
{
    ...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        SqlFunctions.Register(modelBuilder);
        ...
    }
    ...
}
Call the function(s) in your query:
C#
var query = from obj in yourContext.YourTable
            where SqlFunctions.Format(obj.Number, "0.00").Contains("03.1")
            select obj;

NB: You don't need the CAST; FORMAT already returns an nvarchar.

NB 2: If you need the full abilities of the LIKE function, use EF.Functions.Like:
C#
where EF.Functions.Like(SqlFunctions.Format(obj.Number, "0.00"), "%3.[1-5]%")
But for the simple comparison shown in your question, Contains is sufficient.
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900