BulkInsertIntoTempTableAsync doesn't use PropertiesToInclude on temp table creation.

[JonathanMyer]

I'm trying to create a temp table from a model with 100+ fields, but I only want to insert the primary keys into the temp table. Currently using 3.0.0-beta12 and the creation of the temp table uses all of the fields from the model instead of the ones I pass to the SqlServerTempTableBulkInsertOptions.PropertiesToInsert. So when it tries to do the insert, it fails because some of the fields are non-nullable, and the PropertiesToInsert option only inserts the specified fields.

From what I can tell in the BulkInsertIntoTempTableAsync function, the tempTableOptions is created from the ISqlServerTempTableBulkInsertOptions.TempTableCreationOptions which is a new set of options. I would like the PropertiesToInsert to pass from the SqlServerTempTableBulkInsertOptions.PropertiesToInsert to the SqlServerTempTableCreationOptions.PropertiesToInclude that way the temp table is only created with the fields specified in the SqlServerTempTableBulkInsertOptions.PropertiesToInsert.

Here is my function.

private async Task<IEnumerable<T>> GetExistingRecords<T>(IList<T> models) where T : BaseEntity<T>
{
    var options = new SqlServerTempTableBulkInsertOptions();
    var props = typeof(T).GetProperties()
        .Where(p => p.IsDefined(typeof(PrimaryKeyAttribute)) || p.IsDefined(typeof(IdentityAttribute)))
        .ToList();
    options.PropertiesToInsert = new EntityPropertiesProvider(props);
    var tempTable = await _dbContext.BulkInsertIntoTempTableAsync<T>(models.ToList(), options);
    var existing = await _dbContext.Set<T>()
        .Join(tempTable.Query,
        models.FirstOrDefault().GetPrimaryKeys,
        models.FirstOrDefault().GetPrimaryKeys,
        (x, y) => x)
        .ToListAsync();
    return existing;
}

I got it to work by changing line 302 of SqlServerBulkOperationExecutor.cs to

tempTableOptions = new SqlServerTempTableCreationOptions(tempTableOptions) { PrimaryKeyCreation = PrimaryKeyPropertiesProviders.None, PropertiesToInclude = options.PropertiesToInsert };

This was working on 3.0.0-beta07 before MembersToInsert was changed to PropertiesToInsert. Perhaps there is another way to accomplish this that I'm not seeing?

[PawelGerr]

Hi, the new behavior is by design. The reason is that the IQueryable<T> is strictly typed and EF expects all properties of T to work properly.

Imagine, we have a class with 2 properties:

public class User
{
   public Guid Id { get; set; }
   public string Name { get; set; }
}

and we want to create a temp table with just 1 property Id

var users = new[] { new User { Id = new Guid("69DB1031-4BC3-42E4-9378-F880CA4D4C21") } };

var options = new SqlServerTempTableBulkInsertOptions { PropertiesToInsert = EntityPropertiesProvider.From<User>(u => u.Id) };
await using var tempTableQuery = await ctx.BulkInsertIntoTempTableAsync(users, options);

IQueryable<User> usersQuery = tempTableQuery.Query;

until now, everything should work as expected. But due to the fact that the query is an IQueryable<User>, we can use the property Name in Select, Where, etc.

var userNames = usersQuery.Select(u => u.Name).ToListAsync();

From the point of view of EF, this Select(u => u.Name) is a valid operation but during runtime we would get a SqlException because the corresponding column does not exist.

---

If you need a temp table with one column then I recommend you to introduce the table(s) with correct type(s) via method ConfigureTempTable<TProperty>.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
         base.OnModelCreating(modelBuilder);

         modelBuilder.ConfigureTempTable<Guid>();
}

Now we can insert Guids into temp table using the method BulkInsertValuesIntoTempTableAsync

private static async Task DoAsync(DemoDbContext ctx)
{
   var users = new[] { new User { Id = new Guid("69DB1031-4BC3-42E4-9378-F880CA4D4C21") } };

   var userIds = users.Select(u => u.Id);

   await using var tempTableQuery = await ctx.BulkInsertValuesIntoTempTableAsync(userIds);

   IQueryable<TempTable<Guid>> usersQuery = tempTableQuery.Query;

   IQueryable<Guid> userIdsQuery = usersQuery.Select(t => t.Column1);
}

[PawelGerr]

@JonathanMyer: I want you to know that the requested behavior will be "hidden" behind the flag "UsePropertiesToInsertForTempTableCreation" in the next version.

var options = new SqlServerTempTableBulkInsertOptions
                    {
                       PropertiesToInsert = EntityPropertiesProvider.From<User>(u => u.Id),
                       Advanced =
                       {
                          UsePropertiesToInsertForTempTableCreation = true
                       }
                    };