How can I use Where("...") after calling ToDynamicArray

[MaklaCof]

Hi,
I am trying to filter data after query it from database. Something like this:

var data0 = await this._context.Set(typeof(Entity1)).Where("Id<10").ToDynamicArrayAsync();        //Works
var data1 = await this._context.Set(typeof(Entity1)).ToDynamicArrayAsync();        //Works
var data2 = data1.AsQueryable().Where("Id<10").FirstOrDefault();          //Doesn't work.

but I get error:

One or more errors occurred. (Operator '<' incompatible with operand types 'Object' and 'Int32')

I also tried this:

var data0 = await this._context.Set(typeof(Entity1)).Where("Id<10").ToDynamicArrayAsync();        //Works
var data1 = await this._context.Set(typeof(Entity1)).ToDynamicArrayAsync();        //Works
var data2 = data1.AsQueryable().Where("Url=@0", "Test").FirstOrDefault();          //Doesn't work.

but I get error:

One or more errors occurred. (Target object is not an ExpandoObject)

Am I doing something wrong, or is this not possible?

Here is also online demo.

[StefH]

As long as you are using IQueryable, you can keep using Dynamic Linq.
But once you have a used ToList() or ToArray, it's dynamic. and the property types are lost..

[MaklaCof]

I am not sure what this means?

That I can not achieve what I am trying to do and that is:
query entire table and then use SingleOrDefault on half of records. Currently I call SingleOrDefault with dynamic linq for every record on database, but this means a lot of round trips, and it is very slow. My idea was to first query entire table and then search them with SingleOrDefault.

[StefH]

Something like:

var q = this._context.Set(typeof(Entity1)).AsQueryable();
var result1 = q.Where("Id<10").FirstOrDefault();
var result2 = q.Where("Id> 50").FirstOrDefault();

[MaklaCof]

Understand but this will trigger 2 round trips to database.

[StefH]

You can use:

var array = await _context.Set(typeof(Entity1)).Where("Id < 10").ToDynamicArrayAsync<Entity1>();
var result = array.AsQueryable().Where("Id < 10").FirstOrDefault();

Does this work for you?

[MaklaCof]

No, because I don't know if I have Entity1 or Entity2. I am using the same API for both (all) entities. That's why I need dynamic.
Same API, same code and input parameter type defined which entity is queried:

public async Task<bool> Import(Type entityType)
{
    var data1 = await this._context.Set(entityType).ToDynamicArrayAsync();        //Works
    var data2 = data1.AsQueryable().Where("Id<10").FirstOrDefault();          //Doesn't work.
}

Logic behind this is importing entities which has relationship:

public class MyEntity 
{
    ...
    public int Entity1Id {get;set;}
    public Entity1 Entity1 {get;set;}
    ...
}

So when importing MyEntity I need to found correct record for Entity1Id. So I don't have generic parameter.

I probably want to use your library in a way that was not intended.

[StefH]

I just found out that using the newly added functionality about Cast (#249) will do the trick.

var array = _context.Set(typeof(Entity1)).Where("Id < 10").ToDynamicArray();
var result1 = array.AsQueryable().Select($"Cast(\"{typeof(Entity1).FullName}\")").Where("Id < 10").FirstOrDefault();

Can you try this?

[MaklaCof]

Outstanding.