.NET - recordsTotal wrong when using Where Conditions

guidols

Hello,

I have some fields where I need to perform particular filterings, for instance an "exact match" search (but I have also other, more complex, use cases). Therefore I marked the fields as searchable: false in the JS definition and I'm handling the search server side (.NET):

For instance:

var searchedId = Request["columns[0][search][value]"];
if (!string.IsNullOrEmpty(searchedId) && int.TryParse(searchedId, out int n))
editor = editor.Where("Main.Id", n, "=");

So far, so good.

The problem is that the query that counts the recordsTotal is now different from the normal behaviour:
- Normally the query is something like: SELECT COUNT( Main.Id ) as 'cnt' FROM [Main]
- When performing the where clause in .NET, the query is something like: SELECT COUNT( Main.Id ) as 'cnt' FROM [Main] LEFT JOIN [Projects] ON [Projects].[Id] = [Main].[Project_Id] LEFT JOIN [Tasks] ON [Tasks].[Id] = [Main].[Task_Id] LEFT JOIN ... WHERE [Main].[Id] = @where_0

Basically every linked table is added to the recordsTotal query.

The query then returns 1, so in the editor is impossible to see the total number of records.

Seems a bug to me, but maybe I'm doing something wrong.

Thanks!

allan

Just to confirm, are you using SearchBuilder here? Can you show me your full controller code please?

Thanks,
Allan

guidols

Hi,

sure:

public JsonResult Test()
{
    var request = System.Web.HttpContext.Current.Request;

    using (var db = new Database("sqlserver", System.Configuration.ConfigurationManager.ConnectionStrings["MyContext"].ConnectionString))
    {
        var editor = new Editor(db, "Main", "Main.Id")
            .Model<Main>("Main")
            .Model<LinkedProject>("Projects")
            // other linked tables here

        var searchedId = Request["columns[" + SpecialSearchFields["Id"] + "][search][value]"];

        if (!string.IsNullOrEmpty(searchedId) && int.TryParse(searchedId, out int n))
            editor = editor.Where("Main.Id", n, "=");

        editor = editor.Field(new Field("Main.Id").Set(false))
        // other fields here

        var response = editor.Process(request).Data();  
        return Json(response);

btw, I now added:

response.recordsTotal = _db.Main.Count();

and now it's working correctly, but this seems to me a workaround

allan

Great to hear you've got a workaround.

Can I just check - does this issue only happen when you are using SearchBuilder? Or does it happen even if you aren't using SearchBuilder in your DataTable? I'm wondering if there might be a grouping error in the SQL condition.

If you could add .Debug(true) just before the .Process(request) call and then show me the Ajax response when the error happens, that would be really useful.

Thanks,
Allan

guidols

Seems to me that this happens only with SearchBuilder. If I don't use it everything seems correct.

How can I post the reposnse?

allan

I've got an idea why it might be happening - I think there might be a grouping issue with the query condition. Let me experiment with that a little can get back to you.

Allan

allan

Turns out my idea wasn't right - so I'm not yet sure what is causing this I'm afraid (I'd wondered about the grouping of where conditions).

Could you add the .Debug(true) call just before .Process(request) and then show me the Ajax response when the error happens please?

Thanks,
Allan