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

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);       
}

}

CodePudding user response：

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.

CodePudding user response：

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.