Designing INNER JOIN for Asp.net

dynasoft

Hi

I' trying to design an INNER JOIN and adapt the example given here https://datatables.net/forums/discussion/comment/116150/#Comment_116150 for MVC, but getting some errors. The INNER JOIN has to be done on CustomerVoiceCLI.id = CustomerVoiceCLIDetails.CustomerVoiceCLIIndex. I'd be grateful for some help. Many thanks.

• Server:

          using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
          {
              editor = new Editor(db, "CustomerVoiceCLI").Model<CustomerSNsDBModel>();
              editor.Field(new Field("CustomerVoiceCLI.id")
                  .Options(new Options()
                      .Table("CustomerVoiceCLIDetails")
                      .Value("CustomerVoiceCLIDetails.CustomerVoiceCLIIndex")
                  )
              );
              editor.Field(new Field("CustomerVoiceCLI.CustID")
                  .Set(true)
                  .SetValue(lngContIdx)
              );
              editor.Field(new Field("CustomerVoiceCLI.CLI")
                  .SetFormatter(Format.IfEmpty(null))
              );
              editor.Where("CustomerVoiceCLI.CustID", lngContIdx);
              editor.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=");
              editor.TryCatch(false);
              editor.Debug(true);
              editor.Process(formData);
              editor.Data();
          }
  

• Model:

  public class CustomerSNsDBModel
  {
  public class CustomerVoiceCLI
  {
  public long id { get; set; }
  public long CustID { get; set; }
  public string CLI { get; set; }
  }

  public class CustomerVoiceCLIDetails
  {
      public long id { get; set; }
      public long CustomerIndex { get; set; }
      public long CustomerVoiceCLIIndex { get; set; }
      public string CLIDetails { get; set; }
  }
  

  }
  }

• Error:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=The multi-part identifier "CustomerVoiceCLIDetails.id" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CustomerIndex" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CLIDetails" could not be bound.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>

dynasoft

• Server:
  using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
  {
  editor = new Editor(db, "CustomerVoiceCLI", "CustomerVoiceCLI.id")
  .Model<CustomerSNsDBModel.CustomerVoiceCLIDetails>("CustomerVoiceCLIDetails")
  .Model<CustomerSNsDBModel.CustomerVoiceCLI>("CustomerVoiceCLI");
  editor.Field(new Field("CustomerVoiceCLI.id")
  .Options(new Options()
  .Table("CustomerVoiceCLI")
  .Value("id")
  .Label("CLI")
  )
  );
  editor.Field(new Field("CustomerVoiceCLI.CustID")
  .Set(true)
  .SetValue(lngContIdx)
  );
  editor.Field(new Field("CustomerVoiceCLI.CLI")
  .SetFormatter(Format.IfEmpty(null))
  );
  editor.Where("CustomerVoiceCLI.CustID", lngContIdx);
  editor.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=");
  editor.TryCatch(false);
  editor.Debug(true);
  editor.Process(formData);
  editor.Data();
  }

I'm now getting this:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=The multi-part identifier "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex" could not be bound.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>

I'm just trying to make a simple IINER JOIN on CustomerVoiceCLI.id = CustomerVoiceCLIDetails.CustomerVoiceCLIIndex

Thank you.

allan

The editor.Where() method is only used for simple literal comparisons. You need to use an anonymous function to be able to compare against another field in the db - see the docs here. e.g.:

editor.Where(
  q => q.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=", false);
);

Allan

dynasoft

Many thanks