Use Like in Where Clause

Feb 20, 2008 at 12:56 AM
Edited Feb 20, 2008 at 12:59 AM
This may be a simple question, but I cannot get the correct result. I use the following DbEntry to get a list of objects of MyClass:

DbCollection<MyClass> objs = DbEntry.From<MyClass>().Where(CK.K"[SNAME]").Like("'%MC%'").Select();

The equivalent SQL statement is "SELECT [field_name_list] FROM MyClass WHERE [SNAME] LIKE '%MC%'. I can get a list of records from SQL but not by DbEntry (0 count back). Is there any wrong in the LIKE operator here?

Note: DbCollection<> may be wrong here, it may be DbObjectList<> or something for a collection.
Coordinator
Feb 20, 2008 at 11:52 AM
Edited Feb 20, 2008 at 11:56 AM
Don't use "[" "]" to quote table name, column name and other db names.
DdEntry will add the quotes based on the taget database.

Use following code:

List<MyClass> objs = DbEntry.From<MyClass>().Where(CK.K["SNAME"].MiddleLike("MC")).Select();
Feb 20, 2008 at 5:54 PM
Thanks for your reply. Actually, the problem is caused by another column's type mismatching. I tried both Like and MiddleLike clauses, and got the same number of objects back, 549.

List<MyClass> objs = DbEntry.From<MyClass>().Where(CK.K["SNAME"].MiddleLike("MC")).Select(); // or
// List<MyClass> objs = DbEntry.From<MyClass>().Where(CK.K"SNAME".Like("%MC%")).Select();

I further debuged the codes into DbEntry codes, and found the SQL object is something like:

Sql = {Select [tagId],[sName] From [mytag] Where [SNAME] Like @SNAME_0;
<Text><60>(@SNAME_0='%MC%':String)}

I tried to use the similar SQL statement in SQL Server to verify:

declare @SNAME_0 varchar(60);
set @SNAME_0 = '%MC%';
Select [tagId],[sName] From [mytag] Where [SNAME] Like @SNAME_0;

I got a message "(554 row(s) affected)", which means 554 rows of data. I copied the result from SQL and tried to find if there is any lower case of "mc", but there is none. So this is not the case problem. Why there is difference?
Coordinator
Feb 21, 2008 at 1:00 AM
I cann't imaging why it happens.
But I think maybe you can get the two results and find the records which in SQL result but not in DbEntry result.
And try to find out why it happens.
Feb 21, 2008 at 4:32 AM
Sorry, it is my mistake. I run the SQL against to a production db, while the DbEntry console to a dev db. The result of SQL against dev db is as same as that of DbEntry. Thanks!