If I write a DynamicQuery and use it with FetchAsDataTable and that query happens to return no rows, then a completely empty DataTable is returned - ie no columns defined.

I would have expected it to return a configured DataTable with the columns even if there were no rows. In the query example below for example, I would expect it to have columns for ID and Description.

SELECT With projection: Field: RoleEntity.ID Field: RoleEntity.Description

WHERE PredicateExpression: PredicateExpression: Predicate: FieldCompareValuePredicate: Field: Field: RoleEntity.ID GreaterThan Constant: 100

ORDER BY SortExpression: Field: RoleEntity.Description Ascending

Additional information: Page number: 0 Page size: 0 Alias: ''

There is an overload to this method that accepts a dataTable to be filled. In your case you should use this overload, and pass the TypedDataTable.

But it isn't a TypedDatatable, it is a dynamic query.

It would seem that the FetchAsDatatable routine does the fetch and returns immediately if no rows were found. It would be preferably for it to create the columns first and then do the fetch.

Could you please explain why do you need this?

Walaa wrote:

Could you please explain why do you need this?

Are you kidding

Essentially, I have a routine that takes a Dynamic Query as input. It returns a DataTable populated with the results of the query.

The caller of this routine can place a sort or filter on the DataTable and it gets used for lookups etc. and this works very well normally.

However, if the query just happens to return no rows, then the app will crash since the DataColumn for the Sort or Filter is not there.

My opinion is that the FetchAsDataTable LLBLGen method should create the fields regardless of the number of rows returned to ensure a consistent return result - ie a DataTable with the DataColumns as specified in the query. It may or may not contain result rows.

Reproduced with the latest RTL. However I think this is by design, I have to double check that though.

A possible workaround is to create your dataTable structure before fetch the query. Something like:

var qf = new QueryFactory();
var query = qf.Customer.Select(CustomerFields.CustomerId, CustomerFields.CompanyName)
    .Where(CustomerFields.Country == "XYZ");
            
var results = new DataTable();
results.Columns.Add("CustomerId");
results.Columns.Add("CompanyName");

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchAsDataTable(query, results);
}

Assert.AreEqual(0, results.Rows.Count);
Assert.AreEqual(2, results.Columns.Count);

This workaround is a bit more dynamic...

            DataTable result;

            using(var adapter = new DataAccessAdapter())
            {
                result = adapter.FetchAsDataTable(query);
            }

            if (result.Columns.Count == 0)
            {
                foreach (var field in ((IDynamicQuery) query).Projection)
                {
                    result.Columns.Add(field.Name, field.DataType);
                }
            }

...but I still maintain that FetchAsDataTable should always provide a consistent result - try running a zero-row returning query in SQL Server Management Studio - it will still always return the columns.

It's the DataProjectorToDataTable class. It creates the columns when the first row is added. This is a bit silly, admitted, as the columns have to be there eventually, and the columns are created from the projectors anyway, so no row is required. I think this is left-over design from the time when the projector did use the rows to create the columns, but that's not needed anymore, it can create the columns right away.

If you fetch a typed list with an empty datatable and you disable the string cache it will create the columns, however if you keep the string cache, it will use our own datatable filler as well, and will not create the columns if the table is empty.

I'm not sure what to do though: changing it now will break application which assume an empty table and there's shoddy code in place to check this in the user's application.

So I'm reluctant to change this now. I'll add the change for v4. THis will then also affect typedlist fetches which would now return an empty datatable.

You can work around this in another way btw. Use the FetchAsProjection overload which accepts a projector and pass in a modified DataProjectorToDataTable instance (one which calls CreateColumns in the Ctor)

There might be sideeffects as well, in edge cases with typeconverters, as the datatable filler re-uses column names if they exist instead of the ones from the resultset. In the case they differ from the ones in the projectors, this could mean a problem, but I don't know how big (or if it occurs at all, just thought about this when looking at the code).

I.o.w.: we'll postpone this to v4 so we can test it properly and document the changes.

I looked at something like that but CreateColumns is internal.

simmotech wrote:

I looked at something like that but CreateColumns is internal.

Copy the class change the name, then modify the code a bit so createcolumns is called up front. Then pass an instance of that to the projection method.

Feature added to v4. It only occurs in projection fetches. If the fetch occurs through datatable fills through fetchtypedlist on the adapter, it does create the columns in v3.5. It will now also in v4.