Like query for an int column

[Trobeloo]

I have an SQL Server table with an int identity column and I wish to use Query to find all invoice (Facture) starting with a particular number sequence "1406" for example.
In TSQL I use:
SELECT TOP (1000) [Id_Facture]
FROM [Factures]
where Id_Facture like '1406%'

If I use Query with the following QueryField I get an error "Inputs string was not in a correct format"
var field = new QueryField("Id_Facture", Operation.Like, "1406%");

if I remove the percent sign it works but it doesn't return what I need:
var field = new QueryField("Id_Facture", Operation.Like, "1406");

[Trobeloo]

Using ExecuteQuery with a parameter is not working either as RepoDb want to convert the string criteria to an int.
Only solution is to pass the query string to ExecuteQuery literally.

[mikependon]

The reason for this is because the type of the column is an INT and there is no way for us to identify the value passed unless we changed the type projection by parsing the value type, which is a bit anti pattern architecturally. But I guess, this problem can be rectified by using the functional query field.

[Trobeloo]

Ty Mike
I tried
var where = new FunctionalQueryField("Id_Facture", Operation.Like, "1406%", "{0}");
ContactSourceList = connection.Query(where);

but get the same error "Input string was not in a correct format"
Of course if I remove the % it works but only return one record.

What I am doing wrong?

[mikependon]

It is the same error due to the fact that the underlying DbParameter in used has the property DbType set to an Int (since the Id_Facture column is an INT type from the DB). It is the ADO.Net itself who is not accepting an input of string into that int-type parameter object.

To rectify this, you can do a little hack like below. (not recommended)

var where = new FunctionalQueryField("Id_Facture", Operation.NotEqual, null, "[Id_Facture] LIKE '1406%' AND {0}");
ContactSourceList = connection.Query(where);

Atleast, the code below is working on my end.

SqlServerBootstrap.Initialize();

using (var connection = new SqlConnection("Server=.;Database=TestDB;Integrated Security=True;TrustServerCertificate=True;"))
{
    // Drop table
    connection.ExecuteNonQuery("DROP TABLE IF EXISTS [dbo].[TestTable];");

    // Create table
    connection.ExecuteNonQuery("CREATE TABLE [dbo].[TestTable]([Id] INT IDENTITY(1, 1) PRIMARY KEY, [Name] NVARCHAR(256)) ON [PRIMARY];");

    // Insert data
    for (var i = 0; i < 1000; i++)
    {
        var id = connection.Insert("[dbo].[TestTable]", new { Name = $"Name-{i}" });
        Console.WriteLine($"Record created with id = {id}");
    }

    // Id as String query
    var where = new FunctionalQueryField("Id",
        Operation.NotEqual,
        null,
        "[Id] LIKE '1%' AND {0}");
    Console.WriteLine(where.GetString(connection.GetDbSetting()));
    var result = connection.Query("[dbo].[TestTable]", where);
}

[Trobeloo]

I understand the source of the problem and will stay with the literal SQL query as your solution a bit cumbersome.
FYI here is the where statement I had in EF
where invoice.Id.ToString().Contains("1406")

[mikependon]

Referencing #1097

[Trobeloo]

Ok I will try again when #1097 is implemented.
A quick one, how can I get the database field name for an entity property? In my case the database field is called Id_Facture and the property is called Id. I tried to use PropertyCache.Get(e => e.Id); with no luck.

[ngardon]

If I am not mistaken, your EF solution is equivalent to LIKE '%1406%', not LIKE '1406%'.

I think the equivalent is .StartWith("1406").

I didn't test it though.