Evil Framework about non orderd but distinct select
I was able to create some stuff using EF. I must admit that EF is nice tool. It help a lot with basic daily database work. I’m not very experienced ORM user, but must admit - EF is so easy to use and got all features of LINQ expressions. I was amazed how easy I can add new table to in DB and then just use it. Some problems occurs when DB lose track with code (e.g. local development db) but are pretty easy to detect. Another great thing in EF is designer. Just create desired data model and then generate tables from it. So, why EF is evil?
I have to get some data from DB. To do it I must join 3 tables. Quite easy… but not for me. I also have to order data and select them distinct. Still not hard task, less than 10 lines of formatted SQL query. I created something similar to
from table1 t1 from table2 t2 from table3 t3 where t1.id == desired.id && t2.table1_id == desired.id && t2.table3_id == table3.id && t3.isDesired == true orderby t1.order select t1.Object
OK, query worked but as a result I got each value multiple times. No problem, I can use
Not so easy. When I tried that my list got shuffled. I was confused and search for solution.
Firstly I wanted put distinct keyword somewhere in query. But I can’t. Second try was to omit
orderby in query and put it after list.
Hopefully I can use one filed in
t1.Object to store data from
t1.order and sort by it.
Problem was that you can only call constructors in this select statement -
Object have only FactoryMethod.
Because table1 contain small data, I could take
t1 entities and after distinct them and sort, exclude from them list of
Another approach is use grouping showed here I’ve got working solution so I put a little effort to read this article and promise to fix it when I do other more important stuff.