How to get table name using DbDataReader in Entity Framework 6?

Question

I have a custom DbDataReader in my application, which overrides GetDateTime method to change DateTimeKind.

public class MyDbDataReader : BaseDbDataReader
{
    private string _dbName;

    public MyDbDataReader(string dbName, DbDataReader source) : base(source)
    {
        _dbName = dbName;
    }

    public override DateTime GetDateTime(int ordinal)
    {
        var tableName = base.GetSchemaTable().TableName; //this doesn't work
        return DateTime.SpecifyKind(base.GetDateTime(ordinal), base.GetName(ordinal).EndsWith("UTC", StringComparison.OrdinalIgnoreCase) ? DateTimeKind.Utc : DateTimeKind.Local);
    }
}

Here is my BaseDbDataReader:

public abstract class BaseDbDataReader : DbDataReader
{
    readonly DbDataReader source;
    public BaseDbDataReader(DbDataReader source)
    {
        this.source = source;
    }

    ...
    public override DataTable GetSchemaTable() { return source.GetSchemaTable(); }
}

This dbReader is used in my custom interceptor:

public class MyInterceptor : DbCommandInterceptor
{

    public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {           
        base.ReaderExecuted(command, interceptionContext);
        if (!command.Connection.Database.Equals("...") &&
            !(interceptionContext.Result is MyDbDataReader) &&
            interceptionContext.Result != null &&
            interceptionContext.Exception == null)
        {               
            interceptionContext.Result = new MyDbDataReader(command.Connection.Database, interceptionContext.Result);
        }
    }
}

And all i want here, is to get the TableName in GetDateTime method. But GetSchemaTable method returns an incomprehensible result, where the TableName property equals to "SchemaTable". What i'm doing wrong here and how to get correct table name (like "Users").

Note: i dont use SqlCommand and SqlCommand.ExecuteReader to execute queries. I just use dbSet. I.e dbContext.Users.Where(x => x.Id = 1).Single();

EF Core *uses* DbDataReader, not the other way around. What are you trying to do? Change the loaded date's `DateTimeKind` perhaps? [There are far simpler solutions](https://stackoverflow.com/questions/4648540/entity-framework-datetime-and-utc). The problem is finally solved in EF Core 2.1 and later — Panagiotis Kanavos, Sep 22 '21 at 16:47
@PanagiotisKanavos, i can't migrate to EF core. And the provided solution is not working in my case. Because `((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized` event only works when creating a EF model (models from dbContext). But it does not work when mapping IQueryable to an anonymous type, or any other model that is not tracked by EF. Example: 1) `dbContext.Users.Where(x=>x.IsActive).ToArray()` - This code will trigger ObjectMaterialized event. 2) `dbContext.Users.Where(x=>x.IsActive).Select(ToCustomModel).ToArray()` - this code will not trigger ObjectMaterialized event — Denis Kaminsky, Sep 23 '21 at 13:55
EF uses DbDataReader as well. The version doesn't matter. All higher level data libraries use ADO.NET's DbDataReader. You don't need to create a custom one to change the DateTimeKind. And ORMs *won't* use your custom DbDataReader anyway, they'll use the database provider's class. Converting types is the job of a type converter — Panagiotis Kanavos, Sep 23 '21 at 13:57
But anyway, the solution with DateTimeKind attribute will not work in my case. My goal is to change the loaded date's DateTimeKind. But not for all dateTimes. I want to change it only for specific dateTime properties. — Denis Kaminsky, Sep 23 '21 at 14:02

Richard Deeming · Accepted Answer · 2021-09-23T11:04:01.020

0

The TableName property will always return "SchemaTable", because there's no other meaningful name it can return.

As per the link you found, the table name for each column should be returned in the BaseTableName column of the schema table. But this will only be returned if the CommandBehavior.KeyInfo flag is specified.

Digging through the source code, it looks like you'll need to use the ReaderExecuting method and take over responsibility for executing the command in order to do that:

public override void ReaderExecuting(
    DbCommand command, 
    DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
    // Skip processing for a specific database:
    if (command.Connection.Database.Equals("...")) return;
    
    var commandBehavior = interceptionContext.CommandBehavior | CommandBehavior.KeyInfo;
    
    try
    {
        var reader = command.ExecuteReader(commandBehavior);
        var result = new MyDbDataReader(command.Connection.Database, reader);
        interceptionContext.Result = result;
    }
    catch (Exception ex)
    {
        interceptionContext.Exception = ex;
    }
}

You should theoretically then be able to extract the BaseTableName column from the schema table:

public override DateTime GetDateTime(int ordinal)
{
    var schemaTable = base.GetSchemaTable();
    var tableName = schemaTable.Rows[ordinal].Field<string>("BaseTableName");
    ...
}

edited Sep 23 '21 at 11:04

answered Sep 22 '21 at 16:06

Richard Deeming

29,830
10
79
151

Good point. But i found some examples where GetSchemaTable was used to get the table name - [link](https://stackoverflow.com/questions/3102902/how-to-get-table-name-of-a-column-from-sqldatareader). It seems that i need to change commandBehaviour to KeyInfo. But i dont know where to use it, cause i dont use ExecuteReader to execute a sql query. – Denis Kaminsky Sep 22 '21 at 16:24
Interesting. I hadn't spotted that column in the schema table. It looks like you need to change the `CommandBehavior` via the `DbCommandInterceptionContext` parameter. – Richard Deeming Sep 22 '21 at 16:29
i trided to put this code `public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext) { interceptionContext = interceptionContext.WithCommandBehavior(System.Data.CommandBehavior.KeyInfo); base.ReaderExecuting(command, interceptionContext); }` in my custom interceptor, but it didn't helped. TableName is still "SchemaTable" – Denis Kaminsky Sep 22 '21 at 16:34
This method will be called before execution the query – Denis Kaminsky Sep 22 '21 at 16:35
unfortunately, you solution doesn't work. – Denis Kaminsky Sep 22 '21 at 17:17
My goal is to change DateTimeKind also for some of the DateTime fields in the Users table. Maybe it will be better to write this logic in the get {} methods for DateTime properties in User model? But will this conflict with MyDbDataReader? – Denis Kaminsky Sep 22 '21 at 17:20
By "doesn't work", do you mean it doesn't populate the `BaseTableName` column, or do you mean you're getting an error? – Richard Deeming Sep 23 '21 at 06:56
The MyDbDataReader it is not even called, because after calling `base.ReaderExecuted(command, newInterceptionContext)`, _Result_ property will be null. That's why we cant pass further validation – Denis Kaminsky Sep 23 '21 at 09:23
Digging through the source code, I suspect you're going to need to take over responsibility for executing the command. – Richard Deeming Sep 23 '21 at 10:56
do i need to use ReaderExecuting instead of ReaderExecuted? Or i need to use both methods? Its seems first option – Denis Kaminsky Sep 23 '21 at 16:16
Yes, use `ReaderExecuting`. The `ReaderExecuted` method isn't called until after the command has already been executed. – Richard Deeming Sep 23 '21 at 16:58

How to get table name using DbDataReader in Entity Framework 6?

1 Answers1