Cascasing Lists - MVC Issues

airmaster

I have read the following post which is reasonably informative, but I have questions that linger. Essentially, I can get it to call the function, but it won't return an updated list.

Controller

        public ActionResult Join()
        {
            var request = HttpContext.Request.Form;

            string connectionString = GetDBConnectionString();
            string dbType = "sqlserver";
            using (var db = new DataTables.Database(dbType, connectionString))
            {
                var response = new Editor(db, "tblWinLoss", "ID")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinMarketSegmentType>("tblMarketSegment")
                    .Model<JoinMarketSubSegmentType>("tblMarketSegmentSub")
                    .Field(new Field("tblWinLoss.ID").Set(false))
                    .Field(new Field("tblWinLoss.MarketSegmentID")
                        .Validator(Validation.Required())
                        .Options(new Options()
                            .Table("tblMarketSegment")
                            .Value("ID")
                            .Label("Market")
                        )
                    )
                    .Field(new Field("tblWinLoss.MarketSegmentSubID")
                        .Validator(Validation.Required())
                        .Options(new Options()
                            .Table("tblMarketSegmentSub")
                            .Value("ID")
                            .Label("MarketSub")
                        )
                    )
                    .LeftJoin("tblMarketSegment", "tblMarketSegment.ID", "=", "tblWinLoss.MarketSegmentID")
                    .LeftJoin("tblMarketSegmentSub", "tblMarketSegmentSub.ID", "=", "tblWinLoss.MarketSegmentSubID")
                    .Debug(true)
                    .Process(request)
                    .Data();



                return Json(response, JsonRequestBehavior.AllowGet);
            }
        }

View

        <script type="text/javascript" language="javascript" class="init">
            var editor; // use a global for the submit and return data rendering in the examples
            $(document).ready(function () {
                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#winloss",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "display"
                    }, {
                        label: "Title",
                        name: "tblWinLoss.Title",
                        type: "text"
                   }, {
                        label: "Market Segment",
                        name: "tblWinLoss.MarketSegmentID",
                        type: "select",
                        placeholder: "-- Select --"
                    }, {
                        label: "Market Sub-Segment",
                        name: "tblWinLoss.MarketSegmentSubID",
                        type: "select",
                        placeholder: "-- Select --"
                    }

                    ]
                });
                var table = $('#winloss').DataTable({
                    dom: '<"wrapper"Bflipt>',
                    pageLength: 10,
                    lengthMenu: [10, 15, 20, 50, 75, 100],
                    ajax: {
                        url: "/winloss/join",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID"},
                        { data: "tblWinLoss.Title", editField: "tblWinLoss.Title" },
                        { data: "tblMarketSegment.Market", editField: "tblWinLoss.MarketSegmentID" },
                        { data: "tblMarketSegmentSub.MarketSub", editField: "tblWinLoss.MarketSegmentSubID" },
                    ],
                    order: [1, 'asc'],
                    select: 'single',
                    buttons: [
                        { extend: "create", editor: editor },
                        {extend: "edit", editor: editor},
                        { extend: "remove", editor: editor}                        
                    ]

                });
                table.columns().every(function () {
                    var that = this;

                    $('input', this.header()).on('keyup change clear', function () {
                        if (that.search() !== this.value) {
                            that
                                .search(this.value)
                                .draw();
                        }
                    });
                });
            });

            $(document).ready(function () {
                editor.dependent( 'tblWinLoss.MarketSegmentID', '/winloss/getMarketSegmentSub_datatables' );
            });
        </script>    
</head>
<body>
    <table id="winloss" class="display responsive " cellspacing="0">
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Market Segment</th>
                <th>Market Segment Sub</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Market Segment</th>
                <th>Market Segment Sub</th>

            </tr>
        </tfoot>
    </table>

</body>

</html>

<script>
    (function (factory) {
        if (typeof define === 'function' && define.amd) {
            // AMD
            define(['jquery', 'datatables', 'datatables-editor'], factory);
        }
        else if (typeof exports === 'object') {
            // Node / CommonJS
            module.exports = function ($, dt) {
                if (!$) { $ = require('jquery'); }
                factory($, dt || $.fn.dataTable || require('datatables'));
            };
        }
        else if (jQuery) {
            // Browser standard
            factory(jQuery, jQuery.fn.dataTable);
        }
    }(function ($, DataTable) {
        'use strict';


        if (!DataTable.ext.editorFields) {
            DataTable.ext.editorFields = {};
        }

        var _fieldTypes = DataTable.Editor ?
            DataTable.Editor.fieldTypes :
            DataTable.ext.editorFields;


        _fieldTypes.display = {
            create: function (conf) {
                conf._div = $('<div/>').attr($.extend({
                    id: conf.id
                }, conf.attr || {}));

                return conf._div[0];
            },

            get: function (conf) {
                return conf._rawHtml;
            },

            set: function (conf, val) {
                conf._rawHtml = val;
                conf._div.html(val);
            },

            enable: function (conf) { },

            disable: function (conf) { }
        };


    }));
</script>

Routine to update the MarketSegmentSub

        [HttpPost]
        public ActionResult getMarketSegmentSub_datatables()
        {
            var request = System.Web.HttpContext.Current.Request;
            string connectionString = GetDBConnectionString();
            string dbType = "sqlserver";
            using (var db = new DataTables.Database(dbType, connectionString))
            {
                var query = db.Select(
                    "tblMarketSegmentSub",
                    new[] { "id as value", "MarketSub as label" },
                    new Dictionary<string, dynamic>() { { "MarketSegmentID", request.Params["values[tblWinLoss.MarketSegmentID]"] } }
                );
                dynamic result = new ExpandoObject();
                result.options = new ExpandoObject();
                result.options.MarketSegmentSub = query.FetchAll();
                return Json(result)

            }

        }

Here are the Join tables

    public class JoinWinLoss
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public int? MarketSegmentID { get; set; }
        public int? MarketSegmentSubID { get; set; }


    }
    public class JoinMarketSegmentType
    {
        public int ID { get; set; }
        public string Market { get; set; }
    }
    public class JoinMarketSubSegmentType
    {
        public int ID { get; set; }
        public int MarketSegmentID { get; set; }
        public string MarketSub { get; set; }
    }

Here is a JSON return from the call to getMarketSegmentSub_datatables

{"JSON":[{"Key":"options","Value":[{"Key":"MarketSegmentSub","Value":[{"value":1,"label":"Tuna"},{"value":3,"label":"Perch"},{"value":4,"label":"Swordfish"}]}]}],"Response payload":{"EDITOR_CONFIG":{"text":"[{\"Key\":\"options\",\"Value\":[{\"Key\":\"MarketSegmentSub\",\"Value\":[{\"value\":1,\"label\":\"Tuna\"},{\"value\":3,\"label\":\"Perch\"},{\"value\":4,\"label\":\"Swordfish\"}]}]}]","mode":"application/json"}}}

This format doesn't look quite the same as posted in the thread I referenced above. I tried constructing the right string manually, but that didn't work either. Anyway, the MarketSegmentSub didn't update.

Any thoughts? This is a great package, and I do invest quite a bit of time troubleshooting myself before I ask for help. I am at a loss right now.

allan

This is the JSON response from the server - formatted for readability:

{
    "JSON": [{
        "Key": "options",
        "Value": [{
            "Key": "MarketSegmentSub",
            "Value": [{
                "value": 1,
                "label": "Tuna"
            }, {
                "value": 3,
                "label": "Perch"
            }, {
                "value": 4,
                "label": "Swordfish"
            }]
        }]
    }],
    "Response payload": {
        "EDITOR_CONFIG": {
            "text": "[{\"Key\":\"options\",\"Value\":[{\"Key\":\"MarketSegmentSub\",\"Value\":[{\"value\":1,\"label\":\"Tuna\"},{\"value\":3,\"label\":\"Perch\"},{\"value\":4,\"label\":\"Swordfish\"}]}]}]",
            "mode": "application/json"
        }
    }
}

But the dependent() method needs JSON that would look like this:

{
  "options": {
    "tblWinLoss.MarketSegmentSubID": [
      { "label": ..., "value": ... },
      ...
    ]
  }
}

i.e. an "options" object which contains the field names (the full name - matching what was used for fields.name) with the options for each field.

You could use a custom Ajax call and then modify the JSON that you are currently returning if you'd rather do it that way than making a change on the server-side, but the easiest way would be to modify the server-side return I'd suggest.

Regards,
Allan

airmaster

Thanks for the response. Yes, I was thinking that the return value didn't look right. I had just followed the server side instructions as per the link I followed. I guess the instructions on that link are no longer valid.

I will look and see if I can construct my own server-side link. I am not that familiar with how to construct JSON objects in C#.

airmaster

One irritating thing is that it doesn't seem to be easy to include a arbitrary string, like "tblWinLoss.MarketSegmentID" as a key. It seems like it will take only keys w/o quotes when creating them.

airmaster

Okay, I modified the code to return the string, which I think is in the correct format:

{"options":{"tblWinLoss.MarketSegmentSubID":[{"label":"Tuna","value":1},{"label":"Perch","value":3},{"label":"Swordfish","value":4}]}}

C# code, using entity framework for the actual DB calls. At the end, I need to do a string replace to actually get it into the required form. Unfortunately, in the popup data editor, it doesn't make any changes to the MarketSegmentSub. Isn't this supposed to happen automatically?

        [HttpPost]
        public ActionResult getMarketSegmentSub_datatables()
        {
            var request = System.Web.HttpContext.Current.Request;
            var marketSegmentID = Convert.ToInt32(request.Form["rows[0][tblWinLoss][MarketSegmentID]"]);
            var marketSegmentSubList = _context.tblMarketSegmentSubs.Where(p => p.MarketSegmentID == marketSegmentID).ToList();

            dynamic dyamicMarketSegmentSubJSON = new List<dynamic>();
            for (int i = 0; i < marketSegmentSubList.Count(); i++)
            {
                dyamicMarketSegmentSubJSON.Add(new { label = marketSegmentSubList[i].MarketSub , value = marketSegmentSubList[i].ID });
            }

            var json = new JavaScriptSerializer().Serialize(new
            {
                options = new { MarketSegmentSubID = dyamicMarketSegmentSubJSON } 
            });

            return Json(json.Replace("MarketSegmentSubID", "tblWinLoss.MarketSegmentSubID"));

        }

allan

Isn't this supposed to happen automatically?

Yes absolutely. So:

{
    "options": {
        "tblWinLoss.MarketSegmentSubID": [{
            "label": "Tuna",
            "value": 1
        }, {
            "label": "Perch",
            "value": 3
        }, {
            "label": "Swordfish",
            "value": 4
        }]
    }
}

is what is returned from the server in response to the /winloss/getMarketSegmentSub_datatables request? I can't recall if I've asked you before (sorry) could you give me a link to your page / app so I can take a look and see what is going wrong with that please?

Thanks,
Allan

airmaster

Yes, it is. The page is internal, though. Below is the return from Firefox debug network response.

I mocked up a temporary table, so you have to ignore the TODELETE, but that is what is returned from the call.

Obviously, I have the TODELETE inside my javascript as well, or it wouldn't work.

airmaster

I threw a project together, maybe it will help determine what is wrong. Go to the /winloss url.

I mailed you the link to the project. Hopefully it represents the problem.

allan

Perfect - thank you. That screenshot shows that the return is a string rather than plain JSON.

In the code above you seralise the object into a string (var json) and then use return Json(json...); which I think is likely the issue.

You probably need to deserialise the JSON string...

var j2 = JsonConvert.DeserializeObject<JObject>(json.Replace(...));

return Json(j2);

Or you could return your own response object:

    var response = Request.CreateResponse(HttpStatusCode.OK);
    response.Content = new StringContent(json, Encoding.UTF8, "application/json");
    return response;

Allan

airmaster

I tried both of your suggestions. Only the second one worked.

1st Suggestion

Note I did this in a class derived from Controller, not ApiController...maybe that could have made the difference.

The first one is of some dynamic looking object type, which, when returned (as above), looks like this.

The object before the return (in C# code) is:

The only way I could get it to return an object in the right form is to use a real object on deserialize.

    public class OptionsObject
    {
        public MarketSubSegmentObject options { get; set; }
    }


    public class MarketSubSegmentObject
    {
        public List<ValueLabelObject> MarketSegmentSubID { get; set; }
    }

    public class ValueLabelObject
    {
        public int value { get; set; }
        public string label { get; set; }
    }

and then in the controller

OptionsObject j2 = JsonConvert.DeserializeObject<OptionsObject>(json);

The problem is that the key is "MarketSegmentSubID", and not "tblWinLoss.MarketSegmentSubID". The latter is really an invalid key for the Json object, and I could not find a way to coerce it into the right form. Maybe I could use that [Deserialize] label on the object, found in the RestSharp package, but at this point, I wanted to move on to the second suggestion.

2nd Suggestion
On your second suggestion, I could not get it to work with and MVC controller, but had to implement a separate ApiController type, which isn't present in my application. It isn't my favorite way of doing this, hopefully will find a better solution.

Regarding the code that I referred to at the beginning, it doesn't seem to work, as it doesn't return the json in the format you would expect. I had to roll my own to get it to the correct format, which you see in the code below.

So, what was the end-state of my code? I am enclosing it below. I know that instead of the replace string, some people prefer adding a [JsonProperty (Name = "tblWinLoss.tblMarketSegmentSubID], but either way, that didn't get me to the object I wanted.

Implementation of the ApiController

namespace MyNameSpace.Controllers
{
    public class MyAPIController : ApiController
    {
       [HttpPost]
       [HttpGet]
        public  HttpResponseMessage getMarketSegmentSub_datatables()
        {
            // I use entity framework for most of my database calls
            using (MySQLEntities _context = new MySQLEntities())
            {
                var request = System.Web.HttpContext.Current.Request;
                // Need to throw in a check for a blank value for the MarketSegmentID
                var marketSegmentID =   Convert.ToInt32(request.Form["values[tblWinLoss.MarketSegmentID]"]);
                var marketSegmentSubList = _context.tblMarketSegmentSubs.Where(p => p.MarketSegmentID == marketSegmentID).ToList();

                dynamic dyamicMarketSegmentSubJSON = new List<dynamic>();
                for (int i = 0; i < marketSegmentSubList.Count(); i++)
                {
                    dyamicMarketSegmentSubJSON.Add(new { label = marketSegmentSubList[i].MarketSub, value = marketSegmentSubList[i].ID });
                }
                var json = new JavaScriptSerializer().Serialize(new
                {
                    options = new { MarketSegmentSubID = dyamicMarketSegmentSubJSON }
                });

                json = json.Replace("MarketSegmentSubID", "tblWinLoss.MarketSegmentSubID");

                var response = Request.CreateResponse(HttpStatusCode.OK);
                response.Content = new StringContent(json, Encoding.UTF8, "application/json");
                return response;
            }
        }
    }
}

and then to call it

editor.dependent('tblWinLoss.MarketSegmentID', '/api/MyAPI/getMarketSegmentSub_datatables');

I have a mixed route config, due to use of both Controller (MVC) and ApiController, so you can follow these directions to get both working in the same code.

If I go back to the beginning, where I looked at the example you provided, and I showed in the link, and for the #2 example here, they both seem to require the ApiController, and don't work with the MVC controller. So, the example is not .NET MVC, but .NET Web API. I went back to the linked example, and I sorta got it working, but it does respond with the Json object that you would expect. Of course, it still has problems, due to the inability to have a "." in the key, so I prefer my method.

allan

Wow - thank you for your research into this and posting back your findings. The key issue I guess is that . is not valid in a C# variable name, which really messes with our use of dot delimited object notation in a string to match field names. It might be that we need to look into allowing nested objects such as:

{
    "options": {
        "tblWinLoss": {
            "MarketSegmentSubID": [{
                "label": "Tuna",
                "value": 1
            }, {
                "label": "Perch",
                "value": 3
            }, {
                "label": "Swordfish",
                "value": 4
            }]
        }
    }
}

I didn't realise it would be so hard to put a . into a JSON parameter name in C#!

There is one other option thinking about it which is to rename the data sent to the client from tblWinLoss.MarketSegmentID to be just MarketSegmentID (assuming there is no conflicting name from other tables). That can be done with the optional second parameter for Field:

.Field(new Field("tblWinLoss.MarketSegmentID", "MarketSegmentID")

Then on the client-side just refer to the field / column as MarketSegmentID. This does preclude using Model() on the server-side, which you might or might not want to do - each field needs to be listed as a new Field() rather than with a model.

That way there wouldn't be any need to mess around with . in property names.

Regards,
Allan

airmaster

I think the problem with the optional second parameter is that I can no longer access the field name for the dropdown, that being tblMarketSegment.Market.

I don't really see an option right now, other than relying on the ApiController. Its interesting that, using pretty much the same C# code, that each controller will output different json looking objects. I know that ApiController is more designed for such a thing, but really?

Anyway, I think for the sake of being compliant with the json key constraints, some type of aliasing will be required. You can't get away from the need to refer to object properties with the "." notation.

For the future, do you think it would be possible to modify the dependent field?

editor.dependent('tblWinLoss.MarketSegmentID', '/api/CompetitiveAPI/getMarketSegmentSub_datatablesY');

to allow aliasing?

editor.dependent('tblWinLoss.MarketSegmentID', 'MYALIAS', '/api/CompetitiveAPI/getMarketSegmentSub_datatablesY');

I guess in your dll code, it would somehow then massage the information into the correct fields?

On another note, it seems to update correctly, but the updated row disappears from the table. I have to reload the page to get it to show up. I don't know if this is a bug, or I need to make some type of additional call to refresh that row?

allan

If you alias tblMarketSegment.Market as well - e.g.:

.Field( new Field("tblMarketSegment.Market", "Market") )

then you should be able to access it as simply Market. So yes, if you start aliasing properties, you'd probably need to alias all of them.

do you think it would be possible to modify the dependent field?

Its a nice idea, but the dependent method will actually submit information about all fields in the form and it also allows all fields to be controlled in the JSON return. So all fields would need to be aliased, which basically brings us back to the alias suggestion above.

updated row disappears from the table. I have to reload the page to get it to show up.

That suggests that the server is not returning data about the updated row. It should return the data for the row.

Allan

airmaster

I hate to necro this, but finally getting around to replacing the ApiController. Am I right when you say that I would have to abandon using the model on tblWinLoss and have to alias all of the properties from that? I have quite a few of them.

I can't seem to bind the dropdown for MarketSegment. I wanted to ask before I really dove into this and see if I can come up with a working example of what you suggested...and if I even want to do that.

I assume that a different solution for this isn't very high on the priority list?

allan

Am I right when you say that I would have to abandon using the model on tblWinLoss and have to alias all of the properties from that?

You don't have to, but it you want to be able to access them using a non-nested property (e.g. Market rather than tblMarketSegment.Market) then yes, you'd need to nest them.

I assume that a different solution for this isn't very high on the priority list?

Ideally you'd be able to return something like:

{
    "options": {
        "tblWinLoss.MarketSegmentSubID": [{
            "label": "Tuna",
            "value": 1
        }, {
            "label": "Perch",
            "value": 3
        }, {
            "label": "Swordfish",
            "value": 4
        }]
    }
}

That would allow the JSON return to work with Editor as is. It looks like it is possible using JsonProperty.

I'll try experimenting a little locally.

Allan

airmaster

Allan,

Looking at that link, it gave me some further direction. One reason that I am doing this is that I am considering making a server-side search instead. Things get really messy there with the ".".

Anyway, here is what I am working on now, with some good success so far, but I am not willing quite yet to declare victory.

        public class Options
        {
            [JsonProperty("tblMarketSegment.ID")]
            public List<JoinMarketSegment> MarketSegment_ID { get; set; }
        }

List<JoinMarketSegment> tblMarketSegment = ......  // call to Entity Framework
var options = new Options { MarketSegment_ID = tblMarketSegment};

So, you would think that this would work with the standard JsonResult conversion, so I did this

 JsonResult { Data = new { draw = model.draw, recordsTotal = 1, recordsFiltered = 1, data = res, options = options } };

Nope, still had the underscore in MarketSegment_ID.

Then I found someone who wrote their own JsonResult action.

So I did this:

            JsonNetResult jsonNetResult = new JsonNetResult();
            jsonNetResult.Formatting = Formatting.Indented;
            jsonNetResult.Data = new { draw = model.draw, recordsTotal = 1, recordsFiltered = 1, data = res, options = options };

           return JsonNetResult;

And it seems to work.

I believe that this is because JsonProperty is part of the (Newtonsoft) Json package (which JsonNetResult utilizes), which will then utilize JsonProperty, but JsonResult is part of MVC, which uses the JavascriptSerializer class, which was discussed here and therefore will never use JsonProperty.

In case his site ever goes down, I am including his custom ActionResult derived class here:

        public class JsonNetResult : ActionResult
        {
            public Encoding ContentEncoding { get; set; }
            public string ContentType { get; set; }
            public object Data { get; set; }
            public JsonSerializerSettings SerializerSettings { get; set; }
            public Formatting Formatting { get; set; }

            public JsonNetResult()
            {
                SerializerSettings = new JsonSerializerSettings();
            }
            public override void ExecuteResult(ControllerContext context)
            {
                if (context == null)
                    throw new ArgumentNullException("context");
                HttpResponseBase response = context.HttpContext.Response;
                response.ContentType = !string.IsNullOrEmpty(ContentType)
                  ? ContentType
                  : "application/json";

                if (ContentEncoding != null)
                    response.ContentEncoding = ContentEncoding;
                if (Data != null)
                {
                    JsonTextWriter writer = new JsonTextWriter(response.Output) { Formatting = Formatting };
                    JsonSerializer serializer = JsonSerializer.Create(SerializerSettings);
                    serializer.Serialize(writer, Data);
                    writer.Flush();
                }
            }
        }

I will update you later if I can indeed declare victory.

allan

Good grief. I'm genuinely surprised it is so difficult in C#, but I'm also very much appreciative of your posting your findings here for others - thank you!

Allan

airmaster

Okay, I think we have it. This addresses the original question, which was how do I get cascading dropdown lists working, once the option for the first list is picked.

Models

    public class MarketSubSegmentObject
    {
        [JsonProperty("tblWinLoss.MarketSegmentSubID")]
        public List<ValueLabelObject> MarketSegmentSubID { get; set; }
    }
    public class ValueLabelObject
    {
        public int value { get; set; }
        public string label { get; set; }
    }

Controller method to return Json

        [HttpPost]
        public ActionResult getMarketSegmentSub_datatables()
        {
            var request = System.Web.HttpContext.Current.Request;
            string marketSegmentString = request.Form["values[tblWinLoss.MarketSegmentID]"];
            List<ValueLabelObject> marketSegmentSubList;
            if (marketSegmentString != "")
            {
                int marketSegmentID = Convert.ToInt32(marketSegmentString);
                marketSegmentSubList = (from s in _context.tblMarketSegmentSubs
                                        where s.MarketSegmentID == marketSegmentID
                                        select new ValueLabelObject
                                        {
                                            value = s.ID,
                                            label = s.MarketSub
                                        }).ToList();                                       
            }
            else
            {
                marketSegmentSubList = new List<ValueLabelObject>();
            }
            MarketSubSegmentObject options = new MarketSubSegmentObject { MarketSegmentSubID = marketSegmentSubList };
            return new JsonNetResult { Data = new { options = options } };
        }

And to be complete, the function which calls it, where is the controller.

                editor.dependent('tblWinLoss.MarketSegmentID', '/winloss/getMarketSegmentSub_datatables');

So, I just need to include a bit more error checking, and then we are good. The Json has the nicely placed tag of "tblWinLoss.MarketSegmentSubID" instead of MarketSegmentSubID, which means that JsonProperty did its job.

Overall, this is a pretty nice solution. Its only dependent on one external package, Newtonsoft Json, which is extremely popular, so you aren't relying on something obscure. You just need the relatively simple JsonNetResult object (source code above), and the JsonProperty tag.

My suggestion is that, given this, there is no reason to make any changes to the Datatables platform, unless there are other underlying reasons. For a pure MVC solution, it might be a good idea to include this as an option here.

Now that this is solved, I may go back to the example above this one, and continue working on a server-side solution.

allan

That's excellent - thank you!