The SQL-99 with clause is used for substitute where conditions, if you have a sql with multiple sub selects. Puhh. It’s Hard to explain. But: Imagine you have a sql query like the following one:

select * from tp_anwender where anwender in

 (select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 1 union all

  select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 2 union all

  select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 3);

Sure. It’s not the best example. But you can see, that the where conditions in the sub selects are almost the same. Only the "Rechte" column differs. This can be very good optimized, when using the with clause.

with sub01 as (

      select * from tp_entries e where Computer = ‘xyz’ /* more queries */

)

select * from tp_anwender where anwender in

 (select anwender from sub01 where Rechte = 1 union all

  select anwender from sub01 where Rechte = 2 union all

  select anwender from sub01 where Rechte = 3);

To build such queries, the AdFactum ObjectMapper .NET does now support the "with clause" for Oracle and the Sql Server. Microsoft Access and SqlServerCE won’t support the with clause.

WithClause subClause = new WithClause("sub01", typeof(TpEntries),

    new AndCondition(typeof (TpEntries), "Computer", "xyz"));

SubSelect anwender01 = new SubSelect(typeof(TpEntries), "Anwender",

    new AndCondition(typeof(TpEntries), "Rechte", 1),

    new TableReplacement(typeof(TpEntries), "sub01"));

SubSelect anwender02 = new SubSelect(typeof(TpEntries), "Anwender",

    new AndCondition(typeof(TpEntries), "Rechte", 2),

    new TableReplacement(typeof(TpEntries), "sub01"));

ICondition selection = new ConditionList( subClause,

    new InCondition(typeof(TpAnwender), "Anwender", 

    new UnionAll(activityId01, activityId02)));

using (AdFactum.Data.ObjectMapper mapper = OBM.CreateMapper(Connection))

{

    int result = mapper.Count(typeof(TpAnwender), selection);

    …

)

If you have any questions to this new functionality, don’t hesitate to ask.

That’s all for now folks.

Wish you a great weekend.

Cheers

- Gerhard