MVC error on MJoin

MVC error on MJoin

montoyammontoyam Posts: 568Questions: 136Answers: 5
edited April 2021 in DataTables

I am hoping there is another set of eyes that can see what is going on here

    public class DevicesController : ApiController
    {
        [HttpGet]
        [HttpPost]
        public IHttpActionResult Devices()
        {
            var request = HttpContext.Current.Request;
            var settings = System.Configuration.ConfigurationManager.ConnectionStrings["msSql"];
            using (var db = new Database("sqlserver", settings.ConnectionString))
            {
                var response = new Editor(db, "CallManagerDevices_new('3/1/2021') as Devices", "PKID")
                    .Model<DevicesModel>("Devices")
                    .LeftJoin("CallManager_Lines as Lines", "Devices.PKID", "=", "Lines.FKDEVICE")
                        .Field(new Field("Lines.DIRECTORY_NUMBER"))
                        .Field(new Field("Lines.VOICEMAILPROFILE"))
                    .MJoin(new MJoin("CallManager_Lines")
                        .Link("CallManager_Lines.FKDEVICE", "Devices.PKID")
                        .Field(new Field("VOICEMAILPROFILE"))
                    )
                    .Where("Lines.NUMPLANINDEX",1)
                    .Process(request)
                    .Data();
                return Json(response);
            }
        }
    }

I get an error when I have the mjoin. if I comment out the mjoin the data loads just fine.

Object reference not set to an instance of an object.

Here is a sample of the return with the MJoin commented out:

0: {DT_RowId: "row_8b66def1-1608-1d39-50bf-0bee3e5ccd07",…}
DT_RowId: "row_8b66def1-1608-1d39-50bf-0bee3e5ccd07"
Devices: {CM_ID: 47907, PKID: "8b66def1-1608-1d39-50bf-0bee3e5ccd07", DEVICE_NAME: "AND81419D108000",…}
Lines: {DIRECTORY_NUMBER: "84007", VOICEMAILPROFILE: "NoVoiceMail"}

Notice how the mjoin is basically the same as the LeftJoin. I MUST have a syntax error but I can't see it. Sorry, I can't supply a link to the page and I'm not sure how to reproduce this on the dataTables test site.

Answers

  • tangerinetangerine Posts: 3,365Questions: 39Answers: 395
    edited April 2021

    leftJoin, Mjoin - case sensitivity.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    this is a mvc/c# project. MJoin is used there.

  • tangerinetangerine Posts: 3,365Questions: 39Answers: 395

    Sorry - my mistake.

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    Hi,

    It isn't immediately obvious what is going wrong unfortunately. I suspect it isn't an error in your configuration but rather in the Editor Mjoin, but I'm not sure what as I haven't been able to reproduce it myself.

    Immediately before the .Process(request) could you add .Debug(true).TryCatch(false).

    Now when the error occurs, it should break and show some more information about where the error is actually happening. Could you let me know what you find?

    Thanks,
    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited April 2021

    here is the stacktrace

       at DataTables.MJoin.Data(Editor editor, DtResponse response)
       at DataTables.Editor._Get(Object id, DtRequest http)
       at DataTables.Editor._Process(DtRequest data)
       at DataTables.Editor.Process(DtRequest data)
       at DataTables.Editor.Process(NameValueCollection data, String culture)
       at DataTables.Editor.Process(HttpRequest request, String culture)
       at CallManager.Controllers.DevicesController.Devices() in V:\SBT\APPL\Application Development\dataTables\CallManager\CallManager\CallManager\Controllers\CallManagerDataController.cs:line 31
       at lambda_method(Closure , Object , Object[] )
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_1.<GetExecutor>b__3(Object instance, Object[] methodParameters)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()
    
  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    Thank you - I think I might know what is going wrong:

    CallManagerDevices_new('3/1/2021')
    

    Does that result in new details each time it is called? If so, that is going to cause a problem here.

    The way MJoin works in Editor's .NET libraries is it makes the main query on the host table as normal and will then make a second query, joining the child table (optionally with a link table) with the parent table to read only the required information from the child.

    I'm afraid I don't think your use case is going to work here.

    What does CallManagerDevices_new do? Does it insert a new row, or something else?

    Thanks,
    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    the name "_new" can be deceiving. I have an "old" version and a "new" version of a User Defined function to return records. They are only select statements in the end, no inserts. I can probably change it to pull straight from the table or view with a where statement instead of using a user defined function though. Let me try that and get back with the results.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I have changed it to a simple view, and I am still getting an error when using MJoin

    public class DevicesController : ApiController
        {
            [HttpGet]
            [HttpPost]
            public IHttpActionResult Devices()
            {
                var request = HttpContext.Current.Request;
                var settings = System.Configuration.ConfigurationManager.ConnectionStrings["msSql"];
                using (var db = new Database("sqlserver", settings.ConnectionString))
                {
                    var response = new Editor(db, "vw_CallManagerDevices as Devices", "PKID")
                        .Model<DevicesModel>("Devices")
                        .LeftJoin("CallManager_Lines as Lines", "Devices.PKID", "=", "Lines.FKDEVICE")
                            .Field(new Field("Lines.DIRECTORY_NUMBER"))
                            .Field(new Field("Lines.VOICEMAILPROFILE"))
                        .MJoin(new MJoin("CallManager_Lines")
                            .Link("CallManager_Lines.FKDEVICE", "Devices.PKID")
                            .Field(new Field("FKDEVICE"))
                        )
                        .Where("Lines.NUMPLANINDEX",1)
                        .Debug(true).TryCatch(false)
                        .Process(request)
                        .Data();
                    return Json(response);
                }
            }
        }
    
This discussion has been closed.