Efcore: Set operations over non-entity projections with different facets

[yepeekai]

When doing a Union for the same result class but coming from different entity sources or with default values, it fails because of: InvalidOperationException: Set operations over different store types are currently unsupported even when types are identical except for maxLength attribute...

if (innerColumn1.TypeMapping.StoreType != innerColumn2.TypeMapping.StoreType) will fail for nvarchar(100) on one side and nvarchar(max) on the other side...

If I have a nullable string field with maxLength of 100. I can't do a union to something that has different maxlength or default value since default value will be interpreted as nvarchar(max)

I understand that this check is there to prevent conversion from one type to another that may produce unexpected results but I don't think it is the case here since both ends are nvarchar. There is an issue(16298) that tries to solve this in a smart way, but I think it will take some time before it gets released...

I think in the meantime (a a patch) that EF should allow different nvarchar(x) to match where x can vary.

Here is what I am trying to do:

mydbsetA.Select(a => new MyDto { Id = a.Id, Name = a.Name }) //maxLength(100)=>nvarchar(100)
.Union(mydbsetB.Select(b => new MyDto {Id = b.Id, Name = null })) //default to nvarchar(max)
.Union(mydbsetC.Select(c => new MyDto {Id = c.Id, Name = c.Name })) //maxLength(25)=>nvarchar(25)

Further technical details

EF Core version: 3.1 preview 3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 7
IDE: Visual Studio 2019 16.3.10

[roji]

16298 is about set operations over different entity types, whereas you are trying to perform a set operation over non-entity projections; the two are different problems, so we can use this issue to track this.

The tricky part of this is to understand database behavior around the differing facets. For example, in SQL Server a set operation over nvarchar(50) and nvarchar(100) seems to yield a type of nvarchar(200) (odd...), we'd have to investigate thoroughly and across databases to see exactly what should be done. Based on the results of that investigation, supporting this could be easy or hard, and therefore may or may not be appropriate for a 3.1 patch - but it's unlikely we'll get around to this anytime soon.

SELECT SQL_VARIANT_PROPERTY((SELECT CAST('foo' AS nvarchar(50)) UNION SELECT CAST('foo' AS nvarchar(100))), 'MaxLength'); -- Result is 200

Note that specifying store types explicitly (#4978) could help here, as you could manually up-cast one side to match the other, will add a note there.

[roji]

Of course, you can use client evaluation to temporarily work around this (by placing AsEnumerable appropriately).

[yepeekai]

Thanks for all the explanations. Not sure why you closed it since it is not related to what I suggested and you said

we can use this issue to track this"

Unfortunately, client evaluation isn't something I want to do in this case because it is a paged source (I only retreive 25 records out of thousands). 4978 is a promising workaround but it is in the backlog :(

[yepeekai]

One additional note: I don't know if it makes sense, but I think EF should use the type of the destination instead of the source in this kind of situation. I am selecting this to a Dto without any kind of length restriction.

[roji]

Sorry for closing, that was a mistake.

Depending on what exactly you're doing, you may be able to get away with adding limits (Take) on both sides of the set operation, and then pulling that and client-evaluating it - but that won't work if you have to apply the limit on the result of the set operation.

I don't know if it makes sense, but I think EF should use the type of the destination instead of the source in this kind of situation. I am selecting this to a Dto without any kind of length restriction.

I understand the logic, but things don't quite work like in the pipeline. The database has a specific, well-defined behavior when typing resulting columns of set operations, and it doesn't depend on what you're going to do with it; we need to understand and match that in EF Core. Also, it wouldn't be good for this to work only if you happen to select this to a DTO without a length restriction - a solution here would make it work in the general case.

[smitpatel]

Related #15586

[ViRuSTriNiTy]

@yepeekai I'm facing this issue too. You can use Convert.ToString(Name) to fool the logic and avoid that unnecessary exception ^^

[krajek]

@ViRuSTriNiTy thanks, Convert.ToString worked in my case as well.

[AlbertoMonteiro]

@ViRuSTriNiTy you are my hero, this is awesome man, worked flawlessly to me too

[igorfarias30]

Thanks, @ViRuSTriNiTy !!

[rruntex]

But have you seen the monster it creates on execution plan?