How can i specify data type for sql query?

[rscx-r1]

Hello, i have this code:

[Sql("SELECT * FROM lines WHERE CONTAINS(url, @url)")] Task<IList<LinesDB>> SelectLinesByUrl(string url);

With this code i cant use a CONTAINS predicate because Insight interpretes a nvarchar(MAX) automatically, how can i specify for example varchar(1500) (as in my database)?

• Dotnet version: [net7]
• Database: [MSSQL]
• Library version: [6.3.11]

[jonwagner]

Hm...sql text doesn't give us enough information to detect the type.

We might be able to add parameter annotations like @url:varchar(1500). Let me think about how we can do that without breaking existing code.

[rscx-r1]

Looks great, but i need solve a problem today, did you have any ideas? project already based on your orm. =c

[jonwagner]

• stored procedures can detect the type properly but you may not be using them.
• try CONTAINS(url, CONVERT(varchar(1500), @url))

[rscx-r1]

Incorrect syntax around the "CONVERT" keyword.

SELECT * FROM lines WHERE CONTAINS(url, CONVERT(varchar(1500), @url))

[jonwagner]

interesting. didn't know you can't use a function in the contains parameter list.

You might be able to declare a variable and convert with that:

DECLARE @u varchar(1500)
SELECT @u = @url
select * from url where CONTAINS (url, @u)

[rscx-r1]

nah, that's not working too, Invalid object name "url". i think @url:varchar(1500) is the better idea, i solved it by temporarily by selfmade function but i will wait this update, and please add net7.0 support, thank you!

[jonwagner]

in v8.0.1 I added support for type tags in sqltext.

e.g.

[Sql("SELECT FROM lines WHERE CONTAINS(url, / TYPE: string(1500)" */ @url)")]

• putting a comment of /* TYPE: type(size) */ before the parameter will set its type
• NOTE: to make it compatible with all types of databases, the types should be the enum values of DbType. See: https://learn.microsoft.com/en-us/dotnet/api/system.data.dbtype?view=net-8.0
• You can add size to string parameters, and precision/scale to numeric types.

I probably should have done this a while ago.

LMK how it works for you.