Using SQL IN Operator

Apr 16, 2014 at 4:58 PM
Edited Apr 16, 2014 at 4: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?
Apr 16, 2014 at 5: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 6: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.

Apr 16, 2014 at 6: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 6: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

Apr 16, 2014 at 7: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 8: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 8: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