Cast IQueryable to IQueryable with better performance

Question

I have 2 different entities, Entity1 and Entity2, with the very same properties. They are auto-generated from different views from DB and each view has its own Entity type.

I query these entities via :

protected generatedRepository<Entity1Type> _myRepository1;
_myRepository1.GetQueryable();

protected generatedRepository<Entity2Type> _myRepository2;
_myRepository2.GetQueryable();

I'm creating an API endpoint using OData, and I must return a IQueryable<...> to let the user apply OData filters to its request

When I want to return entities from Entity1, I just have to write :

public IQueryable<Entity1Type> Get()
{
    return _myRepository.GetQueryable();
}

and this new endpoint is accessible from /api/ControllerName?$ODataFilter=...

However, I'd like to return data conditionally from _myRepository1 or _myRepository2 using the very same endpoint

If I use the same signature, Entity2Type must be cast to Entity1Type to be returned

I tried

return _myRepository2.GetQueryable().Cast<Entity1Type>();

But it fails :

Unable to cast the type 'MyEntities2' to type 'MyEntities1'. LINQ to Entities only supports casting EDM primitive or enumeration types.

I also tried :

return _myRepository2.GetQueryable().ToDTO<Entity2, Entity1>();

It works, but the views have more than 1M rows and it loads all rows, which is not acceptable

The ToDto<> method came from : https://stackoverflow.com/a/8819149

I also tried following @DavidG comment :

Mapper.CreateMap<Entity2Type, Entity1Type>

return _myRepository2.GetQueryable().ProjectTo<Entity1>();

But it fails with this error :

The entity or complex type 'Entity1Type' cannot be constructed in a LINQ to Entities query."

How can I create only one endpoint, returning queryable data from _myRepository1 or _myRepository2 with good performance ?

You can't cast but you should be able to select: `_myRepository2.GetQueryable().Select(c => new Entity1 { ... copy all properties here ... })` — Evk, Nov 12 '21 at 12:39
It would probably work but as I have a lot of properties, is there any other way to do it automatically insead of select them one by one ? — user2687153, Nov 12 '21 at 12:47
If you don't want to write out all of the properties you could use Automapper and use the [`ProjectTo` feature](https://docs.automapper.org/en/stable/Queryable-Extensions.html). — DavidG, Nov 12 '21 at 12:51
@DavidG See my edit, I tried your solution but it keeps failing at execution-time — user2687153, Nov 12 '21 at 13:03

Orkad · Answer 1 · 2021-11-12T12:49:40.927

0

You could try to use a common DTO that match your 2 entities and write the proper projection.

return _myRepository2.GetQueryable().Select(e2 => 'write your projection from e2 to dto')

same for you entity1 :

return _myRepository1.GetQueryable().Select(e1 => 'write your projection from e1 to dto')

also about the ToDTO<T1, T2) exposed in the other post. There is a "ToList()" inside that execute the query.

edited Nov 12 '21 at 12:49

answered Nov 12 '21 at 12:44

Orkad

630
5
15

As I have more than 50 properties and maybe more in the future, should I really project all properties one by one ? Is there any way to do it automatically (based on their name for example) ? – user2687153 Nov 12 '21 at 12:46
1

You can use the ToDto<> removing the ToList(). Or you can consider using a mapping tool like automapper to do the job – Orkad Nov 12 '21 at 12:51
You can't use that ToDto here, because even without ToList it materializes the source collection (by doing foreach on it). – Evk Nov 12 '21 at 12:55

score 0 · Accepted Answer · answered Nov 16 '21 at 12:35

I've finally found a solution, but I'm not sure it's the most elegant :

I've changed my Mapper.Map<> like this :

public IQueryable<Entity2> Get(ODataQueryOptions opts)
{
    Mapper.CreateMap<Entity2Type, Entity2Type>() //Yes, Entity2 to Entity2, no typo
        .ForMember(d => d.Prop1, option => option.MapFrom(src => someCondition ? src.Prop1 : src.Prop2))
        .ForMember(d => d.Prop3, option => option.MapFrom(src => someCondition ? src.Prop3 : src.Prop4))
        .IgnoreAllNonExisting();

    var query = opts.ApplyTo(_myRepository2.GetQueryable()) as IQueryable<Entity2>;
    var results = Mapper.Map<IList<Entity2>>(query.ToList());

    return results.AsQueryable();
}

It returns a IQueryable<EntityB>, so my user can use OData filters. They are applied thanks to ApplyTo<> (so before the request is executed), which avoids to load the entire table in memory but only returns the fetched results.

Cast IQueryable to IQueryable with better performance

2 Answers2