Subsonic help, how to list only the first record

Question

Hi I'm doing a little report using subsonic I'm pretty noob And I can't figure how to list only the first record in my report I'm doing something like:

new Select("id,Name,place,group").From(User.Schema)
                                 .InnerJoin(Profile.Schema)
                                 .InnerJoin(userGroup.Schema)
                                 .Where("place")
                                 .IsEqualTo("insomeplace")
                                 .ExecuteReader();

    result:
    093007 Joe doe insomeplace S2A
    093007 Joe doe insomeplace S2A
    093007 Joe doe insomeplace S2A
    093007 Joe doe insomeplace S2A

I have try to new Select("bla bla").Distinct() new Select("bla bla").Top("1") but none of those appear to work... so what I can do??? any ideas???

When used Top("1") I got a error saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1user.id, user.Name, Place, Place FROM user ' at line 1"

I'm using subsonic 2.x

thanks in advance

Answer 1

quit!!!! I proffered use of

string sqlString = "Select * ...";
new InlineQuery().ExecuteReader(sqlString);

Answer 2

I'm assuming from the reference in your question that you are using MySql. I'm afraid that I don't know much about MySql, but the following may be helpful:

1. I can assure you that .Top("1") certainly works in SubSonic 2.x against SQL Server.
2. Can you find an equivalent to SQL Server's Profiler for MySql to see what SQL code is being sent to the database?
3. TOP 1 isn't valid SQL in MySql, instead you need to use the LIMIT clause - http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 3

You can use the Paged(...) method:

new Select("id,Name,place,group").From(User.Schema)
                                 .InnerJoin(Profile.Schema)
                                 .InnerJoin(userGroup.Schema)
                                 .Where("place")
                                 .Paged(1, 1)
                                 .IsEqualTo("insomeplace")
                                 .ExecuteReader();

I'm not 100% percent sure if you have to use Paged(1,1) or Paged(0,1), try both.

Answer 4

Another way to do this would be to use .ExecuteSingle(), which would return only the first result. The downside of this is that the database would actually return all rows of the query (SubSonic simply discards anything but the first record).