'Parameter count mismatch' Error when trying to insert List<dynamic> to Excel using closedXml InsertData

Question

I am trying to export dynamic list items to excel using ClosedXML. The goal is to allow end user to select the columns to be exported. Since the columns are dynamic, I use.ToDynamicListAsync() of System.Linq.Dynamic.Core name space. The problem is excel export works fine if I create anonymous object but throws error when I directly pass the select query as shown below. I directly pass the select cols bcoz the cols will be known only at runtime.

Below is the code snapshot for clarity

            using ClosedXML.Excel;
            using System.Linq.Dynamic.Core;

            var filter = "MeetingNo = 85";
            var query = context.PrmProjects.AsNoTracking().Where(filter);

            var items = await query.Select(x => new
            {
                x.OrganizationId,
                x.ProjectNo,
                x.MeetingNo,
                x.Name
            }).ToDynamicListAsync();
            using var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add();
            ws.Cell(3, 1).InsertData(items); /// WORKS FINE


            var selectQuery = $"new(OrganizationId,ProjectNo,MeetingNo,Name)";
            var items1 = await query.Select(selectQuery).ToDynamicListAsync();
            using var wb2 = new XLWorkbook();
            var ws2 = wb2.Worksheets.Add();
            ws2.Cell(3, 1).InsertData(items1); /// THROW ERROR 'Parameter count mismatch' 

`

In the above sample code InsertData with the list 'items' works fine but 'items1' throws an error 'Parameter count mismatch'

Screenshot of List 'items', working fine:

Screenshot of List 'items1', throw error:

Error screenshot:

Can any one please help me. What am I doing wrong.

Answer 1

The cause for this error is that System.Linq.Dynamic.Core creates a new dynamic type based on DynamicClass which does also have an indexer:

public object? this[string name]
{
    get
    {
        //
    }

    set
    {
        //
    }
}

The ClosedXml calls GetProperties(...) in the ObjectReader.cs class and this GetProperties does return 5 entries:

• OrganizationId
• ProjectNo
• MeetingNo
• Name
• Index --> This is the indexer defined on the DynamicClass which is not really a normal property.

So actually ClosedXML should be fixed to exclude indexer methods. Example:

int parameters = propertyInfo.GetIndexParameters().Length;
if (parameters > 0)
{
    // The property is an indexer, skip this.
    continue;
}