Open Trobeloo opened 2 years ago
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.
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.
Ty Mike
I tried
var where = new FunctionalQueryField("Id_Facture", Operation.Like, "1406%", "{0}");
ContactSourceList = connection.Query
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?
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);
}
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")
Referencing #1097
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
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.
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");