CompositionAPI independent Datatypes

Now I've done some further work and I works in the Visual Studio. (The problem mentioned above still exists).

When I apply that to the API Designer I'll get the error:

Api0015;Await construct is missing ConfigureAwait(false) call (C...\One Identity\One Identity Manager\ApiDesigner\523DAFCF-51D2-4621-9004-3B253BE684EE\CSharpFiles\CCC_....cs: (45,48)-(45,140));CCC_..._ESET;;Await construct is missing ConfigureAwait(false) call (...\One Identity\One Identity Manager\ApiDesigner\523DAFCF-51D2-4621-9004-3B253BE684EE\CSharpFiles\CCC_.;;CSharp

Any idea? Why does it work within the APISampleProject but not in the APIDesigner?

It works now as expected. The additional bracket with the "Result": {...} disappears while using the CancellationToken as well:

                .HandleGet(async (qr, ct) =>
                {

                    Query qEsets = Query.From("ESet").Select("Ident_ESet", "Remarks", "UID_PersonResponsible", "UID_ESetType").Where("UID_ESetType = N'e65d9baa-d737-4de9-a005-2f1f8f6263c8' or UID_ESetType=N'83b1694a-1d6c-4fb8-a5ea-f3f0e0d58e1d'");
                    var entityRead = await qr.Session.Source().GetCollectionAsync(qEsets, ct).ConfigureAwait(false);

The "Api0015;Await construct is missing ..." error in the API Designer was caused by wrong order of brackets. (The API Designer isn't showing that issue as the Visual Studio would).

Coming back to my request:

I'v just found out, that I should use a predefined SQL for my purpose for better performance, since I have to collect also from DialogMultilanguage.

Now I have to problem, hat the result contains to much information

            "Columns": {
                "Ident_ESet": {
                    "Value": "SR1_TK_000509",
                    "IsReadOnly": true,
                    "DisplayValue": "SR1_TK_000509"
                },
                "LastUpdated": {
                    "Value": "2020-02-06T13:02:26.293Z",
                    "IsReadOnly": true,
                    "DisplayValue": "2/6/2020 2:02:26 PM"
                },

The client isn't interested in isReadOnly or not, and Value and DisplayValue are redundant in our case.

Wow could i transfer that to a more simple json?

Something like the following is all i need:

    {
        "Ident_ESet": "SR1_TK_000509",
        "LastUpdated": "2020-02-06T13:02:26.293Z"
    },

Any idea?

I couldn't find enough information in the documentation nor in the apisamples.

 Hanno Bunjes : Could you give me some advice?

Hi,

You are already in control of the serialized data, because the ESetList-typed and ESet-typed objects are simply being serialized to generate the JSON result.

Can you post the definitions of the ESetList/ESet classes please?

(By the way, using a predefined SQL statement only for performance reasons is not recommended for simple object queries.)

Thanks for your fast reply.

The Eset and ESetList classes are quite simple

    public class ESetList
    {
        public List<ESet> esets { get; set; } = new List<ESet>();
    }
    public class ESet
    {
        public string Key { get; set; }
        public string Ident_ESet { get; set; }
        public string Name_ESet_DE { get; set; }
        public string Name_ESet_FR { get; set; }
        public string Name_ESet_IT { get; set; }
        public string Description_ESet_DE { get; set; }
        public string Description_ESet_FR { get; set; }
        public string Description_ESet_IT { get; set; }
        public string Person_Responsible { get; set; }
        public string Eset_Type { get; set; }
        public DateTime lastUpdate { get; set; }
    }

Collecting data is quite expensive. For Ident_ESet (Name_Eset) and Remark (Description_ESet) I need the translation for German, French and Italian.
The LastUpdate should reflect the latest update including the translations. So I need the MAX of XDateUpdate spread over ESet and the corresponding six DialogMultiLanguage Entries.

My predefined SQL Statement would look like this:

select e.Ident_ESet,
(SELECT MAX(LastUpdateDate)
      FROM (VALUES (e.XDateUpdated),(td.XDateUpdated),(dd.XDateUpdated),(tf.XDateUpdated),(df.XDateUpdated),(ti.XDateUpdated),(di.XDateUpdated)) AS UpdateDate(LastUpdateDate)) 
   AS LastUpdated,
td.EntryValue as TitelDE,
dd.EntryValue as DescriptionDE, 
ti.EntryValue as TitelFR,
di.EntryValue as DescriptionFR, 
ti.EntryValue as TitelIT,
di.EntryValue as DescriptionIT

from eset e 
left join DialogMultiLanguage td on e.Ident_ESet = td.EntryKey and td.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and td.UID_DialogCulture = 'QBM-CULT-de-CH'
left join  DialogMultiLanguage dd on e.Remarks = dd.EntryKey and dd.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and dd.UID_DialogCulture = 'QBM-CULT-de-CH'
left join DialogMultiLanguage tf on e.Ident_ESet = tf.EntryKey and tf.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and tf.UID_DialogCulture = 'QBM-CULT-fr-CH'
left join  DialogMultiLanguage df on e.Remarks = df.EntryKey and df.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and df.UID_DialogCulture = 'QBM-CULT-fr-CH'
left join DialogMultiLanguage ti on e.Ident_ESet = ti.EntryKey and ti.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and ti.UID_DialogCulture = 'QBM-CULT-it-CH'
left join  DialogMultiLanguage di on e.Remarks = di.EntryKey and di.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and di.UID_DialogCulture = 'QBM-CULT-it-CH'
order by td.UID_DialogCulture

It collects all the required information fast enough. But if I use .HandleGetBySqlStatement(..) I get the Datatypes with all the additional (Meta)Data.

Is there a way to convert the result of .HandleGetBySqlStatement into my Data Types, or could i call a predefined SQL Statment in my .HandleGet Code?

HandleGetBySqlStatement uses a fixed result type, so you would have to use the generic HandleGet variant to execute the predefined SQL statement. It would look similar to this:

Method.Define("some_method")
   .HandleGet(async (request, ct) =>
   {
            var session = request.Session;
            var result = await session.Resolve<IStatementRunner>().SqlExecuteAsync("<Statement_ID>", new[]
            {
                new QueryParameter("<Some_Parameter>", ValType.String, "Some_ParameterValue>")
            }, ct).ConfigureAwait(false);

            using (result)
            {
                while (result.Read())
                {
                // ... process each data row
                }
            }
            
            return result_object;
        }
        }

Make sure to include the "using" keyword, otherwise an open data reader will cause subsequent SQL statements to fail.

Cool, that works perfect. I've just tried out. That is the solution I was looking for.

Thanks a lot for your support. I wouldn't have any chance to find that out myself.