Set operations: allow operands with different entity types

[roji]

trafficstars

Our current set operation support requires that both siblings have the exact same entity type. Quite a lot of logic is already in place for different entity types (of the same hierarchy), but the feature is currently disabled.

The issue is that when performing a set operation over two different siblings, null projections have to be injected, as the two siblings don't have the same set of properties. In addition, since the entire set operation is cast up to return the closest common ancestor of the two siblings, we also need to inject nulls for properties of unrelated siblings, since the shaper expects them there.

~We currently lack a way to represent these null constants in the projection mappings of the set operation. Project mappings typically contain ColumnExpressions, but in our case there is no subquery table alias (since union operands don't have an alias).~ This should no longer be an issue after #17340.

[snailcatcher]

@roji Will this be fixed in the 3.0 release?

[roji]

@snailcatcher unfortunately no, this has been punted for post-3.0 work, but we'll definitely consider this as a high-priority item for the next release.

However, note that you can still perform any sort of set operation on the client via client evaluation - simply insert an AsEnumerable() before performing your set operation. This is how all set operations were handled in pre-3.0 versions, and depending on the exact use case client evaluation may perform just as well as server evaluation (e.g. if no filtering is applied afterwards and all results are sent to the client anyway).

[mhDuke]

according to this issue, if i have this setup

public class Reservation
{
   public int Id {get; set;}
   public TimeSpan Length {get; set;}
}
public class Appointment : Reservation
{
   public Customer Customer {get; set;}
}

DbContext

public DbSet<Reservation> Reservations { get; set; }
public DbSet<Appointment> Appointments { get; set; }

I cannot perform a Union like this

var reservations = context.Appointments.Include(e => e.Customer)
                   .Union(context.Reservations)

except in memory via AsEnumerable()?

you might want to answer this question as well :)

[SoftCircuits]

Why has this not been considered important enough to implement before 7.0? It really makes it difficult to work with EF when this is needed. Is there a workaround that can be offered? Any comments on using Convert.ToString() to convert columns so they match string literals? That seems to work but appears it may have memory concerns.

[ajcvickers]

This issue has 9 votes (👍). This puts it in 164th position in the list of most requested features. See the planning process for more information on how we decide what to work on. Make sure to vote for this issue if it is important to you.

[roji]

@SoftCircuits

Is there a workaround that can be offered? Any comments on using Convert.ToString() to convert columns so they match string literals?

Using Convert.ToString() should be a good workaround.

That seems to work but appears it may have memory concerns.

What makes you think that, can you provide more details?

[mhDuke]

@roji @SoftCircuits

Using Convert.ToString() should be a good workaround.

I am not sure how Convert.ToString() is linked to working with sets of different types? could you explain!

[roji]

@MHDuke see #29027, which is what @SoftCircuits was referring to above.

[SoftCircuits]

@MHDuke In some cases, wrapping the column with Convert.ToString() will allow it to be unioned with a string literal. That discussion is here, although it doesn't provide many details.

[SoftCircuits]

@roji I responded to that same question here.

[PavelFischerCoupa]

Got same issue with owned types like: AuditDetail owned type, Entity1 and Entity2 own it

class Entity1{
   int Id, string Name, AuditDetail AuditDetail
}

class Entity2{
   int Id, string Name, AuditDetail AuditDetail
}

class EntityView{
   int Id, string Name, AuditDetail AuditDetail
}

class AuditDetail{
  int UserId, string UserName, DateTime Timestamp
}

and the query

var entityOnes= Entities1.AsNoTracking()
        .Select(entity1 => new EntityView
        {
            Id = entity1 .Id,
            Name = entity1.Name,
            AuditDetail = entity1.AuditDetail
        });

    var entityTwoes= Entities2.AsNoTracking()
        .Select(entity2=> new EntityView
        {
            Id = entity2.PortfolioId,
            Name = entity2.Name,
            AuditDetail = entity2.AuditDetail
        });

    return entityOnes.Concat(portfolios).OrderBy(x => x.Name).Skip(100).Take(100).ToList();

[Suchiman]

Also just run into this while migrating from EF6 to EFC, similiar construct as Pavel: System.InvalidOperationException: Unable to translate set operation when matching columns on both sides have different store types.

var entityOnes= Entities1
        .Select(entity1 => new EntityView
        {
            Id = entity1 .Id,
            Description = entity1.Name + " / " + entity1.Street
        });

    var entityTwoes= Entities2
        .Select(entity2=> new EntityView
        {
            Id = entity2.PortfolioId,
            Description = entity2.Risk
        });

    return entityOnes.Concat(entityTwoes).ToList();

Using Convert.ToString helps to workaround that, as does adding (string)(object), however both workarounds make EF6 unhappy LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression. and Unable to cast the type 'System.String' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.. The reason this matters for me is that i have EF sufficiently abstracted away that i can dynamically switch between EF6 and EFC to evaluate performance, correctness and functionality. So far it looks like the only way to support both is to duplicate the queries and switch between them depending on whether EF6 or EFC is currently in use.