Bulk Operations do no work on tables outside of 'dbo' schema

[mbargar-ssi]

EF Core 2.1.2 Z.EntityFramework.Extensions.EFCore 2.1.33

In doing some testing and tracing I have found that we get an exception thrown when we try and stating "Invalid object name 'TableName'. I did a trace on MSSQL server while I was running the program and found out that OBJECT_ID() take a value as [schem].[tablename] I have changed the SQL statement as below to account for not using the Schema, though you could have a table named the same in multiple schema, and my change does not account for that. `exec sp_executesql N' / SELECT server information / SELECT @@VERSION

/ SELECT table information / SELECT A.Name AS DestinationName , ( SELECT 1 WHERE EXISTS ( SELECT 1 FROM sys.triggers AS X WHERE X.parent_id = A.object_id AND X.is_disabled = 0 AND OBJECTPROPERTY(X.object_id, ''ExecIsInsertTrigger'') = 1 ) ) AS HasInsertTrigger , ( SELECT 1 WHERE EXISTS ( SELECT 1 FROM sys.triggers AS X WHERE X.parent_id = A.object_id AND X.is_disabled = 0 AND OBJECTPROPERTY(X.object_id, ''ExecIsUpdateTrigger'') = 1 ) ) AS HasUpdateTrigger , ( SELECT 1 WHERE EXISTS ( SELECT 1 FROM sys.triggers AS X WHERE X.parent_id = A.object_id AND X.is_disabled = 0 AND OBJECTPROPERTY(X.object_id, ''ExecIsDeleteTrigger'') = 1 ) ) AS HasDeleteTrigger FROM sys.tables AS A LEFT JOIN sys.synonyms AS B ON B.object_id = A.object_id AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID()) WHERE A.Name = @Table_0
ORDER BY DestinationName

/ SELECT column information / SELECT A.name AS DestinationName , C.name AS ColumnName , C.column_id AS ColumnOrder , C.precision AS Precision , C.scale AS Scale , C.max_length AS MaxLength , C.collation_name AS Collation , C.Is_Identity AS IsIdentity , ( CASE WHEN EXISTS ( SELECT 1 FROM sys.index_columns AS X WHERE X.index_id = B.index_id AND X.object_id = B.object_id AND X.column_id = C.column_id ) THEN 1 ELSE 0 END ) AS IsPrimaryKey , C.system_type_id AS System_Type_Id , LOWER(D.Name) AS TypeName, (CASE WHEN E.base_object_name IS NOT NULL THEN 1 ELSE 0 END) AS IsSynonym, D.is_user_defined, F.name FROM sys.tables AS A LEFT JOIN sys.synonyms AS E ON E.object_id = A.object_id AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID())
LEFT JOIN sys.indexes AS B ON B.object_id = A.object_id AND B.is_primary_key = 1 INNER JOIN sys.columns AS C ON C.object_id = A.object_id INNER JOIN sys.types AS D ON D.system_type_id = C.system_type_id AND D.user_type_id = C.user_type_id INNER JOIN sys.schemas AS F ON D.schema_id = F.schema_id WHERE A.name = @Table_0 ORDER BY DestinationName , ColumnOrder ',N'@Table_0 nvarchar(21)',@Table_0=N'Animal'`

[JonathanMagnan]

Hello @mbargar-ssi ,

Unfortunately, we cannot use your SQL statement due to how the library is build (We could cause some side impact).

Do you think it could be possible for you to provide a project sample with this issue? We will for sure look at it to try to make it work.

Maybe I'm missing something but using the OBJECT_ID with the [Shema].[Name] should not cause an Invalid Object Name issue. If we can get a project sample, that will for sure help us to fix it.

Best Regards,

Jonathan

[mbargar-ssi]

correct OBJECT_ID with [Schema].[Name] does not cause the issue, but your code is not passing in tables and schema to the generated SQL code. The code is only passing the table name and NOT the schema and table name. I don't have the time to make a sample project, but to recreate the issue just make a MSSQL DB and put a table in separate schema and scaffold that table in then try and try and call against that table

var bulk = new BulkOperation<TEntity>(DbConnection);

 bulk.BulkUpdate(dataSource);

[JonathanMagnan]

Thank you for the additional information.

We will try a few scenarios to reproduce it on our side later today.

[JonathanMagnan]

Hello @mbargar-ssi ,

We didn't success to reproduce this scenario but it looks you don't use the BulkUpdate extension for EF Core, perhaps that's the issue.

Is there a reason why you don't use the BulkUpdate this way:

using (var ctx = new EntitiesContext())
{
    // ...code...

    ctx.BulkUpdate(list);
}

If you directly use the Z.BulkOperations library then the EF Core mapping is not used. You can specify it by providing a DestinationTableName that includes the schema and table name but I believe you probably want to use the first example with your context.

Let me know if that's the issue.

Best Regards,

Jonathan

[mbargar-ssi]

So yes we ended up using the bulk operations on the DbContext, though we have wrapper around our context that we have throw an error when a parameter less constructor is is called and the BulkUpdate calls the DbContextwith a parameter less constructor. We have since opened it up, to accomodate using BulkUpdate but we wanted to avoid doing it that way if possible.

But thanks for your feed back.

[JonathanMagnan]

You don't have to use a default constructor.

You can use a ContactFactory

For example:

// Using a constructor that requires optionsBuilder (EF Core) 
EntityFrameworkManager.ContextFactory = context =>
{
    var optionsBuilder = new DbContextOptionsBuilder<EntityContext>();
    optionsBuilder.UseSqlServer(My.ConnectionString);
    return new EntityContext(optionsBuilder.Options);
};

Documentation: https://entityframework-extensions.net/context-factory

[mbargar-ssi]

Perfect thanks for all your help.