does not work with navigation properties

[mithom]

DbContext.DeleteRangeAsync<MyTable>(table=>
                    table.navProp!= null && table.navProp.SecondNavProp.ThirdNavProp== thirdNavPropInstance);

results in an error, equalityOperator not found

when changing the last navProp to it's Id and comparing with the instance.Id, it gives an 'invalid property error' (EDIT: navPropProp had to be navProp)

[yangzhongke]

Did your lambda work well with Select? For example, dbctx.MyTables.Where(table=> table.navPropProp!= null && table.navProp.SecondNavProp.ThirdNavProp== thirdNavPropInstance) If the lambda above doesn't work either, the Delete version doesn't work well too. Because my library translate lambda into SQL based on Where. If the Select version works well, please let know the DBMS you used.

[mithom]

the Where clause did work, the old code i had hoped to replace was this (it worked, but was slow due to fetching):

DbContext.MyTable.RemoveRange(DbContext.MyTable.Where(table=>
        table.navProp!= null && table.navProp.SecondNavProp.ThirdNavProp== thirdNavPropInstance))

i am not 100% sure what you mean with DMBS, but i was user the optionsBuilder.UseBatchEF_MSSQL(); as we are using azure T-sql databases. If you want me to do some experiments to investigate the issue, i'm willing to do them

[yangzhongke]

Thanks for your feedback. I'll try to reproduce your issue. If possible, can you provide me with reproducible code snippets instead of the demo code above? Thanks

[mithom]

the issue is on my working PC, i will try to recreate something over there tomorrow

[yangzhongke]

I should find out the issue. Do you mean"DbContext.DeleteRangeAsync(table=> table.navProp!= null && table.navProp.SecondNavProp.ThirdNavProp== thirdNavPropInstance);" works well, but "DbContext.DeleteRangeAsync(table=> table.navProp!= null && table.navProp.SecondNavProp.ThirdNavProp.Id== thirdNavPropInstance.Id);" doesn't. Right? If the answer is yes, does "DbContext.MyTable.RemoveRange(DbContext.MyTable.Where(table=> table.navProp!= null && table.navProp.SecondNavProp.ThirdNavProp.Id== thirdNavPropInstance.Id))" work well?

[mithom]

sorry if this was unclear, neither of the 2 instances using DeleteRangeAsync works. They both give a different error though. The first one using only objects/navigationProps gave an error that it couldn't find the equality operator. Using the Id at the end resulted in the second error where it said: ThirdNavPropId is not a valid property i used ThirdNavPropId instead of ThirdNavProp.Id as to prevent an unessecary join operation. Once again, the exact errors i can only recreate tomorrow.

i had the feeling it was trying to fetch the ThirdNavPropId from the initial table instead of the SecondNavProp table. (A.B.CId --> tries to find Cid on A instead of on B). that's the feeling i had, but didn't look into it yet

[yangzhongke]

Do those two lambda expressions work well with "DbContext.MyTable.RemoveRange(DbContext.MyTable.Where)"?

[mithom]

yes they do, the only reason i wanted to replace them was for speed. (i think easiest will be to wait for tomorrow when i have the example project rdy, i will include the working slow version)

[yangzhongke]

If they work well with "DbContext.MyTable.RemoveRange", maybe because they result in "Client side evaluation", which means "table.navProp.SecondNavProp.ThirdNavProp.Id== thirdNavPropInstance.Id" is evaluated in memory of client computer instead of DB server-side. Could you please check the Select SQL statement of "DbContext.MyTable.RemoveRange(DbContext.MyTable.Where)" to check if it's a client-side evaluation or a server-side one?

[mithom]

oooh, that's smart, i think you are correct here, i will check it tomorrow as i can't access the code rn, but i remember it was generating massive amounts of sql, so i assume you might be correct on the client side evaluation. i will confirm tomorrow. I suppose I would need to find a way to get it to server-side if i want it to work with this package, right?

[mithom]

btw my current solution was to convert it to raw sql with inner joins, but i would prefer it staying in lambda's if i can get this sorted out

[yangzhongke]

Do you mean the Where part of your "DbContext.MyTable.RemoveRange(DbContext.MyTable.Where)" can be translated into a SQL with inner joins correctly?

[mithom]

manually yes, but i don't think it is a statement that would be auto generated. i combined the null check and the first id into an inner join. the rest would be left or inner joins depending on required/optional. it looks like this:

select * from myTable innerjoin navProp on myTable.navPropId = navProp.Id
    inner join secondNavProp on navProp.secondNavPropId = secondNavProp.Id
    where secondNavProp.thirdNavPropId = {0}

with {0} being the Id of my thirdNavProp being filled in. As mentioned before, the second inner join could be a left join, but in my case this was a required relationship, so i made it an inner anyways.

[yangzhongke]

I'm confused. Can you provide me with codes which can run on my computer, including entity classes, DbContext and DbContext.DeleteRangeAsync() tomorrow?

[yangzhongke]

Any update?