Comments seem to lie a lot

[VictorioBerra]

Sample project JoinArrayController says:

    /// This example shows a very simple join using the `LeftJoin` method.
    /// Of particular note in this example is that the `JoinModel` defines two
    /// nested classes that obtain the data required from the two tables

JoinModelUsers and JoinModelSites are not nested classes:

using DataTables;

namespace EditorNetCoreDemo.Models
{
    public class JoinModelUsers
    {
        public string first_name { get; set; }

        public string last_name { get; set; }

        public string phone { get; set; }

        public int site { get; set; }

        public int manager { get; set; }
    }

    public class JoinModelSites
    {
        public string name { get; set; }
    }
}

I am trying to mirror the examples and get my code working and getting an "Object is not set to an instance of an object" error with no-stack trace on a simple LeftJoin and am struggling.

[AllanJard]

Sorry yes, the header comments in the examples are sometimes the result of duplicating the file and then modifying the content, but not the comment.

"Object is not set to an instance of an object"

Add .TryCatch(false).Debug(true) just before the .Process(...) call and it might give something a bit more useful. Failing that, perhaps you can show me the code you are using?

[VictorioBerra]

@AllanJard

My code does work if I remove the LeftJoin to PatchPartyDowntime. That is to say my code is working fine with the many-to-many with products. It broke when I started to configure the one-to-many to PatchPartyDowntime.

Schema:

CREATE TABLE [dbo].[InventoryServer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [PatchPartyDowntimeId] [int] NULL
    -- many more redacted
 CONSTRAINT [PK_InventoryServer] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_InventoryServer_PatchPartyDowntimeId] ON [dbo].[InventoryServer]
(
    [PatchPartyDowntimeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PatchPartyDowntime](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL
 CONSTRAINT [PK_PatchPartyDowntime] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Product](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL
        -- Many more redacted
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[InventoryServerProductLine](
    [InventoryServerId] [int] NOT NULL,
    [ProductId] [int] NOT NULL
 CONSTRAINT [PK_InventoryServerProductLine] PRIMARY KEY CLUSTERED 
(
    [InventoryServerId] ASC,
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InventoryServerProductLine_ProductId] ON [dbo].[InventoryServerProductLine]
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Code:

            var response = new Editor(db, "InventoryServer")
                .Model<InventoryServerModel>()
                .Field(new Field("InventoryServer.Id").Set(false)) // Id is DB autogenerated... do I need this?
                .Field(new Field("InventoryServer.PatchPartyDowntimeId")
                    // .SetFormatter(Format.IfEmpty(null)) // ?? I need to allow nulls (selecting none). Do I need this?
                    .Options(new Options()
                        .Table("PatchPartyDowntime")
                        .Value("Id")
                        .Label("Name")
                    )
                )
                .LeftJoin("PatchPartyDowntime", "PatchPartyDowntime.Id", "=", "InventoryServer.PatchPartyDowntimeId")
                .MJoin(new MJoin("Product")
                    .Link("InventoryServer.Id", "InventoryServerProductLine.InventoryServerId")
                    .Link("Product.Id", "InventoryServerProductLine.ProductId")
                    .Order("Product.Name")
                    .Model<Product>()
                    .Field(new Field("Id")
                        .Options(new Options()
                            .Table("Product")
                            .Value("Id")
                            .Label("Name")
                        )
                    )
                )
                .TryCatch(false)
                .Debug(true)
                .Process(Request)
                .Data();

    // Nesting is required for MJoin
    public class InventoryServerModel
    {
        public class InventoryServer
        {
            public int Id { get; set; }

            public string Name { get; set; }

            public int  PatchPartyDowntimeId { get; set; }
        }

        // one-to-many
        public class PatchPartyDowntime
        {
            public int Id { get; set; }

            public string Name { get; set; }
        }
    }

    // This defines the field for the linked table
    // many-to-many
    public class Product
    {
        public int Id { get; set; }

        public string Name { get; set; }
    }

Stack Trace:

   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(HttpRequest request, String culture)
   at WuitCMDB.API.Controllers.InventoryServersController.InventoryServers() in C:\Source\BBEEWT\ASPNET CORE\iamm.web.service.wuitcmdb2\src\WuitCMDB.API\Controllers\InventoryServersController.cs:line 21

I whipped this up quick so SQL code might not be perfect.

[VictorioBerra]

More questions about my code compared to your sample:

1. Your samples never mention id. My tables all have Id (capital I). Is there a way for me to omit public int Id { get; set; }? I would like my code to be cleaner like the samples.

2. Does my above approach for Producs allow "nested editing" on the client side, or do I need to configure the join and model differently?

[AllanJard]

Yes, drop the Id part from your models and the field name. Unless you want the end user to see it, you can drop it. But since your id column isn't id, you'll need to pass the optional third parameter to the constructor as discussed here:

var response = new Editor(db, "InventoryServer", "Id")

For nested editing like in this example, you'll need two controllers - one for the parent table, one for the child. The .NET download package for Editor includes that example and the controllers for it.

// .SetFormatter(Format.IfEmpty(null)) // ?? I need to allow nulls (selecting none). Do I need this?

Yes. HTTP variables are always strings. So there is no way to tell if null is actually null or a string "null". The formatter allows an empty string to be treated as null when written to the database. This does mean you can't have an empty string though, which may or may not be okay for your application).

[VictorioBerra]

@AllanJard var response = new Editor(db, "InventoryServer", "Id") only handles the top-level table right? Regarding my above code, how could I omit Id for MJoin and LeftJoin tables?

[VictorioBerra]

So I got the code working... not sure how yet lol

    // Nesting is required for MJoin
    public class InventoryServerModel
    {
        public class InventoryServer
        {
            public int Id { get; set; }

            public string Name { get; set; }

            public bool IsVirtual { get; set; }

            public int? PatchPartyDowntimeId { get; set; }
        }    

        // one-to-many
        public class PatchPartyDowntime
        {
            public int Id { get; set; }

            public string Name { get; set; }
        }
    }

    // This defines the field for the linked table
    // many-to-many
    public class Product
    {
        public int Id { get; set; }

        public string Name { get; set; }
    }

// ...

            var response = new Editor(db, "InventoryServer", "Id")
                .Model<InventoryServerModel>()
                .Field(new Field("InventoryServer.Id").Set(false)) // Id is DB autogenerated... do I need this?
                .Field(new Field("InventoryServer.PatchPartyDowntimeId")
                    .SetFormatter(Format.IfEmpty(null)) // HTTP Variables are always strings, convert empty PatchPartyDowntimeId to null.
                    .Options(new Options()
                        .Table("PatchPartyDowntime")
                        .Value("Id")
                        .Label("Name")
                    )
                )
                .LeftJoin("PatchPartyDowntime", "PatchPartyDowntime.Id", "=", "InventoryServer.PatchPartyDowntimeId")
                .MJoin(new MJoin("Product")
                    .Link("InventoryServer.Id", "InventoryServerProductLine.InventoryServerId")
                    .Link("Product.Id", "InventoryServerProductLine.ProductId")
                    .Order("Product.Name")
                    .Model<Product>()
                    .Field(new Field("Id")
                        .Options(new Options()
                            .Table("Product")
                            .Value("Id")
                            .Label("Name")
                        )
                    )
                )
                .TryCatch(false)
                .Debug(true)
                .Process(Request)
                .Data();