Why Entity Framework generates bad column names when using Linq .Union() or .Except()?

Question

I'm using EF 6 in my project. All i want is to fetch some data from the database and sometimes join additional data.

Here is my code:

var queryable = dbContext.Manuals
    .AsNoTracking()
    .Where(x => x.Status == "ACTIVE");

if (showDisabledParents)
{
    var parentsIds = queryable
        .Where(x => x.ParentId.HasValue)
        .Select(x => x.ParentId.Value)
        .Distinct()
        .ToArray();

    queryable = queryable.Union(
        dbContext.Manuals
            .AsNoTracking()
            .Where(x => parentsIds.Contains(x.Id))
    );
}

It works well when showDisabledParents is false. EF will generate a SQL query with correct column names.

But when showDisabledParents is true then EF generates UNION statement(which is expected) but it uses C1, C2, C3 ... for column names.

And the problem is that i have a custom DbDataReader which calls DateTime.SpecifyKind(..., DateTimeKind.Utc) if column name ends with "Utc". And since EF is using wrong column names (C1, C2, C3, ...) my logic is not working.

Is it somehow possible to prevent this behavior? I mean if there is a way to tell EF to not use these weird column names.

UPDATED: Here is my DBDataReaderCode:

public class MyDbDataReader : DelegatingDbDataReader
{

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

public override DateTime GetDateTime(int ordinal)
{   
    return DateTime.SpecifyKind(base.GetDateTime(ordinal), base.GetName(ordinal).EndsWith("UTC", StringComparison.OrdinalIgnoreCase) 
        ? DateTimeKind.Utc 
        : DateTimeKind.Local);       
}

}

score 0 · Answer 1 · answered Feb 16 '22 at 11:59

0

I think this is not possible since EF uses internal aliases to produce the whole query and by that makes sure there are no duplicates in the names collected from different tables etc.

Anyhow, I would rather search for an issue about your approach to the dates. Could you please provide some more details about your case? In general the rule of thumb says to always persist your dates in UTC and convert to the specific time zone on demand.

answered Feb 16 '22 at 11:59

toffik325

161
3
11

I already store dates in UTC. And after i fetch them from the database i want to convert them to my local timezone. But the method which is used to convert UTC to Local time expects to see date with DateTimeKind.UTC. Otherwise it will throw an exception. That's why i created a custom DbDataReader class inherited from DelegatingDbDataReader and just set DateTimeKind to UTC when column name ends with "UTC". It works in 99% of my repositories and queries (i have a lot of them). But it doesn't work when i use .Union() or .Except(). Because EF generates weird column names – Denis Kaminsky Feb 16 '22 at 12:12
Added DbDataReader code to the question – Denis Kaminsky Feb 16 '22 at 12:14
So then the question is: if you always persist your dates in UTC, why you keep the conversion tightly binded to the column name? :) In my opinion it would be much simpler and easier to just tell EF to always convert DateTime to DateTime with UTC kind every time it is fetched. You can do so in your ```OnModelCreating``` method, like here in the question: https://www.google.com/url?sa=t&source=web&rct=j&url=https://stackoverflow.com/questions/50727860/ef-core-2-1-hasconversion-on-all-properties-of-type-datetime&ved=2ahUKEwjZrMOTnIT2AhWhtIsKHSnjB94QFnoECAoQAQ&usg=AOvVaw36H_XiJtnPfSjzAMuNN__L – toffik325 Feb 16 '22 at 12:26
And then you can never again bother yourself or anybody else about a column name and whether it ends with "UTC". If you have many properties of such type and you are using EF Core 6.0 you can override new method ```ConfigureConventions``` in your context class which applies conversion to all properties of the given type: https://www.google.com/url?sa=t&source=web&rct=j&url=https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-6-0-preview-6-configure-conventions/&ved=2ahUKEwi30ZCJnYT2AhUOlIsKHdMGC1YQFnoECAgQAQ&usg=AOvVaw0Xji2jxH9EYjbA7TK97jDp – toffik325 Feb 16 '22 at 12:30
The problem is that not all columns stores UTC. Some of them store site time instead of UTC. I know it's bad, but it's a pretty old project. And we don't want to call DateTime.SpecifyKind for all datetime. Only for those who ends with "UTC" – Denis Kaminsky Feb 16 '22 at 13:15

score 0 · Accepted Answer · answered Feb 17 '22 at 01:50

The typical approach I use for handling DateTime.Kind is using an attribute on the applicable entity property. For instance this would go on any DateTime entity property that was UTC:

    [DateTimeKind(DateTimeKind.Utc)]
    public DateTime SomeDateUTC { get; set; }

Where if you want other date times to be marked as Local time:

    [DateTimeKind(DateTimeKind.Local)]
    public DateTime SomeDate { get; set; }

DateTimes with no attribute would be left as Unspecified kind.

The attribute itself: See (Entity Framework DateTime and UTC)

Then in your DbContext, you just add this to the InitializeContext method:

((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized +=
    (sender, e) => DateTimeKindAttribute.Apply(e.Entity);

Otherwise, if you want to persist with your current method and you are dependent on the column name, Union cannot be counted on so split the condition into two queries. This will need to be done wherever the query is executed, so if your method is returning an IQueryable<Manual>, it would need to be changed to IEnumerable<IQueryable<Manual>> where if more than one query is returned the results are combined.

var queryables = new List<IQueryable<Manual>();

queryables.Add(dbContext.Manuals
    .AsNoTracking()
    .Where(x => x.Status == "ACTIVE"));

if (showDisabledParents)
{
    var parentsIds = dbContext.Manuals
        .Where(x => x.Status == "ACTIVE"
            && x.ParentId.HasValue)
        .Select(x => x.ParentId.Value)
        .Distinct()
        .ToArray();

    queryables.Add = dbContext.Manuals
            .AsNoTracking()
            .Where(x => parentsIds.Contains(x.Id));
    
}

return queryables;

If you are instead executing the queries within this method then just capture the second query if it is needed and combine the results to be returned.

The trickiest bit here will be if you need to do pagination across the combined set. In this case I would instead use a two-pass approach, getting the relevant task IDs after appropriate sorting, then using a pagination fetch on the IDs, load the entities by ID:

var queryable = dbContext.Manuals
    .AsNoTracking()
    .Where(x => x.Status == "ACTIVE");

if (showDisabledParents)
{
    var parentsIds = queryable
        .Where(x => x.ParentId.HasValue)
        .Select(x => x.ParentId.Value)
        .Distinct()
        .ToArray();

    queryable = queryable.Union(
        dbContext.Manuals
            .AsNoTracking()
            .Where(x => parentsIds.Contains(x.Id))
    );
}

var ids = queryable
    .OrderBy(/* condition */)
    .Select(x => x.Id)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToList(); // Should execute union without worrying about column transformation since we have requested only IDs.

var manuals = await dbContext.Manuals.Where(x => ids.Contains(x.Id)).ToListAsync();

A few options to consider at least.

Why Entity Framework generates bad column names when using Linq .Union() or .Except()?

2 Answers2