Hi there,

There's something I've noticed which I'm not sure is true or whether its just blindingly obvious to everyone else...

So, if I have a view, v1, in the database and I want to join to it, for example, within another db view, v2, then this is trivial.

Lets say I instead want to define v1 and v2 in my app using LLBLGen code, then as far as I can see, I can't do this because there's no way to encapsulate v1 such that it can be referred to by v2. I can call the code version of v1 from multiple places in my app just to get its output, but as far as other queries which would like to use v1 are concerned, v1 is defined inline.

One way around this is to define v1 as a TypedList but then only the simplest of views can be defined in this way. I can't add 'case' statements and sub-queries for example.

So I reckon I'm forced into defining v1 at the db which always makes me feel a bit heavy because my code is that much more dependent on T-SQL and my business layer starts being spread to two different tiers. Or I can duplicate v1's definition when I define v2 which is obviously bad.

Any thoughts?

Ian.

Hi Ian,

I don't got you 100%. What I see is that you have two views on you DB. Then you want to use them in your code, and relate them at code?

Besides the TypedView and TypedList, you can map a database view as an Entity. That way you could create DynamicList to relate the entities (mapped from views) and select some fields of this and that, etc.

Does that make sense in your scenario? or Did I misunderstand you?

Hi daelmo,

What I see is that you have two views on you DB. Then you want to use them in your code, and relate them at code?

I want to define them in code _and _ relate them in code. (To avoid storing the query in T-SQL and spreading the business layer to the db.)

Ian.

Ian wrote:

Hi daelmo,

What I see is that you have two views on you DB. Then you want to use them in your code, and relate them at code?

I want to define them in code _and _ relate them in code. (To avoid storing the query in T-SQL and spreading the business layer to the db.)

Ian.

Ok. Then you should use DynamicList. You can do almost everything in there. Please post the approximate T-SQL of them and how you would want to relate them.

Well to keep it simple, here are two db views...

CREATE VIEW v1 AS

SELECT col1 + 1 AS colSum FROM tbl_1


CREATE VIEW v2 AS

SELECT colSum FROM v1

..and lets say that tbl_1 => 'entity1'.

What I'm after is to define v1 and v2 with LLBLGen code such that v2 is defined in terms of v1 _without _re-defining v1.

Is Linq an option here?

I'm stuck on C# 2.

But that sounds like I'm correct in saying this can't be done with LLBLGen code.

I don't think I'm asking for anything obscure here. I just want to be able to factor queries to maximize their granularity. I'm already doing this but the queries are going in the db as views.

Hi Ian,

Yes, you are able to do relations in code. I am doing this successfully in VB.NET 2.0.

For example, here I am successfully using a TypedList as a starting point, and modifying it directly in code by adding relations, filters, etc.

Hope this helps!

Ryan

        'TypedList we will modify
        Dim myTypedList As New ReportYtdsummaryByGroupTypedList

        'Create a New Query based on the TypedList above
        Dim myQuery As New TypedListDAO
        Dim myFields As ResultsetFields = myTypedList.BuildResultset
        Dim myRelations As RelationCollection = myTypedList.BuildRelationSet
        Dim myDataTable As New DataTable
        myDataTable.TableName = myTypedList.TableName


        Dim myFilter As New PredicateExpression
        Dim mySort As New SortExpression

        'Special Relations
        '-----------------------------------------------------------------------------------------------------

        'Special Relation - FixedCostType
        myRelations.Add(FixedCostEntity.Relations.FixedCostTypeEntityUsingFixedCostTypeId, JoinHint.Left)

        'Special Relation - Join on Fixed Costs (Multiple Relations to one table)
        Dim myFixedCostRelation As IEntityRelation = New EntityRelation(RelationType.OneToMany, True)
        myFixedCostRelation.AddEntityFieldPair(PlanFields.BenefitId, FixedCostFields.BenefitId)
        myRelations.Add(myFixedCostRelation, JoinHint.Left)


        'Additional Fields
        '-----------------------------------------------------------------------------------------------------

        'Define Field for Special Fixed Costs
        Dim myFieldCount As Integer = myFields.Count - 1
        myFields.Expand(8)
        myFields.DefineField(ContractTierFields.SortOrder, myFieldCount + 1, "TierSortID")            'ContractTier Sort ID
        myFields.DefineField(FixedCostFields.Id, myFieldCount + 2, "FixedCostID")                      'Fixed Cost ID
        myFields.DefineField(FixedCostFields.AmountPepm, myFieldCount + 3, "FixedCostAmountPEPM")      'Fixed Cost PEPM
        myFields.DefineField(FixedCostFields.TierId, myFieldCount + 4, "FixedCostTierID")              'Fixed Cost Tier ID   (Composite = NULL     or Normal = NOT NULL)
        myFields.DefineField(FixedCostFields.NetworkId, myFieldCount + 5, "FixedCostNetworkID")      'Fixed Cost Network ID  (Applies to All = NULL  or Normal = NOT NULL)
        myFields.DefineField(FixedCostFields.Name, myFieldCount + 6, "FixedCostName")                  'Fixed Cost Name
        myFields.DefineField(FixedCostTypeFields.Name, myFieldCount + 7, "FixedCostType")              'Fixed Cost Type
        myFields.DefineField(FixedCostTypeFields.Id, myFieldCount + 8, "FixedCostTypeID")              'Fixed Cost Type ID

        'Retreive Data from Database
      '-----------------------------------------------------------------------------------------------------
        myQuery.GetMultiAsDataTable(myFields, myDataTable, 0, mySort, myFilter, myRelations, True, Nothing, Nothing, 0, 0)

Your results are now contained in myDataTable.

Hi Ryan,

I think that's cheating. You're _starting _with a TypedList, extracting the query features of the typed list and using them to create a new extended query.

But what if the TypedList was defined in code? Or to put it another way, you couldn't take...

myQuery.GetMultiAsDataTable(myFields, myDataTable, 0, mySort, myFilter, myRelations, True, Nothing, Nothing, 0, 0)

..pass it to another method and create another query which extends that. I think we need to be able to create a TypedList or something in code.

Standard Linq does enable this...

            List<int> ints = new List<int>();
            ints.Add(1);
            ints.Add(2);
            ints.Add(3);
            
            var intsPlus1a = from i in ints select i + 1;

            ints.Add(4);
            ints.Add(5);

            var intsPlus1b = (from i in intsPlus1a select i).ToArray();

..gives... { 2, 3, 4, 5, 6 }. (I added 4 and 5 to ints to prove that 'intsPlus1b' has been generated from a reference to the first query and not just a set of data.)

I think that LLBLGen needs an IEnumerable type interface so that we can easily chain up our queries.

Ahh, well looks like you can do this sort of thing with LINQ to LLBLGen. Awesome.

Perhaps its time to get a better hosting company...