Linq to SQL with WCF in a Multi Tiered Action - Part 2

In the first part of this article, I tried to define a Users & Favorites scenario and the things to keep in mind about Linq to SQL. In this post I’ll continue building that application and show its implementation in different tiers connected with WCF.

Here are the sources for the article.

Service Layer Design (Cont’d from Part 1)

Service Host (Web Service in our case)

This is a host project (a plain Web project) needed to host our web service. It has our .svc files and needed configuration. In .svc file we have the mapping from contract to the implementation:

<%@ ServiceHost Language=”C#” Debug=”true” 
Service=”ServiceImplementations.UsersService” %>

And the endpoint configuration goes as follows:

   1: <system.serviceModel>
   2:         <behaviors>
   3:             <serviceBehaviors>
   4:                 <behavior name=”FavoritesServiceBehavior”>
   5:                     <serviceMetadata httpGetEnabled=”true” />
   6:                     <serviceDebug includeExceptionDetailInFaults=”false” />
   7:                 </behavior>
   8:                 <behavior name=”UsersServiceBehavior”>
   9:                     <serviceMetadata httpGetEnabled=”true” />
  10:                     <serviceDebug includeExceptionDetailInFaults=”false” />
  11:                 </behavior>
  12:             </serviceBehaviors>
  13:         </behaviors>
  14:         <services>
  15:             <service behaviorConfiguration=”FavoritesServiceBehavior”
  16: name=”ServiceImplementations.FavoritesService”>
  17:                 <endpoint address=”" binding=”wsHttpBinding”
  18: name=”IFavoritesService_Endpoint”
  19:                     contract=”ServiceContracts.IFavoritesService”>
  20:                     <identity>
  21:                         <dns value=”localhost” />
  22:                     </identity>
  23:                 </endpoint>
  24:             </service>
  25:             <service behaviorConfiguration=”UsersServiceBehavior”
  26: name=”ServiceImplementations.UsersService”>
  27:                 <endpoint address=”" binding=”wsHttpBinding”
  28: name=”IUsersService_Endpoint”
  29:                     contract=”ServiceContracts.IUsersService”>
  30:                     <identity>
  31:                         <dns value=”localhost” />
  32:                     </identity>
  33:                 </endpoint>
  34:             </service>
  35:         </services>
  36:     </system.serviceModel>

Service Clients (Consumers)

The client layer is a very thin façade to invoke the requested methods from the channel. Clients are meant to be called through controllers if you are using MVC, and in our case our web application will consume the service so the endpoint configurations will live in web tier:

   1: <system.serviceModel>
   2:     <client>
   3:       <endpoint binding=”wsHttpBinding” bindingConfiguration=”"
   4: contract=”ServiceContracts.IFavoritesService”
   5: address=”http://localhost/WebServiceHost/FavoritesService.svc”
   6:         name=”FavoritesClient”>
   7:         <identity>
   8:           <dns value=”localhost” />
   9:           <certificateReference storeName=”My” storeLocation=”LocalMachine”
  10:             x509FindType=”FindBySubjectDistinguishedName” />
  11:         </identity>
  12:       </endpoint>
  13:       <endpoint binding=”wsHttpBinding” bindingConfiguration=”"
  14: contract=”ServiceContracts.IUsersService”
  15: address=”http://localhost/WebServiceHost/UsersService.svc”
  16:         name=”UsersClient”>
  17:         <identity>
  18:           <dns value=”localhost” />
  19:           <certificateReference storeName=”My” storeLocation=”LocalMachine”
  20:             x509FindType=”FindBySubjectDistinguishedName” />
  21:         </identity>
  22:       </endpoint>
  23:     </client>
  24: </system.serviceModel>

Presentation

The challenge in the presentation tier is we need to maintain the state of the each entity according to the user interaction. For this purpose, I put 2 GridViews , one for Users and One for favorites to enable insert, update, delete and select operations.

We will bind strongly typed collections (IList<User> and IList<Favorite>) to our GridViews and we will use the ID fields of the objects to associate with the gridview, and then use them in the code behind:

Here is the definition for Users GridView:

   1: <asp:GridView ID=”usersGrid” runat=”server”
   2:     AutoGenerateColumns=”False” CellPadding=”4″
   3:     ForeColor=”#333333″ GridLines=”None”
   4:     DataKeyNames=”UserId”
   5:     OnRowDeleting=”usersGrid_RowDeleting”
   6:     OnRowUpdating=”usersGrid_RowUpdating”
   7:     OnSelectedIndexChanged=”usersGrid_SelectedIndexChanged”
   8:     OnSelectedIndexChanging=”usersGrid_SelectedIndexChanging”
   9:     OnRowCancelingEdit=”usersGrid_RowCancelingEdit”
  10:     OnRowEditing=”usersGrid_RowEditing”>
  11:     <RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
  12:     <Columns>
  13:         <asp:CommandField ShowDeleteButton=”True” />
  14:         <asp:TemplateField HeaderText=”First Name”>
  15:             <ItemTemplate>
  16:                 <asp:Label ID=”firstNameLabel” runat=”server”
  17: Text=’<%# Bind(”FirstName”) %>’></asp:Label>
  18:             </ItemTemplate>
  19:             <EditItemTemplate>
  20:                 <asp:TextBox ID=”firstNameTextBox” runat=”server”
  21: Text=’<%# Bind(”FirstName”) %>’></asp:TextBox>
  22:             </EditItemTemplate>
  23:         </asp:TemplateField>
  24:         <asp:TemplateField HeaderText=”Last Name”>
  25:             <ItemTemplate>
  26:                 <asp:Label ID=”lastNameLabel” runat=”server”
  27: Text=’<%# Bind(”LastName”) %>’></asp:Label>
  28:             </ItemTemplate>
  29:             <EditItemTemplate>
  30:                 <asp:TextBox ID=”lastNameTextBox” runat=”server”
  31: Text=’<%# Bind(”LastName”) %>’></asp:TextBox>
  32:             </EditItemTemplate>
  33:         </asp:TemplateField>
  34:         <asp:CommandField ShowEditButton=”True” />
  35:         <asp:CommandField ShowSelectButton=”True” />
  36:     </Columns>
  37:     <FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
  38:     <PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
  39:     <SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
  40:     <HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
  41:     <EditRowStyle BackColor=”#999999″ />
  42:     <AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
  43:     </asp:GridView>

The one for Favorites is pretty much the same so I’ll go over Users grid.

Let’s go to code behind which is more important to us. We are going to do a batch update and send List of Users, and each user in the list will have their favorites. All the entities will have their latest status in their Status field.

Here is a sequence diagram to make things easier and more clearer to understand :

image

Picture 1. Sequence diagram of what’s happening

Now, in the page load, we are going to populate the Users GridView:

   1: if (!IsPostBack)
   2: {
   3:    try
   4:    {
   5:        if (SessionStateUtility.Users == null)
   6:        {
   7:            // error may occur during disposal, not caring for the time being
   8:            using (UsersClient client = new UsersClient())
   9:            {
  10:                SessionStateUtility.Users = client.GetAllUsers().ToList<User>();
  11:            }
  12:        }
  13:        BindUsersGrid(SessionStateUtility.Users, -1);
  14:    }
  15:    catch (Exception ex)
  16:    {
  17:        Response.Write(ex.ToString());
  18:    }
  19: }

In the grid, user can update and delete users from session. For insert, there is a separate panel included at the bottom with an add button. In the add button what we are doing is quite simple, just adding a new user to the session:

   1: protected void addUserButton_Click(object sender, EventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     User u = new User()
   7:     {
   8:         FirstName = firstNameTextBox.Text,
   9:         LastName = lastNameTextBox.Text,
  10:         EMail = emailTextBox.Text,
  11:         Status = EntityStatus.New,
  12:         UserId = SessionStateUtility.NextUserId,
  13:     };
  14: 
  15:     SessionStateUtility.Users.Add(u);
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);
  18: }

You’ll notice 2 things here, one of them is the Status is set to Entity Status.New . The other one is the SessionStateUtility class. This acts as a provider and a helper for User lists. The Users list that it provides is the below:

   1: /// <summary>
   2: /// Gets or sets the users.
   3: /// </summary>
   4: /// <value>The users.</value>
   5: public static List<User> Users
   6: {
   7:     get
   8:     {
   9:         Debug.Assert(HttpContext.Current != null);
  10:         Debug.Assert(HttpContext.Current.Session != null);
  11:
  12:         return HttpContext.Current.Session[“Users”] as List<User>;
  13:     }
  14:     set
  15:     {
  16:         Debug.Assert(HttpContext.Current != null);
  17:         Debug.Assert(HttpContext.Current.Session != null);
  18: 
  19:         HttpContext.Current.Session[“Users”] = value;
  20:     }
  21: }

And it provides another method to get NextUserId. This is necessary because since there can be multiple new records in the screen, we will need to identify them. Next User Id brings the next highest negative number that is available:

   1: /// <summary>
   2: /// Gets the next id.
   3: /// </summary>
   4: /// <value>The next id.</value>
   5: public static int NextUserId
   6: {
   7:    get
   8:    {
   9:        if (SessionStateUtility.Users.Count == 0)
  10:        {
  11:            return -1;
  12:        }
  13:        int minId = SessionStateUtility.Users.Min<User>(user => user.UserId);
  14:
  15:        if (minId > 0)
  16:        {
  17:            return -1;
  18:        }
  19: 
  20:        return –minId;
  21:    }
  22: }

And then we need to handle the grid events. I wrote a helper function to Get the User object from Selected row index in the grid (it retrieves from session)”

   1: private User GetUserFromRowIndex(int index)
   2: {
   3:     int userId = usersGrid.DataKeys[index].Value as int? ?? 0;
   4: 
   5:     //retrieve the instance in the session
   6:     User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);
   7:     return user;
   8: }

Another helper function is there for just to get user’s full name formatted:

   1: private string GetFullNameForUser(User u)
   2: {
   3:     return String.Format(CultureInfo.InvariantCulture, “{0} {1}”, u.FirstName, u.LastName);
   4: }

And this one updates the UI fields for a selected user:

   1: private void UpdateUiForUser(User u)
   2: {
   3:    if (u != null)
   4:    {
   5:        favoritesPanel.Visible = true;
   6:        userNameLabel.Text = GetFullNameForUser(u);
   7:        BindFavoritesGrid(u.Favorites.ToList<Favorite>(), -1);
   8:    }
   9: }

And of course one method for binding the grid:

   1: private void BindUsersGrid(IList<User> users, int editIndex)
   2: {
   3:     usersGrid.DataSource = users
   4:     .Where<User>(usr=>usr.Status != EntityStatus.Deleted);// only bind non deleted ones
   5:     usersGrid.EditIndex = editIndex;
   6:     usersGrid.DataBind();
   7: }

As you can see we are not binding the deleted ones but we are still keeping them in the session because we need to know what is deleted when we send them back to the data tier.

Then within the light of these methods, here goes the SelectedIndex_Changing event handler. It updates the favorite’s grid for the selected user:

   1: protected void usersGrid_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5:     usersGrid.SelectedIndex = e.NewSelectedIndex;
   6: 
   7:     User u = GetUserFromRowIndex(e.NewSelectedIndex);
   8:     UpdateUiForUser(u);
   9: }

And when the row is being edited, following event handler will get executed:

   1: protected void usersGrid_RowEditing(object sender, GridViewEditEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5:
   6:     usersGrid.SelectedIndex = e.NewEditIndex;
   7: 
   8:     BindUsersGrid(SessionStateUtility.Users, e.NewEditIndex);
   9: }

And after user clicks edit, when he/she clicks update following handler will run:

   1: protected void usersGrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     int userId = usersGrid.DataKeys[e.RowIndex].Value as int? ?? 0;
   7:     //retrieve the instance in the session
   8:     User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);
   9:     user.FirstName = (usersGrid.Rows[e.RowIndex].FindControl(“firstNameTextBox”)
  10: as TextBox).Text;
  11:     user.LastName = (usersGrid.Rows[e.RowIndex].FindControl(“lastNameTextBox”)
  12: as TextBox).Text;
  13:
  14:     user.Status = user.Status == EntityStatus.New ?
  15: EntityStatus.New :EntityStatus.Updated; // manage the state
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
  18: }

As you see if the edited users’ current status is already new, then we are not modifying it. But else, the state is changed to the updated.

A similar situation also exists for deletion. Have a look at the handler below:

   1: protected void usersGrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
   2: {
   3:     Debug.Assert(sender != null);
   4:     Debug.Assert(e != null);
   5: 
   6:     User user = GetUserFromRowIndex(e.RowIndex);
   7:     // If user is new and deleted now, we shouldnt send it over the wire again
   8:     if (user.Status == EntityStatus.New)
   9:     {
  10:         SessionStateUtility.Users.Remove(user);
  11:     }
  12:     else
  13:     {
  14:         user.Status = EntityStatus.Deleted;
  15:     }
  16: 
  17:     BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
  18: }

We have done our work as a presentation layer, and we are now sending all the data through the service to data layer along with all the information needed for it to manage the generation of the SQL Statements (fingers crossed)

Data Layer Design

Since we are going to implement the IUsersDataAccess contract, we need to implement 4 methods: But I’ll focus on 2 of them especially. First one is GetAllUsers:

   1: /// <summary>
   2: /// Gets all users.
   3: /// </summary>
   4: /// <returns>The list of all users along with their favorites.</returns>
   5: public IList<User> GetAllUsers()
   6: {
   7:     using (FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
   8:     {
   9:         DataLoadOptions options = new DataLoadOptions();
  10:         options.LoadWith<User>(u => u.Favorites);
  11: 
  12:         context.LoadOptions = options; // load with favorites
  13:         context.ObjectTrackingEnabled = false; // retrieving data read only
  14: 
  15:         return context.Users.ToList<User>();
  16:     }
  17: }

As you see, we are telling the context to load every user with their favorites. This can cause some damage if these tables are very big, and there are methods to enhance this experience.

The UpdateUsers(IList) method is a bit more complicated. Here are the list of things that we are going to do:

  • Attach the users to the context who have their status “Updated’ – obvious one

  • Attach the users to the context who have their status “Deleted’ – since the context does not know about an object that is not attached, we need to attach them too.

  • We aren’t going to attach the objects to insert, because Data Context doesn’t need to know about the objects those are being added.

  • Call the relevant of one of those by looking at their status: InsertAllOnSubmit, DeleteAllOnSubmit

  • Do the same for the child entities of each. (Keep in mind that we need to delete all children regardless of their status if their parent is deleted)

So now hopefully the following implementation will be more understandable:

   1: /// <summary>
   2: /// Updates the users list.
   3: /// </summary>
   4: /// <param name=”updateList”>The list of users to perform the operations.</param>
   5: public void UpdateUsers(IList<User> updateList)
   6: {
   7:     using(FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
   8:     {
   9:         context.Users.AttachAll<User>(
  10: updateList.Where<User>(
  11:   usr=>usr.Status == EntityStatus.Updated ||
  12:   usr.Status == EntityStatus.Deleted), true);
  13:         context.Users.InsertAllOnSubmit<User>(
  14: updateList.Where<User>(
  15:   usr=>usr.Status == EntityStatus.New));
  16:         context.Users.DeleteAllOnSubmit<User>
  17: (updateList.Where<User>(usr => usr.Status == EntityStatus.Deleted));
  18: 
  19:         // do the same for the children
  20:         // If the parent is deleted, to prevent orphan records we need to delete
  21:         // children too
  22:         foreach (User user in updateList)
  23:         {
  24:             context.Favorites.AttachAll<Favorite>
  25: (user.Favorites.Where<Favorite>
  26:   (fav=>fav.Status == EntityStatus.Updated
  27:   || fav.Status == EntityStatus.Deleted
  28:   || fav.User.Status == EntityStatus.Deleted
  29:   || fav.User.Status == EntityStatus.Updated));
  30:             //we shouldnt insert the new child records of deleted entities
  31:             context.Favorites.InsertAllOnSubmit<Favorite>
  32: (user.Favorites.Where<Favorite>
  33:   (fav => fav.Status == EntityStatus.New
  34:   && fav.User.Status != EntityStatus.Deleted));
  35:             context.Favorites.DeleteAllOnSubmit<Favorite>
  36: (user.Favorites.Where<Favorite>
  37: (fav => fav.Status == EntityStatus.Deleted ||
  38: fav.User.Status == EntityStatus.Deleted));
  39:         }
  40: 
  41:         context.SubmitChanges();
  42:     }
  43: }

That’s the end of fun(!) folks. As you have seen already, there is some work involved with making Linq to SQL work in Multi Tiered architecture, but it is doable still. Again, download the sources and please don’t hesitate to post any comments, criticisms or crossword puzzles via here or sidarok@sidarok.com, they are all welcome.

kick it on DotNetKicks.com

Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone

11 Responses so far »

  1. 1

    WMOC#7: Teched, Velocity - Service Endpoint said,

    June 9, 2008 @ 4:51 pm

    […] Call Forwarding Via Steve Main WCF Visualizers Paul Stovell Bindable LINQ 1.0 Beta 1 Part 2 of Linq to SQL with WCF in a Multi Tiered Action Mary Jo Foley - More Microsoft ‘Oslo’ modeling details fall into place Sam Gentile New and […]

  2. 2

    Egil said,

    June 12, 2008 @ 7:55 am

    Interesting article, good work.

    One thing that struck me when reading through it was the last part where you update the database.

    The hole second part where you update the Favorite table could be much less complicated if you just let the database handle it for you, by putting an “ON DELETE CASCADE” on the foreign key constraint between User and Favorite tables.

    This would automatically delete a favorite if its user was deleted.

  3. 3

    Sidar Ok said,

    June 12, 2008 @ 9:50 am

    Hi Egil, thanks for the comment.

    You are right indeed, that would be probably the way to go in a normal application and what I would do while designing it, thanks for bringing this up.

    While I am on this, as I mentioned in the beginning of the first article (http://www.sidarok.com/web/blog/content/2008/06/02/linq-to-sql-with-wcf-in-a-multi-tiered-action-part-1.html), I didn’t want to go down the easy and obvious route, I wanted to keep database as lean as possible and delegate all the logic as a responsibility of application. In some cases you may not want to delete all the child records or you may want to delete them based on a condition - and even roll back if the condition is not satisfied.

    But again, if I was sure that that won’t ever be the case, I would push the cascading rules to the db.

    Thanks,

    Sidar

  4. 4

    Xon said,

    June 22, 2008 @ 10:49 am

    Great article, i resolve all my problems. Thanks for u contritution.

  5. 5

    Danial said,

    May 14, 2009 @ 12:12 pm

    Great blog this is exactly what i’m trying to do at the moment.

    One question though, im trying to create a GetSingleUser method but keep running into the “Could not find an implementation of the query pattern for source” i guess this is because User does not implement IQueryable.

    My question is what would be the correct way to query a single User say by ID from the database and have it populate the User object.

    Thanks
    Danial

  6. 6

    Sidar Ok said,

    May 19, 2009 @ 12:57 pm

    Hi Danial,

    >> One question though, im trying to create a GetSingleUser method but keep running into the “Could not find an implementation of the query pattern for source” i guess this is because User does not implement IQueryable.

    I believe you may need to get your type stated explicitly since Linq can not determine your source type to query like done in this post : http://blogs.interakting.co.uk/mattnield/archive/2008/01/25/LINQ-Error-19-Could-not-find-an-implementation-of-the.aspx

    >> My question is what would be the correct way to query a single User say by ID from the database and have it populate the User object.

    User u = myDataContext.Users.Single( u => u.UserId == userIdThatComesAsAParameter);

    Where userIdThatComesAsAParameter is always assured to be existing. If you don’t want to throw when it is not found, then you can use SingeOrDefault( specification ) and the result will be null when not found.

    Hope this helps.

  7. 7

    Danial said,

    June 4, 2009 @ 12:01 pm

    My issues are all sorted now!

    Thanks for your response.
    Danial

  8. 8

    Prudhvi said,

    July 14, 2009 @ 7:12 am

    It’s long and detailed which is what most of us need as it has almost everything. Thanks for the article.
    I have one question though. What if I want ‘change tracking’ to be implemented on an object ?
    Will it be possible in this disconnected scenario ?

  9. 9

    Sidar Ok said,

    July 16, 2009 @ 9:23 pm

    @Prudhvi

    Glad that you found the article useful. Actually if you look at the last code sample in Update, I am doing a context.Attach to enable change tracking. I am attaching the entities that are marked as “dirty” and context tracks what fields are changed and generates the query. So the answer is yes, it is possible but is not fully automatic.

  10. 10

    Sripraks said,

    July 30, 2009 @ 11:54 am

    Hi Sidar,

    Nice Article. I have build application using Linq to SQL using the similar approach that you mentioned. But, I have below hurdle.
    Our Main Design Goal is WCF service should be very thin. So, we have put all our business logic in to a BO. The BO classes are built with partial classes of Linq To SQL entity classes and has own methods for CRUD operations and validations. The On Top of BO we have build an AL(Activity Layer) which mainly deals with Transactions/Security for BO. The SL calls AL and inturn AL Calls BO and BO uses the LTS to perform necessary operations. Here the problem i see is, i have to expose the LTS entities to all layers which is required in order my Client to work. Since i have tied my BO also with LTS entities via parital class all my static methods are exposed till service layer which i dont want. In this specific case, do you have any ideas to over come this?

    More clearly i can illustrate with an example

    Service Layer Method
    [Operation Contract]
    Public User GetAllUsers(userid)
    {
    UserActivity UA = new UserActivity();
    return UA.Getalluser(Userid)

    // I can do this as below from here with out AL
    // Return User.Getalluser();
    }

    Activity Layer Method
    Public User GetAllUser(userid)
    {
    Return User.Getalluser();
    }

  11. 11

    Rahul Pahak said,

    February 25, 2011 @ 2:56 pm

    Excellent concept for starter

Comment RSS · TrackBack URI

Say your words

You must be logged in to post a comment.