LINQPAD & a great blog

I was doing some research and came across this blog, every post has value.
http://sptechpoint.wordpress.com
Check it out

LINQPaD looks like a great tool for LINQ
http://sptechpoint.wordpress.com/2009/12/30/create-linq-expressions-with-linqpad/

Data access options - SharePoint Lists or Database Tables

Problem:  I have built a composite application that uses SP 2010 lists for storing the application data.  There are 2 related lists within the application that are high transactional lists like a shopping cart (order with order items).  These 2 lists have had many fields added to them over the duration of the project and are no longer light lists. 
Hypothesis: SharePoint 2010 supports up to 50 million lists and I need to insert, edit and read often the information in these 2 related lists.  The lists are getting rather complex and transactional support is not available vie LINQ to SharePoint.  I need a full audit history which is pretty useful in SharePoint.  The volume and complexity of this transactional data is to heavy for SP especially as the lists grow.  I have to use SQL Server to store the data directly and link the appropriate SP lists using BCS and external content types.  My preferred methods of accessing data are the Entity Framework v4 or Linq to SQL.  As my data is pretty small, I don't need a multi tier system and we are using LINQ to SharePoint. 
Resolution: Create the SP lists in SQL, use LINQ to SQL to add and work with the SQL data.  I use tomb tables in SQL Server to keep a full audit history.  There are now 2 areas of storage: SP lists and directly to SQL tables.  Not ideal but worth that change.

Pros of using SharePoint List vs Database tables

SharePoint Lists	Database Tables
SharePoint Security and Permissions	Transactional
Workflow	Performance & higher transaction volumes
Visioning	Distinct queries and aggregations
Office intregration	Greater Flexibility
Native / Easy setup	Easier for existing .NET developers
SP generates UI

Update 18 Oct 2010 - Storing data for SharePoint 2010 comparison

SPMetal is throwing a ThreadAbortException

Problem: I am inserting new items into a list using Linq-to-sharepoint (SPMetal), my code throws a ThreadAbortException exception. 

Initial Hypothesis: When I run the code as a Site Collection Owner the new list item is inserted.  So this is a permissions issues.  I increase the permissions on the user that can't insert the list item.  It works so this is clearly a permissions issue.  After the user tries to perform the insert list item, they are directed to the "Error: Access Denied" message on the web page.  Clearly the error message is misleading however, SharePoint seems to understand the ThreadAbortException is a permissions issue.  The issue is the user does not have sufficient permissions to perform the action.  By elevating the user permissions the error goes away.  All well except the error logging is throwing an odd error, after some digging this issue has been blogged before however not for LINQ to SharePoint.

Resolution: Assign the correct permissions to the user performing an action.

Problems with SPMetal in SharePoint 2010

Overview: I have been doing a fair amount of LINQ to SharePoint lately and I have found a couple of issues, my vitriolic rants are:

1. SP metal can run against external content type (BCS) - external content types don't get created by SPMetal;
2. Hidden fields are not available to the SPMetal created proxy i.e. createdby, modified. Parameters.xml can be changed to display these hidden fields;
3. Only SharePoint Foundation field types are generated.  Column types are not picked up by SPMetal include the "Managed Metadata columns", "Publishing Html" or "Publishing Image". Additionally any custom created columns are not included by SPMetal;
4. Anonymous LINQ needs a work around.  Update 27/11/2010, August Cumulative Update (CU) for SharePoint 2010 apparently fixes the anaonymous LINQ to SharePoint issue. Ensure you get the latest CU due to the re-release issues.
5. Update: 08 Oct 2010 - List attachments are not picked up by SPMetal.  You will need to use the Server side object model or extend SPMetal using a partial class.
6. Update: 14 Oct 2010 - Multiselect columns are not update-able with multiple values using LINQ to SharePoint.  You can update with 1 value only.
7. Update 18 Oct 2010 - SPMetal does not like spaces in the url to the site that it generates off. Error the web at 'http://demo.dev/sites/my site' could not be found.
8. Update 27 Nov 2010 - Using Linq to SharePoint across site collections.  Scope is to the current site collection.

LINQ to SharePoint Posts on this blog

• Dynamic Linq
• Inefficient Linq Queries
• SPMetal issues

Extending SPMetal for field columns no available to SPMetal by AC (Update) or AC
CAML query for retrieving Publishing HTML and Publishing image columns
Configuring SPMetal default generated code

To see the CAML generated by SPMetal:
StringBuilder sb = new StringBuilder();

System.IO.TextWriter tw = new System.IO.StringWriter(sb);
updatedataContext.Log = tw;
// CAML Query here i.e. var x from customers select customers;
string camlOut = sb.ToString();  // CAML generated.

Update: 09/10/2010 Extend SPMetal to retrieve list attachments

Dynamic LINQ to SharePoint 2010

Problem: I have 2 connected Web Parts, the provider provide multiple values for lookup columns. I started with LINQ to SharePoint (SPMetal) but could not build up the query dynamically.
Hypothesis: My initial reaction was to use a dynamic CAML query as done in MOSS using U2U to work out my query. The issue is that the CAML is not safe (as we don't get validation until run-time) but at least I can dynamically build up my query.

Using Dynamic link with LINQ to SharePoint to can achieve the required result. The code is safe as least for run-time (my logic is still dodgy). Only issue is with the performance and the results if the list if large. Using a very broad LINQ-to-Sharepoint query that is converted into a CAML query anyway I get a large result set. I then filter using dynamic LINQ. Pretty heavy filtering and inefficient querying. Throttling concern: If I returned more than 5,000 items (default list throttling limit for SP2010 lists) I now loose results that should be queried (SharePoint would trim my SPMetal query and then Dynamic LINQ would work on the max of 5,000 items). Sure you can turn throttling off as a farm admin but not a great idea. You could programatically override the throttling (SPQueryThrottleOption.Override) using the server OM but this doesn't help for your SPMetal query.
In this case, my best option is to use a dynamic CAML query and get the exact set of data I am looking for.

Tip: Only get the fields you are using in CAML queries.
More Info:
Building CAML queries
Dynamic LINQ - Scott Guthrie

LINQ to SharePoint Posts

• Dynamic Linq
• Inefficient Linq Queries
• SPMetal issues

Inefficient Queries - SPMetal join issue

Problem: LINQ to SharePoint 2010 (SPMetal) is not querying 2 SharePoint lists, when using SPMetal on a visual web part. I am trying to JOIN 2 lists inside a web part. The Application error message is "The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.".



Hypothesis: At the SharePoint conference in Vegas Oct 2009 I remember in 1 of the sessions that inefficient LINQ queries are blocked. This was a little vague but a good start noting the stack trace message "InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.]
Microsoft.SharePoint.Linq.Rules.QueryEfficiencyProcessor.Process(Expression e)" .
Exception Details: System.InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.
Using LINQ I can query each of the lists.  Each list has a field of the same type that I am trying to join on. I was totally confused but realised it must be an issue with the efficiency/validity of the query. A simpler query worked so it looks like the CAML is not being correctly formed. I extracted the CAML using the DataContext Log method.
My choices are:

1. To get and fix the CAML query and then run a CAML query manually in my code;
2. Use LINQ to objects; or
3. Perform 2 queries and link the data manually.

Resolution:
Reading a post on MSDN on 2 stage queries gave me a quick fix. By adding the ToList() method, the query works.
var empQuery = from bug in Bugs.ToList()
join emp in Employees on bug.AssignedTo equals emp.Title
select new
{ emp.Title,
Project = bug.Project
};

Explanation: ToList() method forces immediate query evaluation and returns the generic that contains the query result.  As described in the MSDN article above 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.

Tip: Turn off Object Change Tracking if you are only reading data.

LINQ to SharePoint Posts on this Blog

• Dynamic Linq
• Inefficient Linq Queries
• SPMetal issues

Links:
Linq to SharePoint 2010 examples
Tip: CAML query for optimised speed settings, only bring back columns you will use querySearch.ViewFieldsOnly = true;
querySearch.IncludeMandatoryColumns = false;
Display the CAML that LINQ to SharePoint is running

SPMetal not generating correctly on lists with only a Title column

Problem: I have 7 custom lists in my SharePoint 2010 site. I run SPMetal against these lists and only 5 lists as available in LINQ.

Initial Hypothesis: SPMetal is broken followed by alot of frustration which eventually I worked out that a custom list must have additional columns to Show up in SPMetal.

Resolution: Create an optional column if you only have the "Title" column in a list that you wish to use Linq to SharePoint on.

Application Architecture in SharePoint 2010

Problem: I have a client that needs a composite built using 2010. The application is used to manage digital assets for suppliers. Key questions are where to store data and how to access the data.
Hypothesis: Where to store data, previously in MOSS I would of used SQL Server and accessed it via the BDC or custom web parts. In SP 2010 lists have been vastly improved and are now a good option for storing data. The new External Content Type can be used on SQL but goes beyond my requirement.
The key improvements to SharePoint lists are:

1. Lookup lists are improved and easier to implement;
2. Query joins and LINQ improve retrieving data;
3. LINQ gives you strongly typed lists which using the old "Server Object Model" were weakly typed;
4. LINQ querries are converted to CAML querries and therefore more performant than using the Server object model method and alot cleaner than using CAML to query data;
5. Formula based validation on fields;
6. Store level enforcement (MOSS enforce requirements such as null values only at the UI level);
7. Improved referential integrity between lookup lists.

My choice was between using SQL Server to store the application data and SharePoint lists. SQL tables have the following key advanatages of they are more performant, and are transactionable. The easy of use and adavantages meant that SharePoint lists are the prefered option for storage.
Application for the suppliers and users also has several options however SharePoint provides most of the admin screens. The actual app can be built using application pages or web parts or the method i prefer, the client object model.

Accessing SharePoint Data options to consider are: Client Object Model (Weakly typed), however REST API's are Strongly typed for SP lists only. New in SP2010
Server Object Model (OM) is weakly type lists but offers the most flexibility. The Server OM existed on MOSS but is improved in SP2010. LINQ gives strongly type SP lists that can be read-write (new in SP2010)
Resolution:
Used SP lists to store the digital assets, the ordering data is also stored in SharePoint lists. For speed of development in the prototype I built using application pages using web parts that accessed the lists using LINQ to SharePoint. The final application will be built using the Client OM.

Linq to SharePoint overview

Problem: Access data in SharePoint Lists.
Hypothesis:
1.> LINQ for SharePoint is a data access mechanism that allows developers to create SQL like syntax against a data sources. Linq improve performance by allowing the back end data source to be queried using CAML to solve the query. SharePoint 2010 fully supports LINQ for lists so that developers can query SharePoint easily and quickly.
2.> LINQ for SharePoint is also know as SPMetal.
3.> The SPMetal utility generates C# or vb.net code to allow you access to existing SharePoint lists.
4.> You can automate you VS environment using the prebuild cmd prompt to regenerate your Linq data access class for builds.
Steps to work with Linq for SharePoint:
1.> Using the stsadm cmd, create the API to your existing SharePoint App

2.> Import the created Data.cs file into the Visual Studio solution. Fix the namespaces on the file.
3.> Begin Using LINQ to SharePoint.
DataDataContext dc = new DataDataContext(http://intra);
// TODO! - retrieve from the web.config
EntityList Assets;
Assets = dc.GetList("My Assets");
var assetQuery = from asset in Assets
where asset.Id == Assetid
select new{
asset.Title,
asset.Description
};
// Bind to a Grid view
More Info:
Getting started with LINQ for SharePoint 2010
Channel 9