Wednesday, May 6, 2009

Damn, Where did my isolated storage’d file go?

LOL, I was doing some work writing some data to IsolatedStorage. Loaded the app, loaded some data that also was supposed to be copied to IsolatedStorage and then I though, cool, no errors, let’s look at that file! ;-)

Problem being that I could not file the little bastard, I look where where the MSDN article tells you to .. (below) and couldn’t find it either. (MSDN says look for Microsoft/IsolatedStorage)

 

image

I couldn’t find it..

Ok, so I went to trusty? old search in WindowsXP and got it, the trick was to search with “Search hidden files and folders” ticked…

image

End result after 2 days..

Note that the MSDN article was correct! but since I don’t always read instructions I couldn’t find it at first!

 

image

Friday, April 24, 2009

Sharepoint Web Services: creating Lists

 

Just felt like posting this snippet that shows how to create a List in Sharepoint using the web service that SP provides. Those web services could be found at hhtp://superDomain/sites/elSite/_vti_bin/Lists.asmx. Once you add the service reference to your project and create the proxy, the function of interest here is AddList() also how you define your InnerXml property for the new fields node is important and easy to make a mistake with. So as always with Sharepoint is very trial and errorish at least for me (scarred for ever by SP)

I hope this snippet stands the test of time in this blog post and I can easily find it one day, or if I can help some poor soul traveling through the Sharepoint ‘realms* the better/

More information can be found in MSDN at  http://msdn.microsoft.com/en-us/library/lists.lists.addlist.aspx

 

public void CreateList()
   {
 
     var listService = new Lists
     {
       Url = @"http://superDomain/sites/elSite/_vti_bin/Lists.asmx",
       Credentials = CredentialCache.DefaultCredentials
     };
 
     const string listName = "Web Service Created";
 
     var listCreatedAsXmlNode = listService.AddList(listName, "Web Service Created List Description", 100);
     
     var listRetrieved = listService.GetList(listName);
     XmlNode version = listRetrieved.Attributes["Version"]; 
 
     var xmlDoc = new XmlDocument();
 
     var titleAttrib = (XmlAttribute)xmlDoc.CreateNode(XmlNodeType.Attribute, "Title", String.Empty);
     var descriptionAttrib = (XmlAttribute)xmlDoc.CreateNode(XmlNodeType.Attribute, "Description", String.Empty);
 
     titleAttrib.Value = listName; // Can use this to update it
     //descriptionAttrib.Value = "!" ;
 
     var properties = xmlDoc.CreateNode(XmlNodeType.Element, "List", String.Empty);
     
     properties.Attributes.Append(titleAttrib);
     properties.Attributes.Append(descriptionAttrib); 
     
     var newFields = xmlDoc.CreateNode(XmlNodeType.Element, "Fields", String.Empty);
     newFields.InnerXml =
     @"<Method ID='1'>
       <Field Type='DateTime' DateOnly='TRUE' DisplayName='My Date Field' FromBaseType='TRUE' Required='TRUE' Description='some text description'/>
     </Method>
     <Method ID='2'>
       <Field Type='Number' MAX='10' DisplayName='NumberColumn' FromBaseType='TRUE' Required='TRUE' Description='This is a Number' />
     </Method>  
     <Method ID='3'>
       <Field Type='Calculated' DisplayName='MyCalcField' ResultType='Text'>
         <Formula>=Title&amp;NumberColumn</Formula>
           <FormulaDisplayNames>=Title&amp;NumberColumn</FormulaDisplayNames>
           <FieldRefs>
             <FieldRef Name='Title'/>
             <FieldRef Name='NumberColumn'/>
            </FieldRefs>
           </Field>
       </Method>
       <Method ID='4'>
         <Field ReadOnly='TRUE' Type='Counter' PrimaryKey='FALSE' DisplayName='Counter' FromBaseType='TRUE' />
       </Method>";
 
     var returnedNode = listService.UpdateList(listName ,properties,  newFields, null, null, version.Value);
     Console.WriteLine(returnedNode.OuterXml);
   }

Tuesday, September 30, 2008

Coming back

Hi there, I really want to get back into blogging. I really enjoyed it and I learned a lot when I was posting. Lately I been receiving some comments and emails from people that have found my (old) posts useful. I find this very humbling and extremely rewarding. It's great to know you helped someone somewhere. I'm posting this entry to say I will do my best to get back into it. In the last few months I been very busy settling into a new job and with other personal things here and there. I haven't neglected my own learning though, although I would have liked to spend more time with my own projects etc. At the moment (and in the past few months) I been playing around with:
  • Continuos Integration via CruiseControl.net.
  • NAnt
  • nUnit
  • ASP.net MVC
  • jQuery
  • Mocking concepts
  • IoC (using the Repository pattern)
  • Silverlight and WPF (tiny wee bit)
I'm not totally sure how I'm going to tackle the *comeback* but maybe having a target of one post a week could be a good starting point. Look forward to my first post, Cheers, Juan

Tuesday, April 22, 2008

Handling concurrency conflicts in LINQ to SQL

Apologies for the delay in getting this post out, been busy and been playing around with too many good things lately. Virtual machines, Unit Testing etc, recording some radio from the net etc. Anyways, I have been planning to write about my experiences with Conflict handling in Linq2Sql, what happens when two users want to update the infamous Product table in Northwind.

When I started playing around with it I also discovered that I needed to investigate a bit more about DataContexts, so I went reading around the web to gather info, did some experiments in my machine etc and interestingly enough I had a very related question at work where a colleague asked about the what are best practises when instantiating them. He had a static DataContext to be shared amongst his data access for a particular entity. My opinion on this is that DataContexts are cheap and light to create and that creating them for a single unit of work I do say in my GetProductByID function should be good enough. I'll say that persisiting DataContexts would create a bit of an overhead when I think that it has to keep all the mappings and ObjectTracking etc ( mind due, you can turn off ObjectTracking if you are going to do read only..). Also DCxt are not thread safe so that's another big reason not to do that. But when I started Linqing2Sql I did entertain the idea of sharing my DataContext through a singleton. Hrmm.

public voidTurnOffObjectTracking()
{
   var dal = newProductDataContext();
   // If you don't want to update stuff, don't track them.
 
dal.ObjectTrackingEnabled = true;           
   Productprod = dal.Products.SingleOrDefault(a => a.ProductID == Guid.NewGuid());
   Assert.IsNull(prod);
}

Let me not get sidetracked, it's 10:30pm and I want to go to sleep, back to our interleaving..

First of all, let's recreate the case where <  1 users are updating a record and our sys throws up a smelly. But before I do that, let's keep in mind that DataContexts are neat, I mean sometimes too neat since some stuff happens behind the scenes kind of magic, but that maybe just me :-(

For example, note how in the example below two products are obtained from the db and one of them is upated, and then without committing this change to storage, I get another instance of the same product, then I commit the original change and then compare the Products....

[TestMethod]
public void Test_DataContext_Product_Independence()
{
    using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
    {
        var linq = new ProductDataContext();
        linq.Log = Console.Out;

        Guid productID = new Guid("50BD6DEB52C14D65BEC268957601EF25");
        
        // Get a Product with the ID as above
        Product product_1 = linq.Products.Single(p => p.ProductID == productID);
        
        // check that the Prod Name is as expected..
        Assert.AreEqual("Tourtière", product_1.ProductName);
        // Update the Prod Name
        product_1.ProductName = "Côte de Tourtière";
        // Let's retrieve the product again, note that the update above has not been committed to the database
        Product product_2 = linq.Products.Single(p => p.ProductID == productID);
        
        // Now commit!
        linq.SubmitChanges();

        // Note that the new name of Product2...obtained from DataContext Memory, before I committed the change! neat!
        Assert.AreEqual("Côte de Tourtière", product_2.ProductName);               
    }
}

firstTestPassed

OK, so to simulate interleaving, we'll simulate the two users trying to update a record in the db. The record look like below

ProductID / ProductName / UnitPrice

 455984DB-92DD-4BA4-87D7-91E4E2E5E00D    Camomile Tea    10.00    ....

Now our two unsuspecting users are going to try to update the Product Name for this product

[TestMethod]
public void RecreateInterleavingCondition()
{
    var user1DAL = new ProductDataContext();
    var user2DAL = new ProductDataContext();

    Guid pID = new Guid("455984db-92dd-4ba4-87d7-91e4e2e5e00d");
    Product user1Product = user1DAL.Products.Single(p => p.ProductID == pID);
    user1Product.ProductName = "Royal Camomile Tea";
    Console.WriteLine(user1Product.ProductName);

    Product user2Product = user2DAL.Products.Single(p => p.ProductID == pID);
    user2Product.ProductName = "Royal Camomile Tigris Tea";
    Console.WriteLine(user1Product.ProductName);

    user1DAL.SubmitChanges();
    Console.WriteLine(user2Product.ProductName);
    user2DAL.SubmitChanges();
}

Run the above results in: Conflict Exception!

interleavingException

User1 retrieves our Product and changes the name to "Royal Camomile Tea", not Submitting the changes, now User2 gets in there, retrieves the record changes to "Royal Camomile Tigris Tea", but User1 submits his changes and then User2 submits her changes. End result Conflict!

Now...How does LINQ to SQL handles it?

L2SQL uses what is called Optimistic concurrency is based on the un-healthy assumption that db transactions won't conflict with other transactions, that basically the chances of two users at exactly the same time wont happen that often. The alternative is pessimistic concurrency, this is where users will retrieve a record from the database and hold a lock on that record/table to prevent other pesky users to change that, problem is that is not scalable at all, systems grew larger and records where locked for ages before the sys will release the handle on it, also the emergence of the Web, the demands of stateles apps made it totally unfeasible for the pessimistic model.

LINQ to SQL uses Optimistic concurrency, what it does is that when updating a record, it checks the previous values of that record, it the values where unchanged, then is all good, otherwise it will complain and it will throw an exception and then you can handle it and choose how to handle the conflict.

In effect, if you turn Log on when Updating a record, and see the query passed onto the db you'll see that all values will be passed in the WHERE clause to compare against the columns in your table, not only the ID for the record. See below for the updates we tried to do above. MSDN has a good article on LINQ to SQL and Optimistic conc here

Royal
UPDATE [dbo].[Product]
SET [ProductName] = @p5
WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([UnitPrice] = @p2) AND ([CategoryID] = @p3) AND ([CreatedDate] = @p4)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [455984db-92dd-4ba4-87d7-91e4e2e5e00d]
-- @p1: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Camomile Tea]
-- @p2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [c9735b53-9c82-4f11-891a-8a53dc89565d]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [17/04/2242 11:07:05 PM]
-- @p5: Input VarChar (Size = 5; Prec = 0; Scale = 0) [Royal]

Royal Tigris
UPDATE [dbo].[Product]
SET [ProductName] = @p5
WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([UnitPrice] = @p2) AND ([CategoryID] = @p3) AND ([CreatedDate] = @p4)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [455984db-92dd-4ba4-87d7-91e4e2e5e00d]
-- @p1: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Camomile Tea]
-- @p2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [c9735b53-9c82-4f11-891a-8a53dc89565d]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [17/04/2242 11:07:05 PM]
-- @p5: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Royal Tigris]

DataContext implements Optimistic by default, but you can turn it off, imagine that it passed lots of parameters, this will certainly cause performance dramas or you simply could streamline it. You can fix your mappings with the UpdateCheck attribute. More info check MSDN article here and here

Ok, so the above will throw an exception when the Update query cannot find a match, it will interpret this as "oh, someone else got in first and changed something before I did! :-_(  , I'll throw a ChangeConflictException then..."

The art of handling conflicts

First of all, wrap your potentially conflict-causing code in a try/catch block and look out for a ChangeConflictException as below..

try
{
    linq_2.SubmitChanges();
    // Here the values change!
    var result = new ProductDataContext();
    Product product13 = result.Products.Single(i => i.ProductID == pID);
    linq.SubmitChanges();    
}
catch (ChangeConflictException ex)
{ ....

Once you catch the exception, you have choices of how to handle the conflict by basically examining the guilty entities and members and choosing which values to keep, be it Database, overwriting database  values  or merging changes with the database. Let's have a look first at how to retrieve the conflicting data.

DataContext objects have a property called ChangeConflicts thats gives you a ChangeConflictCollection of ObjectChangeConflict objects that eventually you can enumerate and then drill down and get at the values that are in conflict.

catch (ChangeConflictException ex)
{
    foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
    {
    ...........................................
    ...........................................
Each ObjectChangeConflict object represents the conflicted instance, in plain english it encapsulates the update attempt that caused the conflict. 
We can then iterate over the entities that "participated" in that conflict and get specific information about them. You do this by accessing the MemberConflicts collection of ObjectChangeConflict.
catch (ChangeConflictException ex)
{
    foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
    {
        foreach (MemberChangeConflict mcc in occ.MemberConflicts)
        {
            Console.WriteLine("Original: " + mcc.OriginalValue);
            Console.WriteLine("Database value: " + mcc.DatabaseValue);
            Console.WriteLine("CurrentValue: " + mcc.CurrentValue);
        }

Here is where it starts to get juicy. See how above we can get the different values for a particular entity through the "stages" of the conflict. This information could be very useful, you might want to present this details to the user and let her make a decision of what to submit to the database in  a grid or something...right?

But of course, you can explicitly resolve the conflicts by once the error is detected, choosing to keep current values, database values or merge. Let's have a look at it.

Overwriting Database values

First up we are going to solve the conflict by merging the database values with User1 only since User1 got in there last, he is the last committer (is there such a work in english?), User2 won't see anything since her changes when in and the Optimistic handling didn't detect any changes. So the conflict is a conflict from the context of User1, he created the conflict, he is the last in so he has to tell the database what to do with the changes..The database if it talked, it could ask the question.."oh..you are last in...what do you we do with your changes...", That's the way I see it. :-)  Shut up and show me code please ... thanks.

[TestMethod]
public void CheckProductNamesWithTwoDataContexts()
{           
    var linq = new ProductDataContext();
    var linq_2 = new ProductDataContext();
    Guid pID  = new Guid("455984db-92dd-4ba4-87d7-91e4e2e5e00d");

    Product p1 = linq.Products.Single(p => p.ProductID == pID );
    p1.ProductName = "Red Tea";
    p1.CategoryID = new Guid("00000000-0000-0000-0000-000000000000");
    
    Product p2 = linq_2.Products.Single(p => p.ProductID == pID);
    p2.ProductName = "Green Tea";
    p2.CategoryID = new Guid("90000000-1111-1111-1111-000000000000");
    p2.UnitPrice = 10.0M;
    
    try
    {
        linq_2.SubmitChanges();
        var result = new ProductDataContext();
        linq.SubmitChanges(ConflictMode.ContinueOnConflict);  
    }
    catch (ChangeConflictException ex)
    {
        foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
        {
            foreach (MemberChangeConflict mcc in occ.MemberConflicts)
            {
                Console.WriteLine("Original: " + mcc.OriginalValue);
                Console.WriteLine("Database value: " + mcc.DatabaseValue);
                Console.WriteLine("CurrentValue: " + mcc.CurrentValue);
            }  
            linq.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
        }
        linq.SubmitChanges(); 
    }
}

Quite a lengthy function yeah, but let's see what happens. User1 retrieved the Product, changed the name and the CategoryID, User2 changed the name, CategoryID and UnitPrice. Now note that I called ResolveAll of the ChangeConflict fame from our DataContext, in this case I called it linq. ( You can also call ObjectChangeConflict's Resolve function) This function takes an enum that could be KeepCurrentValues, KeepChanges and OverwriteCurrentValues. In this case we use KeepCurrentChanges, which means we'll keep our changes for the data that clashed with User2, anything else that User2 changed will be ignored and the database defaults will be reinstated. I suppose the way to interpret it is KeepCurrentValues keeps the current values in the DataContext, in this case User1. Note: Make sure once you handle the exception any way you see fit, make sure you SubmitChanges() again for that DataContext otherwise nothing will happen! :-)

Also note that you can choose also when to handle the exceptions. Say you have a lot of changes that the user made and it will be too cumbersome for them to re-enter all those values again. Since SubmitChanges accumulates all conflicts in a batch, the number of changes could be huge.. Good news is that SubmitChanges() can be overloaded to take a ConflictMode enum that will either tell the instance of DataContext to either throw the ConflictException as soon as it detects it (thus minimising the potential hassles for users) called FailOnFirstConflict, or to keep accumulating and attempting to update data and then report the whole lot (ContinueOnConflict).

  ProductName CategoryID UnitPrice
Database row (originally) Camomile Tea E1422E86-.... 0.00
User 1 Red Tea  Guid.Empty  
User 2 Green Trea Guid.NewGuid 10

Result is:

  ProductName CategoryID UnitPrice
database RedTea Guid.Empty 0.00

Note that User1 values went it , User2 data was thrown out and even though in UnitPrice there wasn't a clash between the users, the database value was the chosen one!

Retaining Database values

This is in fact the opposite as our previous example, User2 values are merged with the database and User1's values are chucked out of the window. Basically User1 says, "I'll let the other guy's values go through..I'll comeback to it again and see where my data fits with hers..". The term OverwriteCurrentValues, the way I interpret it that is read from the point of view of User1, "Overwrite my current values, therefore since User2 has committed and are in the db, keep them..".  This is achieved by using:

linq.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

Table please...

  ProductName CategoryID UnitPrice
Database Camomile E1422E86 0.00
User1 Red Tea Guid.Empty  
User2 Green Tea NewGuid 10.0
  ProductName CategoryID UnitPrice
  Green Tea NewID 10.0

Merging Conflicts with Database values

Using ResolveAll(RefreshMode.KeepChanges) means keeps all the changes in the DataContext and merge them with the database, overwritting the row values if necessary.

  ProductName CategoryID UnitPrice
Database Camomile E1422E86 0.00
User1 Red Tea    
User2 Green Tea NewGuid 10.0

The results are as expected, User1 keeps his Red Tea, and User2 keep hers NewGuid and UnitPrice, in effect the merging has been "between" the users only.

  ProductName CategoryID UnitPrice
  Red Tea NewID 10.0

 

Apologies for the delay in getting this one out, I have been busy and I was writing this up when we noted our beautiful and very lively ( and vicious) pet parrot passed away last Thursday night.  Very sad and it all was all of a sudden. I hope the poor fella didn't suffer and I certainly miss his singing in the mornings. Lesson learned though, birds should be free and while they are very entertaining I don't think is fair to keep them [birds] inside for our selfish gratification.

I certainly enjoyed writing this post and playing around with the DataContext, generating errors etc. There is ton of info on this in MSDN, I certainly think the guys have done a great job in documenting Conflict resolution in LINQ to SQL.

Place for you to start would be:

http://msdn2.microsoft.com/en-us/library/bb399389.aspx

Sunday, April 6, 2008

Playing around with LINQ to SQL: Enumerable.ToDictionary Extension Method

Playing around with Linq I noticed the Enumerable.ToDictionary extension method on IEnumerables<T>. I found it very handy to store expected results for Unit Testing. Yesterday I was playing around with general stuff and I did some looking around a ToDictionary.

We all know what Dictionaries are in the programming world right? Well they are data structures where each element is a key/value pair. Each key in it must be unique and it cannot be null. They are blindly fast for element retrieval by key too, O(n) . So basically they are very efficient at locating, inserting, deleting records associated by keys,

The ToDictionary<TSource,TKey> can be used in many ways, 4 to be more precise but in all them you have to specify a Func<TSource,TElement> to be your key selector, you also have the choice to provide another function to your element selector, ie the object that you are going to store as key and finally you can pass on a IEqualityComparer<T> to compare keys so the underlying HashTable can determine whether keys used are equal. (If you try to add an Element with a Key that already exists in the Dictionary, an ArgumentException will be thrown.

Examples:

 

[TestMethod]
public void CreateDictionary_of_AnonymousTypes()
{
var ps = from p in productos
orderby p.ProductID ascending
select new
{ p.ProductID,p.ProductName};

// Creates a Dictionary where the Key is a productID and the Object is an Anonymous type.
var dummyDictionary = ps.ToDictionary(a => a.ProductID); // Note the Ano

// 77 Products in Northwind...
Assert.AreEqual(77, dummyDictionary.Count());
Assert.AreEqual("Tofu", dummyDictionary[14].ProductName);
// Is it a fib that oo.ProductName is not 'Chai tea' ?
Assert.IsFalse(dummyDictionary[1].ProductName != "Chai tea"); // Product Name of Product with ID = 1 is 'Chai tea'
}

In this example all I did was crated a Dictionary by using the productID as key selector, the dictionary will have a integer as key and the Element will be an Anonymous type.

[TestMethod]
public void CreateDictionary2()
{
var ps = from p in productos
orderby p.ProductID ascending
select new
{ p.ProductID, p.ProductName };

// Creating a dictionary by explicitly determining the Key and the Element
Dictionary<int,string> elDictionary = ps.ToDictionary(a => a.ProductID, a=>a.ProductName);
CollectionAssert.AllItemsAreUnique(elDictionary.Keys);
}

This example a function to select keys and select Elements was done, resulting in a Dictionary<int,String>

[TestMethod]
public voidDictionary_Products_By_CategoryName()
{
       Dictionary<string, IEnumerable<Product>> superDictionary;        
          
        var prods = fromp inproductos
                    groupp byp.Category.CategoryName intoprods_by_categoryName
                    select new
                  
{                           
                        list = prods_by_categoryName
                    };
        superDictionary = prods.ToDictionary(a => a.list.Select(b => b.Category.CategoryName).First(), c => c.list.Select(y=>y));
        IOrderedEnumerable<String> coll = superDictionary.Keys.OrderBy(a=>a);

        IEnumerable<String> orderedCategories = ((fromcats inctx.Categories
                                                     selectcats.CategoryName).OrderBy(h=>h));
        List<String> myList = coll.ToList();
        myList.Reverse();
        myList.Reverse();
        CollectionAssert.AreEqual(myList , orderedCategories.ToList());
       
}


This example I created a Dictionary of Products, where the key is the CategoryName and the Elements is a IEnumberable of Products for that Category.

The same can be achieved as below, much simpler!


[TestMethod]
public void Dictionary_Products_By_CategoryName_Simple()
{
Dictionary<string, IEnumerable<Product>> superDictionary;
superDictionary = (from categories in ctx.Categories
select new
{
categories.CategoryName,
ProductsInCategory = categories.Products.Select(a=>a)
}).ToDictionary(a=>a.CategoryName,a=>a.ProductsInCategory);

Assert.IsInstanceOfType(superDictionary, typeof(Dictionary<string, IEnumerable<Product>>));
}

Here I want to list all Products by Category, and sort them by total unit price...


[TestMethod]
public void do_all_that_stuff_in_less_lines_of_code()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (from categories in ctx.Categories
select new
{
CategoryName = categories.CategoryName,
TotalCategoryUnitPrice = categories.Products.Sum(a=>a.UnitPrice),
Products = categories.Products.OrderByDescending(a=>a.UnitPrice).ToList()
}).OrderByDescending(a=>a.TotalCategoryUnitPrice);

foreach (var category in result)
{
Console.WriteLine( String.Format("\n{0}:\n{1}",category.CategoryName, new String('-', category.CategoryName.Length )));
category.Products.ForEach(p => Console.WriteLine(String.Format("{0,-35} {1,10:C2}", p.ProductName, p.UnitPrice)));
Console.WriteLine(String.Format( "{0,46}", new String('-',6) ));
Console.WriteLine("Total:".PadLeft(38) + String.Format("{0,8:C2}", category.TotalCategoryUnitPrice));
}
}

Capture


Also the ToLookup function is worth mentioning. What it does is creates a Lookup<TKey,TElement> ( basically a collection where each key is mapped to one or more values)


[TestMethod]
public void ToLookup()
{
var prods_by_cat = from products in ctx.Products
select products;

Lookup<string, string> ellookup = (Lookup<string, string>)prods_by_cat.ToLookup(
a => a.Category.CategoryName, // Key
a => a.ProductName); // Value
// Iterate through each IGrouping in the Lookup and output the contents.
foreach (IGrouping<string, string> packageGroup in ellookup)
{
// Print the key value of the IGrouping.
Console.WriteLine(packageGroup.Key);
// Iterate through each value in the IGrouping and print its value.
foreach (string str in packageGroup)
Console.WriteLine(" {0}", str);
}
}

and the output..note that packageGroup is just a collection of Products for that particular Category. Cool eh?


Beverages
Chai tea
Changta
Guaraná Fantástica
Steeleye Stout
Côte de Blaye
Chartreuse verte
Ipoh Coffee
Laughing Lumberjack Lager
Outback Lager
Rhönbräu Klosterbier
Lakkalikööri
Condiments
Aniseed Syrup
Chef Anton's Cajun Seasoning
Chef Anton's Gumbo Mix
Grandma's Boysenberry Spread
Northwoods Cranberry Sauc

Anyway, I don't think I'll be playing with any Dictionaries for a while, done and dusted!

Playing around with LINQ to SQL: Using DataLoadOptions. Immediate loading and filtering of related data

Linq2Sql use deferred execution of queries, lazy loading to get data from the database. The data is only retrieved from the source when we are ready to use it, say when we iterate through it and do something to it. The are advantages to it, say when we have hierarchical data like Orders->OrderDetails, iterating through the Order will not get you all the child objects ( the OrderDetails) by default. This is a good thing of course, it returns you what you want but it can also lead to unexpected results and unnecessary round trips to the database if not careful.

order_order_details_Products

 

[TestMethod]
public void Lazy_Load_Them()
{
    var context = new NorthwindDataContext();
    context.Log = Console.Out;

    var mydata = from order in context.Orders
                 select order;

    foreach (Order o in mydata)
    {
          Console.WriteLine(o.OrderID);
    }
}

 

The only query generated is below:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]

In the above case it all very simple, all we wanted was the Order(s) entities, network bandwidth was minimised, memory consumption minimised etc.  But what if inside the foreach we also want to output the OrderDetails children of Orders and their corresponding Products? All the child records will be requested when we want, in the below case when we output them...

[TestMethod]
public void Lazy_Load_Them()
{
    var context = new NorthwindDataContext();
    context.Log = Console.Out;
    var mydata = from order in context.Orders
                 select order;

    foreach (Order o in mydata)
    {
         Order_Detail od = o.Order_Details.First();//Just the first record for brevity
         Console.WriteLine(od.ProductID + ", " + od.Product.ProductName);
     }
}

Well, the results are gory, as we request details and the Product for each Order, a separate query will be created and this will mean a new round trip to the database for every Order. The queries are below and trust me, the are like that for every row of the Order table.

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10248]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [11]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

11, Queso Cabrales
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10249]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [14]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

14, Tofu
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10250]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

As you can see we have queries for the OrderDetails and another query for the Product data..Three queries for for that! For evey single Order? Hrmm

I have made a total stuff-up, a good thing 'lazy loading' turned into a monster. I have been careless in this case, but how can we improve it? How can minimize the round trips?

DataLoadOptions: LoadWith(LambdaExpression)

LinqToSql provides a method for immediate loading of data related to the main Entity we are working through the DataLoadOptions class. This class provides two methods to get immediate loading of related data, The LoadWith method and AssociateWith method that allow for filtering of related objects.

All you have to do is specify which data that is related to your main target and should be retrieved at the same time with a Lambda expression, for example in our case above our main Entity is the Order, and we would like to get the Order_Details for each Order. This will result in only one trip to the database for all that information, we eliminate all the subqueries that we needed because of the lazy loading. This will improve things significantly for our applications and our users.

In the exaple below I want to get all the Order_Details related to my main interest, Order.

public void Get_Deferred_Data_With_Loading_Options()
{
    var dataContext = newNorthwindDataContext();
    DataLoadOptionsdataLoadOptions = newDataLoadOptions();           
    dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
    dataContext.LoadOptions = dataLoadOptions;
     
    var orders = from order indataContext.Orders
                       select order;

    foreach (Order order in orders)
    {
      List<Order_Detail> ods = order.Order_Details.ToList();
      ods.ForEach(od => Console.WriteLine( order.CustomerID+ " "+ od.OrderID + " , ProdID: "+ od.ProductID + " : "+ od.UnitPrice));
    }          
}

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t2]
    WHERE [t2].[OrderID] = [t0].[OrderID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

VINET 10248 , ProdID: 11 : 14.0000
VINET 10248 , ProdID: 42 : 9.8000
VINET 10248 , ProdID: 72 : 34.8000
TOMSP 10249 , ProdID: 14 : 18.6000
TOMSP 10249 , ProdID: 51 : 42.4000
HANAR 10250 , ProdID: 41 : 7.7000
HANAR 10250 , ProdID: 51 : 42.4000

 

And we can also further load more child records as below.. by adding the LoadWith to get Product associated records from the Order Details entity

 

public void Get_Deferred_Data_With_Loading_Options()
{
      var dataContext = new NorthwindDataContext();

      DataLoadOptions dataLoadOptions = new DataLoadOptions();
      dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
      dataLoadOptions.LoadWith<Order_Detail>(od => od.Product);
        
      dataContext.LoadOptions = dataLoadOptions;
      dataContext.Log = Console.Out;
      
       var orders = from order in dataContext.Orders
                    select order;

       foreach (Order order in orders)
       {
           List<Order_Detail> ods = order.Order_Details.ToList();
           ods.ForEach(od => Console.WriteLine( od.Product.ProductName + ", " + order.CustomerID+ " " + od.OrderID + " , ProdID: " + od.ProductID + " : " + od.UnitPrice ));
       }           
}
 

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], [t2].[ProductID] AS [ProductID2], [t2].[ProductName], [t2].[SupplierID], [t2].[CategoryID], [t2].[QuantityPerUnit], [t2].[UnitPrice] AS [UnitPrice2], [t2].[UnitsInStock], [t2].[UnitsOnOrder], [t2].[ReorderLevel], [t2].[Discontinued], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t3]
    INNER JOIN [dbo].[Products] AS [t4] ON [t4].[ProductID] = [t3].[ProductID]
    WHERE [t3].[OrderID] = [t0].[OrderID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN ([dbo].[Order Details] AS [t1]
    INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t1].[ProductID]) ON [t1].[OrderID] = [t0].[OrderID]
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Queso Cabrales, VINET 10248 , ProdID: 11 : 14.0000
Singaporean Hokkien Fried Mee, VINET 10248 , ProdID: 42 : 9.8000
Mozzarella di Giovanni, VINET 10248 , ProdID: 72 : 34.8000

Query is a bit more complex, but thats all! No more round trips to our database. Cool eh?

 

AssociateWith: Filtering at the DataContext Level

This is used to specify in advance, sub-queries to limit the amount of data retrieved for any query against that particular context. Your DataContext instance will filter the data given by the Lambda you specified with AssociateWith all the time.

For example, I want to only process orders where the QuantiyInOrder is less than 10 because of shipping difficulties or special offers.

public void Get_Deferred_Data_With_Loading_Options()
{
      var dataContext = new NorthwindDataContext();
      DataLoadOptions dataLoadOptions = new DataLoadOptions();
      dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
      dataLoadOptions.AssociateWith<Order>(order => order.Order_Details.Where(p => p.Quantity < 10));            

 

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t2]
    WHERE ([t2].[Quantity] < @p0) AND ([t2].[OrderID] = [t0].[OrderID])
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON ([t1].[Quantity] < @p0) AND ([t1].[OrderID] = [t0].[OrderID])
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

VINET 10248 , ProdID: 72 : 5
TOMSP 10249 , ProdID: 14 : 9
VICTE 10251 , ProdID: 22 : 6
HILAA 10257 , ProdID: 39 : 6
ERNSH 10258 , ProdID: 32 : 6
CENTC 10259 , ProdID: 37 : 1
RATTC 10262 , ProdID: 56 : 2

All our queries within the scope of this DataContext will be filtered in advance rather than you having to specify a Where clause.

 

DataLoadOptions in MSDN can be found below

http://msdn2.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx

Monday, March 31, 2008

Localisation and Profiles: Programmatically changing the user's preferred culture.

A while ago I wrote a post on how to use Localisation in ASP.NET using resource files etc. We'll since then I been asked about how to change the user's culture programmatically by clicking a button or similar.

In that previous article I described how culture selection is based on browser preferences and HTTP language headers. While this is pretty good, there are many scenarios where this could be improved. Say you are travelling overseas and are accessing your appl remotely from someone's elses machine, if the culture of that browser is set to Japanese, then your application might not be readable to you. You might not be able to change your language preferences either because that might not be available in a controlled environment.

What options do we have?

In general, to get manage the user's preferences we have:

  • Use the browser settings ( not that great as described above but good enough for 80% of the time)
  • When a user registers of first uses your application, they can choose language of choice and then save this in the database for later retrieval
  • Persist the above choice in Session state or Cookies.
  • Use ASP.NET Profiles.
  • Let the users choose this in the application. ( buttons. ddls etc ) 

In ASP.NET is very straightforward to select the preferred culture based on the user's browser settings, you can do it at page level like I did in in my prev post but you can also set it up for all your pages in the web.config file saving you the pain of adding the attribute to all your pages throughout your application.

<system.web>
        <globalization culture="auto" uiCulture="auto" fileEncoding="utf-8" requestEncoding="utf-8" 
            responseEncoding="utf-8" />
 ....
What if I want to save the user settings after they register/access the app for the first time?

Think about the options you have to persist the preferred culture: Session: Session is not the best for many reasons, most importantly Session is not there forever, for example by default ASP.NET assumes that the user left the site after no requests have been made for 20 mins ,  your user might get up to get a cup of tea and his Session might expire, therefore when she gets back she might not have the application in her culture of choice. Also, you might not be able to access the Session object when you want to access the user's preferences. So I wouldn't use Session myself.

What about Cookies?

Cookies are more favourable if all you want is to persist a simple user' preference like the culture or their favourite colour I think, it will get hard pretty quickly if you where storing more than just their colour and horoscope preferences.

For example in Global.asax you could implement the Application_BeginRequest event and read the cookie there and setup your CurrentThread to the value stored in the cookie that you created after the user made the choice etc.

void Application_BeginRequest(Object sender, EventArgs args)
{
    HttpCookie myPreferencesCookie = (HttpCookie)Request.Cookies["Culture"];
    // check for null etc etc
    string culture = myPreferencesCookie.Value;
    System.Threading.Thread.CurrentThread.CurrentUICulture = 
         new System.Globalization.CultureInfo(culture);
    System.Threading.Thread.CurrentThread.CurrentCulture = 
         System.Globalization.CultureInfo.CreateSpecificCulture(culture);
}

But I think it will get complicated very quickly and I don't think is the best way. Much better I think is ASP.NET's Profile

The Profile objects is strongly typed and persisted, and you can even implement your own ProfileProvider!

The easiest way to create a Profile is by creating some properties in the your root web.config. Just like the Resource files, ASP.NET compiles the Profile's propertied dynamically and then you have this strongly typed profile assets.

<profile>
   <properties>
      <add name="MyFavouriteNumber" allowAnonymous="true"/>
      <group name="Preferences">
        <add name="Culture" allowAnonymous="true" />
        <add name="Color" allowAnonymous="true"  />
       </group>
   </properties>
 </profile>

Note the attributes, name is very simple, but note alllowAnonymous: This allows anonymous users to read/write properties, you have to set it explicitly because by default, this is set to false. ASP.NET cannot know which user has which profile unless the user is authenticated. So to use this anonymous feauture you have to enable anonymous indentification in your web.config too. More details in MSDN here.

All I did what this...

<anonymousIdentification  enabled="true" />

You can also set defaultvalues, type, readonly attributes to your profile entries too.

Also I defined what is called 'Profile Groups', that lets you organise the properties better and into more logical groups. Then in your app the intellisense will pick it up beautifully! ( Profile.Preferences.Culture )

So far so good, we can store it in the Profile Object but using Profile is a bit of a problem in the same league as the Sesssion Object: Can we access Profile in Global.asax BeginRequest()? Nop, we can't, the only way is to write some code to access the data store where you are persisting the user's preferences. The reason this is the case is that just like Session, Profile is not initialised until Session is ready to roll.

Getting started with a solution to the problem

So far we know we don't want to use Session, we don't want to use Cookies and that Profile is good but we can't really use it straight out of the box.

Question I had was, how can I localise pages based on the choices the user made? and what if the user wants to change back and forth this setting for say language preferences. Anyway, I started easy, lets give them a UI so they can change languages for example.

I created a MasterPage and added some big, impossible to miss flags that can be associated to languages and cultures as shown below. The plan is that by clicking on the flag, the culture associated with the flags will be the Thread.Culture that the page will be running under.

Flags

Simple code too..just a few asp:Images..

<asp:ImageButton CommandName="es-UY" OnCommand="Flag_ClickedCommand" AlternateText="Spanish"
       CssClass="Flag" ImageUrl="~/Profile/FlagsImages/uy.gif" ID="imgUruguay" runat="server"
       />
<asp:ImageButton CommandName="zh-CN" OnCommand="Flag_ClickedCommand" AlternateText="Chinese"
       CssClass="Flag" ImageUrl="~/Profile/FlagsImages/cn.gif" ID="ImageButton2" runat="server"
        />
<asp:ImageButton CommandName="en-AU" OnCommand="Flag_ClickedCommand" AlternateText="English"
       CssClass="Flag" ImageUrl="~/Profile/FlagsImages/au.gif" ID="ImageButton1" runat="server"
        />

OK, note that I have setup an EventHandler for the OnCommand event, when this fires, Flag_ClickedCommand will be called and then culture that that particular flag represents will be passed on as CommandName.

protected void Flag_ClickedCommand(Object sender, CommandEventArgs args)
{
      if (!args.CommandName.IsValidCulture()) return;        
       Profile.Preferences.Culture = args.CommandName;
       Response.Redirect(Request.Path);        
}

Note what I set the Profile.Preferences.Culture to the command name passed on from the ImageButton, but this could well be a simple button or a dropdownlist value etc. I redirect the page to itself since if the user clicks on a flag, they'll expect the changes to take change immendiately! ( I would!)

I created an extension method for strings to check if the culture passed along was valid too. Not relevant to this post but since they are very neat I'll copy it here too. :-)

public static bool IsValidCulture(this string cult)
{
     if (Constants.ChineseCulture == cult
              || Constants.EnglishCulture == cult
              || Constants.SpanishCulture == cult)
     {
         return true;
     }
     return false;    
}

Setting the Thread.CurrentThread.CurrentCulture to your preferred culture

What we now want to do is for a page to load and pick the user's preferred culture.

I noted that you can override the InitializeCulture() method solely to intialise the page's culture. This function gets called very early in the page life cycle, well before and controls are created, meaning that if you want to get some values from controls, you must get them directly from the request using Form!

protected override void InitializeCulture()
{       
    string culture = Profile.Preferences.Culture;
    if (!culture.IsEmpty())
    {
        Thread.CurrentThread.CurrentUICulture = new CultureInfo(culture);
        Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(culture);
    }
}

Unfortunately you have to override this function for every page, not very practical if you have a zillion pages. Next best is to provide a base common class and then inherit from this class so all your webforms can benefit and you don't have to implement the above in every form. This works fine!

Note that the Thread culture is only set for the current page and will not affect your "other" pages, initially I thought that the CurrentThread's culture will be set to the new values and we'll all happy. But then I also thought what If a user has a different entry point to the application, so the need for all your pages to be able to read and set the user's fav culture.

public partial class LocalisationBase : System.Web.UI.Page
{
    protected override void InitializeCulture()
    {
        ProfileCommon common = HttpContext.Current.Profile as ProfileCommon;
        string culture = common.Preferences.Culture;

        if (!culture.IsEmpty())
        {
            Thread.CurrentThread.CurrentUICulture = new CultureInfo(culture);
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(culture);
        }
    }    
}
So base class it was and all my other pages inherited from this..
public partial class Profile_Default : LocalisationBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
...........

Now lets have a look at my initial UI that I wanted to localise and see if this works.

 englishUI

Just a simple form with some dates, numbers and a calendar what we hope we can localise and so the right names and formatting appear for the right culture setting. For the form labels I used resource files just like in my previous post. I have three resource files, one for english, one for spanish and one for mandarin.

The currency is interesting. All I have is a number where I call ToString("c")

private void FillPersonalisedMoney()
{
    lblMoney.Text = (500.23D).ToString("c");
}

Now when the user click on the Chinese flag...then the Chinese version of the page will render, dates will be rendered accordingly and the calendar will be in Chinese too..

chinaUI

And in Spanish too...

spanishUI

Clicking on Employee Details will take you to a page that also inherits from LocalisationBase and where Employees from ubiquitous Northwind will be displayed with to their likely annoyance, their date of birth, nicely formatted according to the culture of choice!

EmployeesDetails

EmployeesChineseDetails

And there you have it.

In conclusion I used the native Profile ASP.NET objects, you can implement your own Provider or you can use SQLProfileProvider that ASP.NET offers, either way I honestly think that these guys offer you much more flexibility and power when you want to customize your apps according to user's preferences.

Au revoir!

Resources:

#Must read for any localisation entrepreneurs

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/localization/default.aspx

# ASP.NET 2.0 Localization (MSDN Article)

http://msdn2.microsoft.com/en-us/library/ms379546.aspx

#Profiles

http://msdn2.microsoft.com/en-us/library/at64shx3(VS.80).aspx