In previous posts, how to insert new records using entity framework and how to update records using entity framework. Those two processes are foundation blocks of most of the data operations we all perform in an application. The examples that showed in those posts were very simple involving one record for one table. In more complex work flow processes you have more than one database operations that need to take place to accomplish the intended outcome. When you have multiple records and tables being manipulated, you have to ensure ACID nature of transactions. There are very few cases I have come across where one could afford to have change in one table succeed if another operation in the same chain fail.

In this post I will show how to use transactions in Entity Framework. If you have dealt with transactions in SQL server queries or for that matter any database operations, you will notice that when using Entity Framework the concept stays the same. You perform the following steps.

• Create a transaction
• Perform database operations with in scope of that transaction
• Commit or rollback transaction depending on out come of database operation

When you are using Entity Framework, transactions are handled by Transaction framework. You will not find reference to those namespaces in EntityFramework assembly. Those references exist in System.Transactions assembly. Lets see how you will use transactions in entity framework.

Assembly reference and namespace

Add references to System.Transactions.dll assembly in your project. And now include System.Transactions namespace in your code file.

Create new transaction

Key to using transaction is creating a transaction or logically speaking create a transaction scope which will enclose all the data operations that need to be included in one single transaction. Use TransactionScope class to create new instance of implicit transaction scope for operations.

using System.Transactions;

var txScope = new TransactionScope();
using (txScope){ ......  }

Perform database operations

With in scope of transaction perform all the data operations that I talked about in previous posts.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
}                        

Commit Transaction

After all the database operations have completed and succeeded, then you can commit transaction by calling Complete method on TransactionScope object.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
 txScope.Complete();
}

If you are looking for method like Rollback, you will not find it. No, this does not mean that transaction can not be rolled back using Entity Framework. The framework is designed to commit a transaction only after Complete method is called. If you do not want to commit a transaction, you can explicitly call Dispose on TransactionScope object or wait till TransactionScope object goes out of scope.

Here is complete code snippet from one of my sample projects.

protected void OnAdd(object sender, EventArgs e)
{
  if (!ValidateContracts())
  {
    ErrorMessage_Label.Visible = true;
    return;
  }

  var project = new Project()
  {
   ProjectName = AntiXss.HtmlEncode(ProjectName_TextBox.Text.Trim()),
    ProjectDescription = AntiXss.HtmlEncode(ProjectDescription_TextBox.Text.Trim()),
    Active = true,
    Deleted = false,
    ClientId = _thisClient.ClientId,
    CreateDate = DateTime.UtcNow
  };

  var dataContext = new PMDataContext(Global.ConnectionString);
  using (dataContext)
  {
   var txScope = new TransactionScope();
   using (txScope)
   {
    try
    {
     dataContext.Projects.Add(project);
     dataContext.SaveChanges();
     txScope.Complete();
    }
    catch (UpdateException upEx)
    {
     //TODO: log this update exception
     System.Diagnostics.Debug.WriteLine(upEx.Message);
    }
    catch (Exception ex)
    {
     //TODO: log thjis general exception
     System.Diagnostics.Debug.WriteLine(ex.Message);
    }
   }
  }
}

In previous post How to insert new record using Entity Framework, I discussed one aspect of CRUD operation in an application or process. In this post I will talk about another aspect of the process, how to update a record using Entity Framework. If you are building some kind of business intelligence aplication where only action you have to take is read the data, then you may not be interested in UPDATE part of the process. But most of the applications require you to undertake whole spectrum of operations. And UPDATE is big part of the process.

The process of updating database records using Entity Framework is pretty straight forward. In previous post you saw that you use the collection property of Data Context to add new object in it and then call SaveChanges method. You will still be using SaveChanges method. Only difference is that you are not operating on the collection object. First you will use LINQ query on Data Context to get instance of the record object. Then make the changes to that object and then call SaveChanges. Entity Framework keeps track of what records are being changed when you change any property value of an object. When you call SaveChanges, it prepares a list of objects that need to be updated. And then take appropriate action. All the tracking is transparent to you. The following code snippet shows how you can update record using Entity Framework.

protected void OnUpdate(object sender, EventArgs e)
 {
     if (!ValidateInput())
     {
         ErrorMessage_Label.Visible = true;
         return;
     }

     decimal billRate = 0.0M;
     if (!string.IsNullOrEmpty(BillRate_TextBox.Text.Trim()))
     {
         decimal.TryParse(BillRate_TextBox.Text.Trim(), out billRate);
     }
     var displayName = AntiXss.HtmlEncode(DisplayName_TextBox.Text.Trim());
     if (string.IsNullOrEmpty(displayName))
     {
         displayName = AntiXss.HtmlEncode(FirstName_TextBox.Text.Trim());
         if (!string.IsNullOrEmpty(AntiXss.HtmlEncode(LastName_TextBox.Text.Trim())))
         {
             displayName += string.Format(" {0}", AntiXss.HtmlEncode(LastName_TextBox.Text.Trim()));
         }
     }
     var dataContext = new PMDataContext(Global.ConnectionString);
     var member = (from memberData in dataContext.Members
                   where (memberData.CompanyId == _thisCompany.CompanyId 
                             && memberData.MemberId == _thisMember.MemberId)
                   select memberData).First();

     member.MemberFirstName = AntiXss.HtmlEncode(FirstName_TextBox.Text.Trim());
     member.MemberLastName = AntiXss.HtmlEncode(LastName_TextBox.Text.Trim());
     member.MemberDisplayName = displayName;
     member.Active = _thisMember.Active;
     member.Deleted = _thisMember.Deleted;
     member.BillingRate = billRate;
     member.Availability = Convert.ToInt32(Availability_Dropdown.SelectedValue);

     try
     {
         dataContext.SaveChanges();
     }
     catch (Exception ex)
     {
         // TODO: Log the exception.
         System.Diagnostics.Debug.WriteLine(ex.Message);
     }
}

The implementation gets record for a specified Member using LINQ query. Then updates various values. And at the end calls SaveChanges. Very simple and straightforward process.

In earlier post, How to insert records in database using Entity Framework, I discussed how you can use code first approach to create a POCO (Plain old CLR object) and then use data context to manipulate the tables. In that example POCO name was one to one match with table names in the database. Entity Framework was able to map the table with class name without any problem.

But you will always have cases where table names will not match with class names. This will happen if you are trying to reuse some existing code where classes were created based on some older schema. But now you have database tables that have totally different names. Another example, which I will use in this discussion, is using ASP.Net membership provider tables and procedures. You will notice that each database table is prefixed with aspnet_ by default. Now if you have classes with named like aspnet_Applications in your project, it will violate all the coding standard constraints and policies. And if you have some TFS check-in policy in place then you are out of luck because all policies will be violated.

There is a simple solution to this problem. You can create your POCO names to comply with standard naming convention. For example in my application, I want to query aspnet_Applications table but I want to keep my class name as Application. Following code snipper shows how I defined my class.

using System.ComponentModel.DataAnnotations;

[Table("aspnet_Applications")]
public class Application
{
 public Guid ApplicationId { get; set; }
 public string ApplicationName { get; set; }
 public string Description { get; set; }
}

I added TableAttribute to the class and set the name of the actual database table to which this class maps to. To add this attribute you will need to include System.ComponentModel.DataAnnotations in your class file.

If you do not add this attribute, you will see an exception like below when trying to query data context for the objects from that table.

Invalid object name dbo.Application

You can follow the same approach to map data base fields to class properties that do not match one to one.

In one of my earlier posts I wrote about using LINQ To SQL to call stored procedures. That opens up a way for you to very handy ORM tool provided by Microsoft in form of LINQ to SQL classes. In this post I will discuss how you can use LINQ to SQL to add new records in the tables in database.

• First step in the process, as discussed in earlier post, is to add Linq to Sql class item in your project. This will add a file with DBML extension. For example I have PMDataObject.dbml in my class which I use to create all business objects from database.

• Next step is to drag and drop the database tables on the design surface of ORM tool. As you can see I have added Client table from my database to this surface. Once you have added a table to ORM, it will automaticallyu create a class related to that table in your project. In my case it added Client class.

  

• Now you may be looking for method like Insert or AddClient etc. to insert a new record in the database. You will not find any method like this. Linq To Sql ORM works on idea that you are making a change to collection of records in database. You will notice that you will have properties on DataContext class that represent collection of objects representing classes for each table. For example in my project I have property that looks like dataContext.Clients. You will notice that this collection has a method InsertOnSubmit. So the way this works is that you will create a new object, in my case Client, and then add it to collection. Once you have added all the objects in the collection, then you will call InsertOnSubmit method and you will have new record inserted into the table in the database. Following code sample show how I used it in my sample project.

  protected void OnAddClient(object sender, EventArgs e)
  {
    var conn = ConfigurationManager.
                     ConnectionStrings[Constants.ConnectionStringName].
                      ConnectionString;
    var dataContext = new PMDataObjectDataContext(conn);
    var client = new ByteBlocks.PMObjects.Client() 
    { 
      ClientName = AntiXss.HtmlEncode(ClientName_TextBox.Text.Trim()),
      ClientDescription = AntiXss.HtmlEncode(ClientDescription_TextBox.Text.Trim()),
      CreateDate = DateTime.UtcNow,
      Active = true,
      Deleted = false
    };
    dataContext.Clients.InsertOnSubmit(client);
    try
    {
      dataContext.SubmitChanges(ConflictMode.FailOnFirstConflict);
    }
    catch (Exception ex)
    {
       // TODO: Log the exception.
       System.Diagnostics.Debug.WriteLine(ex.Message);
    }
  }
  

As you can see how easy it is to add new record in the tables using Linq to Sql class objects. In subsequent posts I will discuss some advanced techniques of using Linq To Sql.

If you have a collection of class object and you want to extract an array or list of only one of the properties, lot of time we end up writing code that iterates over each item and then putting it in a new array or list. There is a clean and quick way of doing it using Linq. Following code snippet shows how I extracted an array of ingredient names from a List of Ingredient class objects.

public string[] SearchIngredient(string prefixText, int count)
{
 var ingredientsList = IngredientData.GetIngredientsWithNameLike(prefixText);
 if (null == ingredientsList ||
     ingredientsList.Count == 0)
  {return null;}

 var nameQuery = from ingredient in ingredientsList
                   select ingredient.Name;
 return nameQuery.ToArray();
}

While developing Twitter applications, one of the common patterns that used to emerge is that some particular users will post lot of messages in a short time span. And when searching records, I used to end up with multiple messages from one particular user. So for some user interface, I always had to group messages by user posting the message.

Following code shows how easy it is to group records using LINQ. Just to explain the code a little bit, when search is performed for a certain query term using twitter rest api with Tweetsharp, results are returned as list of TwitterSearchStatus objects. The user who posted the message is indicated by FromScreenName property. So this property becomes the key on which grouping is to be performed. Following group statement shows how grouping is done in LINQ statement.

 var resultsGrpByUser = from searchResult in searchResults
   group searchResult by searchResult.FromUserScreenName
   into userMessages
 select new { FromUser = userMessages.Key, Messages = userMessages };

Above code uses group statement to create group on user name, and the grouping results are returned as Dictionary of anonymous objects that have properties named FromUser and Messages. Following code snippet shows the whole implementation of grouping using LINQ.

static Dictionary<string, List<TwitterSearchStatus>> GetPopularTweets()
{
 List<TwitterSearchTrend> trends = GetDailyTrends();
 if (null == trends ||
     trends.Count == 0)
 {
  return null;
 }
 // For now only process first trend term.
 List<TwitterSearchStatus> searchResults = SearchForTerm(trends[0].Query);
 if (searchResults.Count == 0)
 {
  return null;
 }
 var resultsGrpByUser = from searchResult in searchResults
   group searchResult by searchResult.FromUserScreenName
   into userMessages
 select new { FromUser = userMessages.Key, Messages = userMessages };
 Dictionary<string, List<TwitterSearchStatus>> dict = 
  new Dictionary<string, List<TwitterSearchStatus>>();
 foreach(var userMessage in resultsGrpByUser)
 {
  Console.WriteLine(userMessage.FromUser);
  foreach(var twitterStatus in userMessage.Messages)
  {
   Console.WriteLine("\t{0}", twitterStatus.Text);
  }
  dict[userMessage.FromUser] = userMessage.Messages.ToList();
  Console.WriteLine("*************");
 }
 return dict;
}

While working on Local Twitter application, I had to parse a very simple XML stream to create a business object. In classic XML days, we all used to do it using XPath or SAX or other related techniques. Xml to Linq and Linq to Xml provides a very intuitive way to perform parsing task. Lets look at the code snippet below.

<?xml version="1.0" encoding="UTF-8"?>
<Response>
 <Ip>74.125.45.100</Ip>
 <Status>OK</Status>
 <CountryCode>US</CountryCode>
 <CountryName>United States</CountryName>
 <RegionCode>06</RegionCode>
 <RegionName>California</RegionName>
 <City>Mountain View</City>
 <ZipPostalCode>94043</ZipPostalCode>
 <Latitude>37.4192</Latitude>
 <Longitude>-122.057</Longitude>
</Response>

private SiteUser ProcessResponse(string strResp)
{
 StringReader sr = new StringReader(strResp);
 XElement respElement = XElement.Load(sr);
 string callStatus = (string)respElement.Element("Status");
 if (string.Compare(callStatus, "OK", true) != 0)
 {
  return null;
 }
 SiteUser user = new SiteUser() {IP = (string) respElement.Element("Ip"), 
 City = (string) respElement.Element("City"),
 Country =  (string)respElement.Element("CountryName"),
 CountryCode = (string)respElement.Element("CountryCode"),
 RegionCode =  (string)respElement.Element("RegionCode"),
 RegionName = (string)respElement.Element("RegionName"),
 PostalCode =  (string)respElement.Element("ZipPostalCode"),
 Latitude = (decimal)respElement.Element("Latitude"),
 Longitude = (decimal)respElement.Element("Longitude")};
 return user;
}

You can see from the code above, how easy the task is. You load up the XML string as StringReader and then use that to initialize XElement object. When you create XElement it is already at documentElement level. The rest of the API is self explanatory. You simple use Element method to access elements by their node names under a given XElement object.