Can OrmLite create views from POCO as it creates tables?
I need views to overcome Where<> statement limit about lamba.
Lambas in where cannot be nested and I cannot create lambas which refer to tables from joins. As I know Where<> is limited to 3 tables.
Regards.
OrmLite only creates tables not views.
How many tables do you need on Where<>? Do you have an example of a query you’d like to be able to run?
Something like this
select tab1.*, tab2.*, tab3.*
from tab1
join tab2
join tab3
join tab4
join tab5
leftjoin tab6
leftjoin tab7
where (
(
(tab1.fieldn == value1 and (tab2.fieldn >= value2 or tab3.fieldn = value3))
or
(tab1.fieldn > value4 and tab5.fieldn < value5)
)
and
(tab6.fieldn == value6 or tab7.fieldn == value7)
and so on…
)
Notice:
The problems are the Lambdas in Where that should not be bounded to the where POCOs list (i.e. Where<POCO1, POCO2…>) but allow to refer to POCOs from join.
What I mean is something like ThenBy for OrderBy. Unfortunatly ThenBy seems not be implemented for Where.
PredicateBuilder has the same limitation,
The only solution I found is to create a view which aggregate all joins so the type is only one but this is not a good design.
Regards.
Yeah in the case of 20 tables you’re better off constructing the SQL yourself and executing it with the Custom SQL APIs. We
Also if you create the Views out-of-band you can still use OrmLite’s POCOs to populate it, if needed you can use the [Alias] attribute to map it to the RDBMS View name, e.g:
[Alias("CustomerView")]
public class ViewCustomer { ... }
The service to implement is behind this tool
the service receives the json from the query builder, parses it, creates the necessary joins builds the sqlexpression and exec the sql.
All is fine except for the Where<<table1, table2, table3>> limit.
I would avoid custom sql. The service must query sqlserver, oracle and postgre databases.
Does OrmLite allow plugins? Can I write a plugin to extend the Where<> or to extend the ThenBy<>, have you some examples?
The normal way to extend OrmLite is to just write extension methods locally with additional APIs, although in this case you’d need access to the protected AppendToWhere() method, so you’d need to inherit SqlExpression<T> and have OrmLite use it by overriding SqlExpression() in a custom Dialect provider (or we could make AppendToWhere public).
But if it’s just a matter of extending the number of Where overloads we could accept a PR, although 20 seems like a lot, could you reduce the number needed by using multiple Where<T>/And<T> calls instead?
15 tables is approachable?
I’d prefer <10, is your query not able to call Where or And multiple times?
What do you mean Where or And multiple times?
I tried db.From(db.From but I cannot run it so I switched to the view.
Like adding multiple Where’s, e.g:
q.Where<Table1,Table2,Table3>((t1,t2,t3) => ...);
q.Where<Table4,Table5,Table6>((t4,t5,t6) => ...);
Exactly from where I started.
Please take a look to the following (simple) example of what I need:
var q = db.From<LRARisultato>();
q.Join<LRAAnalisi>((ris, ana) => ana.Id == ris.AnalisiId);
q.Join<LRAAnalisi, LRAContenitore>((ana, con) => ana.ContenitoreId == con.Id);
q.Join<LRAContenitore, LRARichiesta>((con, ric) => ric.Id == con.RichiestaId);
q.Join<LRAAnalisi, LRDAnalisi>((ana, dana) => ana.AnalisiId == dana.Id);
q.LeftJoin<LRDRisultato>((ris, dris) => ris.RisultatoId == dris.Id);
q.Where<LRDRisultato, LRAAnalisi>((dris, ana) => dris.AnalisiId == 8 && ana.AnalisiId == 9);
q.Where<LRAContenitore, LRAAnalisi>((con, ana) => con.Stato == 0 && (con.RichiestaId == 1 || ana.Id == 2));
q.Or<LRAPaziente, LRARichiesta>((pat, ric) => pat.Id == 5550 || ric.Id == 7770 || ric.LaboratorioRichiedenteId == 222);
q.Where<LRAPaziente, LRAAnalisi>((pat, ana) => pat.Eta == 7 && ana.Id == 2);
this is the where statement
WHERE
((“LRDRISULTATI”.“DANALISIID” = @0) AND (“LRAANALISI”.“DANALISIID” = @1))
AND
((“LRACONTENITORI”.“STATO” = @2) AND ((“LRACONTENITORI”.“ARICHIESTAID” = @3) OR (“LRAANALISI”.“IDAANALISI” = @4)))
OR
(((“LRAPAZIENTI”.“IDAPAZIENTE” = @5) OR (“LRARICHIESTE”.“IDARICHIESTA” = @6)) OR (“LRARICHIESTE”.“DLABORATORIORICHIEDENTEID” = @7))
AND
((“LRAPAZIENTI”.“ETA” = @8) AND (“LRAANALISI”.“IDAANALISI” = @9))
where every Where<>, Or<> is a couple of ()
the first issue is about the number of table I need in each condition which is more than the 2 available
a tipical condition is something like
q.Where<POCO1, POCO2, POCO3…POCO10>((p1, p2…p10) =>
(
(p1.F1 == 1 && p2.F2 == 2) || ((p3.F3 == 2 || p4.F4 == 9) && p5.F5 == 5) && … p10.F10 == 10)
);
the second issue is about the nesting, I need to get something like
WHERE
((p1.F1 = @0) AND (p2.F2 = @1)) <- first group
AND
((p3.F3 = @2) AND ((p4.F4 = @3) OR (p5.F5 = @4))) <- second group
( <- notice Where<> inside external Where<>
OR
(((p6.F6 = @5) OR (p7.F7 = @6)) OR (p8.F8 = @7)) AND ((p9.F9 = @8) AND (p10.F10 = @9)) <- third group nested inside the second group
) <- end of internal Where<>
which I find out as something like
q.Where<p1, p2>()
q.Where<p3, p4, p5>().ThenOrBy<p6, p7, p8, p9>()
and so on using ThenAndBy
ok the nesting means it will be needed to be within the one Where API.
I’ve added 15 overloads for Where, And and Or APIs in this commit.
This change is available from v4.5.9 that’s now available on MyGet.
Correct, nesting withing the one Where.
Thanks.
I will try ASAP.
Regards.