Adding a LeftJoin in .Net Editor causes updates to fail?
Adding a LeftJoin in .Net Editor causes updates to fail?
HollyAllen
Posts: 3Questions: 1Answers: 0
I'm not sure how to link to a test case for this, since it's on the server side. Please let me know if there's a better way to describe this.
I've been struggling with this problem for several days now, and I've boiled down the test case to its bare essentials. Simply put, I can use Editor to update a single table in my database. However, as soon as I add a join, it stops issuing an UPDATE statement when I try to edit.
Here is what works:
var table;
$(document).ready(function () {
var editor = new $.fn.dataTable.Editor({
ajax: '/api/Access',
table: '#DataTable',
idSrc: "AccessPerson.AccessPersonId",
fields: [
{ label: 'Person', name: 'AccessPerson.Person' },
{ label: 'Access', name: 'AccessPerson.AccessId', /*type: 'select', placeholder: "Select access type..."*/ },
{ label: 'OfficeId', name: 'AccessPerson.OfficeId' }
]
});
table = $('#DataTable').DataTable({
ajax:
{
url: "/api/Access",
type: "POST",
},
autoWidth: false,
dom: "Bltip",
paging: true,
columns: [
{ data: "AccessPerson.Person", class: "text-search" },
{ data: "AccessPerson.AccessId", class: "text-search", editField: "AccessPerson.AccessId" },
{ data: "AccessPerson.OfficeId", class: "text-search" }
],
colReorder: false,
select: true,
buttons: [
{ extend: 'createInline', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor }
]
});
});
In my ApiController:
using (var db = new Database("sqlserver", connectionString))
{
var response = new Editor(db, "meta.AccessPerson", "AccessPersonId")
.Model<AccessData>()
.Debug(true)
.Process(request)
.Data();
return Json(response);
}
My data object:
public class AccessData
{
public class AccessPerson
{
public int AccessPersonId { get; set; }
[EditorSet(Field.SetType.Both)]
public int AccessId { get; set; }
public string Person { get; set; }
public int OfficeId { get; set; }
}
}
With the code in this state, I can edit an AccessId in the AccessPerson table just fine. I have to know the ID of the Access I want, so it isn't really a workable solution, but the point is that Editor issues an UPDATE statement against the database, followed by a SELECT to get the updated row, and everything works as expected.
However, if I update the controller with a LeftJoin:
using (var db = new Database("sqlserver", connectionString))
{
var response = new Editor(db, "meta.AccessPerson", "AccessPersonId")
.Model<AccessData>()
.LeftJoin("meta.Access", "Access.AccessId = AccessPerson.AccessId")
.Debug(true)
.Process(request)
.Data();
return Json(response);
}
Even without changing anything else -- the data model or the front end (which is still showing IDs) -- it breaks my ability to update. More specifically, I can see in the debugger that the requested update is passed in the Http Request, but SQL Profiler tells me that no UPDATE is issued against the database. The only query performed in this case is a SELECT of the row I tried to edit.
I've tested everything I can think of and worked through the available examples online, but once that LeftJoin is in there, I can't get Editor to issue an UPDATE against the database. Am I missing something incredibly obvious?
This question has an accepted answers - jump to answer
Answers
The only thing I can immediately think of is the
meta.AccessPersoncausing an issue.When you trigger an update from the client-side, does the JSON response from the server show anything? Hopefully it will show the SQL that was generated and executed - or at least attempted to execute! That might give us a clue.
Thanks,
Allan
I know, it's weird, right? Here's the JSON response I got after the LeftJoin was added:
I noticed that there was a "meta" tag in there, and although it doesn't seem like it should conflict, paranoia prompted me to try making a different schema called "foo" and doing the same test against that. No difference.
In contrast, here's the JSON response from the exact same test, but with the LeftJoin commented out in the controller:
Both of the SELECTs are fine (pulled out below for easier reading). But the UPDATE isn't even generated as long as the LeftJoin is there.
SELECT [AccessPersonId] as 'AccessPersonId',
[AccessPerson].[AccessPersonId] as 'AccessPerson.AccessPersonId',
[AccessPerson].[AccessId] as 'AccessPerson.AccessId',
[AccessPerson].[Person] as 'AccessPerson.Person',
[AccessPerson].[OfficeId] as 'AccessPerson.OfficeId'
FROM [meta].[AccessPerson]
WHERE [AccessPersonId] = @where_0
SELECT [meta].[AccessPerson].[AccessPersonId] as 'meta.AccessPerson.AccessPersonId',
[AccessPerson].[AccessId] as 'AccessPerson.AccessId',
[AccessPerson].[AccessPersonId] as 'AccessPerson.AccessPersonId',
[AccessPerson].[Person] as 'AccessPerson.Person',
[AccessPerson].[OfficeId] as 'AccessPerson.OfficeId'
FROM [meta].[AccessPerson]
LEFT JOIN [meta].[Access] ON Access.AccessId = AccessPerson.AccessId
WHERE [meta].[AccessPerson].[AccessPersonId] = @where_0
Allan, I think you're right that it's the explicit schema that's screwing with things. When I changed the connection string to use a database user with a default schema of "meta", and then removed the references to the schema in the Editor declaration, I was suddenly able to update even with the LeftJoin in place.
Long-term that isn't an ideal solution for me -- I'll need to be able to access multiple schemas in this application. But hopefully that piece of information helps?
That does help thank you, and also good to know you have a short term workaround. A medium term workaround for handling multiple schemas would be to use a VIEW and have that construct the data as needed from a more complex expression.
However, yes, this is something we need to address in the Editor libraries and I've added a bug to our tracker for this - thank you. I expect it to be part of Editor 2.1.
Regards,
Allan