mtanneryd/ef-bulk-operations

Bug When Parsing Table Names

noopectro opened this issue · 24 comments

noopectro commented

Hello!

I think I've discovered a bug when the extension is parsing out table names.. For my model, when Entity Framework builds the SQL output that the Regex parses in the GetTableName method, for some reason EF is adding an extra space in the FROM clause (between the FROM keyword and the table name), e.g:

FROM  [dbo].[StagingTransactions] AS [Extent1]

When GetTableName parses this, it parses it out as " dbo.StagingTransactions" (with a leading space) and thus returns:
Schema = " dbo"
TableName = "StagingTransactions"

When this is then returned as a TableName object and parsed back into SQL, it's outputting it as [ dbo].[StagingTransactions] which then causes the Temp Table code to fail.

mtanneryd commented

Hi!

Thanks for the feedback. I have changed the regular expression to be more robust and uploaded 1.2.5-beta1 to nuget. It should be available soon if not already.

mtanneryd commented

Fixed in 1.2.5

vk5880 commented

Hi! Thank you for support.
I've found the issue in 1.2.2, I've updated to 1.2.5. But I still have a bug with error:

System.Data.SqlClient.SqlException (0x80131904): Invalid object name 
' Residuals.OfficeResidualItems'.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.CreateTempTable(SqlConnection connection, SqlTransaction transaction, TableName tableName, String[] columnNames, IncludeRowNumber includeRowNumber)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkCopy(DbContext ctx, IList entities, Type t, Mappings mappings, SqlTransaction transaction, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction sqlTransaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, Dictionary`2 mappingsByType, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction sqlTransaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, Dictionary`2 mappingsByType, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, BulkInsertRequest`1 request)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive)

Could you help with that?

mtanneryd commented

I'll have another look. If you could provide the entity class in question or perhaps the sql code generated by var dbSet = ctx.Set(t); var sql = dbSet.ToString();

vk5880 commented

Sure,
Here it is:

SELECT 
    [Extent1].[OfficeResidualItemId] AS [OfficeResidualItemId], 
    [Extent1].[ReportDataId] AS [ReportDataId], 
    [Extent1].[GeneratedReportId] AS [GeneratedReportId], 
    [Extent1].[ReportPeriodId] AS [ReportPeriodId], 
    [Extent1].[OfficeId] AS [OfficeId], 
    [Extent1].[GrossId] AS [GrossId], 
    [Extent1].[ScheduleId] AS [ScheduleId], 
    [Extent1].[AgentPayoutProfileId] AS [AgentPayoutProfileId], 
    [Extent1].[ItemOfficeId] AS [ItemOfficeId], 
    [Extent1].[ItemAgentId] AS [ItemAgentId], 
    [Extent1].[ItemProfileId] AS [ItemProfileId], 
    [Extent1].[ItemId] AS [ItemId], 
    [Extent1].[Transactions] AS [Transactions], 
    [Extent1].[Volume] AS [Volume], 
    [Extent1].[Income] AS [Income], 
    [Extent1].[Total] AS [Total], 
    [Extent1].[Payout] AS [Payout], 
    [Extent1].[ACHCollectedGross] AS [ACHCollectedGross], 
    [Extent1].[ACHCollectedPayout] AS [ACHCollectedPayout], 
    [Extent1].[ACHRejectedGross] AS [ACHRejectedGross], 
    [Extent1].[ACHRejectedPayout] AS [ACHRejectedPayout], 
    [Extent1].[RefundsGross] AS [RefundsGross], 
    [Extent1].[RefundsPayout] AS [RefundsPayout], 
    [Extent1].[NonLiability] AS [NonLiability], 
    [Extent1].[NonLiabilityResiduals] AS [NonLiabilityResiduals], 
    [Extent1].[ReferralResiduals] AS [ReferralResiduals], 
    [Extent2].[ResidualItemId] AS [ResidualItemId]
    FROM  [Residuals].[OfficeResidualItems] AS [Extent1]
    LEFT OUTER JOIN [Residuals].[ResidualItem] AS [Extent2] ON ([Extent2].[OfficeResidualItem_OfficeResidualItemId] IS NOT NULL) AND ([Extent1].[OfficeResidualItemId] = [Extent2].[OfficeResidualItem_OfficeResidualItemId])
vk5880 commented

Before update error was:

System.Data.SqlClient.SqlException (0x80131904): Invalid object name ' Residuals.OfficeResidualItems'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkCopy(DbContext ctx, IList entities, Type t, Mappings mappings, SqlTransaction transaction, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, BulkInsertResponse response)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, BulkInsertRequest`1 request)
   at Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll[T](DbContext ctx, IList`1 entities, SqlTransaction transaction, Boolean recursive)
vk5880 commented

something wrong with nuget package?
I use
<package id="Tanneryd.BulkOperations.EF6" version="1.2.5" targetFramework="net452" />
and dll md5 is
6cbbd4b3a07d1ea641718385c1add3d1 *Tanneryd.BulkOperations.EF6.dll

vk5880 commented

I've built last commit and it worked.

vk5880 commented

Are you going to update Nuget package or I am missing something in my environment?

mtanneryd commented
vk5880 commented

I've seen some changes, are you going to release an updated package?

mtanneryd commented
  • 👀1
mtanneryd commented
vk5880 commented
mtanneryd commented
vk5880 commented

Hi!
Now another error: Incorrect syntax near the keyword 'IF'.
Database name 'tempdb' ignored, referencing object in tempdb.

mtanneryd commented
mtanneryd commented
vk5880 commented

Looks good now. Works without error. Thank you!

vk5880 commented

Hi,
Believe or not it happens again!

[SqlException (0x80131904): Invalid object name 'ResidualsUpload.FDP'.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3302284
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +736
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4061
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1293
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +421
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +380
   Tanneryd.BulkOperations.EF6.DbContextExtensions.CreateTempTable(SqlConnection connection, SqlTransaction transaction, TableName tableName, String[] columnNames, IncludeRowNumber includeRowNumber) +599
   Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkCopy(DbContext ctx, IList entities, Type t, Mappings mappings, SqlTransaction transaction, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, BulkInsertResponse response) +2050
   Tanneryd.BulkOperations.EF6.DbContextExtensions.DoBulkInsertAll(DbContext ctx, IList`1 entities, SqlTransaction sqlTransaction, Boolean recursive, Boolean allowNotNullSelfReferences, TimeSpan commandTimeout, Dictionary`2 savedEntities, Dictionary`2 mappingsByType, BulkInsertResponse response) +13973
   Tanneryd.BulkOperations.EF6.DbContextExtensions.BulkInsertAll(DbContext ctx, BulkInsertRequest`1 request) +787

table name is FDPASODetailResidualsReports with schema it is ResidualsUpload.FDPASODetailResidualsReports

mtanneryd commented
vk5880 commented

Im going to submit a fix with merge request

mtanneryd commented
mtanneryd commented

As far as I can tell this has been resolved. Please reopen if you find this not to be true.