Editor - .NET Field.Options Method

andyr

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>?

allan

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

andyr

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.

allan

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

andyr

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?

allan

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

allan

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

andyr

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.

allan

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

Allan

andyr

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>

allan

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

Allan

andyr

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!