Hello,

I'm a trial version 3 user running against Oracle and have a couple questions that came up from my project manager:

First, I have entities that are backed by tables and have written a simple LINQ query and a native filter against that entity:

            var metaData = new LinqMetaData(adapter);
            var accounts = (from m in metaData.Master
                            where m.Zip == txtZipCode.Text
                            select m).ToList();

            var filter = new RelationPredicateBucket(
                MasterFields.Zip == txtZipCode.Text);
            var accounts = new EntityCollection<MasterEntity>();
            adapter.FetchEntityCollection(accounts, filter);

His question to me is that in the future, if we decide to retieve the Master entities via a stored procedure or view, he wouldn't want similar code that our developers have written to change. He doesn't want them to be concerned about where the data is coming from, he wants that abstracted away from them.

He would like to go into the model and where the Master entity is currently backed by a table, change it to be backed by a SP which returns a result set or maybe even a view.

How can this be done within the framework?

His second question was if we decide to update some of the entities back to the DB via stored procs, how do we specify the Insert, Update and Delete procedures within the model?

Thanks,

Ronnie

ronnieb wrote:

I'm a trial version 3 user running against Oracle and have a couple questions that came up from my project manager:

First, I have entities that are backed by tables and have written a simple LINQ query and a native filter against that entity:

            var metaData = new LinqMetaData(adapter);
            var accounts = (from m in metaData.Master
                            where m.Zip == txtZipCode.Text
                            select m).ToList();

            var filter = new RelationPredicateBucket(
                MasterFields.Zip == txtZipCode.Text);
            var accounts = new EntityCollection<MasterEntity>();
            adapter.FetchEntityCollection(accounts, filter);

His question to me is that in the future, if we decide to retieve the Master entities via a stored procedure or view, he wouldn't want similar code that our developers have written to change. He doesn't want them to be concerned about where the data is coming from, he wants that abstracted away from them.

If you want such separation, you should first move business logic. In that code you are building the filter with a GUI object parameter, so your GUI is full of linq queries. An OR/M tool can give a lot of advantages but they have to be used with the proper design.

ronnieb wrote:

He would like to go into the model and where the Master entity is currently backed by a table, change it to be backed by a SP which returns a result set or maybe even a view.

There is nothing too generic. As you can see, an Entity is different from a TypedView in many ways. For example, the view may or may not have a PK, or maybe it's not editable. So the resulset of an StoreProcedure is. The result of an SP can be anything, not even the same thing if you pass different parameter. My point is: if in the future you change such thing, that is a big change, and some code has to be rewritten.

ronnieb wrote:

How can this be done within the framework?

Well, in V3 you can map the resulset of an StoredProcedure into an TypedView. I think that could help you. The original programmer's custom code could be un-touch.

ronnieb wrote:

His second question was if we decide to update some of the entities back to the DB via stored procs, how do we specify the Insert, Update and Delete procedures within the model?

We don't support that behavior. Please read these posts written by Frans Bouma, LLBLGen's Lead Developer to understand why:

Stored procedures are bad, m'kay? http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Stored Procedures vs. Dynamic Queries. http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx

Yay! A new Stored Proc vs. Dyn. Sql battle! http://weblogs.asp.net/fbouma/archive/2006/05/26/Yay_2100-A-new-Stored-Proc-vs.-Dyn.-Sql-battle_2100.aspx

Move away from stored procedures or not? http://weblogs.asp.net/fbouma/archive/2003/06/16/8731.aspx

The SP Benchmark code http://weblogs.asp.net/fbouma/pages/7049.aspx

Roundtrips and the real bottlenecks http://weblogs.asp.net/fbouma/archive/2003/11/19/38519.aspx

Thanks for the response.

I see the reasons for using dynamic SQL vs SP, but our DBA is moving towards only using SP's for updating the tables.

He's using one procedure for each table and one of the parameters is a code to indicate whether this is an Insert, Update or Delete.

In our current app, I pass him in two string fields, one containing a delimited list of field names and the other containing a delimited list of values for those fields.

Is there a way I can override the save method for an entity, grab the list of fields and values and call his SP to do the database save?

Thanks,

Ronnie

ronnieb wrote:

Is there a way I can override the save method for an entity, grab the list of fields and values and call his SP to do the database save?

In short no. Well, in theory you can, but that is too much work in my opinion, I don't even can imagine how complicated it would be. The best approach if that is needed is call the SP. For example, you have a SaveBananas Method in your business logic layer, in there you receive the entity to update and you can call the stored procedure to save it. So you are not using the entity persisting logic, just are just calling stored procedures. This is something you have to code, or you can create your own templates so they write the code for you.

This, however is not ideal, and you are not using a lot of interesting features of the framework (auditing, validation, authorization, field versioning, dynamic query construction performance, recursive saves, entity-entity synchronization, etc.). IMHO, this is something your Team (including your DBA) should consider.

Hope helpful