searchBuilder with serverside scripting without using editor library

sarooptrivedi

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem: Is it possible that to use searchBuilder for serverside scripting without using editor library in .net core. My company allow us to use datatables.net but not it's editor library. I could not find single example. please give some small example.

colin

The Editor server-side scripts are open-source. Is there a reason why you don't want to use them? Here is an example with Editor, but the same example would work the same without the Editor functionality on the client. This blog post explains the process in more detail,

Colin

sarooptrivedi

Is it paid version? My company only approve free free free.

sarooptrivedi

I tried the Library but it is not quite mature. Some of my observation.

1. Library not support the entity framework
2. It is stuck with Primary Key of ID, Id, and id
   https://datatables.net/forums/discussion/comment/195203/#Comment_195203
3. I have table with four different tables associated with foreign key. all tables primary key is Id and table has ID

4. public class ClassA
   {
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key]
   public int ID { get; set; }
   public string Name { get; set; }

   public ClassB Items{ get; set; }
   [ForeignKey("Id")]
   public int ClassBId{ get; set; }

   public ClassC Product{ get; set; }
   [ForeignKey("Id")]
   public int ClassCId{ get; set; }
   }
   public class ClassB
   {
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key]
   public int Id { get; set; }
   public string Name { get; set; }

   }
   public class ClassC
   {
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key]
   public int Id { get; set; }
   public string Name { get; set; }
   }

My final expectation is Id,Name,ClassB.Name,ClassC.Name

If you can help me with Controller Code with Editor library then great help.

sandy

Hi @sarooptrivedi ,

1. You're right. The Editor libraries do not support the Entity Framework. But they do support .NET that it is built upon so I suspect that you will still be able to get something going using the Editor Libraries.
2. I don't think you understand that post correctly. There I am pointing out how to set a custom primary key for the Editor instance to use. It is not restricted to ID, Id or id.
3. That shouldn't be an issue as the Editor Libraries support joins. See the blog post that colin linked to above (specifically here).

Without knowing more about your database I would guess that your controller is going to look something like this...

var response = new Editor(db, "ClassA", pkey="ID")
    .Model<ClassModel>()
    .Field(new Field("ClassA.ID"))
    .Field(new Field("ClassA.Name"))
    .Field(new Field("ClassB.Name"))
    .Field(new Field("ClassC.Name"))
    .Write(False)
    .LeftJoin("ClassB", "ClassB.Id", "=", "ClassA.ClassBId")
    .LeftJoin("ClassC", "ClassC.Id", "=", "ClassA.ClassCId")
    .Process(Request)
    .Data()
 
return Json(response)

... assuming your tables are called 'ClassA', 'ClassB' and 'ClassC'?

Hopefully this can get you on the way to working something out. I'd strongly suggest that you work through the blog post.

sarooptrivedi

I followed your instructions already but still have same ID issue.

One more observation what is your overall design in response. How I design the expected Json outside without using your all query function. is there any specific structure require to write the datatable?

This is my code sample

     var response = new Editor(db, "ClassA","Id")
         .Model<ClassA>("ClassA")
         .Model<ClassB>("ClassB")
         .Model<ClassC>("ClassC")
         .Model<ClassD>("ClassD")
         .Model<ClassE>("ClassE")
         .Model<ClassF>("ClassF")
        .Field(new Field("ClassA.ID").Set(false))
         .Field(new Field("ClassA.InnerID"))
         .Field(new Field("ClassA.A1"))
         .Field(new Field("ClassA.B1"))
         .Field(new Field("ClassA.CalculationDate "))
         .Field(new Field("ClassA.Comments"))
         .Field(new Field("ClassA.Misc"))
         .Field(new Field("ClassA.IsActive"))
          .Field(new Field("ClassA.CreationDate"))
         .Field(new Field("ClassA.CreationUser"))
         .Field(new Field("ClassA.UpdatedDate"))
         .Field(new Field("ClassA.UpdatedDate "))

         .LeftJoin("ClassB as SamplerType", "ClassA.SamplerTypeId", "=", "SamplerType.Id")
         .Field(new Field("SamplerType.Name").Options("SamplerType", "Id","Name"))
          .LeftJoin("ClassE as MakeModel", "ClassA.SamplerTypeId", "=", "MakeModel.Id")
           .Field(new Field("MakeModel.Make").Options("MakeModel", "Id", "Make"))
         .Field(new Field("MakeModel.Model").Options("MakeModel", "Id", "Model"))
           .LeftJoin("ClassC as Calibrator", "ClassA.SamplerTypeId", "=", "Calibrator.Id")
             .Field(new Field("Calibrator.Name").Options("Calibrator", "Id", "Name"))
            .LeftJoin("ClassF as Owner", "ClassA.SamplerTypeId", "=", "Owner.Id")
            .Field(new Field("Owner.Name").Options("Owner", "Id", "Name"))
            .LeftJoin("ClassD as Location", "ClassA.SamplerTypeId", "=", "Location.Id")
             .Field(new Field("Location.Name").Options("Location", "Id", "Name"))

         .Process(Request)
         .Data();

sarooptrivedi

What is require in Json? Should I design outside without complex query? What is Json structure required. Below my actual code. I just change the tables name

     var response = new Editor(db, "ClassA","Id")
         .Model<ClassA>("ClassA")
         .Model<ClassB>("ClassB")
         .Model<ClassC>("ClassC")
         .Model<ClassD>("ClassD")
         .Model<ClassE>("ClassE")
         .Model<ClassF>("ClassF")
        .Field(new Field("ClassA.ID").Set(false))
         .Field(new Field("ClassA.InnerID"))
         .Field(new Field("ClassA.A1"))
         .Field(new Field("ClassA.B1"))
         .Field(new Field("ClassA.CalculationDate "))
         .Field(new Field("ClassA.Comments"))
         .Field(new Field("ClassA.Misc"))
         .Field(new Field("ClassA.IsActive"))
          .Field(new Field("ClassA.CreationDate"))
         .Field(new Field("ClassA.CreationUser"))
         .Field(new Field("ClassA.UpdatedDate"))
         .Field(new Field("ClassA.UpdatedDate "))    
         .LeftJoin("ClassB as SamplerType", "ClassA.SamplerTypeId", "=", "SamplerType.Id")
         .Field(new Field("SamplerType.Name").Options("SamplerType", "Id","Name"))
          .LeftJoin("ClassE as MakeModel", "ClassA.SamplerTypeId", "=", "MakeModel.Id")
           .Field(new Field("MakeModel.Make").Options("MakeModel", "Id", "Make"))
         .Field(new Field("MakeModel.Model").Options("MakeModel", "Id", "Model"))
           .LeftJoin("ClassC as Calibrator", "ClassA.SamplerTypeId", "=", "Calibrator.Id")
             .Field(new Field("Calibrator.Name").Options("Calibrator", "Id", "Name"))
            .LeftJoin("ClassF as Owner", "ClassA.SamplerTypeId", "=", "Owner.Id")
            .Field(new Field("Owner.Name").Options("Owner", "Id", "Name"))
            .LeftJoin("ClassD as Location", "ClassA.SamplerTypeId", "=", "Location.Id")
             .Field(new Field("Location.Name").Options("Location", "Id", "Name"))                 
         .Process(Request)
         .Data();

allan

The SearchBuilder docs include information on the data being sent to, and received from the server. Specifically it notes that the data submitted is defined by searchBuilder.getDetails().

The response from the server is defined here and as the docs note:

SearchBuilder itself does not require any additional data from the server

Allan