MVC error on MJoin

montoyam

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.

tangerine

leftJoin, Mjoin - case sensitivity.

montoyam

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

tangerine

Sorry - my mistake.

allan

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

montoyam

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()

allan

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

montoyam

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.

montoyam

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);
            }
        }
    }