Want a Strongly Typed Result from a JOIN in .netTiers

Question

Given a query like:

SELECT table1.field1 FirstField, table2.field2 SecondField
    FROM table1
    INNER JOIN table2 ON table1.FK = table2.PK
    WHERE table1.somefield = 'somevalue';

My objective is to return a strongly typed result set using .netTiers. I assume that I cannot use a view because the WHERE clause needs a parameter, which cannot be passed to a view. A stored procedure can be passed the 'somevalue' parameter but returns a weakly typed DataSet or DataReader.

I figure I'm just missing a concept here. Just to be clear, what I'd like to end up with is to be able to write something like this:

TList <some-entity-name> entityList = DataRepository.SomeProvider.Get( "somevalue" );

foreach ( some-entity-name entity in entityList ) {
    DoSomethingWith( entity.FirstField, entity.SecondField );
}

I'd like to avoid a solution that involves a server-side filter after the query has executed; the tables involved are very large.

Answer 1

Create a view and use the strongly typed ParameterBuilder object to filter the view on the specific column. I can't quite remember what layer this object is in.

This is how you would use it:

MyViewParameterBuilder builder = new MyViewParameterBuilder();
builder.AppendEquals(TableColumn.Column, "value");
DataRepository.MyViewEntityProvider.Find(builder.GetParameters());

I may be wrong, but i do not believe net-tiers actually 'filters' the TList/VList object with the method above, which is what the name of the object would suggest. The builder generates there where clause and nettiers runs a query against your database using this clause.

Your 2nd option is to try generating a stored procedure, but only if the resultset of your stored proc matches the schema of one of the tables in your database. Otherwise, net-tiers will not know how to generate against it. You can read more on that here

Answer 2

If you want a TList collection with a join you'll need to create a custom stored procedure. Solution here: http://blog.benpowell.co.uk/2009/02/paging-and-sorting-in-nettiers-custom.html

If you have scenario like this:

• Account (PK AccountId)
• Client (PK ClientId)
• ClientAccount (PK ClientId, AccountId)

As long as you have the correct foreign keys setup, .NetTiers will provide you with the appropriate method:

AccountService s = new AccountService();
TList<Account> accountCollection = s.GetByClientIdFromClientAccount(1);

There is also an overloaded method generated that supports paging (sadly no orderBy clause).