data from both tables not returning after table join.

[JuiceyBrucey]

Hi,
7.2.1.0
SR 16
I have butchered the code below to try to make it return a list from a table join, but it only returns the list from the IN_DebtorSpecificPrice table and not the joined table as well.
I think this has something to do with the response model, but as this is not my native language, I am a bit confused.
Can you tell me what I am doing wrong?

Code: Select all

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite;

namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
   public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
   {
      public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost  AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
      {
           AppHost.RegisterService<CustomServices>();       
         AppHost.Routes.Add(typeof(DebtorGetAllSpecificPricesRequest), "/Custom/{DebtorID}/SpecificPrices", "GET", "Retrieves all debtor specific prices for a debtor.", "");
      }
   }

   #region "Requests"
      [Serializable()]
      [ApiResponse(200, "Transactions read OK")]
      [ApiResponse(401, "Not authenticated")]
      [ApiResponse(404, "No debtor with the Account No. provided was found")]
      public class DebtorGetAllSpecificPricesRequest : IReturn<DebtorGetAllSpecificPricesResponse>
      {
          public string DebtorID { get; set; }
      }
   #endregion
      
   #region "Responses"
      [Serializable()]
      public class DebtorGetAllSpecificPricesResponse
      {
         public List<IN_DebtorSpecificPrice> DebtorPriceList {get; set;}      
      }
   #endregion
      
   #region "Services"   
      public class CustomServices : Service
      {
         [Authenticate]
          public DebtorGetAllSpecificPricesResponse Get(DebtorGetAllSpecificPricesRequest request)
         {                                         
              //Join<Customer, QuoteHeader>((c, q) => c.Id == q.CustomerId)
            var query = Db.From<IN_DebtorSpecificPrice>().RightJoin<IN_Main>((main, ds) => main.InventoryID == ds.InventoryID).Where<IN_DebtorSpecificPrice>((Prices)=>Prices.DebtorID == request.DebtorID);
              return new DebtorGetAllSpecificPricesResponse() { DebtorPriceList =  Db.Select(query)} ;         
         }
      }
   #endregion
}


[Mike.Sheen]

Try debugging and examining what Db.Select(query) evaluates to before returning from the method.

I think your right join smells, but also I think your implicit cast to DebtorGetAllSpecificPricesResponse() is resulting in an empty object being serialised.

[SBarnes]

You can also do this if you are debugging which will give you the exact SQL that is being created which I find often helps if you do it before the select call.

Code: Select all

 string sql = query.SelectExpression + Environment.NewLine + query.FromExpression + Environment.NewLine + query.WhereExpression;