I recently purchased the 35-page Migrating to LINQ to SQL in TheBeerHouse Wrox Blox (for those of you who haven't seen it, get it - it's well worth the price and the time). I wanted to share some thoughts I had about it, and about using LINQ with N-tier architecture in general.

First, my congrats to author Doug Parsons for a clear, concise, and very well-written work. Nice job Doug!

That being said... I have to admit that, as I worked through it, something bothered me about the approach that was taken. In it, the author migrates the Articles module to LINQ to SQL by altering the methods in the BLL's Articles class (Articles.cs). Or course, this is perfectly valid - the application behaves exactly as the original. Despite this, I would respectfully suggest this isn't the best approach for TheBeerHouse, and in fact wouldn't really be the ideal approach for any N-tier application.

The Wrox Blox Approach

Upon reflection, it seems to me there are two general approaches to developing an application using LINQ to SQL. The first, which I'll call the "Thin DAL" approach, involves creating the LINQ to SQL file (.dbml), and letting the data entities it generates become the data object model. This is the way it's done in Migrating to LINQ to SQL in TheBeerHouse.

Again, while valid, here are a few of the issues I see in using this approach:

• It abandons the three-tier architecture central to the original application. That's right - you're now placing the data access code directly in the business layer, a violation of the basic tenet of N-tier. If you take a look at the entities in the TBH.designer.cs file, you'll find there isn't a single line of data access code in it. The real data access code, which is actually generated by the DataContext objects, must be placed elsewhere - in this case, in the BLL. The .dbml may look and act somewhat like a data access layer, but it's not a true DAL by any means.
  
• It abandons the data provider model so pervasively used throughout the original book. One of the best things about the architecture of the original TheBeerHouse is the ingenious use of the provider model to create a totally "plug-and-play" DAL. Want to switch from SQL to Oracle? No worries - just write a new provider. Once you place the data access code in the business layer, you've given up that functionality.
  
• Want to host the Articles data in a different database, or on a different machine? Simple - just create a new connection string in web.config, and point to it in the <articles> element in the <TheBeerHouse> configuration section. But (at least the way it's written in the Wrox Blox) that doesn't work anymore either. You'll notice that when you create the TBH.dbml file, it automatically generates a new connection string and kindly places it in web.config for you -- ignoring your custom setting!

I could go on, but you get the point.

An Alternate Approach

I decided to tackle this from a totally different perspective. Foremost, I wanted to achieve the following:

• Retain the original three-tier architecture.
• Retain the original "plug-and-play" data provider model.
• Retain the web.config custom settings functionality.

This is the second approach to LINQ To SQL development - creating a real, fully separate DAL with it -- which isn't anything new at all, since it's the same proven approach we've been using all along.

In addition, I decided I wanted to do it without using any T-SQL at all - no stored procedures, no database user functions, no nothing. Now, I understand that SPROCS have their place -- LINQ to SQL makes it pretty easy to use SPROCS, functions, and even free-form T-SQL strings. The Wrox Blox makes liberal use of the existing SPROCS. There's absolutely nothing wrong with that - in fact, in some cases it might even be better. However, at least to me, one of the big advantages of LINQ to SQL is freedom from T-SQL! I hate writing SQL more than I hate Brussels sprouts. So, I wanted to show that you could, if you wanted, migrate the app (or write a similar one from scratch) using only LINQ queries, while still retaining all the performance of the original SPROCS -- including lazy-loading of the Body property as implemented by the original solution.

Setting It Up

How do we do all this? Easy... and if you've been paying attention, you probably already guessed. Instead of abandoning the DAL, I simply wrote a new provider, this time using LINQ to SQL.

Of course, you still have to create the DataContext entities, so I created a TBH.dbml file exactly as it was done in the Wrox Blox. I placed this in a new folder, under the DAL folder in App_Code, called [u]LinqToSql</u>. I added a namespace in the TBH.designer.cs file called MB.TheBeerHouse.DAL.LingToSql. However, unlike the Wrox Blox, I didn't drag any stored procedures into the method pane. Remember, I'm not going to be using any SPOCS here.

I then created a new class in the same folder, called LinqToSqlArticlesProvider, under the same namespace, and inheriting from the ArticlesProvider abstract class.

Of course, then you have to implement all of those abstract methods! But first, I added a simple extra property to my new LinqToSqlArticlesProvider:

protected TBHDataContext Context = new TBHDataContext(MB.TheBeerHouse.Globals.Settings.Ar ticles.ConnectionString);

Attribute it to laziness if you must. I just hate having to instantiate a new DataContext every time you want to write a LINQ query -- and I knew I'd be writing a lot of LINQ queries! This way, I could just have a property to hold the DataContext and refer to it as needed.

Notice also that the DataContext retrieves the connection string from the Globals class, which is pulled from web.config.

Oh, and speaking of web.config... since we're using a new provider class, we want to make sure to specify that like so:


<articles pageSize="10" providerType="MB.TheBeerHouse.DAL.LinqToSql.LinqTo SqlArticlesProvider"/>


Implementing the Methods

Once you get to this point, it's all pretty straightforward. You just have to slog through all the "Get-this" and "Update-that" stuff. In fact, I'll leave it up to you adventurous souls to give it a try yourselves. (I'll post the whole provider code at the end.) First, I'd like to point out a few of the more interesting bits:

EDIT: The finished working code is available for download at http://www.originaltalent.com/downlo..._Alternate.zip (thanks, jimibt!)

• Remember that the business layer expects to see objects with data entity types as defined in the DAL (ArticlesProvider.cs), not in the DataContext (TBH.designer.cs). That means, for example, in GetCategoryByID, you're returning CategoryDetails, not tbh_Category; in GetCategories, you're returning List<CategoryDetails>, not List<tbh_Category>. The same applies to Articles and Comments.
• In queries where you need to return a single object (Get???ByID, or the various Delete and Update methods), you'll be using the .SingleOrDefault() method on the query result. This will return the one and only matching record. If more than a single matching record is found, an exception is thrown; if no matching records are found, it returns null.
• In the methods where you're returning collections of Articles, you can save bandwidth by not fetching the Body from the database. The body is never displayed in lists, but only when the whole article is requested. You can do that by returning an anonymous type that doesn't contain Body, and passing null to the ArticleDetails constructor in place of the article.Body parameter.
• In the methods that return counts, you'll be using the IQueryable<T> Count() method.
• In overloads that employ paging, you'll be using the .Skip() and .Take() methods on the query results. The formula for doing this, given a pageIndex and pageSize, is .Skip(pageIndex * pageSize).Take(pageSize).

The LinqToSqlArticlesProvider class


using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;

namespace MB.TheBeerHouse.DAL.LinqToSql
{
   public class LinqToSqlArticlesProvider : ArticlesProvider
   {
      protected TBHDataContext Context = new TBHDataContext(MB.TheBeerHouse.Globals.Settings.Ar ticles.ConnectionString);

      /// <summary>
      /// Returns a collection with all the categories
      /// </summary>
      public override List<CategoryDetails> GetCategories()
      {
         List<CategoryDetails> categoryDetailsCollection = new List<CategoryDetails>();

         IOrderedQueryable<tbh_Category> categories =
            from category in this.Context.tbh_Categories
            orderby category.Title ascending
            orderby category.Importance descending
            select category;

         foreach (tbh_Category category in categories)
         {
            CategoryDetails categoryDetails = new CategoryDetails(
               category.CategoryID, category.AddedDate, category.AddedBy, category.Title,
               category.Importance, category.Description, category.ImageUrl);

            categoryDetailsCollection.Add(categoryDetails);
         }

         return categoryDetailsCollection;
      }

      /// <summary>
      /// Returns an existing category with the specified ID
      /// </summary>
      public override CategoryDetails GetCategoryByID(int categoryID)
      {
         tbh_Category category =
            (from cat in this.Context.tbh_Categories
             where cat.CategoryID == categoryID
             select cat).SingleOrDefault();

         if (category != null)
         {
            CategoryDetails categoryDetails = new CategoryDetails(category.CategoryID,
               category.AddedDate, category.AddedBy, category.Title, category.Importance,
               category.Description, category.ImageUrl);

            return categoryDetails;
         }
         else
            return null;
      }

      /// <summary>
      /// Deletes a category
      /// </summary>
      public override bool DeleteCategory(int categoryID)
      {
         tbh_Category category =
            (from cat in this.Context.tbh_Categories
             where cat.CategoryID == categoryID
             select cat).SingleOrDefault();

         this.Context.tbh_Categories.DeleteOnSubmit(categor y);
         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Deletes.Count == 1);
      }

      /// <summary>
      /// Updates a category
      /// </summary>
      public override bool UpdateCategory(CategoryDetails category)
      {
         tbh_Category cat =
            (from c in this.Context.tbh_Categories
             where c.CategoryID == category.ID
             select c).SingleOrDefault();

         cat.AddedBy = category.AddedBy;
         cat.AddedDate = category.AddedDate;
         cat.Description = category.Description;
         cat.ImageUrl = category.ImageUrl;
         cat.Importance = category.Importance;
         cat.Title = category.Title;

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }

      /// <summary>
      /// Creates a new category
      /// </summary>
      public override int InsertCategory(CategoryDetails category)
      {
         tbh_Category cat = new tbh_Category();
         cat.AddedBy = category.AddedBy;
         cat.AddedDate = category.AddedDate;
         cat.Description = category.Description;
         cat.ImageUrl = category.ImageUrl;
         cat.Importance = category.Importance;
         cat.Title = category.Title;

         this.Context.tbh_Categories.InsertOnSubmit(cat);
         this.Context.SubmitChanges();

         return cat.CategoryID;
      }

      /// <summary>
      /// Retrieves all articles
      /// </summary>
      public override List<ArticleDetails> GetArticles(int pageIndex, int pageSize)
      {
         List<ArticleDetails> articleDetailsCollection = new List<ArticleDetails>();

         var articles =
            (from article in this.Context.tbh_Articles
             orderby article.ReleaseDate descending
             select new
             {
                article.ArticleID,
                article.AddedDate,
                article.AddedBy,
                article.CategoryID,
                CategoryTitle = article.tbh_Category.Title,
                article.Title,
                article.Abstract,
                article.Country,
                article.State,
                article.City,
                article.ReleaseDate,
                article.ExpireDate,
                article.Approved,
                article.Listed,
                article.CommentsEnabled,
                article.OnlyForMembers,
                article.ViewCount,
                article.Votes,
                article.TotalRating
             }).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (var article in articles)
         {
            ArticleDetails articleDetails = new ArticleDetails(article.ArticleID,
               article.AddedDate, article.AddedBy, article.CategoryID,
               article.CategoryTitle, article.Title, article.Abstract, null,
               article.Country, article.State, article.City,
               (DateTime)article.ReleaseDate, (DateTime)article.ExpireDate,
               article.Approved, article.Listed, article.CommentsEnabled,
               article.OnlyForMembers, article.ViewCount, article.Votes,
               article.TotalRating);

            articleDetailsCollection.Add(articleDetails);
         }

         return articleDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of articles
      /// </summary>
      public override int GetArticleCount()
      {
         return this.Context.tbh_Articles.Count();
      }

      /// <summary>
      /// Retrieves all articles for the specified category
      /// </summary>
      public override List<ArticleDetails> GetArticles(int categoryID, int pageIndex, int pageSize)
      {
         List<ArticleDetails> articleDetailsCollection = new List<ArticleDetails>();

         var articles =
            (from article in this.Context.tbh_Articles
             where article.CategoryID == categoryID
             orderby article.ReleaseDate descending
             select new
             {
                article.ArticleID,
                article.AddedDate,
                article.AddedBy,
                article.CategoryID,
                CategoryTitle = article.tbh_Category.Title,
                article.Title,
                article.Abstract,
                article.Country,
                article.State,
                article.City,
                article.ReleaseDate,
                article.ExpireDate,
                article.Approved,
                article.Listed,
                article.CommentsEnabled,
                article.OnlyForMembers,
                article.ViewCount,
                article.Votes,
                article.TotalRating
             }).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (var article in articles)
         {
            ArticleDetails articleDetails = new ArticleDetails(article.ArticleID,
               article.AddedDate, article.AddedBy, article.CategoryID,
               article.CategoryTitle, article.Title, article.Abstract, null,
               article.Country, article.State, article.City,
               (DateTime)article.ReleaseDate, (DateTime)article.ExpireDate,
               article.Approved, article.Listed, article.CommentsEnabled,
               article.OnlyForMembers, article.ViewCount, article.Votes,
               article.TotalRating);

            articleDetailsCollection.Add(articleDetails);
         }

         return articleDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of articles for the specified category
      /// </summary>
      public override int GetArticleCount(int categoryID)
      {
         return (from article in this.Context.tbh_Articles
                 where article.CategoryID == categoryID
                 select article).Count();
      }

      /// <summary>
      /// Retrieves all published articles
      /// </summary>
      public override List<ArticleDetails> GetPublishedArticles(DateTime currentDate, int pageIndex, int pageSize)
      {
         List<ArticleDetails> articleDetailsCollection = new List<ArticleDetails>();

         var articles =
            (from article in this.Context.tbh_Articles
             where article.Approved &&
             article.Listed &&
             article.ReleaseDate <= currentDate &&
             article.ExpireDate > currentDate
             orderby article.ReleaseDate descending
             select new
             {
                article.ArticleID,
                article.AddedDate,
                article.AddedBy,
                article.CategoryID,
                CategoryTitle = article.tbh_Category.Title,
                article.Title,
                article.Abstract,
                article.Country,
                article.State,
                article.City,
                article.ReleaseDate,
                article.ExpireDate,
                article.Approved,
                article.Listed,
                article.CommentsEnabled,
                article.OnlyForMembers,
                article.ViewCount,
                article.Votes,
                article.TotalRating
             }).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (var article in articles)
         {
            ArticleDetails articleDetails = new ArticleDetails(article.ArticleID,
               article.AddedDate, article.AddedBy, article.CategoryID,
               article.CategoryTitle, article.Title, article.Abstract, null,
               article.Country, article.State, article.City,
               (DateTime)article.ReleaseDate, (DateTime)article.ExpireDate,
               article.Approved, article.Listed, article.CommentsEnabled,
               article.OnlyForMembers, article.ViewCount, article.Votes,
               article.TotalRating);

            articleDetailsCollection.Add(articleDetails);
         }

         return articleDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of published articles
      /// </summary>
      public override int GetPublishedArticleCount(DateTime currentDate)
      {
         return (from article in this.Context.tbh_Articles
                 where article.Approved &&
                 article.Listed &&
                 article.ReleaseDate <= currentDate &&
                 article.ExpireDate > currentDate
                 select article).Count();
      }

      /// <summary>
      /// Retrieves all published articles for the specified category
      /// </summary>
      public override List<ArticleDetails> GetPublishedArticles(int categoryID, DateTime currentDate, int pageIndex, int pageSize)
      {
         List<ArticleDetails> articleDetailsCollection = new List<ArticleDetails>();

         var articles =
            (from article in this.Context.tbh_Articles
             where article.CategoryID == categoryID &&
             article.Approved &&
             article.Listed &&
             article.ReleaseDate <= currentDate &&
             article.ExpireDate > currentDate
             orderby article.ReleaseDate descending
             select new
             {
                article.ArticleID,
                article.AddedDate,
                article.AddedBy,
                article.CategoryID,
                CategoryTitle = article.tbh_Category.Title,
                article.Title,
                article.Abstract,
                article.Country,
                article.State,
                article.City,
                article.ReleaseDate,
                article.ExpireDate,
                article.Approved,
                article.Listed,
                article.CommentsEnabled,
                article.OnlyForMembers,
                article.ViewCount,
                article.Votes,
                article.TotalRating
             }).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (var article in articles)
         {
            ArticleDetails articleDetails = new ArticleDetails(article.ArticleID,
               article.AddedDate, article.AddedBy, article.CategoryID,
               article.CategoryTitle, article.Title, article.Abstract, null,
               article.Country, article.State, article.City,
               (DateTime)article.ReleaseDate, (DateTime)article.ExpireDate,
               article.Approved, article.Listed, article.CommentsEnabled,
               article.OnlyForMembers, article.ViewCount, article.Votes,
               article.TotalRating);

            articleDetailsCollection.Add(articleDetails);
         }

         return articleDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of published articles for the specified category
      /// </summary>
      public override int GetPublishedArticleCount(int categoryID, DateTime currentDate)
      {
         return (from article in this.Context.tbh_Articles
                 where article.CategoryID == categoryID &&
                 article.Approved &&
                 article.Listed &&
                 article.ReleaseDate <= currentDate &&
                 article.ExpireDate > currentDate
                 select article).Count();
      }

      /// <summary>
      /// Retrieves the article with the specified ID
      /// </summary>
      public override ArticleDetails GetArticleByID(int articleID)
      {
         tbh_Article article =
            (from art in this.Context.tbh_Articles
             where art.ArticleID == articleID
             select art).SingleOrDefault();

         if (article != null)
         {
            ArticleDetails articleDetails = new ArticleDetails(article.ArticleID,
               article.AddedDate, article.AddedBy, article.CategoryID,
               article.tbh_Category.Title, article.Title, article.Abstract,
               article.Body, article.Country, article.State, article.City,
               (DateTime)article.ReleaseDate, (DateTime)article.ExpireDate,
               article.Approved, article.Listed, article.CommentsEnabled,
               article.OnlyForMembers, article.ViewCount, article.Votes,
               article.TotalRating);

            return articleDetails;
         }
         else
            return null;
      }

      /// <summary>
      /// Retrieves the body for the article with the specified ID
      /// </summary>
      public override string GetArticleBody(int articleID)
      {
         return (from article in this.Context.tbh_Articles
                 where article.ArticleID == articleID
                 select new { article.Body }).Single().Body;
      }

      /// <summary>
      /// Deletes an article
      /// </summary>
      public override bool DeleteArticle(int articleID)
      {
         tbh_Article article =
            (from art in this.Context.tbh_Articles
             where art.ArticleID == articleID
             select art).SingleOrDefault();

         this.Context.tbh_Articles.DeleteOnSubmit(article);

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Deletes.Count == 1);
      }

      /// <summary>
      /// Inserts a new article
      /// </summary>
      public override int InsertArticle(ArticleDetails article)
      {
         tbh_Article art = new tbh_Article();
         art.AddedDate = article.AddedDate;
         art.AddedBy = article.AddedBy;
         art.CategoryID = article.CategoryID;
         art.Title = article.Title;
         art.Abstract = article.Abstract;
         art.Body = article.Body;
         art.Country = article.Country;
         art.State = article.State;
         art.City = article.City;
         art.ReleaseDate = article.ReleaseDate;
         art.ExpireDate = article.ExpireDate;
         art.Approved = article.Approved;
         art.Listed = article.Listed;
         art.CommentsEnabled = article.CommentsEnabled;
         art.OnlyForMembers = article.OnlyForMembers;

         this.Context.tbh_Articles.InsertOnSubmit(art);
         this.Context.SubmitChanges();

         return art.ArticleID;
      }

      /// <summary>
      /// Updates an article
      /// </summary>
      public override bool UpdateArticle(ArticleDetails article)
      {
         tbh_Article art =
            (from a in this.Context.tbh_Articles
             where a.ArticleID == article.ID
             select a).SingleOrDefault();

         art.CategoryID = article.CategoryID;
         art.Title = article.Title;
         art.Abstract = article.Abstract;
         art.Body = article.Body;
         art.Country = article.Country;
         art.State = article.State;
         art.City = article.City;
         art.ReleaseDate = article.ReleaseDate;
         art.ExpireDate = article.ExpireDate;
         art.Approved = article.Approved;
         art.Listed = article.Listed;
         art.CommentsEnabled = article.CommentsEnabled;
         art.OnlyForMembers = article.OnlyForMembers;

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }

      /// <summary>
      /// Approves an article
      /// </summary>
      public override bool ApproveArticle(int articleID)
      {
         tbh_Article article =
            (from art in this.Context.tbh_Articles
             where art.ArticleID == articleID
             select art).SingleOrDefault();

         article.Approved = true;

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }

      /// <summary>
      /// Increments the ViewCount of the specified article
      /// </summary>
      public override bool IncrementArticleViewCount(int articleID)
      {
         tbh_Article article =
            (from art in this.Context.tbh_Articles
             where art.ArticleID == articleID
             select art).SingleOrDefault();

         article.ViewCount++;

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }

      /// <summary>
      /// Inserts a vote for the specified article
      /// </summary>
      public override bool RateArticle(int articleID, int rating)
      {
         tbh_Article article =
            (from art in this.Context.tbh_Articles
             where art.ArticleID == articleID
             select art).SingleOrDefault();

         article.Votes++;
         article.TotalRating += rating;

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }

      /// <summary>
      /// Retrieves all comments
      /// </summary>
      public override List<CommentDetails> GetComments(int pageIndex, int pageSize)
      {
         List<CommentDetails> commentDetailsCollection = new List<CommentDetails>();

         IQueryable<tbh_Comment> comments =
            (from comment in this.Context.tbh_Comments
             orderby comment.AddedDate descending
             select comment).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (tbh_Comment comment in comments)
         {
            CommentDetails commentDetails = new CommentDetails(comment.CommentID,
               comment.AddedDate, comment.AddedBy, comment.AddedByEmail,
               comment.AddedByIP, comment.ArticleID, comment.tbh_Article.Title,
               comment.Body);

            commentDetailsCollection.Add(commentDetails);
         }

         return commentDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of comments
      /// </summary>
      public override int GetCommentCount()
      {
         return this.Context.tbh_Comments.Count();
      }

      /// <summary>
      /// Retrieves all comments for the specified article
      /// </summary>
      public override List<CommentDetails> GetComments(int articleID, int pageIndex, int pageSize)
      {
         List<CommentDetails> commentDetailsCollection = new List<CommentDetails>();

         IQueryable<tbh_Comment> comments =
            (from comment in this.Context.tbh_Comments
             where comment.ArticleID == articleID
             orderby comment.AddedDate descending
             select comment).Skip(pageIndex * pageSize).Take(pageSize);

         foreach (tbh_Comment comment in comments)
         {
            CommentDetails commentDetails = new CommentDetails(comment.CommentID,
               comment.AddedDate, comment.AddedBy, comment.AddedByEmail,
               comment.AddedByIP, comment.ArticleID, comment.tbh_Article.Title,
               comment.Body);

            commentDetailsCollection.Add(commentDetails);
         }

         return commentDetailsCollection;
      }

      /// <summary>
      /// Returns the total number of comments for the specified article
      /// </summary>
      public override int GetCommentCount(int articleID)
      {
         return (from comment in this.Context.tbh_Comments
                 where comment.ArticleID == articleID
                 select comment).Count();
      }

      /// <summary>
      /// Retrieves the comment with the specified ID
      /// </summary>
      public override CommentDetails GetCommentByID(int commentID)
      {
         tbh_Comment comment =
            (from com in this.Context.tbh_Comments
             where com.CommentID == commentID
             select com).SingleOrDefault();

         if (comment != null)
         {
            CommentDetails commentDetails = new CommentDetails(comment.CommentID,
               comment.AddedDate, comment.AddedBy, comment.AddedByEmail,
               comment.AddedByIP, comment.ArticleID, comment.tbh_Article.Title,
               comment.Body);

            return commentDetails;
         }
         else
            return null;
      }

      /// <summary>
      /// Deletes a comment
      /// </summary>
      public override bool DeleteComment(int commentID)
      {
         tbh_Comment comment =
            (from com in this.Context.tbh_Comments
             where com.CommentID == commentID
             select com).SingleOrDefault();

         this.Context.tbh_Comments.DeleteOnSubmit(comment);

         ChangeSet cs = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Deletes.Count == 1);
      }

      /// <summary>
      /// Inserts a new comment
      /// </summary>
      public override int InsertComment(CommentDetails comment)
      {
         tbh_Comment com = new tbh_Comment();
         com.AddedBy = comment.AddedBy;
         com.AddedByEmail = comment.AddedByEmail;
         com.AddedByIP = comment.AddedByIP;
         com.AddedDate = comment.AddedDate;
         com.ArticleID = comment.ArticleID;
         com.Body = comment.Body;

         this.Context.tbh_Comments.InsertOnSubmit(com);
         this.Context.SubmitChanges();

         return com.CommentID;
      }

      /// <summary>
      /// Updates an comment
      /// </summary>
      public override bool UpdateComment(CommentDetails comment)
      {
         tbh_Comment com =
            (from c in this.Context.tbh_Comments
             where c.CommentID == comment.ID
             select c).SingleOrDefault();

         com.Body = comment.Body;

         ChangeSet cs =

__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

lee - nice one. i too have struggled to break away from the plug n play nature of TBH and the linq stuff that i'd seen to date, didn't give me hope in that direction. looking at what you've done above looks like a positive step in the right direction. I'll give it a play based on what you've done above and might (time-permitting) create a set of flixon templates that mimic the functionality, so that you can automagically produce a whole linq-sql TBH structure in a click.

i'll get back to you soon, also, will prolly ask a few questions as i delve into it.

thanks for making this so easy to understand and more importantly, retaining the original concepts that made/make TBH architechture so compelling.

[EDIT] lee, any chance you could throw your entire c# solution up onto a webspace for download??, that way i caould work thro code that has been well worked etc.. TIA
jimi

http://www.originaltalent.com

I probably should do that. However, I don't have a personal website or blog, and I can't put it on a client's site or my work's site.

I can zip it up no problem, but I would need a place to host the .zip.

lee - you're welcome to zip it up and leave it on my website (i'll even set up your own login for you). or you could just email it me and i'll put it up there for you.


jimi

http://www.originaltalent.com

Jimi -- you got mail. Thanks for offering to put this up. Once it's there, I'll make a note of it in the original post.

lee - ok, i've picked up the zip and posted onto my website space. you can pick up thew link here:

http://www.originaltalent.com/downlo..._Alternate.zip

Also, i've got a quick question. in the 'standard' SQLserver sqlclient code, every method opens up a connection (with the using statement) and then closes it after the method has run, thus keeping the database stateless. i can't see in linq how this is working. is the data connection being opened and closed in the background somewhere or is it simply open for the duration of the user's session. i hope this isn't one of those 'newbie' type questions, but i just can't see how it's operating (and rather ask the question and appear a fool for 5 mins than carry on for ever in ignorance :D).

jimi

http://www.originaltalent.com

Jimi,

The default behavior for LINQ to SQL is to open/close the connection automatically upon execution of each query, unless there is a transaction in scope or the connection was already open.

It is also possible to programmatically open/close the connection as well, if you need to.

Hello Lee,
    Sorry I am so late in getting to this, John McCain's team has been particularly brutal with updates and such the last week and half so I haven't had time to keep up on p2p =
You have done a very good job in your approach! The only real "criticism" (and I use that word very, very loosely) is with your approach to a complete LINQ Syntax approach over a TSQL approach without explicitly pointing out a few things. As you have shown, it is quite possible to replace all of the sprocs/TSQL with simplistic LINQ Syntax but (as most of you should know) LINQ queries, such as the ones shown here and in my Blox, are ultimately converted into a TSQL query under the hood.

Now I for one love Microsoft because, without their existence, I would probably be unemployed but 9 times out 10 doing things the "Microsoft Way" != "The Right Way". What I mean by this is that while you may write a seemingly simplistic query in LINQ, the TSQL that is ultimately generated could be atrocious. (Sorry I don't have a query handy to illustrate this but step through your code sometime and take a look at the TSQL that is actually being generated, it may bewilder you!).

So why bring this up? Well, in the context of TBH there is little chance of the LINQ statements getting out of control (I am assuming a vanilla install of course) but for those of you that have heavily modified the code you should take both Lee's example and my Blox into consideration. If following Lee's example you notice a hit in performance, take a look at the TSQL that LINQ is generating it may be causing your hiccup. By the same token if you don't like dealing with Sprocs or Functions (or if you are like Lee and just hate TSQL ;] ) by all means follow Lee's example! Regardless of this, choose the style that is most comfortable to you because one way isn’t necessarily “better” than the other since the sheer definition of the word is subjective.

Anyway I think that I have rambled on long enough! Again, very nicely done!

-Doug


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Hi Doug,

First, thanks for the kind words.

Second, and in response to your caveat above: while I admit I haven't been working in LINQ for a very long time (has anyone?) I must say that, in my experience this far, the T-SQL that I've seen generated by LINQ To SQL is has been remarkably clean and efficient. While working on TheBeerHouse, I carefully checked every T-SQL being generated (using LINQ To SQL Query Analyzer), as well as having SQL Profiler running to measure performance. In all cases, query performance was comparatively equivalent to using the SPROCS, and in a couple of instances even better.

Of course, as I point out, I decided not to use the SPROCS just to show it could be done. If for some reason you dig writing SPROCS, or find they are more efficient in your application, or work with a DBA who won't grant you direct access to the tables, it would be really easy to reuse the existing SPROCS while still employing the provider method I demonstrated, as I point out in my post. You could even mix and match SPROCS and LINQ queries in the same provider.

-- Lee

P.S. Maybe you should go to work for Obama. ;)

Morning Lee,
   Please don't misunderstand me, I was simply pointing out that it is possible for LINQ to generate some overly complex TSQL queries when an overly simplistic TSQL alternative exists. Most of the time, as you have noted, LINQ does a fairly decent job of this but it is still something readers should be aware of. =]


-Doug

P.S. - I am pretty sure the developers for Hilliary and Obama are equally as busy right now, all I know is that I will be happy when Nov. has come and gone!

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========