4

I have a C# .NET Core 2.0 Web API project (implemented as a microservice).

I am using NuGet Packages "Dapper" and "FastMember" (latest versions) in my repository layer to try to convert an IEnumerable<T> to a DataTable via the ObjectReader that FastMember provides, so that I can take advantage of Dapper's built-in ICustomQueryParameter SqlMapper to pass a table-valued parameter to a stored procedure that I am using in SQL Server.

When I execute and debug my code, it throws an exception on the line where the FastMember ObjectReader attempts to be loaded into a DataTable: table.Load(reader);.

Here is my Dapper wrapper/extension method that contains the problematic line:

public static class DapperExtensions
{
    public static SqlMapper.ICustomQueryParameter AsTvp<T>(this 
    IEnumerable<T> enumerable, string typeName) where T : class
        {
            var table = new DataTable();
            var members = typeof(T).GetProperties().Select(p => p.Name).ToArray();
            using (var reader = ObjectReader.Create(enumerable.ToList(), members))
            {
                table.Load(reader);
            }
            return table.AsTableValuedParameter(typeName);
        }
}

Here is my Dapper call that uses the above extension method:

using (var dbConnection = new SqlConnection(_connectionString))
{
    var result = dbConnection.Query("[PurchaseOrders].[Add] @tvpNewPurchaseOrderItems, @StartDate", new
    {
        tvpNewPurchaseOrderItems = purchaseOrderCreationRequest.PurchaseOrderItems.AsTvp("NewPurchaseOrderType"),
    StartDate = purchaseOrderCreationRequest.StartDate
    });
}

And here is the stack trace from my C# exception that was thrown:

at System.Data.Common.DbDataReader.GetSchemaTable()
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at PurchaseOrderCreationRepositories.Extensions.DapperExtensions.AsTvp[T](IEnumerable`1 enumerable, String typeName) in C:\Projects-Git\Enterprise\PurchaseOrderCreationService\PurchaseOrderCreationRepositories\Extensions\DapperExtensions.cs:line 16
at PurchaseOrderCreationRepositories.PurchaseOrderRepository.Add(IPurchaseOrderCreationRequest purchaseOrderCreationRequest) in C:\Projects-Git\Enterprise\PurchaseOrderCreationService\PurchaseOrderCreationRepositories\PurchaseOrderRepository.cs:line 22
at PurchaseOrderCreationServices.PurchaseOrderService.Add(IPurchaseOrderCreationRequest purchaseOrderCreationRequest) in C:\Projects-Git\Enterprise\PurchaseOrderCreationService\PurchaseOrderCreationServices\PurchaseOrderService.cs:line 18
at PurchaseOrderCreationServiceApi.Controllers.PurchaseOrderCreationController.Add(PurchaseOrderCreationRequest purchaseOrderCreationRequest) in C:\Projects-Git\Enterprise\PurchaseOrderCreationService\PurchaseOrderCreationServiceApi\Controllers\PurchaseOrderCreationController.cs:line 25

Exception message was: Specified method is not supported.

Can anybody help me with this error?? It doesn't make sense to me that .NET Core 2.0 wouldn't support this method. And I have used FastMember in other .NET Core 2.0 apps successfully with SqlBulkCopy. But this is the first time that I've tried using it with Dapper.

Any help is much appreciated! Thanks in advance.

Chase Harrison
  • 160
  • 1
  • 10
  • Which is the method that is not supported? – Ben Harris Jan 31 '18 at 16:18
  • check this branch https://github.com/dotnet/corefx/issues/24502. Possible bug – vadzim dvorak Jan 31 '18 at 16:18
  • "It doesn't make sense to me that .NET Core 2.0 wouldn't support this method." - oh, believe me, you **would not believe** the arguments that have been had on the dotnet github repo abut this in the past... but! where did `reader` come from in this code? does the same thing happen if you take "dapper" out of the picture and use `ExecuteDataReader`? (just to narrow down what we're talking about) – Marc Gravell Jan 31 '18 at 16:22
  • @BenHarris it is `GetSchemaTable()`, and there's a huge amount of history about this method and .NET Core... – Marc Gravell Jan 31 '18 at 16:30
  • I am using Microsoft SQL Server 2012 and newer. Is this a known bug then, and what I'm trying to do won't work? – Chase Harrison Jan 31 '18 at 16:31
  • @ChaseHarrison see update; I've checked with latest public versions and it worked fine; does the code I've posted work for you? or does it fail? we kinda need to narrow down what the specific failure is... In particular, check your version of `System.Data.SqlClient` – Marc Gravell Jan 31 '18 at 16:37
  • @ChaseHarrison also, you say you're targeting SQL Server: but: *are you using `SqlConnection`*? There are other connection types that can talk to SQL Server, and everything here comes down the the specific connection type – Marc Gravell Jan 31 '18 at 16:39
  • Yes, I am using SQL Server DB Connection string and Dapper to make the Query call. Dapper just extends upon IDBConnection, of which SqlConnection inherits from, right? I will update my original question with a code block containing my Dapper call. – Chase Harrison Jan 31 '18 at 16:43

1 Answers1

4

This is FastMember's fault. Basically, for a very long time, the .NET Core API for ADO.NET didn't provide this API at all, which meant that any code targeting it could not implement it.

This has now been rectified in newer versions of .NET Core, but I have failed to revisit FastMember to add back in the API. I will try to do this tonight and get a new deployment out ASAP.

Essentially, FastMember has:

#if !COREFX
    public override DataTable GetSchemaTable()
    {...}
#endif

because: it couldn't override a method that didn't exist on the base class at the time.

This has been fixed in v1.2.0 when targeting suitable target platforms including netstandard2.0

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900