Hi there,

I'm using VS2005 (C#) with SQL Server and we need to generate the following query:

SELECT *
FROM Users
WHERE "%;"+UserName+";%" LIKE "%;sander;%"

The following code is not enough:

UserCollection users = new UserCollection();
IPredicate filter = (UserFields.Inlognaam % ("%" + user.FullUsername + "%"));
users.GetMulti(filter);

The problem is that i need to add some text ("%;" and ";%") to the left side the expression, but the compiler won't accept that.

Can you help me with this?

SanderF wrote:

Hi there,

I'm using VS2005 (C#) with SQL Server and we need to generate the following query:

SELECT *
FROM Users
WHERE "%;"+UserName+";%" LIKE "%;sander;%"

The following code is not enough:

UserCollection users = new UserCollection();
IPredicate filter = (UserFields.Inlognaam % ("%" + user.FullUsername + "%"));
users.GetMulti(filter);

The problem is that i need to add some text ("%;" and ";%") to the left side the expression, but the compiler won't accept that.

Can you help me with this?

By wrapping the UserName string with "%;" and ";%" you suggest that you have a field in your Users table for each username. Is this correct?

Hi,

In the tabel of Users in the database it is possible to put more than one username in the column "Inlognaam", for example the content is: sander;admsf;administrator. This will often used by our customers when they must create serveral course-accounts. The will add one fysical user to the database which has the following account: course1;course2;course3.

When i want to test if the username is in this table, it is not enough to query: WHERE UserName LIKE "%sander%" because also "sanderf" will also match. That's why i have to put ";" before and after the string. In this case i must also put a ";" before and after the field UserName, otherwise the content "sander" will not match because there are no ";" before and after the word.

for example the content is: sander;admsf;administrator

That's a very risky query.

How would you search for the first name "sander" it has no ";" beforehand? Also the last one doesn't have a terminating ";"

Maybe you want to use starting and ending characters for all the names, eg: ;sander;admsf;administrator;

Or better to store them in an XML field.

Anyway I think you should implement IExpression and override the ToQueryText(). refer to: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I agree with Walaa that the query is very risky and IMHO unnecessary, as it is effectively storing a table in a single field. Just use a separate table for the 1:n relation between realaccount and matching names.

That said, you should be able to do it, IF you're using v2.0, you have to construct an expression with the Add/+ operator to concatenate the strings to the field. If you're using 1.0.2005.1, you have to do what Walaa said, implement IExpression. Though if I were you, I'd solve it properly, by creating a separate table. Storing multiple values in a single field by using a separator is a big no-no if you are going to query the values individually.