amalbala amalbala - 14 days ago 5
C# Question

Merging two iqueryables that are projected to one entity from diferent tables

I have tried This answer, This one and this one to merge two iqueryables. But I always receive the following error:


The type 'Estudio' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.


I'm mapping from two different but similar Entity Framework Entities (EXAMEN and EXPLORACION) to my domain entity Estudio, with the following code.

IQueryable<Estudio> listExamen = context.Set<EXAMEN>().Project().To<Estudio>();
IQueryable<Estudio> listExploracion = context.Set<EXPLORACION>().Project().To<Estudio>();

var listCombined = listExamen.Concat(listExploracion);


Is there anyway of generate a IQueryable (not enumerable) with the merging of both list? If AsEnumerable() is used, then the following filters (Order, Take, etc) are executed on memory. So I need to merge the list but still be able to apply filter to the merged list wihtout execute the queries.

//This will force the next condition is executed on memory
var listCombined = listExamen.AsEnumerable().Concat(listExploracion);


Is that possible?

Answer

I would try to select your data into an anonymous type in your linq query, perform the union, and add your criteria.

var listExamen = context.Examen
    .Select(x => new { x.Prop1, x.Prop2, ... }); // Add properties

var listExploracion = context.Exploraction
    .Select(x => new { x.Prop1, x.Prop2, ... }); // Add identical properties

var listCombined = listExamen.Concat(listExploracion);

var whereAdded = listCombines
    .Where(x => x.Prop1 == someValue);
var result = whereAdded
    .Skip(skipCount)
    .Take(takeCount)
    .ToList();

Note: I have no idea if you can use Common Table Expressions (the SQL necessity for skip/take) in combination with a Union-query

Note: I've changed the methods used to create the expressions, since I do not know your methods (Project, To)

So I think the solution is not to cast to a specific type, but to an anonymous type, since that probably can be translated to SQL.

Warning: didn't test it