Dynamic querying ... RE:#47 (i guess)

[TehWardy]

Hey Jonathon,

I just yesterday updated to the latest version (2.9.6.0) as I knew today I would be working on this particularly troublesome query (it's brutal not being dynamic to be honest) I've extracted this small piece of it to help identify the problem.

It seems that Eval is not agreeing with the same raw C# and I wondered if you'd lend some light on the subject.

I've also uploaded the code to dot net fiddle here ... https://dotnetfiddle.net/MbZJjf

Please note, if you copy paste this code in to a new console app project, then add refs to newtonsoft.json and z.expressions.eval from nuget you should be good to go (I checked this to be sure you could get up and running ASAP :)

using System.Text;
using System.Dynamic;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Newtonsoft.Json;

public class Program
{
    public static void Main()
    {
        // excuse the prep work here, this is an extraction from my code base in it's simplest form
        var doc = XDocument.Parse(TestData.XMLSource);
        var json = BuildCleanJSON(doc);
        dynamic data = JsonConvert.DeserializeObject<ExpandoObject>(json);

        // this is the object we care about, i'm essentially generating "query" below to execute on this object
        dynamic source = new ExpandoObject();
        source.Data = new[] { ((dynamic)data).VPR_DirectData };
        source.Companies = TestData.Companies;

        var query = @"
((IEnumerable<dynamic>)Data)
    .SelectMany((dynamic buyer) => ((IEnumerable<dynamic>)buyer.MASTERDATA)
        .SelectMany((dynamic supplier) => ((IEnumerable<dynamic>)supplier.VENDORITEMS)
            .Select((dynamic item) => new
            {
                BuyerFCSRef = buyer.HEADER.CC_Code,
                BuyerName = buyer.HEADER.CC_DESC,
                Buyer = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference(""FCS"", buyer.HEADER.CC_Code)),
                SupplierFCSRef = supplier.VEND_CODE,
                SupplierName = supplier.VEND_NAME,
                Supplier = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference(""FCS"", supplier.VEND_CODE)),
                FunderFCSRef = supplier.FACTOR,
                ItemRef = item.REFERENCE,
                LineItem = item
            }).ToArray()
        ).ToArray()
    ).ToArray()
    .GroupBy(i => i.ItemRef)
    .Select(group => new { Ref = group.Key, Items = group.ToArray() })
    .ToArray();
";

        // trial run using "native .Net"
        dynamic[] Data = source.Data;
        dynamic[] Companies = source.Companies;

        var result = ((IEnumerable<dynamic>)Data)
            .SelectMany((dynamic buyer) => ((IEnumerable<dynamic>)buyer.MASTERDATA)
                .SelectMany((dynamic supplier) => ((IEnumerable<dynamic>)supplier.VENDORITEMS)
                    .Select((dynamic item) => new
                    {
                        BuyerFCSRef = buyer.HEADER.CC_Code,
                        BuyerName = buyer.HEADER.CC_DESC,
                        Buyer = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference("FCS", buyer.HEADER.CC_Code)),
                        SupplierFCSRef = supplier.VEND_CODE,
                        SupplierName = supplier.VEND_NAME,
                        Supplier = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference("FCS", supplier.VEND_CODE)),
                        FunderFCSRef = supplier.FACTOR,
                        ItemRef = item.REFERENCE,
                        LineItem = item
                    }).ToArray()
                ).ToArray()
            ).ToArray()
            .GroupBy(i => i.ItemRef)
            .Select(group => new { Ref = group.Key, Items = group.ToArray() })
            .ToArray();

        // re-run of the same query using Z.Expressions.Eval 
        // This is where it goes wrong.
        var compilerResult = Z.Expressions.Eval.Execute<dynamic>(query, source);
    }

    public static string BuildCleanJSON(XDocument xDoc)
    {
        var builder = new StringBuilder();
        JsonSerializer.Create().Serialize(new CleanJsonWriter(new StringWriter(builder)), xDoc);
        return builder.ToString();
    }
}

public static class TestData
{
    public static string XMLSource { get; } = @"<?xml version=""1.0"" encoding=""utf-8""?>
<n0:VPR_DirectData xmlns:n0=""http://faurecia.com/sap_ecc/ratool/inr227"" xmlns:prx=""urn:sap.com:proxy:TC1:/1SAI/TAS27C150FBCBD114A88521:740"">
    <FILE_HEADER>
        <FILENAME>FD_FR01_OI_20190128_160827.xml</FILENAME>
    </FILE_HEADER>
    <HEADER CC_Code=""FR01"" CC_DESC=""Faurecia Bloc Avant"" EXT_TYPE=""OI"" EXT_MODE=""C""/>
    <MASTERDATA VEND_CODE=""0000116443"" VEND_NAME=""Valeo Autozubehor"" VATREGNUM=""FR41316014984"" CUSTOMER=""C099000040"" FACTOR=""0000189200"" PAY_TERM=""ZP60"" PAY_TERM_DESC=""50%Z400,50%ZM4M"">
        <VENDORITEMS NUMBER=""PW-001"" FYEAR=""2019"" FI_ITEM=""001"" DOC_TYPE=""RE"" DATE=""20181225"" CURRENCY=""EUR"" POSTING_DATE=""20170620"" REFERENCE=""Ref1"" DEBITCREDIT=""H"" AMOUNT=""900.00"" VAT_AMOUNT=""100.00"" VATREGNUM=""FR41316014984"" DUE_DATE=""20181225"" RELINV_FY=""0000"" RELINV_ITEM=""000"" PAYMENT_TERM=""Z150""/>
        <VENDORITEMS NUMBER=""PW-002"" FYEAR=""2019"" FI_ITEM=""001"" DOC_TYPE=""RE"" DATE=""20181225"" CURRENCY=""EUR"" POSTING_DATE=""20170620"" REFERENCE=""Ref2"" DEBITCREDIT=""H"" AMOUNT=""900.00"" VAT_AMOUNT=""100.00"" VATREGNUM=""FR41316014984"" DUE_DATE=""20181225"" RELINV_FY=""0000"" RELINV_ITEM=""000"" PAYMENT_TERM=""Z150""/>
    </MASTERDATA>
    <MASTERDATA VEND_CODE=""0000116443"" VEND_NAME=""Valeo Autozubehor"" VATREGNUM=""FR41316014984"" CUSTOMER=""C099000040"" FACTOR=""0000189200"" PAY_TERM=""ZP60"" PAY_TERM_DESC=""50%Z400,50%ZM4M"">
        <VENDORITEMS NUMBER=""PW-001"" FYEAR=""2019"" FI_ITEM=""001"" DOC_TYPE=""RE"" DATE=""20181225"" CURRENCY=""EUR"" POSTING_DATE=""20170620"" REFERENCE=""Ref1"" DEBITCREDIT=""H"" AMOUNT=""900.00"" VAT_AMOUNT=""100.00"" VATREGNUM=""FR41316014984"" DUE_DATE=""20181225"" RELINV_FY=""0000"" RELINV_ITEM=""000"" PAYMENT_TERM=""Z150""/>
        <VENDORITEMS NUMBER=""PW-002"" FYEAR=""2019"" FI_ITEM=""001"" DOC_TYPE=""RE"" DATE=""20181225"" CURRENCY=""EUR"" POSTING_DATE=""20170620"" REFERENCE=""Ref2"" DEBITCREDIT=""H"" AMOUNT=""900.00"" VAT_AMOUNT=""100.00"" VATREGNUM=""FR41316014984"" DUE_DATE=""20181225"" RELINV_FY=""0000"" RELINV_ITEM=""000"" PAYMENT_TERM=""Z150""/>
    </MASTERDATA>
</n0:VPR_DirectData>
";

    // MasterData = Supplier Info
    //   vend_code = fcs ref
    //   vatregnum = tax ref
    // Header = Buyer Info
    //   cc_code = FCS reference

    public static Company[] Companies { get; } = new[]
    {
        new Company
        {
            CompanyId = 1,
            CompanyName = "Test Company 1",
            CompanyTaxIdentifiers = new[]
            {
                new CompanyTaxIdentifier { Reference = "FR01", ExternalReferenceSystem = new ExternalReferenceSystem { DisplayName = "FCS" } }
            }
        },
        new Company
        {
            CompanyId = 2,
            CompanyName = "Test Company 2",
            CompanyTaxIdentifiers = new[]
            {
                new CompanyTaxIdentifier { Reference = "0000116443", ExternalReferenceSystem = new ExternalReferenceSystem { DisplayName = "FCS" } }
            }
        },
        new Company
        {
            CompanyId = 3,
            CompanyName = "Test Company 3",
            CompanyTaxIdentifiers = new[]
            {
                new CompanyTaxIdentifier { Reference = "0000189200", ExternalReferenceSystem = new ExternalReferenceSystem { DisplayName = "FCS" } }
            }
        }
    };
}

public class Company
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public ICollection<CompanyTaxIdentifier> CompanyTaxIdentifiers { get; set; }

    public bool HasReference(string system, string reference)
    {
        return CompanyTaxIdentifiers?.Any(cti => cti.ExternalReferenceSystem.DisplayName == system && cti.Reference == reference) ?? false;
    }
}

public class CompanyTaxIdentifier
{
    public string Reference { get; set; }
    public ExternalReferenceSystem ExternalReferenceSystem { get; set; }
}

public class ExternalReferenceSystem
{
    public string DisplayName { get; set; }
}

public class CleanJsonWriter : JsonTextWriter
{
    public CleanJsonWriter(TextWriter writer) : base(writer) { }

    public override void WritePropertyName(string name)
    {
        var result = name;
        if (name.StartsWith("@") || name.StartsWith("#"))
            result = name.Substring(1);

        if (result.Contains(":"))
            result = result.Split(":".ToCharArray()).Last();

        base.WritePropertyName(result);
    }
}

[JonathanMagnan]

Hello @TehWardy ,

Thank you for reporting,

We will look at what can be done with this expression.

We will try to provide you an answer next Monday, dynamic is always a little bit more complex to work with ;)

Best Regards,

Jonathan

[JonathanMagnan]

Hello @TehWardy ,

The v2.9.7 has been released.

• We now support a method called from a dynamic type in a dynamic lambda in a... well ;)
• For Func, we now convert to the right return type if they are different but only for type object

However, we didn't success to fully make work your expression.

We only removed a few Array call

((IEnumerable<dynamic>)Data)
    .SelectMany((dynamic buyer) => ((IEnumerable<dynamic>)buyer.MASTERDATA)
        .SelectMany((dynamic supplier) => ((IEnumerable<dynamic>)supplier.VENDORITEMS)
            .Select((dynamic item) => new
            {
                BuyerFCSRef = buyer.HEADER.CC_Code,
                BuyerName = buyer.HEADER.CC_DESC,
                Buyer = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference(""FCS"", buyer.HEADER.CC_Code)),
                SupplierFCSRef = supplier.VEND_CODE,
                SupplierName = supplier.VEND_NAME,
                Supplier = ((IEnumerable<dynamic>)Companies).FirstOrDefault((dynamic company) => company.HasReference(""FCS"", supplier.VEND_CODE)),
                FunderFCSRef = supplier.FACTOR,
                ItemRef = item.REFERENCE,
                LineItem = item
            })
        )
    ).ToArray()
    .GroupBy(i => i.ItemRef)
    .Select(group => new { Ref = group.Key, Items = group.ToArray() })
    .ToArray();

I'm not sure yet why it was not working with ToArray but let us know if you need this one fixed as well.

Best Regards,

Jonathan

[TehWardy]

The ToArray() calls are just a side effect of the processing my JSON processor builds out.

Here's the example of my source that generates the code I build for your expression parser ...

{
    "$type": "Core.Objects.Query, Core.Objects",
    "From": {
        "$type": "Core.Objects.Query, Core.Objects",
        "From": { "((IEnumerable<dynamic>)Data)": "(dynamic buyer)" },
        "Select": {
            "$type": "Core.Objects.Query, Core.Objects",
            "From": { "((IEnumerable<dynamic>)buyer.MASTERDATA)": "(dynamic supplier)" },
            "Select": {
                "$type": "Core.Objects.Query, Core.Objects",
                "From": { "((IEnumerable<dynamic>)supplier.VENDORITEMS)": "(dynamic item)" },
                "Select": {
                    "BuyerFCSRef" : "buyer.HEADER.CC_Code",
                    "BuyerName" :  "buyer.HEADER.CC_DESC",
                    "Buyer" : {
                        "$type": "Core.Objects.Query, Core.Objects",
                        "From": { "((IEnumerable<dynamic>)Companies)": "(dynamic company)" },
                        "Where": "company.HasReference(\"FCS\", buyer.HEADER.CC_Code)",
                        "Select": { "company": "company" },
                        "SingleResult": true
                    },
                    "SupplierFCSRef" : "supplier.VEND_CODE",
                    "SupplierName" : "supplier.VEND_NAME",
                    "Supplier" : {
                        "$type": "Core.Objects.Query, Core.Objects",
                        "From": { "((IEnumerable<dynamic>)Companies)": "(dynamic company)" },
                        "Where": "company.HasReference(\"FCS\", supplier.VEND_CODE)",
                        "Select": { "company": "company" },
                        "SingleResult": true
                    },
                    "FunderFCSRef" : "supplier.FACTOR",
                    "Funder" : {
                        "$type": "Core.Objects.Query, Core.Objects",
                        "From": { "((IEnumerable<dynamic>)Companies)": "(dynamic company)" },
                        "Where": "company.HasReference(\"FCS\", supplier.FACTOR)",
                        "Select": { "company": "company" },
                        "SingleResult": true
                    },
                    "ItemRef" : "item.REFERENCE",
                    "LineItem" : "item"
                }
            }
        }
    },
    "GroupBy": "i.ItemRef",
    "Select": {
        "Ref" : "group.Key",
        "Buyer" : "group.First().Buyer",
        "Supplier" : "group.First().Supplier",
        "Funder" : "group.First().Funder",
        "Items" : "group.ToArray()"
    }
}

... you should be able to descern from that how it translates.

I might be able to figure out how to remove the excess ToArray() calls but I guess my concern is that "like for like" the equivilent LINQ query should work right?

Thanks for the fast turnaround time on this 👍 I'll pull it now and test!

[JonathanMagnan]

We will try to investigate why the ToArray fails next week.

Yes, the LINQ part should now work.

[TehWardy]

Hmmm ...

... when it comes to dealing with IEnumerable types quite often it's difficult to map things. Separately but possibly related I've noticed that if I add "using Z.Expressions.Eval" at the top of my code I can't use LINQ any more for the same reason ... could this be what this is?

I'm guessing the reason for this is that your expression stack has parts of the API that looks much like the LINQ API.

[TehWardy]

Hey @JonathanMagnan just touching base ... any luck here ? No pressure just a quick passing "hey"!

[JonathanMagnan]

Hello @TehWardy ,

Thank you for the friendly reminder.

The v2.9.11 has been released. Our library should be now compatible with the ToArray in this kind of situation.

It's possible for you to provide an example of the Ambiguous Match, I didn't success to reproduce it on my side.

Best Regards,

Jonathan

[TehWardy]

Just tested the sample code I gave you above ... that appears to be working now on my end too. I think the latest version has got it.