Subject: LINQ-ifying TheBeerHouse - An Alternate Approach
Posted By: Lee Dumond Post Date: 5/9/2008 11:41:39 AM
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 LinqToSql. 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.Articles.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.LinqToSqlArticlesProvider"/>


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/downloads/TheBeerHouse_LINQ_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.Articles.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(category);
         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 = this.Context.GetChangeSet();
         this.Context.SubmitChanges();

         return (cs.Updates.Count == 1);
      }
   }
}
Reply By: jimibt Reply Date: 5/9/2008 12:24:03 PM
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
Reply By: Lee Dumond Reply Date: 5/9/2008 1:38:47 PM
quote:
Originally posted by jimibt


[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


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.
Reply By: jimibt Reply Date: 5/9/2008 2:06:13 PM
quote:
Originally posted by Lee Dumond

quote:
Originally posted by jimibt


[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


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
Reply By: Lee Dumond Reply Date: 5/9/2008 3:12:36 PM
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.
Reply By: jimibt Reply Date: 5/9/2008 3:55:32 PM
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/downloads/TheBeerHouse_LINQ_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 ).

jimi

http://www.originaltalent.com
Reply By: Lee Dumond Reply Date: 5/9/2008 4:27:14 PM
quote:
Originally posted by jimibt


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 ).

jimi


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.
Reply By: dparsons Reply Date: 5/12/2008 8:30:44 PM
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
===========================================================
Reply By: Lee Dumond Reply Date: 5/12/2008 10:09:56 PM
quote:
Originally posted by dparsons

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!).


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.

Reply By: dparsons Reply Date: 5/13/2008 6:04:41 AM
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
===========================================================
Reply By: Lee Dumond Reply Date: 5/13/2008 11:16:45 AM
quote:
Originally posted by dparsons

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. =]


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.
Reply By: jimibt Reply Date: 5/14/2008 10:46:42 AM
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
Reply By: jimibt Reply Date: 5/14/2008 11:35:15 AM
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/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

jimi

http://www.originaltalent.com
Reply By: Lee Dumond Reply Date: 5/20/2008 12:30:58 AM
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.
Reply By: jimibt Reply Date: 5/20/2008 3:58:55 AM
quote:
Originally posted by Lee Dumond

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
Reply By: jimibt Reply Date: 5/20/2008 4:27:27 AM
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
Reply By: Lee Dumond Reply Date: 5/20/2008 9:21:00 AM
quote:
Originally posted by jimibt

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.
Reply By: Lee Dumond Reply Date: 5/20/2008 9:43:16 AM
quote:
Originally posted by jimibt



In linq, is this behaviour not supported (and/or recommended??)... The reason i'd been using this (TransactionScope) was due to parent child relationships (nested TransactionScopes()) and the need to keep them in sync.


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

quote:
Originally posted by jimibt



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.


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.
Reply By: kalel_4444 Reply Date: 5/22/2008 1:16:50 AM
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/09/LINQToSQLAuditTrail.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
Reply By: kalel_4444 Reply Date: 5/22/2008 1:24:22 AM
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...   Thanks Jimi

Go to topic 71545

Return to index page 2
Return to index page 1