Point well taken. Of course, the main thrust of the post was to integrate LINQ with the original three-tier architecture, and to keep the provider model intact. To SPROC or Not To SPROC is pretty much a side issue to that.

Hi all - came across and 'interesting' approach to using in lists via linq today (you know, the old select * from product where productID in(1,2,3,4). basically, you (as with everything linq), do it the other way around. Consider the following:

        public List<Product> GetProducts(List<int> productIDs)
        {
            using (NorthwindDataContext dc = new NorthwindDataContext())
            {
                var products = from p in dc.Products
                             where productIDs.Contains(p.ProductID)
                             select p;

                return products.ToList();
            }
        }

here, we basically use the List<int> productIDs as the inlist BUT instead turn it the other way and request productIDs.Contains(p.ProductID) in the where clause. looks odd at 1st but makes sense once you get your head around it.

anyway, thought i'd 'share' this find.



jimi

http://www.originaltalent.com

btw - i also found a nice little article regarding multiple updates/deletes using linq (something i've found to be an issue when looking at the posts/forums linq implementaion). anyway, this may be interseting for some:

http://www.aneyfamily.com/terryandan...NQ-to-SQL.aspx

jimi

http://www.originaltalent.com

To those who may be interested:

I am just finishing up "Part 2" of implementing LINQ in TheBeerHouse, which will deal with concurrency control. I should be able to post that in a day or two.

lee,

in the past (using odbc and sqlclient), i've always used the BLL layer to handle concurrency via the TransactionScope object

i.e. typical use in the BLL layer would be:

// other code omitted...
using (TransactionScope ts = new TransactionScope())
{
    try
    {
        // do your update/delete stuff here
        // there could be further calls to other BLL objects that have TransactionScope() here..
        // Complete the Transaction
        ts.Complete();
    }

    catch
    {
       // report any error arising here and pass back to UI
    }

    finally {} // clean up any mess as required :)
}

In linq, is this behaviour not supported (and/or recommended??). I had seen the ConflictMode parameter in the SubmitChanges method but had assumed that we'd want to handle this stuff further upstream in the TBH model. The reason i'd been using this (TransactionScope) was due to parent child relationships (nested TransactionScopes()) and the need to keep them in sync. The BLL seemed like the most obvious place to site this logic as it transparently operates across different physical databases (if required) and will only update/delete the rows if all conditions are valid and if the editing user has established his/her 'supremacy' in the scenario. also, placing it in this layer meant that i didn't have to create seperate logic for each new 'provider' type that i attached to my model.

as ever, i'm sure you'll set me straight on this :). all the best for now.


jimi

http://www.originaltalent.com

lee - of course, you could be referring to the issue of resolving those problems before the db is 'hit', in which case this article might allude to a few truths:

http://www.matshelander.com/wordpress/?p=79

looking fwd to seeing your 'take' on this whichever way..

jimi

http://www.originaltalent.com

Wow... is this guy a bit off the deep end or what?

I swear, if Microsoft came up with a goose that laid golden eggs, someone would blog about how they should be rounder and shiner, and while they're at it, what about giving us platinum eggs too?

I think his pronouncement that optimistic concurrency in L2S is "broken", just because it doesn't work exactly like it does in NPersist (his O/R tool of choice) is way overstated.

For example, the lack of support for WhenChanged on deletes, which he claims to be a "showstopper", could be remedied by splitting up a table into different objects, or across different contexts.

He raises a few interesting issues, but I don't agree with his "broken" conclusion. Sorry.

TransactionScope is fully supported in LINQ, and will span multiple classes and multiple connections easily.

First, placing the logic in the DAL does not preclude operation across different physical databases, if you write a provider that supports that.

Second, as to your point about placing the concurrency logic in the BLL... I tend to agree with you that this would be the way to go, as long as you are committed to implementing OC from the get-go. Heck, I've even said before that OC should have been part of TheBeerHouse from the beginning. If I could go back and re-develop the application from scratch, that's probably what I would have done.

But, stop for a minute and think it through carefully. To properly implement OC in the BLL means you've got to pass in the original values to the BLL Update methods. In TheBeerHouse, that means reconfiguring the ObjectDataSources to support retaining and passing those values... which means a rewrite of the BLL Update and Delete methods to handle the new parameters... which means a rewrite of the abstract providers -- for example, UpdateArticle(ArticleDetails article) would become UpdateArticle(ArticleDetails originalArticle, ArticleDetails changedArticle)... which means a rewrite of all the supporting implementing providers (LinqToSqlArticlesProvider, SqlArticlesProvider, and any others you may have added on your own)... which in turn means a rewrite of all the associated stored procedures!

That, my friend, is practically a whole other book. A total overhaul of TheBeerHouse is way outside the scope of what I am trying to show here. I am only trying to demonstrate using LINQ in a new provider that functions in the EXISTING framework. Implementing OC in the BLL as it stands now would necessitate a somwhat involved reworking of the application, as I've outlined above. And if you recall, NOT doing that was the whole point to begin with. :)

I've gotta to admit, I haven't tried this approach yet since I'm neck deep in my project. But I'll be sure to try out this approach when I need to work with LINQ.

As for the following, I didn't think creating a new topic would be necessary and figured posting here would at least revive it and bring it back to page 1. Since virtually 1/2 the page is "sticky" and posts are getting bumped to page 2 quite fast.

Both you and Doug would have a better opinion than I would, so I'll leave it up to you guys for your opinions, if you're interested of course .

As you may have read in a post about SQL design, I've decided to completely normalize my database, which includes the (AddedBy, AddedDate). In my case I need to include UpdatedBy, Date, and what fields were updated. (Which makes me ask, at the very least, why wasn't LastUpdatedBy, LastUpdatedDate not added into this project) It seemed so important to included AddedBy & AddedDate... might as well include the last time it was updated and by whom.

Anyways, so I searched around for the best way to create an "Audit Trail". While still not 100% confirmed, I came across a BLOG about using LINQ to SQL Audit Trail.

http://blog.matthidinger.com/2008/05...uditTrail.aspx

I haven't put this to the test yet, but I read through it and it looks like a good possibility. Just throwing it out there as another "specialty on the LINQ menu".

Ronnie

oh.. I forgot... Jimi, your opinion would be appreciated too !!

As you mentioned, normalization got "bastardized" ... my 4 denormalized tables have turned into 30 smaller ones... But it runs awsome... :D Thanks Jimi