Hi i'm working at a project where 1 datagrid shows data from several tables and some columns are a result of calculating with records from subqueries. this results in quite a huge query.

Now I was planning on starting to use entities and because of the scale of the database Entity Framework just couldn't be used to design the database.

In my search for an alternative wich can design these tables seperatly without any issues I stumbled upon LLBLGen Pro.

It looks very promising and I was wondering if queries like the following can still be created with LLBLGen Pro and how I would even begin to program such a thing:

select 
    ef006.profl, 
    ef006.matrs, 
    ef089.lengte,
    ef089.voorr, 
    isnull(
        (
        select sum(ef065.lengts) as Aantal 
        from ef065 
        where not 
            ef065.tstnd in ('012','015') and 
            ef065.profl = ef089.profl and 
            ef065.stukl = ef089.lengte
        )
    ,0) +
    isnull(
        (
        select sum(floor(ef065.lengts * (ef065.stukl / ef065.llengte))) as Aantal 
        from ef065 
        where not 
            ef065.tstnd in ('012','015') and 
            ef065.profl = ef089.profl and 
            ef065.llengte = ef089.lengte
        )
    ,0) as 'Vraag', 
    ef089.voorr + 
    isnull(
        (
        select sum(ef065.lengts) as Aantal 
        from ef065 
        where 
            ef065.tstnd in ('002','003','004','005','006','007','008','009','010','011','013','016') and 
            ef065.profl = ef089.profl and 
            ef065.stukl = ef089.lengte 
        )
    ,0) - 
    (
        isnull(
            (
            select sum(ef065.lengts) as Aantal 
            from ef065 
            where not
                ef065.tstnd in  ('012','015') and 
                ef065.profl = ef089.profl and 
                ef065.stukl = ef089.lengte 
            )
        ,0) + 
        isnull(
            (
            select sum(floor(ef065.lengts * (ef065.stukl / ef065.llengte))) as Aantal 
            from ef065 
            where not 
                ef065.tstnd in ('012','015') and 
                ef065.profl = ef089.profl and 
                ef065.llengte = ef089.lengte
            )
        ,0)
    ) as 'Besch', 
    isnull(
        (
        select sum(lengts) - 
            (
            select isnull(sum(ef025.lengtes),0) 
            from ef025 
            where ef025.porder in 
                (
                select distinct porder 
                from ef062 
                where 
                    ef062.profl = ef089.profl and 
                    ef062.lperst = ef089.lengte and 
                    not porder is null and 
                    poaf = 'N' and 
                    LEV = 'N'
                )
            ) 
        from ef062
        where 
            poaf = 'N' and 
            LEV = 'N' and 
            profl = ef089.profl and 
            lperst = ef089.lengte
        ),
    0) as 'Verw', 
    isnull(
        (
        select sum(ef065.lengts) as Aantal 
        from ef065 
        where   
            ef065.tstnd in ('002','003','004','005','006','007','008','009','010','011','013','016') and 
            ef065.profl = ef089.profl and 
            ef065.stukl = ef089.lengte 
        )
    ,0) as 'Bew', 
    isnull(
        (
        select sum(ef065.lengts) as Aantal 
        from ef065 
        where   
            ef065.tstnd in ('012') and 
            ef065.profl = ef089.profl and 
            ef065.stukl = ef089.lengte 
        )
    ,0) as 'AF' 
from ef089  
    inner join ef006 on ef006.profl = ef089.profl 
where 
    ef006.matrs = '5167' 
order by 
    ef006.matrs,
    ef089.lengte

Thanks in advance

Hi Michiel,

Welcome aboard on LLBLGen! I's good to see fresh faces.

Now with your query. Let's start disseminating it into small pieces.

Fisrt of all, your query contains muliple objects from multiple tables, including special calculated fields. What container to use? Some options:

• TypedView: these are objects that map 1:1 to database views, so if you want to write such query in a view, you can map it to a TypedView, or even map it to a Entity.

• DynamicList: These are like TypedViews, but aren't based on database views. You construct them in your code. Are very useful when you only need some portion of data.

If you decide to go on DynamicList approach, then lets examine your query.

   ...  (
        select sum(ef065.lengts) as Aantal
        from ef065
        where not
            ef065.tstnd in ('012','015') and
            ef065.profl = ef089.profl and
            ef065.stukl = ef089.lengte
        ) ...

This is a ScalarQueryExpression. It's quite easy, then you only need to apply it as a expression in the DynamicList's field.

ef065.tstnd in ('012','015') and
ef065.profl = ef089.profl and
ef065.stukl = ef089.lengte

The filters, these are quite easy to use (please take a look...). You can pass a PredicateExpression to your ScalarQueryExpression that contains those filters.

 isnull(
...
    ,0) 

You can emit the ISNULL as a DBFunctionCall.

someExpression + someExpression2 - otherExpression 

This kind of stuff can be made nesting and/or operating expressions. ScalarQueryExpression, DBFunctionCall, etc., are IExpression so you can do that.

The rest is more or less the same. My advise on this monster query is:

1. Reproduce only the principal section, to get familiar with DynamicList.

select
    ef006.profl,
    ef006.matrs,
    ef089.lengte,
    ef089.voorr
where
    ef006.matrs = '5167'
order by
    ef006.matrs,
    ef089.lengte

1. Experiment with adding the first subquery as a field of the DynamicList to get familiar with ScalarQueryExpression, IPredicateExpression and DBFuncionCall.

2. Try adding the second subquery and sum both as expressions.

3. Write the rest

The docs will be very useful here. So please take a look at them and give a spin. I have a similar code somewhere. I will find it and post it so you can see an example.

Further questions/issues? Be our guest

PS. When create a new thread, please aways post relevant information so we can help you in a better way (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

Good luck

Thank you for the quick response, the TypedView is out of the equation because the filter (ef006.matrs = '5167') is a parameter.

So trying the DynamicList you proposed wich seems to be the thing I'm looking for.

I'm able to do the following:

        Dim adapter As New DataAccessAdapter()
        Dim fields As New ResultsetFields(4)

        fields.DefineField(ProfielenFields.Profl, 0, "Profiel")
        fields.DefineField(ProfielenFields.Matrs, 1, "Matrijs")

        fields.DefineField(ProbeVoorraadFields.Lengte, 2, "Lengte")
        fields.DefineField(ProbeVoorraadFields.Voorr, 3, "Voorraad")

        Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()

        bucket.PredicateExpression.Add(ProfielenFields.Matrs = "5167")
        bucket.Relations.Add(ProfielenEntity.Relations.ProbeVoorraadEntityUsingProfl)

        Dim dynamicList As New DataTable()
        adapter.FetchTypedList(fields, dynamicList, bucket)

        DataGridView1.DataSource = dynamicList

I can figure out how to do the subqueries seperatly but have no idea how to combine them into 1 dataset that can be bound to my Datagrid.datasource

Thanks in advanced

Sincerely Michiel Alders

Your query is too huge, that I would have written it in a Stored Procedure and just call it from LLBLGen Pro, generally we don't promote or recommend using SPs, but in this case I do for the ssake of productivity.

Anyway:

I can figure out how to do the subqueries seperatly but have no idea how to combine them into 1 dataset that can be bound to my Datagrid.datasource

As David said, you'll have to add a field to the resultset, and then set it's ExpressionToApply property to a ScalarQueryExpression. Please check the ScalarQueryExpression link that David posted.

Actually i'm quite confident this query is pretty doable but i'm running into a strange problem now. I think I have these sub queries figured out. using the following code i'm not getting the exact results as expected:

        Dim adapter As New DataAccessAdapter()
        Dim fields As New ResultsetFields(5)

        fields.DefineField(ProfielenFields.Profl, 0, "Profiel")
        fields.DefineField(ProfielenFields.Matrs, 1, "Matrijs")

        fields.DefineField(ProbeVoorraadFields.Lengte, 2, "Lengte")
        fields.DefineField(ProbeVoorraadFields.Voorr, 3, "Voorraad")

        Dim vraagFilter As New PredicateExpression()
        Dim vraagValues As String() = New String() {"012", "015"}
        vraagFilter.Negate = True
        vraagFilter.AddWithAnd(LakafhandelingFields.Tstnd = vraagValues)
        vraagFilter.AddWithAnd(LakafhandelingFields.Profl = ProbeVoorraadFields.Profl)
        vraagFilter.AddWithAnd(LakafhandelingFields.Stukl = ProbeVoorraadFields.Lengte)

        fields.DefineField(New EntityField2("Vraag", New ScalarQueryExpression(LakafhandelingFields.Lengts.SetAggregateFunction(AggregateFunction.Sum), vraagFilter)), 4)

        Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()

        bucket.PredicateExpression.Add(ProfielenFields.Matrs = "5167")
        bucket.Relations.Add(ProfielenEntity.Relations.ProbeVoorraadEntityUsingProfl)

        Dim dynamicList As New DataTable()
        adapter.FetchTypedList(fields, dynamicList, bucket)

        DataGridView1.DataSource = dynamicList

this results in:

    Profiel Matrijs Lengte  Voorraad        Vraag
    001986  5167    4250    70          23176361
    001986  5167    6000    0           23170339

Everything is correct expect the Aggregate Column.

The query:

select 
    ef006.profl as 'Profiel',
    ef006.matrs as 'Matrijs',
    ef089.lengte as 'Lengte',
    ef089.voorr as 'Voorraad',
    (select sum(ef065.lengts) as aantal from ef065 where not
        ef065.tstnd in ('012', '015') and
        ef065.profl = ef089.profl and
        ef065.stukl = ef089.lengte) as 'Vraag'
from ef089
    inner join ef006 on ef006.profl = ef089.profl
where
    ef006.matrs = '5167'

results in:

    Profiel Matrijs Lengte  Voorraad        Vraag
    001986  5167    4250    70          655
    001986  5167    6000    0           NULL

Any idea why this is?

I see now I did something wrong in the filter of the subquery.

I translated:

where not
        ef065.tstnd in ('012', '015') and
        ef065.profl = ef089.profl and
        ef065.stukl = ef089.lengte) as 'Vraag'

into:

        Dim vraagFilter As New PredicateExpression()
        Dim vraagValues As String() = New String() {"012", "015"}
        vraagFilter.Negate = True
        vraagFilter.AddWithAnd(LakafhandelingFields.Tstnd = vraagValues)
        vraagFilter.AddWithAnd(LakafhandelingFields.Profl = ProbeVoorraadFields.Profl)
        vraagFilter.AddWithAnd(LakafhandelingFields.Stukl = ProbeVoorraadFields.Lengte)

but the whole where isn't negated its not where not (filters) but where not filter1 and filter2 so that resulted in:

        Dim vraagFilter As New PredicateExpression()
        Dim vraagValues As String() = New String() {"012", "015"}
        vraagFilter.AddWithAnd(LakafhandelingFields.Tstnd <> vraagValues)
        vraagFilter.AddWithAnd(LakafhandelingFields.Profl = ProbeVoorraadFields.Profl)
        vraagFilter.AddWithAnd(LakafhandelingFields.Stukl = ProbeVoorraadFields.Lengte)

This gave me the expecting results.

How would I do this?:

select sum(floor(ef065.lengts * (ef065.stukl / ef065.llengte))) from ef065

I guess the negation of the subQuery predicate is implemented in a different way than your actual query.

To make sure you can examine the generated query, please check Troubleshooting and debugging

If you want to negate the entire PredicateExression, please try the following:

        Dim vraagFilter As New PredicateExpression()
        vraagFilter.Negate = True

        Dim vraagInnerFilter As New PredicateExpression()
        Dim vraagValues As String() = New String() {"012", "015"}
        vraagInnerFilter.AddWithAnd(LakafhandelingFields.Tstnd = vraagValues)
        vraagInnerFilter.AddWithAnd(LakafhandelingFields.Profl = ProbeVoorraadFields.Profl)
        vraagInnerFilter.AddWithAnd(LakafhandelingFields.Stukl = ProbeVoorraadFields.Lengte)

        vraagFilter.Add(vraagInnerFilter)

I'v added the config settings to my configuration file but nothing seems to appear in the output window.

I've searched the forum but couldn't find any help in this matter, my config:

<?xml version="1.0"?>
<configuration>
    <appSettings>
        <add key="Main.ConnectionString" value="data source=SOV6;initial catalog=productie;integrated security=SSPI;persist security info=False;packet size=4096"/>
    <add key="SqlServerDQECompatibilityLevel" value="2" />
  </appSettings>

  <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="3" />
      <add name="ORMGeneral" value="3" />
      <add name="ORMStateManagement" value="3" />
      <add name="ORMPersistenceExecution" value="3" />
      <add name="LinqExpressionHandler" value="3" />
    </switches>
  </system.diagnostics>
  </configuration>

Are you running your application in Debug mode?

Started with F5, so that should be running in debug mode right?

The build type should be Debug not Release.

Buildtypes of all 3 projects are set to Debug.

Do you also have any idea how I should start on the following subquery:

select sum(floor(ef065.lengts * (ef065.stukl / ef065.llengte))) from ef065

One could also define additional properties holding the values that need to be displayed in the grid. Instead of a complex stored procedure, the calculatons and subqueries can be done in code.

I have used this approach often and it works fine. Visual Studio often is unable to render the grid properly in designmode, so you have to edit the grid / page in sourcemode. At runtime, there is no problem.

Buildtypes of all 3 projects are set to Debug.

Strange, are you sure nothing appears in V.S output window?

Do you also have any idea how I should start on the following subquery: Code: select sum(floor(ef065.lengts * (ef065.stukl / ef065.llengte))) from ef065

One way of doing this is to use the Constant feature of the DBFucntion call to formulate the expression, as follows:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(ef065Fields.lengts, 0);

fields[0].ExpressionToApply = new DbFunctionCall(
    "sum(floor({0} * ({1} / {0}))) ", 
    new object[] { ef065Fields.lengts , ef065Fields.stukl});

Hi, that should work in an empty query but not as a subquery:

SELECT 
    [productie].[dbo].[EF006].[PROFL] AS [Profiel], 
    [productie].[dbo].[EF006].[MATRS] AS [Matrijs], 
    [productie].[dbo].[EF089].[LENGTE] AS [Lengte], 
    [productie].[dbo].[EF089].[VOORR] AS [Voorraad], 
    (
        SELECT SUM([productie].[dbo].[EF065].[LENGTS]) AS [Lengts] 
        FROM [productie].[dbo].[EF065]  
        WHERE 
        ( 
            [productie].[dbo].[EF065].[TSTND] NOT IN ('012', '015') AND 
            [productie].[dbo].[EF065].[PROFL] = [productie].[dbo].[EF089].[PROFL] AND 
            [productie].[dbo].[EF065].[STUKL] = [productie].[dbo].[EF089].[LENGTE]
    )) AS [Sub1], 
    sum(floor([productie].[dbo].[EF065].[LENGTS] * ([productie].[dbo].[EF065].[STUKL] / [productie].[dbo].[EF065].[LLENGTE]))) AS [Vraag] 
FROM 
    ( 
        [productie].[dbo].[EF006]  
            INNER JOIN [productie].[dbo].[EF089]  ON  
                [productie].[dbo].[EF006].[PROFL]=[productie].[dbo].[EF089].[PROFL]
    ) 
WHERE 
    ( 
        ( 
            [productie].[dbo].[EF006].[MATRS] = '5167'
        )
    )

is generated with this code:

        Dim adapter As New DataAccessAdapter()

        Dim vraagFilter As New PredicateExpression()
        Dim vraagValues As String() = New String() {"012", "015"}
        vraagFilter.Add(LakafhandelingFields.Tstnd <> vraagValues)
        vraagFilter.Add(LakafhandelingFields.Profl = ProbeVoorraadFields.Profl)
        vraagFilter.Add(LakafhandelingFields.Stukl = ProbeVoorraadFields.Lengte)

        Dim fields As New ResultsetFields(6)

        fields.DefineField(ProfielenFields.Profl, 0, "Profiel")
        fields.DefineField(ProfielenFields.Matrs, 1, "Matrijs")

        fields.DefineField(ProbeVoorraadFields.Lengte, 2, "Lengte")
        fields.DefineField(ProbeVoorraadFields.Voorr, 3, "Voorraad")

        fields.DefineField(New EntityField2("Sub1", New ScalarQueryExpression(LakafhandelingFields.Lengts.SetAggregateFunction(AggregateFunction.Sum), vraagFilter)), 4)

        fields.DefineField(ProbeVoorraadFields.Voorr, 5, "Vraag")
        fields(5).ExpressionToApply = New DbFunctionCall("sum(floor({0} * ({1} / {2})))", New Object() {LakafhandelingFields.Lengts, LakafhandelingFields.Stukl, LakafhandelingFields.Llengte})

        Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()

        bucket.PredicateExpression.Add(ProfielenFields.Matrs = "5167")

        bucket.Relations.Add(ProfielenEntity.Relations.ProbeVoorraadEntityUsingProfl)

        Dim dynamicList As New DataTable()
        adapter.FetchTypedList(fields, dynamicList, bucket)

but that last field (the sum(floor... part) should be a sub query:

        sum(floor([productie].[dbo].[EF065].[LENGTS] * ([productie].[dbo].[EF065].[STUKL] / [productie].[dbo].[EF065].[LLENGTE]))) as [Vraag]
=>

        (SELECT sum(floor([productie].[dbo].[EF065].[LENGTS] * ([productie].[dbo].[EF065].[STUKL] / [productie].[dbo].[EF065].[LLENGTE])))
        FROM [productie].[dbo].[EF065]  
        WHERE 
        ( 
            [productie].[dbo].[EF065].[TSTND] NOT IN ('012', '015') AND 
            [productie].[dbo].[EF065].[PROFL] = [productie].[dbo].[EF089].[PROFL] AND 
            [productie].[dbo].[EF065].[STUKL] = [productie].[dbo].[EF089].[LENGTE]
    )) AS [Vraag], 

how can I do this?

The output window remains empty:

Greetings

You should use a ScalarQueryExpression Please check the example in the above link.

You can do the following:

IExpression expression = new DbFunctionCall(
    "sum(floor({0} * ({1} / {0}))) ", 
    new object[] { ef065Fields.lengts , ef065Fields.stukl});
...
fields.DefineField(new EntityField2("Vraag",
                new ScalarQueryExpression(ef065Fields.lengts.SetExpression(expression)), index);

This looks like its working fine. But now I need to combine 2 fields of subqueries into a new field.

So I have:

        Dim sub1 As EntityField2 = New EntityField2("Sub1", New ScalarQueryExpression(LakafhandelingFields.Lengts.SetAggregateFunction(AggregateFunction.Sum), sub1Filter))
        Dim sub2 As EntityField2 = New EntityField2("Sub2", New ScalarQueryExpression(LakafhandelingFields.Stukl.SetExpression(New DbFunctionCall("sum(floor({0} * ({1} / {2})))", New Object() {LakafhandelingFields.Lengts, LakafhandelingFields.Stukl, LakafhandelingFields.Llengte})), sub2Filter))

but in my result I need them to be summed up.

So I thought:

fields(5) = sub1 + sub2

But that would be too nice if that actually worked So it didn't.

How should I add those 2 results of the subqueries together?

Michiel,

I found this on my computer. This may be ilustrative to you. Is a complex query like yours.

// configurar campos
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(RendicionDetalleFields.PrecioAplicado, 0, "TotalVentas");
fields.DefineField(RendicionDetalleFields.PrecioAplicado, 1, "TotalComision");          
                                    
// nota: las cantidades deben convertirse primero a moneda local (por el round)     
IExpression precioAplicadoMonedaLocalExp = new Expression(
    RendicionDetalleFields.PrecioAplicado, 
    ExOp.Mul, TipoCambioFields.TipoCambioDolar);

// precio en moneda local - rounded
IExpression precioAplicadoMonedaLocalRoundedExp = new DbFunctionCall(
    "ROUND", new object[] { precioAplicadoMonedaLocalExp, 2 });

// total ventas moneda local
IExpression totalVentasNormalMonedaLocalExp = new Expression(
    precioAplicadoMonedaLocalRoundedExp,
    ExOp.Mul,RendicionDetalleFields.NumeroVentas);

// ventas promo
IExpression precioAplicadoPromoMonedaLocalExp = new Expression(
    RendicionDetalleFields.PrecioAplicadoPromo,
    ExOp.Mul, TipoCambioFields.TipoCambioDolar);

// precio en moneda local - rounded
IExpression precioAplicadoPromoMonedaLocalRoundedExp = new DbFunctionCall(
    "ROUND", new object[] { precioAplicadoPromoMonedaLocalExp, 2 });

// total ventas moneda local
IExpression totalVentasPromoMonedaLocalExp = new Expression(
    precioAplicadoPromoMonedaLocalRoundedExp,
    ExOp.Mul, RendicionDetalleFields.NumeroVentasPromo);


// total ventas
IExpression totalVentasMonedaLocalExp = new Expression(
    totalVentasNormalMonedaLocalExp, ExOp.Add, totalVentasPromoMonedaLocalExp);


// porcentaje de comisión de ventas
IExpression porcentajeAplicadoExp = new Expression(
            RendicionDetalleFields.PorcentajeComisionVentaAplicado, 
            ExOp.Div, 100);

// field[0] contiene el total de ventas en moneda local
fields[0].ExpressionToApply = totalVentasMonedaLocalExp;

// field[1] contiene el total de comisión de ventas
fields[1].ExpressionToApply = new Expression(
    totalVentasMonedaLocalExp,
    ExOp.Mul, porcentajeAplicadoExp);
    
// aplicar sumatorias
fields[0].AggregateFunctionToApply = AggregateFunction.Sum;
fields[1].AggregateFunctionToApply = AggregateFunction.Sum;

// relaciones, filtros
IRelationPredicateBucket cantidadesRendicionFilter = new RelationPredicateBucket();
cantidadesRendicionFilter.Relations.Add(RendicionDetalleEntity.Relations.RendicionEntityUsingIdRendicion);
cantidadesRendicionFilter.Relations.Add(RendicionEntity.Relations.TemporadaEntityUsingIdTemporada);
cantidadesRendicionFilter.Relations.Add(TemporadaEntity.Relations.TipoCambioEntityUsingIdTemporada);
cantidadesRendicionFilter.PredicateExpression.Add(RendicionFields.Fecha >= fechaInicio & RendicionFields.Fecha <= fechaFin);

// fetch
DataTable rendicionCantidadesResults = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, rendicionCantidadesResults, cantidadesRendicionFilter);
}

I do a lot of things with expressions (follow the variables). Then I have two complex expression and I do this:

// exp1 + exp2
IExpression totalVentasMonedaLocalExp = new Expression(
    totalVentasNormalMonedaLocalExp, ExOp.Add, totalVentasPromoMonedaLocalExp);
...

// field[0] is the total amount of sales
fields[0].ExpressionToApply = totalVentasMonedaLocalExp;

I think that is the part you are interested in. ScalarQueryExpression is also an IExpression so that should work in your case as well.

I got my query and view working. Thanks great way to quick learn the basics of the LLBLGEN code generation.

Thank you for your help.