.NET Join with Options() slows/crashes Chrome when rendering/on initialize

joakimvf

Hi,

I have an API Controller (Web API) with 3 many-to-many joins that point to one and same table(Parter). That table has 7000 rows.

The problem I have is when I use the Options() method. When I load the api it loads 3x7000 objects in the "options" which of course crashes/slows down Chrome alot.

Is there a way to load the options only when you for example click on those dropdownlist? Or when you just want to create/edit a row? Or what I am missing?

It seems that my table is loading everything, even the options when the table initialize.

The table works fine without the Options() method. With postman the api sends me 50k rows without the Options() method. But with Options() method enabled it sends me 130k rows...

I have a site where this one is up and running. Is there a way to share the url with login without posting it?

The controller:

/* ALL NÄRRADIO */
        [Route("api/narradio")]
        [HttpGet]
        [HttpPost]
        [HttpPut]
        public IHttpActionResult GetAllNarradio()
        {
            var userId = RequestContext.Principal.Identity.GetUserId();
            var user = RequestContext.Principal.Identity.GetUserName();
            var request = HttpContext.Current.Request;

            using (db)
            {
                var editor = new Editor(db, "Närradio", "Id")
                    .Field(new Field("Närradio.Id"))
                    .Field(new Field("Närradio.Beteckning"))
                    .Field(new Field("Närradio.ParterId"))
                    .Field(new Field("Närradio.Startdatum"))
                    .Field(new Field("Närradio.Slutdatum"))
                    .Field(new Field("Närradio.Grund"))
                    .Field(new Field("Närradio.Skapad"))
                    .Field(new Field("Närradio.SkapadAv"))
                    .Field(new Field("Närradio.Ändrad"))
                    .Field(new Field("Närradio.ÄndradAv"))
                    .Field(new Field("Närradio.Övrigt"))
                    .Field(new Field("Närradio.Ogiltigt"))
                    .Field(new Field("Närradio.Publicerad"))
                    .Field(new Field("Närradio.Avvaktan"))
                    .Field(new Field("Parter.Namn"))
                    .LeftJoin("Parter", "Parter.Id", "=", "Närradio.ParterId") **<------------- 7000k rows**
                    .MJoin(new MJoin("Län")
                        .Link("Närradio.Id", "NärradioLän.Närradio_Id")
                        .Link("Län.Id", "NärradioLän.Län_Id")
                        .Model<JoinModel>()
                        .Field(new Field("Id")
                            .Options("Län", "Id", "Namn")
                            .Validator(Validation.NotEmpty(new ValidationOpts
                            {
                                Message = "Error"
                            }))
                        )
                        .Field(new Field("Namn"))
                    )
                    .MJoin(new MJoin("Kommuner")
                        .Link("Närradio.Id", "NärradioKommuner.Närradio_Id")
                        .Link("Kommuner.Id", "NärradioKommuner.Kommuner_Id")
                        .Model<JoinModel>()
                        .Field(new Field("Id")
                            .Options("Kommuner", "Id", "Namn")
                            .Validator(Validation.NotEmpty(new ValidationOpts
                            {
                                Message = "Error"
                            }))
                        )
                        .Field(new Field("Namn"))
                    )
                    .MJoin(new MJoin("Parter") **<------------------ 7000k rows**
                        .Name("Utgivare")
                        .Link("Närradio.Id", "NärradioUtgivare.Närradio_Id")
                        .Link("Parter.Id", "NärradioUtgivare.Utgivare_Id")
                        .Model<JoinModel>()
                        .Field(new Field("Id")
                        .Options("Parter", "Id", "Namn")
                        .Validator(Validation.NotEmpty(new ValidationOpts
                        {
                            Message = "Error"
                        }))
                        )
                        .Field(new Field("Namn"))
                    )
                    .MJoin(new MJoin("Parter") **<--------------- 7000k rows**
                        .Name("Stf")
                        .Link("Närradio.Id", "NärradioStf.Närradio_Id")
                        .Link("Parter.Id", "NärradioStf.Stf_Id")
                        .Model<JoinModel>()
                        .Field(new Field("Id")
                        .Options("Parter", "Id", "Namn")
                        .Validator(Validation.NotEmpty(new ValidationOpts
                        {
                            Message = "Error"
                        }))
                        )
                        .Field(new Field("Namn"))
                    )
                    .Process(request)
                    .Data();

                return Json(editor);

The table

// Initialize the editor
        editor = new $.fn.dataTable.Editor({
            ajax: {
                create: {
                    type: 'POST',
                    url: '@ViewBag.AjaxUrl'
                },
                edit: {
                    type: 'PUT',
                    url: '@ViewBag.AjaxUrl'
                }
            },
            table: "#tabell",
            fields: [{
                label: "Beteckning: *",
                name: "Närradio.Beteckning"
            }, {
                label: "Tillståndshavare: *",
                name: "Närradio.ParterId",
                type: "select",
                opts: {
                    placeholder: "Välj tillståndshavare"
                }
            }, {
                label: "Grund för tillstånd: *",
                name: "Närradio.Grund",
                type: "select2",
                opts: {
                    placeholder: "Grund för tillstånd"
                },
                options: [
                    "Lokal ideell förening",
                    "Registrerat trossamfund, del av trossamfund",
                    "Närradioförening"
                ]
            }, {
                label: "Startdatum:",
                name: "Närradio.Startdatum",
                type: 'datetime',
                def: function () { return new Date(); }
            }, {
                label: "Slutdatum:",
                name: "Närradio.Slutdatum",
                type: 'datetime',
            }, {
                label: "Förnyat:",
                name: "Närradio.Förnyat",
                type: 'datetime',
            }, {
                label: "Län:",
                name: "Län[].Id",
                type: "select2",
                attr:
                {
                    multiple: 'multiple'
                },
                options: {
                    placeholder: "Välj län",
                    "allowClear": true,
                }
            }, {
                label: "Kommuner:",
                name: "Kommuner[].Id",
                type: "select2",
                attr:
                {
                    multiple: 'multiple'
                },
                options: {
                    placeholder: "Välj län",
                    "allowClear": true,
                }
            },{
                label: "Utgivare:",
                name: "Utgivare[].Id",
                type: "select2",
                attr:
                {
                    multiple: 'multiple'
                },
                options: {
                    placeholder: "Välj län",
                    "allowClear": true,
                }
            }, {
                label: "Stf. utgivare:",
                name: "Stf[].Id",
                type: "select2",
                attr:
                {
                    multiple: 'multiple',
                    allowClear: true,
                },
                options: {
                    placeholder: "Välj län",
                    
                }
            }, {
                label: "Övrigt:",
                name: "Närradio.Övrigt",
                type: "textarea"
            }, {
                label: "Publicera på webben:",
                name: "Närradio.Publicerad",
            }, {
                label: "Ogiltigt (utgånget/avpublicerat):",
                name: "Närradio.Ogiltigt",
            }, {
                label: "Väntar på beslut: *",
                name: "Närradio.Avvaktan",
            }
            ]
        });

        table = $('#tabell')
            .DataTable({
                serverSide: true,
                ajax: {
                    type: 'POST',
                    url: "@ViewBag.AjaxUrl/active"
                },
                stateSave: false,
                columns: [
                    {
                        data: "Närradio.Beteckning"
                    }, {
                        data: "Parter.Namn"
                    }, {
                        data: "Kommuner", render: "[, ].Namn"
                    }, {
                        data: "Län", render: "[, ].Namn"
                    }, {
                        data: "Närradio.Startdatum"
                    }, {
                        data: "Närradio.Slutdatum"
                    }, {
                        data: "Utgivare", render: "[, ].Namn"
                    }, {
                        data: "Stf", render: "[, ].Namn"
                    }, {
                        data: "Närradio.Grund"
                    }, {
                        data: "Närradio.Frekvenser"
                    }
                ]
            });

joakimvf

I've also noticed that the table renders/initialize faster when serverSide is on false

What am I missing? I'm a noob when it comes to Datables

allan

I've also noticed that the table renders/initialize faster when serverSide is on false

Does url: "@ViewBag.AjaxUrl/active" implement server-side processing?

Is there a way to load the options only when you for example click on those dropdownlist? Or when you just want to create/edit a row?

There isn't I'm afraid. Perhaps your best bet with such a large number of options would be use to Select2 or Selectize to Ajax load the data. Plug-ins for Editor to work with those libraries are available here.

Allan

joakimvf

There isn't I'm afraid. Perhaps your best bet with such a large number of options would be use to Select2 or Selectize to Ajax load the data. Plug-ins for Editor to work with those libraries are available here.

Hi again Allan and thank you for your answer. I've tried Selectize/Select2 with multiple and get following error: Unable to convert an object of System.Collections.Generic.Dictionary`2 [System.String, System.Object] to System.String type.

Code:

            label: "Test:",
            name: "Närradio.Test",
            type: "selectize",
            opts: {
                maxItems: 5,
                valueField: 'value',
                labelField: 'label',
                searchField: ['label'],
                create: false,
                options: [
                    { label: "Ett", value: "1" },
                    { label: "Två", value: "2" },
                    { label: "Tre", value: "3" },
                    { label: "Fyra", value: "4" },
                    { label: "Fem", value: "5" },
                    { label: "Sex", value: "6" },
                ]

I've also tried with GetFormatter(Format.Implode(",")) and SetFormatter(Format.Explode(",")) without success. Any idea of what might be the problem here?

allan

Could you show me the data that the browser is sending to the server please? That will be shown in the "Headers" section of the Ajax request, shown in the "Network" section of your browser's developer tools.

I'll try to set up a local test case here shortly.

Allan

joakimvf

Could you show me the data that the browser is sending to the server please? That will be shown in the "Headers" section of the Ajax request, shown in the "Network" section of your browser's developer tools.

See attached image.

I'm trying the following code:

Datatable

{
            label: "Super:",
            name: "super",
            type: "selectize",
            opts: {
                delimiter: ',',
                persist: false,
                maxItems: 5,
                valueField: 'value',
                labelField: 'label',
                searchField: ['label'],
                create: false,
                onChange: function (value) {
                    console.log(value);
                },
                options: [
                    { label: "Ett", value: "1" },
                    { label: "Två", value: "2" },
                    { label: "Tre", value: "3" },
                    { label: "Fyra", value: "4" },
                    { label: "Fem", value: "5" },
                    { label: "Sex", value: "6" },
                ]
            }
        }

Controller:

var editor = new Editor(db, "RadioModel", "Id")
                    .Field(new Field("super")
                           .GetFormatter(Format.Implode())
                           .SetFormatter(Format.Explode())
                    );

I also tried with PHP with the exact same setup and it worked... So there is something with the .NET code

Thank you once again!

allan

Thanks! Could you try the 1.6.3 release of Editor which I published on Saturday. That has a fix for .NET join issues that might impact here.

Allan

joakimvf

Hi again Allan,

The last example doesn't have any join.

It is a simple table with just strings. Did you have time to try .NET with multiple?

I also tried the generator with both PHP and .NET. Multiple with select2 & selectize only works with PHP... Do I need to create a custom formatter in .NET for it to work or should it work with Get/SetFormatter(Implode())?

I tried the 1.6.3 release with no luck.

allan

Doh - sorry - I misunderstood.

I haven't had a chance to try it yet.

Can you get it to work without the Select2 / Selectize plug-ins? i.e. just the built in select field type which has its multiple option built in.

We need to identify if this is an issue with the external libraries or one with the .NET libraries for Editor.

Allan

joakimvf

Thank you once again Allan for your answer!

I tried the following and it worked:

{
                label: "Kommun:",
                name: "kommun",
                type: 'select',
                multiple: true,
                separator: ',',
                options: [
                    { label: 'Edinburgh'},
                    { label: 'London'}
                ]
            }

I didn't even need the Set/GetFormatter. Same with "Checkbox" if I use the separator-parameter.

So the issue lies within the external libraries. Do you mean the plugin-code? How can we make it work with .NET?

Thank you once again.

allan

Okay good! So the trick here is going to be to have select2 work with a separator option. I actually wrote the code to do that for someone else a little while back, but haven't published it yet. I've just sent you a PM with the code required.

Allan