Greeting,

I'm use llbl version 2.6 ,SQL 2008,VS 2008,C#

I had task that rewrite a PROCEDURE from T-sql to ORM by C#, this PROCEDURE contains a table variable or to be more accurate many of table variables this is sample script

DECLARE @NAV TABLE(
        cntID int Identity(1,1) ,
        PortfolioNo int,
        AccountNo int, 
        TotBalance Numeric(30,12),
        TotBalance_Bid Numeric(30,12), 
        Type int,
        Primary Key(cntID,PortfolioNo ,AccountNo ))


INSERT INTO @NAV 
        SELECT a.PortfolioNo, isnull(a.AccountNo,0), 
            a.TotBalance * dbo.GetExchRate(a.Currency,@PFCurrency,@AsOfDate), 
            a.TotBalance * dbo.GetExchRate(a.Currency,@PFCurrency,@AsOfDate), 1 AS Type
        FROM   @TrialBalance a 
            JOIN dbo.AccChart b ON (a.AccountNo = b.[Id]) 
        WHERE (b.SubClass = 2) 
            
INSERT INTO @NAV 
            SELECT  TrialBalance.PortfolioNo, 
                         TrialBalance.AccountNo, 
                         TrialBalance.TotBalance ,
                         TrialBalance.TotBalance , 
                         1 AS Type
            FROM  @TrialBalance TrialBalance 
            INNER JOIN dbo.AccChart ON TrialBalance.AccountNo = dbo.AccChart.Id 
            WHERE (dbo.AccChart.Parent =  (SELECT value FROM tblconfig WHERE TrxNo = 53)) AND TrialBalance.Currency = @PFCurrency

INSERT INTO @NAV 
        SELECT pffwd.PortfolioNo, isnull(@tblaccounts24,0),
              SUM(pffwd.QuantityBalance * pffwd.ClosePrice) AS MarketValue, 
              SUM(pffwd.QuantityBalance * pffwd.BidPrice) AS MarketValue_Bid, 1 AS Type
        FROM @FwdPosition pffwd     
        WHERE pffwd.PortfolioNo = @PFNo
        GROUP BY pffwd.PortfolioNo

what is the best practises to achieve this table variables and their inserts !!!

Hi mohamed,

May I ask you, Why do you need table variables for?

(Edit) Also, read this: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=15032

Hi daelmo,

I use table variable for collecting data from several tables (these tables have different sructure) after collecting data i make some aggregates on few column and update this table variable finally use it as driven table to select from it any idea for applying this table variable ???

The thread Daelmo suggested is a help desk thread (private), so you can't access it. But here is the core of it:

Please check this out: Derived tables and dynamic relations were added in v.2.6

I know Derived tables but let put this way

if i use ResultsetFields it doesn't apply table variable that source of each column in table variable comes from multiple source

my dig deal is how to cerate table variable and insert and update on this table variable

The following is quoted from that helpdesk thread:

MTrinder wrote:

LLBLGen's virtual/derived tables are only really meant to be used in a query. If you need to hold data in memory for processing reasons there are a multitude of data structures available to you - ADO.NET data tables, arrays/lists/collections/dictionaries etc of objects.

Using LLBLGen can sometimes (particularly if you have come from a heavy database background) need a fairly large shift in mentality to come round to its way of working - but it will be worth it

I second what's been said, you need to shift from processing data inside the database to processing them in your application. Use the database for fetching data and persisting them at the end, and whatever you do in between do it in your .NET application/code.

Duly noted Walaa but to second what Mohammad is trying to explain I will explain the following scenario.

A huge SPROC (1600+ lines) that heavily uses table variables. Now, a developer tasked with transferring this SPROC to the BL using LLBL; he can surly use something like a DataTable for in memory caching of Selected data from the database. LLBL offers a big barrage of SELECT tools including DerviedTables and ResultSetFields and each one of them can be used to FETCH data from the DB and then insert into the in-memory cache (like a DataTable)

The catch comes if you need to JOIN the collected data in the in-memory DataTable with some table in the DB to do your selection.

The only way out of this catch-22 position I can see is to build a huge FilterBucket with OR'ed conditions to simulate the join between the in-memory table and the DB table.

Is there a better way of doing this or at least know workarounds to do the same??

I see what you mean.

Are these derived tables very dynamic or they can be replaced by database views? Needless to say that views can be mapped into entities which then can have relations customly defined with other entities.

But if I am to replace them with table views and then map them to Entities and then use an EntityCollection as my in-memory collection of data, can I then use DynamicRelations to join my in-memory data in the EntityCollection with a DB table???

If you need a lot of temporary sets to produce the final set, it's obvious these temporary sets have to be available when the final set is produced. If you produce these temporary sets in memory, to produce the final set, based on these temporary sets, you have to transport these temporary sets to the database to produce the final set.

With derived tables, you can effectively produce these temporary sets as a derived table, and use them to filter the final set. You can join between derived tables, though it will require some code. The main thing is: try to write the query in such a way that all temporary sets are written as derived tables inside the main query so everything stays inside the database at query execution. Otherwise you have to transport data back/forth and that's not efficient.

It depends on what these temporary sets are of course. It also depends on how huge this query becomes.

I'm not sure what the final filter looks like: if the in-memory data contains customerid's for example of which you want the data of, you could do this by using fieldcomparerange predicates where you fetch the data in batches (if the # of entities to fetch is huge) by simply fetching each batch data into the same entity collection.

So to summarize your recommendation:

1- Try to build the SPROC's table variable as DerivedTables instead of DataTables because DerivedTables can be joined (DynamicRelation) with DB tables

2- Try to combine all these DerivedTables in a single query so to guarantee execution at the Database

3- If point 2 is not possible and a DerivedTable has the in-memory data to join with a DB table, then build fieldCompareRange predicates for each of the in-memory records to simulate the join to the DB table

omar wrote:

So to summarize your recommendation:

1- Try to build the SPROC's table variable as DerivedTables instead of DataTables because DerivedTables can be joined (DynamicRelation) with DB tables

2- Try to combine all these DerivedTables in a single query so to guarantee execution at the Database

3- If point 2 is not possible and a DerivedTable has the in-memory data to join with a DB table, then build fieldCompareRange predicates for each of the in-memory records to simulate the join to the DB table

Yes, and examine what the data collected in the proc is really used for for the final set. Say you want a list of customers who match a complex set of criteria. In the end, you can try to produce the set of customerid's which match the criteria and use that to fetch the customers (for example, I'm sure your setup is more complex), or use the outcome of parts of the criteria queries to produce a set which can be used in another query (the next step of the criteria production) for example.