Jiwa

Hi,
I have the following query but need to limit the fields so that it runs fast and I do not get data I dont need.

public MyGetInventoryBlockResponse Get(MyGetInventoryBlockRequest request)
{
var query = Db.From<IN_Main>().Join<IN_Main, IN_SellingPrices>((main, prices) => main.InventoryID == prices.InventoryID).Limit(request.StartPosition,request.EndPosition);
return new MyGetInventoryBlockResponse() { RecordResults = Db.Select(query) } ;
}

What is the best way of doing this?
Cheers

JuiceyBrucey wrote:need to limit the fields so that it runs fast and I do not get data I dont need.

Without knowing what your constraints and requirements are, it's hard to answer that.

What I think your ormlite is currently doing is getting rows x to y from a query like:

Code: Select all

SELECT *
FROM IN_Main
JOIN IN_SellingPrices ON IN_Main.InventoryID = IN_SellingPrices.InventoryID
ORDER BY IN_Main.PartNo
OFFSET request.StartPosition ROWS
FETCH NEXT request.EndPosition - request.StartPosition ROWS ONLY

So you're asking for all rows between request.StartPosition and request.EndPosition - that may be a lot or a little - depending on what those values provided are.

If you run a SQL Profiler trace to capture the actual SQL query issued by your service, and then post that here along with what you would want your SQL query to look like I can try to reverse-engineer that and give you the ormlite syntax to match.

EDIT - just realised you said you wanted to limit the fields returned... my mind read rows for some reason.

Can you provide your MyGetInventoryBlockRequest and MyGetInventoryBlockResponse classes? Even better would be a small isolated plugin with all I need to get your service + route working.

Yes, to clarify, I dont want all fields/columns returned. I want to specify which fields to return and not any others.

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(MyGetInventoryBlockRequest), "/Custom/GetInventoryBlock/{StartPosition}/{EndPosition}", "GET", "Retrieves a block of inventory specified by start and end position.", "");
}
}

#region "Requests"
[Serializable()]
[ApiResponse(200, "Records retrieved OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "No debtor with the Account No. provided was found")]
public class MyGetInventoryBlockRequest : IReturn<MyGetInventoryBlockResponse>
{
public int StartPosition{ get; set; }
public int EndPosition { get; set; }
}

#endregion

#region "Responses"
[Serializable()]
public class MyGetInventoryBlockResponse
{
public List<IN_Main> RecordResults {get; set;}
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public MyGetInventoryBlockResponse Get(MyGetInventoryBlockRequest request)
{

var query = Db.From<IN_Main>().Join<IN_Main, IN_SellingPrices>((main, prices) => main.InventoryID == prices.InventoryID).Limit(request.StartPosition,request.EndPosition);
return new MyGetInventoryBlockResponse() { RecordResults = Db.Select(query) } ;
}

}
#endregion
}

I can't immediately see a way of doing this using an OrmLite query which is an ad-hoc join - you might want to check the official docs - there may be something in their examples.

Personally, for a curated result like that, I'd create a simple view and use AutoQuery on that instead.

Moved to appropriate forum.