Serenity: Cascaded Editors with many records

[marcobisio]

Hi (again) Volkan,
I'm experiencing a performance issue, but onestly I don't know if it is "by design" or (more likely) I'm doing something wrong.
I've followed your guide in the Serenity Developer Guide and I was able to setup three cascaded editors that are working fine.
The performance issue came up when I fill the db tables with real business data and the side-effect it's that the Dialog of such table take about 4-6 seconds to open.

My scenario is the following:

• Countries
• Sites
• Devices

I want to filter by Countries first, then Sites (of that country) and then Devices of such site.
Just to give you an idea, about 250, 20000 and 90000 records respectively.
When I open the dialog, Serenity performs the queries to fill all three select (I checked the last run queries directly on sql server just to be sure) in order to pre-populate the selects and no filters are applied to such queries.

Probably I can do some tuning on that tables to improve query performance, but I was wondering if there is a way to prevent Lookups to be populated (the last two I mean) until the first one contains a value that can be used as a filter condition.

Moreover, I'm experiencing that issue also in an already existing record where that fields are already filled when the dialog opens (so the query can be previously filtered).

What I can do? The filtering of the select controls is done completely client side? Or there is something I can do subclassing the related RowLookupScript? I was trying with an override of the PrepareQuery method but I don't know if it's possibile to "use" a value from another lookup as a query "where" criteria.

Thanks as always for your help
Best Regards

[volkanceylan]

Lookups are only intended for small tables. Which is about 10000 max. Also their data shouldn't change too often to make caching useful.

You need an ajax based editor to load data partially, here is a such student editor sample from our code that works without lookups, but service calls instead:

using jQueryApi;
using Serenity;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Runtime.CompilerServices;

[Editor, DisplayName("Student")]
public class StudentEditor : Select2AjaxEditor<StudentEditorOptions, StudentRow>
{
    public jQueryXmlHttpRequest lastRequest;
    private Int32? organizationID;

    public StudentEditor(jQueryObject hidden, StudentEditorOptions opt)
        : base(hidden, opt)
    {
        BindToOrganizationEditor();
    }

    public override void Destroy()
    {
        UnbindFromOrganizationEditor();
        base.Destroy();
    }

    protected override string GetService()
    {
        return "Student/Student";
    }

    protected override string GetItemKey(StudentRow item)
    {
        return item.StudentId.ToString();
    }

    protected override string GetItemText(StudentRow item)
    {
        return item.StudentNo + " " + item.FullName;
    }

    protected override int GetTypeDelay()
    {
        return 500;
    }

    protected override void ExecuteQueryByKey(ServiceCallOptions<RetrieveResponse<StudentRow>> options)
    {
        var request = (RetrieveRequest)options.Request;
        request.ColumnSelection = RetrieveColumnSelection.KeyOnly;
        request.IncludeColumns = GetIncludeColumns();

        base.ExecuteQueryByKey(options);
    }

    public List<string> GetIncludeColumns()
    {
        return new List<string> {
            StudentRow.Fields.FullName,
            StudentRow.Fields.StudentNo
        };
    }

    protected override void ExecuteQuery(ServiceCallOptions<ListResponse<StudentRow>> options)
    {
        var request = (ListRequest)options.Request;
        request.ColumnSelection = ColumnSelection.KeyOnly;
        request.IncludeColumns = GetIncludeColumns();
        request.Sort = new[] { StudentRow.Fields.FullName };
        request.IncludeDeleted = true;
        request.ExcludeTotalCount = true;
        if (OrganizationID != null)
        {
            request.EqualityFilter = request.EqualityFilter ?? new JsDictionary<string, object>();
            request.EqualityFilter[StudentRow.Fields.OrganizationId] = OrganizationID;
        }
        options.BlockUI = false;
        options.Error = delegate
        {
        };

        if (lastRequest != null && lastRequest.ReadyState != System.Net.ReadyState.Done)
            lastRequest.Abort();

        lastRequest = Q.ServiceCall(options);
        var self = this;
        lastRequest.Then(() => self.lastRequest = null, () => self.lastRequest = null);
    }

    private void BindToOrganizationEditor()
    {
        if (OrganizationEditorID.IsEmptyOrNull())
            return;

        var editor = Q.FindElementWithRelativeId(this.Element, OrganizationEditorID).TryGetWidget<OrganizationEditor>();
        if (editor != null)
        {
            var self = this;
            editor.Element.Bind("change." + this.uniqueName, delegate
            {
                OrganizationID = (Int32?)editor.Value.ConvertToId();
            });
        }
    }

    private void UnbindFromOrganizationEditor()
    {
        if (OrganizationEditorID.IsEmptyOrNull())
            return;

        var editor = Q.FindElementWithRelativeId(this.Element, OrganizationEditorID).TryGetWidget<OrganizationEditor>();
        if (editor != null)
            editor.Element.Unbind("." + this.uniqueName);
    }

    public string OrganizationEditorID
    {
        get
        {
            return options.OrganizationEditorID;
        }
        set
        {
            if (OrganizationEditorID != value)
            {
                UnbindFromOrganizationEditor();
                options.OrganizationEditorID = value;
                BindToOrganizationEditor();
            }
        }
    }

    public Int32? OrganizationID
    {
        get
        {
            return organizationID;
        }
        set
        {
            if (organizationID != value)
            {
                organizationID = value;
                Value = null;
            }
        }
    }
}

[Serializable, Reflectable]
public class StudentEditorOptions
{
    public string OrganizationEditorID { get; set; }
}

OrganizationEditorID is similar to CascadeFrom attribute of lookup editors.

[marcobisio]

Copy that! :-)
Once again thank you for point me out in the right direction.

Regards

[marcobisio]

Dear @volkanceylan,
I'm trying to do this in a .ts editor...there somewhere a typescript implementation of this kind of editor?
Your example was for the old "Saltarelle" Serenity...

Thank you very much
Bye

[marcobisio]

Here we are (Site aka Organization and Equipment aka Student):

namespace eService.CSH {

    @Serenity.Decorators.registerEditor()
    export class EquipmentLazyEditor extends Serenity.Select2AjaxEditor<EquipmentLazyEditorOptions, EquipmentRow> {

        public _lastRequest: JQueryXHR;
        private _siteID: number;

        constructor(hidden: JQuery, opt: any) {
            super(hidden, opt);
            this.BindToSiteEditor();
        }

        public destroy(): void {
            this.UnbindFromSiteEditor();
            super.destroy();
        }

        public getService(): string {
            return "CSH/Equipment";
        }

        public getItemKey(item: EquipmentRow): string {
            return item.Id.toString();
        }

        public getItemText(item: EquipmentRow): string {
            return item.KNumber;
        }

        public getTypeDelay(): number {
            return 500;
        }

        public executeQueryByKey(options: Serenity.ServiceOptions<Serenity.RetrieveResponse<EquipmentRow>>): void {
            var request = <Serenity.RetrieveRequest>options.request;
            request.ColumnSelection = Serenity.RetrieveColumnSelection.keyOnly;
            request.IncludeColumns = this.GetIncludeColumns();
            super.executeQueryByKey(options);
        }

        public GetIncludeColumns(): string[] {
            return [EquipmentRow.Fields.KNumber];
        }

        public executeQuery(options: Serenity.ServiceOptions<Serenity.ListResponse<EquipmentRow>>): void {
            var request = <Serenity.ListRequest>options.request;
            request.ColumnSelection = Serenity.ColumnSelection.KeyOnly;
            request.IncludeColumns = this.GetIncludeColumns();
            request.Sort = [EquipmentRow.Fields.KNumber];
            request.IncludeDeleted = true;
            request.ExcludeTotalCount = true;
            if (this.SiteID != null) {
                if (request.EqualityFilter == null) {
                    request.EqualityFilter = {};
                }
                request.EqualityFilter[EquipmentRow.Fields.SiteId] = this.SiteID;
            }
            options.blockUI = false;
            options.error = () =>
            {
            };

            if (this._lastRequest != null && this._lastRequest.readyState != XMLHttpRequest.DONE)
                this._lastRequest.abort();

            this._lastRequest = Q.serviceCall(options);
            var self = this;
            this._lastRequest.then(() => self._lastRequest = null, () => self._lastRequest = null);
        }

        private BindToSiteEditor(): void {
            if (this.SiteEditorID == null || this.SiteEditorID == "")
                return;

            var editor = Q.findElementWithRelativeId(this.element, this.SiteEditorID).tryGetWidget(Serenity.LookupEditor);
            if (editor != null) {
                var self = this;
                editor.element.bind("change." + this.uniqueName, () => {
                    this.SiteID = Number(editor.value);
                });
            }
        }

        private UnbindFromSiteEditor(): void {
            if (this.SiteEditorID == null || this.SiteEditorID == "")
                return;

            var editor = Q.findElementWithRelativeId(this.element, this.SiteEditorID).tryGetWidget(Serenity.LookupEditor);
            if (editor != null)
                editor.element.unbind("." + this.uniqueName);
        }

        public get SiteEditorID(): string {
            return this.options.SiteEditorID;
        }

        public set SiteEditorID(val) {
            if (this.SiteEditorID != val) {
                this.UnbindFromSiteEditor();
                this.options.SiteEditorID = val;
                this.BindToSiteEditor();
            }
        }

        public get SiteID(): number {
            return this._siteID;
        }

        public set SiteID(val) {
            if (this._siteID != val) {
                this._siteID = val;
                this.value = null;
            }
        }

    }

    export class EquipmentLazyEditorOptions {
        SiteEditorID: string;
    }

}

But i got errors in the T4 transformation:

Severity    Code    Description Project File    Line    Suppression State
Error       Running transformation: System.NullReferenceException: Object reference not set to an instance of an object.
   at Serenity.CodeGeneration.ClientTypesGenerator.AddOptionMembers(SortedDictionary`2 dict, ExternalType type, Boolean isOptions) in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.Option.cs:line 78
   at Serenity.CodeGeneration.ClientTypesGenerator.GetOptionMembers(ExternalType type, Boolean isWidget) in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.Option.cs:line 149
   at Serenity.CodeGeneration.ClientTypesGenerator.GenerateOptionMembers(ExternalType type, HashSet`1 skip, Boolean isWidget) in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.Option.cs:line 15
   at Serenity.CodeGeneration.ClientTypesGenerator.<>c__DisplayClass3_0.<GenerateEditor>b__0() in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.Editor.cs:line 30
   at Serenity.Reflection.CodeWriter.InBrace(Action insideBlock) in P:\Sandbox\Serene\Serenity\Serenity.Core\Reflection\CodeWriter.cs:line 50
   at Serenity.CodeGeneration.ClientTypesGenerator.GenerateEditor(ExternalType type, String name) in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.Editor.cs:line 16
   at Serenity.CodeGeneration.ClientTypesGenerator.<>c__DisplayClass11_0.<GenerateType>b__0() in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.cs:line 92
   at Serenity.Reflection.CodeWriter.InBrace(Action insideBlock) in P:\Sandbox\Serene\Serenity\Serenity.Core\Reflection\CodeWriter.cs:line 50
   at Serenity.CodeGeneration.ClientTypesGenerator.GenerateType(ExternalType type) in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.cs:line 89
   at Serenity.CodeGeneration.ClientTypesGenerator.GenerateAll() in P:\Sandbox\Serene\Serenity\Serenity.Web\CodeGeneration\ClientTypes\ClientTypesGenerator.cs:line 44
   at Microsoft.VisualStudio.TextTemplatingC80E34DC23035F9A32A34DD91AC13053DAE7D32FA8B9B4F971C553D22C3BB911CB02E29946F8A684EDE45D246945DE70EECAEDA5E24EA29A4D4CF44464B15FEA.GeneratedTextTransformation.TransformText() eService.Web    C:\SVN\Carestream\2016\eService\eService\eService.Web\Modules\Common\Imports\ClientTypes\ClientTypes.tt 1   

I also need a typescript type definition?

[marcobisio]

Fixed!
The options are an interface:

export interface EquipmentLazyEditorOptions {
    SiteEditorID: string;
}

and the constructor is like that:

constructor(hidden: JQuery, opt: EquipmentLazyEditorOptions) {
super(hidden, opt);
this.BindToSiteEditor();
}

if the code sounds good to you ( @volkanceylan ), then I'll put all this information in a wiki article!

Thanks
Bye

[volkanceylan]

Method and property names should be lowercase in TypeScript. Other than that it sounds good, the code i posted first was a bit messy and i should rewrite it in a simpler way, or encapsulate into an editor like ServiceLookupEditor etc. Anyway, it should help some posting it to Wiki thanks.

[marcobisio]

You're welcome!
Thanks
Bye

[marcobisio]

Just one more question @volkanceylan...
If I'm right, the ajax editor perform the inline search only for the name field, but I need a full search on the GetItemText() value.
So for example (in your implementation) I would like to search for:

item.StudentNo + " " + item.FullName

that is the value returned in the GetItemText() method.

protected override string GetItemText(StudentRow item)
{
return item.StudentNo + " " + item.FullName;
}

There is a way to do that?
Because all the attributes that I've put in the editor are equally important and therefore they should be all searchable.

Thank you very much
Bye

[volkanceylan]

Why don't you put [QuickSearch] on these fields or add a expression field containing both

[marcobisio]

I mean the search embedded in the ajax editor itself...when I type the search functionality seems to work only for the name field and not for the entire string

[volkanceylan]

Man ajax editor searches with service, so whatever your name field / QuickSearch attributes is it will be searched at server side.

[marcobisio]

So a quick search attribute will do the job...I'll try!
Thank you very much!
Bye