NHibernate.StaleStateException : Unexpected row count

Situation:
Console application with NHibernate and a MySQL database. The application read several (large) files with data and process it to the database. For several reasons, all entries that are read from the files needs to be compared with existing entries in the database. New one's should be added, existing entries should be updated (if needed) and entries that does not exist anymore in the import files needs to be update (property set to inactive = true). So far so good.

Problem:
I ran several times into the NHibernate.StaleStateException with the unexpected row count message.
I changed from session.save() and session.flush() to a transaction with commit way of dealing with the objects. I also started to use Parallel.ForEach when processing the read entries for performance reasons. During testing (with smaller files) everything went well. Till I started to read files from over 15Mb (I got even files of > 60Mb). That was the moment that those NHibernate.StaleStateException's were fired.

Solution:
After searching a lot and reading a lot of flushing, saves, transactions and versions I still got not the correct answer for my problem.
However, I've found the solution. It was not really an Nhibernate issue, but a MySQL issue. I had to change a few database parameters:

• innodb_buffer_pool_size. I changed the default value of 8M to 512M. This speed up the entire process a lot. On production server(s) this value can be set higher as well (depending on the available RAM of the machine).
• innodb_log_file_size. I changed this one to 128M
• max_allowed_packet. If this value is to small, NHibernate will fire those StaleStateExceptions if you are processing such amount of data(files). So I changed it to 100M

Those parameter you can find in the my.ini file of MySQL (location of my file is at C:\ProgramData\MySQL\MySQL Server 5.7\my.ini). More info of those parameters can be found in the my.ini file.
After changing those parameters, you need to restart the MySQL service.

For me, those 3 db-parameters did the trick.

MS Access and LINQPad

Situation:

As most developers, using LINQ queries saves a lot of time and works absolutely great. For me one of the greatest tools for quick and easy querying data and testing code is LINQPad. However, sometimes you get a project where the (backend)database is MS Access (yes, it still happens, even in 2016 :) ).

Problem:

And there starts the problem. There is no MS Access driver for LINQPad, at least no free one.

Solution:

But we are lucky, LINQPad provides a way to create custom drivers. So one of my colleagues, Frank den Outer, made a driver for MS Access to enable you to connect to *.mdb and *.accdb and allows you to query on data from LINQPad on MS Access database (even with password protected ones). So the credits of this post goes to him!

See the screenshots below:






Our company, DNM, offers you this driver for free. You can download is here: MSAccessDataContextDriver.lpx. After the download, start LINQPad, and when creating a new connection, you can easily add the downloaded driver.

Happy programming!

LINQ to SharePoint: linq query filter on multivalue lookup

Situation:
I have a SharePoint Foundation 2010 environment with a document library. One of the metadata on the documents is a multivalue lookup to a category list.
I used SPMetal to create entity classes for easy usage of linq to the different lists and libraries inside the SharePoint site.
Problem:
Inside a visual web part, I was trying to execute a linq query on the document library, filtering on a given category.
Example code:

QualityDataContext dc = new QualityDataContext(SPContext.Current.Web.Url);
// some other steps made
var mySelectedCat = dc.MyCategories.OrderBy(p => p.Title).Where(p => p.id == "CategoryOfInterestID")

var docs = dc.MyDocLib.OrderBy(p => p.Title).Where(p => p.Categories.Contains(mySelectedCat));

This goes wrong because in this situation linq is messing up with the "p.Categories" en the entity "mySelectedCat"

Solution:
I have searched a lot, without getting a right, easy solution. Some mentioned to use the 'old' "SPWeb, SPList, SPQuery and so on"-way. Others where trying to believe you that the sample above is working.
Sorry folks, it's not. The problem is that querying on the multivalue lookup just works after the data has been retrieved. So I uses the ToList() method to the 'docs' part, just before filtering. For performance reasons, I first get the whole list (I just needed the whole list) and then do the filtering on the moment I needed it.
So this results in the following piece of code that I am using to fill a tree view, grouped on main category and sub category with links to documents.
Note the 'ToList()' I am using, twice just straight in the beginning of the using section. This is doing the trick.

using (QualityDataContext dc = new QualityDataContext(SPContext.Current.Web.Url))
{
 var cats = dc.MyCategories.OrderBy(p => p.Title).ToList();
 var docs = dc.MyDocuments.OrderBy(p => p.Title).ToList();

 SPTreeView tv = new SPTreeView();

 var mainCat = cats.OrderBy(p => p.Category).GroupBy(p => p.Category);
 
 // Step through the main categories
 foreach (var item in mainCat)
 {
  TreeNode catNode = new TreeNode{ Text = item.Key, Value = item.Key };
  
  var subCats = cats.OrderBy(p => p.Title).Where(p => p.Category == item.Key);

  // Step through the sub categories
  foreach (var subCat in subCats)
  {
   TreeNode subCatnode = new TreeNode { Text = subCat.Title, Value = subCat.Id.ToString() };
   var subdocs = docs.Where(p => p.Categories.Contains(subCat));

   if (subdocs.Count() > 0)
   {
    foreach (var doc in subdocs)
    {
     string docUrl = doc.Path + "/" + doc.Naam;
     subCatnode.ChildNodes.Add(new TreeNode { Text = doc.Naam, ToolTip = doc.Title, NavigateUrl = docUrl, Target = "_blank" });
    }
    subCatnode.Collapse();
    catNode.ChildNodes.Add(subCatnode);
   }
  }
  tv.Nodes.Add(catNode);
 }
 this.Controls.Add(tv);

Value does not fall within the expected range

Currently working on SharePoint and yep, running into strange problems and errors. A better name will be ShareProblems or SharePointless. However, just kidding a bit. But seriously, I run into the following situation:
Situation:
Running a webservice outside SharePoint which will receive data from a webform (on another server) in JSON format. The received data will be saved in a SharePoint list by using the client side object model. So far so good. The initial test where run by the administrator account (used for the installation and configuration of SharePoint). It worked fine. Because I have to run a workflow on 'new item created' on the SharePoint list, I needed a 'normal' account. The admin and service accounts are not allowed to start workflows. So, new AD account defined, gave the appropriate rights and voila... uh, not really.
Problem: 
Part of the code:

ListItemCreationInformation lici = new ListItemCreationInformation();
ListItem item = list.AddItem(lici);

item["Title"] = HttpUtility.UrlDecode(name);
item["Category"] = (categorie == "0") ? 15 : int.Parse(categorie);
item["Street"] = HttpUtility.UrlDecode(street);
item["Postal_x0020_Code"] = HttpUtility.UrlDecode(postalcode);

item.Update();
_clientContext.ExecuteQuery();

I got the return message: Value does not fall within the expected range.
The callstack was something like this:

at Microsoft.SharePoint.Client.ClientRequest.ProcessResponseStream(Stream responseStream) at Microsoft.SharePoint.Client.ClientRequest.ProcessResponse() at Microsoft.SharePoint.Client.ClientContext.ExecuteQuery() at DnmWebServices.MessagingService.SendRtvRequest(String name, String category, String street, String postalcode) in c:\inetpub\wwwroot\DnmWebServices\App_Code\MessagingService.cs:line 128

Line 128 in my case points to the _clientContext.ExecuteQuery.
Solution(s):
It seems there can be several causes.
1). Use of invalid field name. You need to use the internal field name! However, that was not the case in my situation.
2). Change the List View Lookup Threshold value of the web application. Default value is 8, I changed it to 20 and big surprise: it worked! Thanks to this post.
Note: My item has some lookup fields some of which some have more than 8 items.
Little conclusion: So it seems that when using (one of) the system admin accounts, this list view lookup threshold is ignored in one way. By using non-system admin accounts, it can be a show stopper.
Steps to edit this value:

• Go to Central Administration > Application Management > Manage web applications
• Select the appropriate web application (in my case the SharePoint - 80)
• In the ribbon bar, select General Settings > Resourse Throttling
• Search for the List View Lookup Threshold and change the value to 20.

The query uses unsupported elements

I am using SPMetal for creating partial classes for accessing my SharePoint lists by code.
Today I was trying to run a Linq query in LinqPad on a SharePoint list (see here how to use LinqPad on SharePoint data). See query:

var q = from w in WorkItems
  where w.ShortUserID == "rob"
  where (((DateTime)(w.WorkDate)) >= firstDay 
     && ((DateTime)(w.WorkDate)) <= firstDay.AddDays(6))
  select new {w.Title, w.WorkDate, w.Hours, sprint = w.SprintItem.Title};
q.Dump();

Well, it's just an ordinary Linq query, but as always with SharePoint related programming, nothing goes well the first time. I got the following error message:

The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet

It has something to do with the 'sprint' information I needed. The 'SprintItem' is a property (Type of SprintItem object) of the WorkItem class. Somehow this will fail.
An explanation given by Paul Beck (see his blog):

ToList() method forces immediate query evaluation and returns the generic that contains the query result. As described in the MSDN article LINQ can't convert the LINQ to SharePoint into a CAML query so by using the ToList() method, the query is broken into 2 stages. This will apply to queries that use JOINS, UNIONS, and various other LINQ operators as described in the MSDN Unsupported LINQ queries article.

In his article he relates this problem with 'join'. However, I didn't use this keyword. Nevertheless his solution worked, I needed to put 'ToList()' right behind 'WorkItems' and the problem is solved. So the code will now looks like:

var q = from w in WorkItems.ToList()
  where w.ShortUserID == "rob"
  where (((DateTime)(w.WorkDate)) >= firstDay 
      && ((DateTime)(w.WorkDate)) <= firstDay.AddDays(6))
  select new {w.Title, w.WorkDate, w.Hours, sprint = w.SprintItem.Title};
q.Dump();

LinqPad on SharePoint data

Linq to SharePoint is great! The SPMetal.exe is really easy to use (see here how to use it).
But I was struggling a bit to get the right Linq queries. So I thought, well let's use LinqPad. I ran into the following error message:

PropertyOrFieldNotInitializedException: The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

I searched for a solution, but I didn't find one. Today I figured out how to use LinqPad to query on SharePoint data, without facing these security-IIS-related issues.
On CodePlex you can find the LinqPad Data Context Driver for SharePoint created by a guy named 'theiwaz'. Creating a new connection in LinqPad, using this driver did the trick. Read the documentation how to install and setup the configuration (very easy!).

In my situation, I ran into a small problem. The documentation shows a screenshot with a sample code, using "C# Expression" a the language.
When I tried that, it failed. I got the following message:

String was not recognized as a valid Boolean

 Changing the language to "C# Statement(s)" did the trick for me. I only need to rewrite the code a little bit. But that's actually a great advantage, because it now looks like the code as I will use inside my VS2010 project.

var q = from prod in Products
    where prod.Customer.Title == "DNM"
    select  prod.Title;

q.Dump(1);

Work around ServerContext en UserProfile troubles

I struggled some time with working with the UserProfile and ServerContext inside SharePoint (MOSS 2007).
I wrote a web part to show more details of a sitecontact. For that, I needed to get details from the UserProfile. The UserProfile was obtained from the UserProfileManager.
The code (web part) was running fine on the SharePoint server itself, but it failed on a client machine.
After struggling a lot, trying different suggestions (like fake SPContext and so on) today I found the solution. You need to use the SPSecurity.RunWithElevatedPrivileges method. RunWithElevatedPrivileges runs the code in the brackets under the SharePoint System Account.
So here is the piece of code I am using:

try
{
  SPSecurity.RunWithElevatedPrivileges(delegate()
  {
    using (SPSite site = new SPSite(SPContext.Current.Site.ID))
    {
      ServerContext context = ServerContext.GetContext(site);
      HttpContext currentContext = HttpContext.Current;
      HttpContext.Current = null;

      UserProfileManager profileManager = 
        new UserProfileManager(context);

      UserProfile user = 
        profileManager.GetUserProfile("domain\\accountname");

      PropertyCollection propCollection =
        user.ProfileManager.Properties;

      if (user != null && propCollection != null)
      {
        \\ do your stuff with the 'user'
      }
      HttpContext.Current = currentContext;
    }
  });
}
catch (Exception)
{
  \\ handle the exception
}

I used this code for retrieving userprofile details. I didn't try to edit the user profile. But I assume it should work as well.

Format file size (C#)

This post is not spectacular, but might be useful for some of you. I wrote a simple function to format the file size like it is in the file explorer (21 KB, 1,23 MB etc). I needed this for showing the file size in a tooltip for a SharePoint treeview.

private string FileSizeFormat(long size)
{
 const int KB = 1024;
 const int MB = 1048576;
 const int GB = 1073741824;

 if (size < KB)
 {
  return string.Format("{0} bytes", size); 
 }
 else if (size < MB)
 {
  return string.Format("{0} KB", (size / KB)); 
 }
 else if (size < GB)
 {
  return string.Format("{0:0.00} MB", (size / MB));
 }
 else
 {
  return string.Format("{0:0.00} GB", (size / GB)); 
 }
}

Just enter the file size (which usually is a long) into this function, you get the appropriate format back.
Edit: Made the code a bit easier (thanks Frank!)