Cannot see navigational properties for FK

Jords2point0

Hi,

I am using DataTables to display the Registrations. Everything worked fine before when I was using the table Microsoft used in their tutorial. This also stops me from Editing the registration and displaying it in Details mode. So I want to display the names of each navigational property object. I know I can install Editor libraries and use Left Join but that's like over $100 dollars. Is there another way? I'm just a student after all.

I have got the files to help replicate the problem including the Registration\Index.cshtml file.

Expected behavior:

It renders the two other navigational properties "User" and "Job".

Actual behavior:

It only displays the RegistrationDate field. And if I select Edit or Details or Delete It crashes saying "No web page was found for the web address."

Environment data
.NET Core SDK (reflecting any global.json):
Version: 2.2
Runtime Environment:
OS Name: Windows
OS Version: 10.0.18363
OS Platform: Windows
RID: win10-x64

allan

I know I can install Editor libraries and use Left Join but that's like over $100 dollars.

If you are just using the server-side libraries, they are open source. It is only the Editor client-side code that has a commercial license.

See also this post - although that is mainly about the PHP libraries, but much also applies to the .NET libs.

Regards,
Allan

Jords2point0

Hi Allan. Thanks for your help so far.

But I have found I can not get the client to communicate with my Controller. I would like to know why?

I have followed the documentation for Editor LeftJoins and have attempted to base my code off the code under the Example heading as shown here https://editor.datatables.net/manual/net/joins.

You said to someone somewhere a few years back that the .NET examples in the Documentation are to communicate with WebAPI yes?

Well I don't use WebAPI so I don't know how to initiate a Client- to-Server Ajax request as I am using ASP.NET MVC Core. Could it be the DateTime field in my Registration model?

I thought it's ok as I am using a DataFormatString. I know I should probably change it into a string but that would mean recreating my database as I am using Entity Framework Code First Migrations unless there is another way?

If you or someone can help, please here is the code:

Registration\Index View:

    @model Pitcher.Models.Registration
    @{
        ViewData["Title"] = "Registrations";
    }   
    <h1>Registrations</h1>

    <p>
        <a asp-action="Create">Create New</a>
    </p>
    @section scripts{ 
        <script src="~/js/jquery.dataTables.min.js"></script>
        <script>    
        $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor( {
            ajax: "../../Controllers/RegistrationsController.cs",      
            table: "#registrationTable",   
            fields: [ {
                    label: "Registration Date:",
                    name: "Registration.RegistrationDate"
                }, {
                    label: "User:",
                    name: "User.UserFullname"
                }, {
                    label: "Job:",
                    name: "Job.JobTitle",
                    type: "select"
                }
            ]
        } );

        $('#registrationTable').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "@Url.Action("LeftJoinJobsAndUsersOntoRegistrations", "../../Controllers/RegistrationsController.cs")",
                type: 'POST'
            },
            columns: [
                {data: "Registration.RegistrationDate"},
                {data: "User.UserFullname"},
                {data: "Job.JobTitle"}
            ],
            select: true,
            buttons: [
                { extend: "/Registrations/Details/", editor: editor },
                { extend: "/Registrations/Edit",   editor: editor },
                { extend: "/Registrations/Delete", editor: editor }
            ]
        } );
    } );
        </script>
    } 

RegistrationsController:

    public IActionResult Index()
    {   
        return View();                 
    }

    public IActionResult LeftJoinJobsAndUsersOntoRegistrations()
    {
        string connectionString = _config.GetConnectionString("DefaultConnection");            

        using (var db = new Database("sqlserver", connectionString))
        {
            DtResponse response = new Editor(db, "Registration")
            .Model<Registration>("Registration")
            .Model<Registration>("User")
            .Model<Registration>("Job")
            .Field(new Field("Registration.RegistrationDate")
                .Options("User", "ID", "UserFullname")
                .Validator(Validation.DbValues(new ValidationOpts {Empty = false}))
            )
            .LeftJoin( "User", "Registration.UserID", "=", "User.UserFullname")
            //.Process(formData)
            .Data();
            return Json(response);
        }
    }

User model:

        public class User
        {      
            public int ID { get; set; }

            [Required]
            [StringLength(20, MinimumLength = 2, ErrorMessage = "* First Name be bettween 2 to 20 characters.")]
            [DataType(DataType.Text)]
            [Display(Name = "First Name")]
            [Column("UserFirstName")]
            public string UserFirstName { get; set; }   

            [Required]
            [StringLength(30, MinimumLength = 2, ErrorMessage = "* Last Name be bettween 2 to 30 characters.")]
            [DataType(DataType.Text)]
            [Display(Name = "Last Name")]
            [Column("UserLastName")]
            public string UserLastName { get; set; }        

            [Required]
            [StringLength(30, MinimumLength = 3, ErrorMessage = "Email address must be bettween 3 to 30 characters.")]
            [DataType(DataType.EmailAddress)]
            [Display(Name = "Email")]
            [Column("UserContactEmail")]
            public string UserContactEmail{get;set;}      

            // [Required(AllowEmptyStrings = true)]
            [Display(Name = "Phone Number")]
            [Phone()]
            [Column("UserPhoneNumber")]
            public string UserPhoneNumber{get;set;}

            [StringLength(37,ErrorMessage = "Address cannot be longer than 37 characters.")]
            [DataType(DataType.Text)]
            [Display(Name = "Address")]
            [Column("UserAddress")]
            public string UserAddress{get;set;}

            //This regular expression allows valid postcodes and not just USA Zip codes.        
            [Display(Name = "Post Code")]
            [Column("UserPostCode")][DataType(DataType.PostalCode)]
            public string UserPostCode { get; set; }

            [StringLength(15,ErrorMessage = "Country cannot be longer than 15 characters.")]
            [DataType(DataType.Text)]
            [Display(Name = "Country")]
            [Column("UserCountry")] 
            public string UserCountry {get;set;}


            [Phone()]
            [Display(Name = "Mobile Number")]
            [Column("UserMobileNumber")]
            public string UserMobileNumber {get;set;}

            [StringLength(3,ErrorMessage = "State cannot be longer than 3 characters.")]
            [DataType(DataType.Text)]
            [Display(Name = "State")]
            [Column("UserState")]
            public string UserState {get;set;}           

            public string UserFullname => string.Format("{0} {1}", UserFirstName, UserLastName);

            public ICollection<Registration> Registrations {get;set;}
        }

Registration Model:

public class Registration
{
    public int ID {get;set;}
    public int UserID { get; set; }
    public int JobID { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "User Start Date")]
    [Column("RegistrationDate")]
    public DateTime RegistrationDate {get;set;}


    public User User {get;set;}
    public Job Job {get;set;}
}

allan

//.Process(formData)

This is commented out, so yes, the server-side script will effectively do nothing.

Details on how to use the Editor server-side libraries with MVC.NET are available here.

In short you probably want to add

var formData = HttpContext.Request.Form;

like in the examples there.

Allan

Jords2point0

I did everything you said. I have created a variable to store the Httpcontext form data and uncommented this line

.Process(formData).

I still get the same problem. Nothing happens. But If I use a url action and put a breakpoint on it the Layout returns this line of code.

@RenderSection("Scripts", required: false).

The file path looks fine to me. Here is a photo of what the folder structure looks like and the files I am using presently including the RegistrationController and View\Registrations\Index view.

---

allan

ajax: "../../Controllers/RegistrationsController.cs",

You almost certainly don’t want the .cs there. In fact I think the whole path is wrong. It isn’t like PHP where you give it a specific file (if for no other reason than that each controller can contain multiple end points!).

You are using an @Action for the URL for the DataTables Ajax, but not the Editor one. Is there a reason for that? It looks like the same @Action could be used for both? What happens in the browser’s network console when you do that?

Allan

Jords2point0

The reason I am using the @Action property for the URL is because that's how I got it working when I was just using DataTables to render other tables.

But then I realised in order to render the navigational property from another table in the same view, I would have to use the Editor libraries and make a Left Join.

Ok so Chrome's network console is returning a bunch of files. I don't see any files to do with the controller or models. Only front end files. Also being returned 1 error and 1 warning. It says the same thing:

    jquery.js:3827 Uncaught TypeError: $.fn.dataTable.Editor is not a constructor
        at HTMLDocument.<anonymous> (VM263 Registrations:90)
        at mightThrow (jquery.js:3534)
        at process (jquery.js:3602)

I researched the error. It appears others who are getting the same error are supposed to pay for the client version but that shouldn't matter as I have installed the server version and can see it referenced in the project file.

Also have found error lies right here in my Index view on line 14 which is this.

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

The line of code in jquery.js line 3534 is this:

returned = handler.apply( that, args );

and on line 3602 it is this:

mightThrow();

allan

If you don’t need Editor, don’t initialise Editor on the client-side (which is where that error message is coming from). You don’t need the client-side Editor if you just want to use the server-side libraries to get the data from the database (the server-side libraries are open source, so you can use them without the proprietary client-side code).

Allan