Editor - .NET Field.Options Method

Editor - .NET Field.Options Method

andyrandyr Posts: 35Questions: 6Answers: 0

I want to use the "Func<List<Dictionary<string, Object..." form of this: https://editor.datatables.net/docs/1.4.2/net/html/027f6dba-788f-f1ac-e840-02e63d8c2bc9.htm
I made a LINQ to Entities query:

   var shipQuery = this.db.Ship.AsNoTracking()
                                .Where(s => s.Active)
                                .OrderBy(s => s.HullNo)
                                .Select(s => new
                                {
                                    label = s.ShipHull,
                                    value = s.ID
                                });

How do I convert that query result to that Func<List<Dictionary<string, Object>?

Answers

  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin

    In the closure function, have you tried simply shipQuery.ToList(). I'm away from my PC at the moment unfortunately so I can't try it (and LINQ isn't top of my knowledge list!). SO might be more useful for how to convert from a LISQ expression to a List of Dictionary values (although I am curious myself and will investigate when I can).

    Allan

  • andyrandyr Posts: 35Questions: 6Answers: 0

    I know little about delegate functions and closures in .NET, so I may need your help.
    Now I have this for the LINQ query:

    // "db" is a predeclared Linq-to-Entities Database DbContext.
    var shipDict = db.Ship.AsNoTracking()
                                 .Where(s => s.CEMATActive && s.Class == "CVN")
                                 .OrderBy(s => s.HullNo)
                                  .Select(s => new
                                   {
                                        ShipHull = s.ShipHull,  // label for select list
                                        UIC = s.UIC             // value for select list 
                                    })
                                    .ToDictionary(s => s.ShipHull,  s => s.UIC);
    

    That stores a Dictionary to "shipDict".

    For Field.Options Method (Func<List<Dictionary<String, Object>>>).
    1) How do I call that in the Editor.Field in the Controller method:

      .Field(new Field("TSSRAvail.UIC")
          .Options(what goes here)
    

    2) How do I get from a Dictionary to a List of Dictionaries.
    3) What do I pass for "String, Object".
        Or are those return values?

    Thanks for any help.

  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin

    There are two options with anonymous functions, which I think would be the best method here:

    1) Use a delegate directly:

    .Field(new Field("TSSRAvail.UIC")
        .Options( delegate() {
            // perform logic and return a value
            return listDic;
        } )
    

    2) Use a fat arrow anonymous function (my preference as it feels a little like Javascript :-) ) - it is a really slick way of doing anonymous functions in the newer C# versions:

    .Field(new Field("TSSRAvail.UIC")
    .Options( () => {
    // perform logic and return a value
    return listDic;
    } )

    Note that the Options delegate is given no parameters.

    The Formatter documentation has an example new the end which might be of interest (not for the fact that it is a formatter, but rather it uses an anonymous function).

    It is possible to use named delegate functions as well, but that is probably a topic for a C# tutorial :-)

    Regards,
    Allan

  • andyrandyr Posts: 35Questions: 6Answers: 0

    I started on an anonymous function:

     .Field(new Field("TSSRAvail.UIC")
                            .Options( () => { 
                                var shipQuery = dbEntities.Ship.AsNoTracking()
                                   .Where(s => s.CEMATActive
                                               && s.Class == "CVN")
                                   .OrderBy(s => s.HullNo)
                                   .Select(s => new
                                   {
                                       ShipHull = s.ShipHull,  // label for select list
                                       UIC = s.UIC             // value for select list 
                                   }) ;
    

    1) In the CSHTML files JavaScript I will use ".Editor({ ... "fields": ... "type": "select" ". So does the the anonymous function need to return a label/value pair (in this case UIC/ShipHull)? If not, what does it need to return?
    2) What do I add to that Linq Query to convert that what I need to a "List<Dictionary<string, object>"? . That can be chained to the query or can use some intermediate variables.
    Can you help me?

  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin

    1) Yes, a List of Dictionary<string, object> needs to be returned.

    The following is from Editor's self join example - it uses Editor DB libraries rather then LINQ, but it shows how it might be done:

                        .Field(new Field("users.manager")
                            .Options(() => db
                                .Sql("SELECT DISTINCT id as value, CONCAT(first_name, ' ', last_name) as label FROM users ORDER BY label ASC")
                                .FetchAll()
                            )
                        )
    

    2) This I'm not sure off the top of my head - I've not used much LINQ before. Let me get back to you.

    Allan

  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin
    edited May 2015

    Just been experimenting with this and the key thing is to create a new Dictionary of the required type in the select statement. For example:

                            .Options(
                                () => itemList
                                    .OrderBy(s => s.ItemName)
                                    .Select(s => new Dictionary<string, object>() { { s.ItemID.ToString(),  s.ItemName } })
                                    .ToList()
                            )
    

    Hopefully that should do it for you!

    Ultimately it would be better if I were to update Editor to allow a simply dictionary return. The List of Dictionaries is somewhat redundant. This is something I will look into doing.

    Regards,
    Allan

  • andyrandyr Posts: 35Questions: 6Answers: 0

    I got the data for the Editor Datatable select list using the .Sql(...) example:

      .Options(
             () => dbForResponse
                .Sql(@"SELECT 
                        UIC AS value, 
                        ShipHull AS label
                        FROM Ship 
                        WHERE CEMATActive = 1 
                            AND Class = 'CVN'    
                            AND Fleet = 'West'  
                        ORDER BY HullNo")
                .FetchAll()
    

    I could get the LINQ query to return a List of Dictionary<string, object>. But I don't know how to then transfer that to the select list. Let me know if you want to see what I tried. I will check this discussion again on Tues May 26.

  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin

    Yes I'd be interested to see - would be good to get to the bottom of it.

    Allan

  • andyrandyr Posts: 35Questions: 6Answers: 0

    I tried the below:

    .Options(
        () =>
        {
            var shipList = db.Ship.AsNoTracking()
                .Where(s => s.CEMATActive && s.Class == "CVN")
                .OrderBy(s => s.HullNo)
                .ToList()
                .Select(s => new Dictionary<string, object>() {
                                {
                                    s.UIC,      // value for select list 
                                    s.ShipHull  // label for select list
                                }
                            }
                )
                .ToList();
    
            return shipList;
        }
    ) // .Options(...
    

    That produced an empty <select> in both inline editing and the New/Edit form:

    <select id="DTE_Field_TSSRAvail-UIC">  
    <option></option>  
    <option></option>
    ... rest of options
    </select>
    
  • allanallan Posts: 54,687Questions: 1Answers: 8,553 Site admin

    The first .ToList() might be unnecessary, although it will depend upon the data structure I suspect.

    Allan

  • andyrandyr Posts: 35Questions: 6Answers: 0
    edited May 2015

    I needed to add the first .ToList() to fix the error "Only list initializer items with a single element are supported in LINQ to Entities."
    The "shipList" variable is of Type System.Collections.Generic.List<System.Collections.Generic.Dictionary<string,object>>.
    The .Sql().FetchAll() seems to produce a Dictionary with Keys containing {[label, The Label String]} and Values containing {[value, The Value String Id]}.
    I don't know what .NET code to use to get from my LINQ statement to what .Sql().FetchAll() does. I'll take any help!

This discussion has been closed.