Kinetic REST API headaches

Kinetic ERP
,
53

I’m not a function guy (yet) but curious whether System.Linq.Dynamic.Core can be referenced?

Would be neat to see if a dynamic query function with simple $select, $filter, $expand equivalents is faster than OData > WebAPI > EF > Linq > db > EF > WebApi > OData > json or whatever their backend is doing.

something like (AI code):

Function-Like class

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Linq.Dynamic.Core;

public class OrderDataService
{
    public (DataSet Data, string Timing) GetOrderData(
        int orderNum,
        string select,
        string expand)
    {
        var sw = new Stopwatch();
        sw.Start();

        // Build dynamic query using select and expand
        var query = DynamicQueryBuilder.BuildQuery(
            Db.OrderHed.AsQueryable(),
            select,
            expand
        );

        // Apply filter and execute
        var result = query.Where("OrderNum == @0", orderNum).ToDynamicList();

        // Wrap in dictionary for serialization
        var data = new Dictionary<string, object>
        {
            ["OrderHed"] = result
        };

        // Serialize and convert to DataSet
        var json = JsonConvert.SerializeObject(data, Formatting.Indented);
        var root = JObject.Parse(json);
        var dataSet = root.ToObject<DataSet>();

        var timing = $"Processing Time -> {sw.ElapsedMilliseconds} ms";
        return (dataSet, timing);
    }
}

DynamicQueryBuilder Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

public static class DynamicQueryBuilder
{// TODO: add filter param parser
    public static IQueryable<dynamic> BuildQuery<T>(
        IQueryable<T> source,
        string select,
        string odataExpand)
    {
        var normalizedExpand = NormalizeODataExpand(odataExpand);
        var expandTree = ParseExpand(normalizedExpand);
        var projection = BuildProjection(select, expandTree);
        return source.Select(projection);
    }

    private static string BuildProjection(string rootSelect, Dictionary<string, ExpandNode> expandTree)
    {
        var projection = $"new ({rootSelect}";

        foreach (var (navProp, node) in expandTree)
        {
            var nestedSelect = string.Join(", ", node.SelectFields);
            var nestedProjection = node.ExpandFields.Count > 0
                ? BuildProjection(nestedSelect, node.ExpandFields)
                : $"new ({nestedSelect})";

            projection += $", {navProp} = {navProp}.Select({nestedProjection})";
        }

        projection += ")";
        return projection;
    }

    private static Dictionary<string, ExpandNode> ParseExpand(string expand)
    {
        var result = new Dictionary<string, ExpandNode>();
        if (string.IsNullOrWhiteSpace(expand)) return result;

        var pattern = @"(\w+)\(([^;()]+)(?:;(.+?))?\)";
        var matches = Regex.Matches(expand, pattern);

        foreach (Match match in matches)
        {
            var navProp = match.Groups[1].Value;
            var selectFields = match.Groups[2].Value.Split(',').Select(f => f.Trim()).ToList();
            var nestedExpand = match.Groups[3].Success ? match.Groups[3].Value : null;

            result[navProp] = new ExpandNode
            {
                SelectFields = selectFields,
                ExpandFields = ParseExpand(nestedExpand)
            };
        }

        return result;
    }

    private static string NormalizeODataExpand(string odataExpand)
    {
        if (string.IsNullOrWhiteSpace(odataExpand)) return "";

        var normalized = Regex.Replace(odataExpand, @"\$select=([^;()]+)", m => m.Groups[1].Value);
        normalized = Regex.Replace(normalized, @"\$expand=", "");
        normalized = Uri.UnescapeDataString(normalized);

        return normalized;
    }

    private class ExpandNode
    {
        public List<string> SelectFields { get; set; } = new();
        public Dictionary<string, ExpandNode> ExpandFields { get; set; } = new();
    }
}

usage:

var service = new OrderDataService();

var select = "OrderNum,OrderDate,CustNum";
var expand = "OrderDtl($select=OrderNum,OrderLine,PartNum;$expand=OrderRel($select=OrderNum,OrderLine,OrderRelNum,ReqDate))";

var (dataSet, timing) = service.GetOrderData(24528, select, expand);

Console.WriteLine(timing);
1 Like