Ambiguous Columns when joining tables using DataTables .NET Editor

joeyiodice

I have tables that have columns with the same name and I am getting Ambiguous column errors.
I fixed this on another page by just renaming the columns in my database but I really don't feel like I should have to keep doing this, especially since I am specifying the table with the column name. Is Editor striping the table names out in the raw SQL?

The ambiguous columns are PrimaryDoctorId, Mileage, & ContractPercentage

Here is my Web API Controller code:

        [Route("api/DataTables/ScheduledSites")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult ScheduledSites()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "ScheduledSites", "Id")
                    .Model<ScheduledSitesDataTableModel>()
                    .Field(new Field("ScheduledSites.ScheduledDate")
                        .Validator(Validation.NotEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("ScheduledSites.Mileage")
                        //.DbField("ScheduledSitesMileage")
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.Numeric())
                    )
                    .Field(new Field("ScheduledSites.PrimaryDoctorId")
                        .Options("Doctors", "Id", "DisplayName")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("ScheduledSites.LocationId")
                        .Options("Locations", "Id", "Description")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("ScheduledSites.StatusId")
                        .Options("Statuses", "Id", "Status")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("Doctors.DisplayName"))
                    .Field(new Field("Locations.Description"))
                    .Field(new Field("Statuses.Status"))
                    .LeftJoin("Doctors", "ScheduledSites.PrimaryDoctorId", "=", "Doctors.Id")
                    .LeftJoin("Locations", "ScheduledSites.LocationId", "=", "Locations.Id")
                    .LeftJoin("Statuses", "ScheduledSites.StatusId", "=", "Statuses.Id")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }

Here is my Javascript:

            var scheduledSitesEditor = new $.fn.dataTable.Editor({
                ajax: '/api/DataTables/ScheduledSites/',
                table: '#ScheduledSitesTable',
                fields: [
                    {
                        "label": "Scheduled Date:",
                        "name": "ScheduledSites.ScheduledDate",
                        "type": 'datetime',
                        "format": 'MM/DD/YYYY'
                    },
                    {
                        "label": "Location:",
                        "name": "ScheduledSites.LocationId",
                        "type": "select"
                    },
                    {
                        "label": "Doctor:",
                        "name": "ScheduledSites.PrimaryDoctorId",
                        "type": "select"
                    },
                    {
                        "label": "Status:",
                        "name": "ScheduledSites.StatusId",
                        "type": "select"
                    },
                    {
                        "label": "Mileage:",
                        "name": "ScheduledSites.Mileage"
                    }
                ]
            });

            var scheduledSitesTable = $('#ScheduledSitesTable').DataTable({
                responsive: true,
                ajax: '/api/DataTables/ScheduledSites/',
                columns: [
                    {
                        data: null,
                        defaultContent: '',
                        className: 'select-checkbox',
                        orderable: false
                    },
                    {
                        "data": "ScheduledSites.ScheduledDate",
                        "render": function (data, type, row) {
                            return moment(data).format('MM/DD/YYYY');
                        }
                    },
                    {
                        "data": "Locations.Description", "editField": "ScheduledSites.LocationId"
                    },
                    {
                        "data": "Doctors.DisplayName", "editField": "ScheduledSites.PrimaryDoctorId"
                    },
                    {
                        "data": "Statuses.Status", "editField": "ScheduledSites.StatusId"
                    },
                    {
                        "data": "ScheduledSites.Mileage"
                    },
                    {
                        data: null,
                        className: "center",
                        defaultContent: '<a href="" class="editor_productions">Productions</a> / <a href="" class="editor_collections">Collections</a>'
                    }
                ],
                select: {
                    style: 'os'
                },
                paging: false
            });

Here is my data models:

    //Scheduled Site Table
    public class ScheduledSiteDataTableModel : EditorModel
    {
        public int LocationId { get; set; }
        public DateTime ScheduledDate { get; set; }
        public int StatusId { get; set; }
        public int PrimaryDoctorId { get; set; }
        public decimal? Mileage { get; set; }
        public int NumOfEmployees { get; set; }
        public int NumOfVehicles { get; set; }
        public decimal ContractPercent { get; set; }
    }

    // Locations Table
    public class LocationDataTableModel : EditorModel
    {
        public string Description { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public int PrimaryDoctorId { get; set; }
        public decimal? Mileage { get; set; }
        public decimal? ContractPercent { get; set; }
        public string PrimaryContactName { get; set; }
        public string PrimaryContactEmail { get; set; }
    }

    //Doctors Table
    public class DoctorDataTableModel :EditorModel
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string DisplayName { get; set; }
    }

joeyiodice

NVM, figured it out.

Had to use the .DbField setting and take out the table name from the main Field setting.
Documentation kind of sucks when it comes to this though.

Only thing I could find was this:
https://editor.datatables.net/docs/1.4.0/net/html/985eba26-3b28-4209-c6d1-9f4f9a36723e.htm

allan

That shouldn't be required (as you suggest)! What I think (/know - I've fairly confident) is happening here is that the model being used is interfering with the fields that are defined by the Field() class and is adding fields which do not have a table identifier.

ScheduledSitesDataTableModel is being used to tell Editor want fields it should use. So it adds fields at the top level such as PrimaryDoctorId.

To overcome this you need to use nested classes as documented here.

It is a good point though - it isn't directly intuitive this since nested classes aren't really used all that often in the .NET world. I'm going to introduce new options in Editor 1.7 to help address this (multiple models with an optional table prefix).

Thanks for the feedback.

Allan

joeyiodice

Thanks for the feedback Allan.
The nested tables make sense and I will give them a try when I get a chance.

So I should just update it to something like this?

public class ScheduledSiteDataTableModel : EditorModel
{
    public class ScheduleSites : EditorModel
    {
         public int LocationId { get; set; }
         public DateTime ScheduledDate { get; set; }
         public int StatusId { get; set; }
         public int PrimaryDoctorId { get; set; }
         public decimal? Mileage { get; set; }
         public int NumOfEmployees { get; set; }
         public int NumOfVehicles { get; set; }
         public decimal ContractPercent { get; set; }
    }

    public class Locations : EditorModel
    {
        public string Description { get; set; }
    }

    public class Doctors : EditorModel
    {
        public string DisplayName {get; set;}
    }

    public class Statuses : EditorModel
    {
        public string Status {get; set;}
    }
    
}

allan

Looks good to me!

Allan