Combining MJoin and c# List to limit records shown to ones in List

dynasoft

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have server and js code shown below. I wish to only show on the editor form the records that are stored in List MyViewModel. how can i combine this? i tried doing a check in the render part of the MJoin's Options but I simply get a check box with null against it in the client. I'm ok doing the check either in the server or client code. Many thanks.

1. Server code:

public DtResponse CRUDGlobalP()
{
    MyModel DM = new MyModel();
    MyViewModel MyViewModel = DM.GetMyListViewModel();
    DM = null;

    HttpRequest formData = HttpContext.Current.Request;

    using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
    {
        editor = new Editor(db, "GlobalSP", "GlobalSP.id").Model<SPDBModel.GlobalSP>("GlobalSP");
        editor.Field(new Field("GlobalSP.id")
            .Set(false)
        );
        editor.MJoin(new MJoin("DD")
        .Model<SPDBModel.DD>()
        .Name("GlobalPDD0")
        .Link("GlobalSP.id", "GlobalSPRelations.SpID")
        .Link("DD.id", "GlobalSPRelations.ParamID")
        .Where(q =>
            q.Where("GlobalSPRelations.ParamType", "4", "=")
        )
        .Order("DD.id ASC")
        .Field(new Field("GlobalSP.id"))
        .Field(new Field("GlobalSPRelations.id"))
        .Field(new Field("GlobalSPRelations.SpID"))
        .Field(new Field("GlobalSPRelations.ParamID"))
        .Field(new Field("id"))
        .Set(false)
        );
        editor.MJoin(new MJoin("DD")
            .Model<SPDBModel.DD>()
            .Name("GlobalPDD1")
            .Link("GlobalSP.id", "GlobalSPRelations.SpID")
            .Link("DD.id", "GlobalSPRelations.ParamID")
            .Where(q =>
                q.Where("GlobalSPRelations.ParamType", "4", "=")
            )
            .Order("DD.id ASC")
            .Field(new Field("GlobalSP.id"))
            .Field(new Field("GlobalSPRelations.id"))
            .Field(new Field("GlobalSPRelations.SpID"))
            .Field(new Field("GlobalSPRelations.ParamID"))
            .Field(new Field("id")
                .Options(new Options()
                    .Table("DD")
                    .Value("id")
                    .Label(new[] { "id", "CustomerID", "DDINumber", "Description", "Country" })
                    .Order("Country ASC").Order("id DESC")
                    .Render(row =>
                    {
                        Dictionary<string, object> dicPCEAValues = new Dictionary<string, object>();
                        dicPCEAValues = (Dictionary<string, object>)row;
                        if (dicPCEAValues.ContainsKey("DDINumber") && dicPCEAValues != null && DDIListViewModel != null)
                        {
                            if (DDIListViewModel?.FirstOrDefault(o => o.ID == CommonUtilities.ToLong(dicPCEAValues["id"])) != null)
                            {
                                strTp = "Some HTML"
                            }
                            else
                            {
                                dicPCEAValues = null;
                                strTp = null;
                            }
                        }
                        else
                        {
                            dicPCEAValues = null;
                            strTp = null;
                        }
                        return strTp;
                    })
                )
            )
        );
    }   
}

1. Js code:

var editor = new $.fn.dataTable.Editor({

        destroy: true,
        ajax: {
            url: '/' + strAccountIdx1 + '/Admin/Packages/CRUDPackages/',
            data: function ( d ) {
                return $.extend({}, d, {
                    strEditorState: strAction,
                    intApplyChangesToCustomerP: intApplyChangesToCustomerP1,
                    strPreviousName: strPreviousName1
                });
            },
            type: 'POST',
            async: true,
            cache: false
        },
        table: '#tblDataTable',
        //template: $('#divEditorForm1').clone(),
        template: '#divEditorForm1',
        fields: [
            {
                label: '',
                name: 'GlobalSP.id',
                type: 'hidden'
            }, {
                label: 'GlobalPDD0[].id',
                name: 'GlobalPDD0[].id',    //ID
                data: function (row, type, val) {

                    //....
                }
            }, {
                label: '',
                name: 'GlobalPDD1[].id',
                type: 'checkbox',
                data: function (row, type, val) {

                    //...
                }
            }
        ]
    });

allan

What does MyViewModel contain? Do you have the primary key for the records you want to show? If so, use a Where() condition similar to what you have on line 20. If you have a collection of id values, use WhereIn to filter to that list.

Allan

dynasoft

Thanks. MyViewModel holds properties:

    public long ID { get; set; }
    public string MyNumber { get; set; }
    //....

I do have the primary key, it is ID

i thought about filering in the Where clause. Wasn't sure of the syntax but I'll check. Thanks again.

dynasoft

Shouldn't i place the Where clause after line 45 in the list of DD.id? If I place it in line 20 I still get all DD's. Thanks.

dynasoft

This would be more in line with what i need: https://datatables.net/forums/discussion/comment/95926/#Comment_95926

What would be the syntax for c#? Thanks a lot.

dynasoft

Am trying this, I think it should work:

                                .Where(q =>
                                    q.Where("id", MyListViewModel.Select(o => o.ID).ToList(), "=")
                                )

dynasoft

Not sure why but the none of the DD get returned when I see in debug there are items.

allan

Ah I see - you want the list of options to be limited. Do you want the options to charge for each host row though? If you, you need to make an Ajax call to get the list of options for each row as it is edited (e.g. using -e-api dependent()).

If the list of options is static and you just want to limit them, then yes, what you have looks like it should work. If you add .Debug(true) just before the .Process(...) call (which interestingly I don't see in your controller above?), then it will dump the SQL that is being executed as part of the JSON response. If you post that, it might give us a clue as it what is going on.

Regards,
Allan