Using SQL IN Operator

Apr 16, 2014 at 3:58 PM
Edited Apr 16, 2014 at 3:59 PM
I am using PostgreSql database. I want to use the SQL IN Operator. I have an SQL Statement:
SELECT u.id, u.username, u.name
FROM users u
WHERE u.username IN ('name1', 'name2','name3’);
unames = “name1, name2,name3";
The Fluent data command I am using is:
var managerId = context.Sql(GET_MANAGERS_IDS)
    .Parameter("Usernames",unames)
    .QueryMany<ReportStructure>();
Is there a way to do this? I have tried multiple ways to do this. When I run the query it fails and the sql generated is:
SELECT … u.username IN ((‘name1,name2,name3')::text)
Can this be done?
Coordinator
Apr 16, 2014 at 4:20 PM
Yes, have a look at this sample code:

var ids = new List<int>() { 1, 2, 3, 4 };

var products = Context.Sql("select * from Product where ProductId in(:0)", ids).QueryMany<Product>();

Does it work?
Apr 16, 2014 at 5:12 PM

Still have problems I have cut down my sql to:

SELECT u.id, u.username FROM users u WHERE u.username IN (:0);

unames is a list<string> of two values

users[0] = "sheryloleniczak1"

users[1] = "Schildwachter"

My Fluent Data query is:

var reterivedUsers =

context.Sql("SELECT u.id, u.username FROM users u WHERE u.username IN (:0)", unames)

.QueryMany<ReportStructure>();

When I run this I get the following

Message=ERROR: 42601: syntax error at or near ":"

ErrorSql=SELECT u.id, u.username FROM users u WHERE u.username IN (:0)

Hint=""

When I chnage to SQL statement to

SELECT u.id, u.username FROM users u WHERE u.username IN (@0)

like the example in the documentation

I get the following

Message = ERROR: 42883: operator does not exist: character varying = integer

ErrorSql = SELECT u.id, u.username FROM users u WHERE u.username IN (@0)

Hint = No operator matches the given name and argument type(s). You might need to add explicit type casts

If I run the SQL in pgAdmin

SELECT u.id, u.username FROM users u WHERE u.username IN ('sheryloleniczak1', 'Schildwachter')

I get two results returned.

Coordinator
Apr 16, 2014 at 5:29 PM
Can you try to remove the space between IN and the parenthese so it should instead be in(:0)

Does that help?
Apr 16, 2014 at 5:45 PM

Removing the space changes nothing I still get the same error (see previous email).

I have solved the problem by building the sql with the parameters embedded and not using Fluent Data to pass parameters.

Thank you for your help

Roy

Coordinator
Apr 16, 2014 at 6:51 PM
This should work, in in lowercase and no space.
context.Sql("SELECT u.id, u.username FROM users u WHERE u.username in(:0)", unames)
Apr 16, 2014 at 7:55 PM

This works but returns nothing. The sql generated by fluent data is:

SELECT u.id, u.username FROM users u WHERE u.username in((('''sheryloleniczak1''')::text),(('''posymoe''')::text),(('''mbmbt4''')::text),(('''Schildwachter''')::text))

If I replace the ''' with ' it works

Roy

Apr 16, 2014 at 7:59 PM

Please disregard the pervious email. It works and returns the correct data. The problem I listed was my fault.

Thank you for the help

Roy