Showing posts with label SPMetal. Show all posts
Showing posts with label SPMetal. Show all posts

Sunday 27 March 2011

SPMetal Jbg Presentation evening

Thank-you to everyone that came to the presentation and Singular who sponsored the event.  I enjoyed doing the 2 sessions and meeting some new SharePoint folk.

Slide decks:

Session 1 - PowerPoint SP2010 Data Access (includes the introduction to LINQ to SharePoint)
Session 2 - PowerPoint presentation on LINQ to SharePoint with the demo

I'll record the slides and if i can get the presentation to a reasonable size I'll add the downloads here - more info to follow.

Code Download:
VS2010 solution - This solution contains 3 projects with the code used for this presentation.
  1. SPDemoLists - Pragmatically deploy the Customers and Order lists with a list lookup and populate the 2 lists with seed data.  Sandbox solution.
  2. SPDemoUI - C# sandbox solution that contains a visual user control (web part) that allows a customer to be selected in a drop down and then display the related orders for that customer.  Additionally there is a button to delete the selected customer. 
  3. SPDemonUnit - is an NUnit blank C# project setup ready to start adding unit tests.
SharePoint 2010 Johannesburg Knowledge Group Meeting Post
Event details: http://sp2010jbg.eventbrite.com/
Session summary's
Session 1
Presentation - Overview of data access in SharePoint. What are your options? Is SharePoint storage always the answer? What is LINQ to SharePoint? What is LINQ to SharePoint not good at? The 8 caveats to LINQ to SharePoint.
Session 2
Demonstrate LINQ to SharePoint 2010. Using Visual Studio creating a visual web part to perform CRUD operations on SharePoint lists.

Monday 29 November 2010

SPMetal Extender CodePlex project

I have release a project on codeplex, SPMetal Extender to be able to use additional field types.  Fields in lists can be extended using partial classes.  The project provides a Visual Studio plug-in vsix that generates Linq to sharePoint code for unsupport list fields such as publishing fields.  Additionally the vsix deploys a new template showing how to add 4 additionaly properties to SPMetal.  The Item template is guidance and needs to be adjusted by the developer after it is created.
SPMetal Extender VSIX extension installed
Add a new SPMetal Extended item to a SharePoint project
Generate code for addionnal list fields using Server Explorer using the VS IDE

SPMetal Extender is also published on Microsoft Visual Studio tools gallery 

Saturday 27 November 2010

Linq to SharePoint - CRUD operations

Problem: Code to perform CRUD operations against a SharePoint list using Linq to SharePoint.
Resolution:
1) Read:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = from Customers in Customer
select Customers;

Alternative approach


2) Insert:
DataContext dc = new DataContext();
CustomersItem Customer = new CustomersItem();
Customer.Title = "Title";
dc.Customers.InsertOnSubmit(Customer);
dc.SubmitChanges();

3) Update:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = (from Customers in Customer
where Customers.Id == Convert.ToInt32(txtEditCustomerId.Text)
select Customers).Single();
CustomerItems.Title = CustomerItems.Title + txtCustomerTitle.Text;
dc.SubmitChanges();

4) Delete:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = (from Customers in Customer
where Customers.Id == Convert.ToInt32(txtEditCustomerId.Text)
select Customers).Single();
dc.Customers.DeleteOnSubmit(CustomerItems);
dc.SubmitChanges();

Alternative approach 


Tip: If you are using read-only operation in your LINQ to SharePoint queries set ObjectTrackingEnabled to false to improve performance.
DataContext dc = new DataContext();
dc.ObjectTrackingEnabled = false;

Tip: Use the context on the generated proxy for performing LINQ to SharePoint queries.
More Info:
LINQ to SharePoint: CRUD operation on SharePoint 2010 list using SPLinq
LinqPad
LinqPad for SharePoint 2010

Wednesday 24 November 2010

Extending SPMetal

Problem: Using SPMetal retrieve a list and display the basic columns and the following 3 columns types:
  • Attachments;
  • Html Publishing field; and
  • Image Publishing field.
Resolution:
Extended partial class to retrieve attachments, a html publishing field and a publishing image.  The code link only does the read operation.
Extended partial class (advanced) to retrieve an attachment, a html publishing field, a publishing image and the hidden CreatedBy field.  You can use the parameters.xml when generating the SPMetal proxy to display additional hidden fields such as the CreatedBy field however, if the proxy has already been generated, it is easy to simple extend the partial class to get hidden fields.  This example also adds the code to insert and update the publishing html and the publishing image field columns.

More Info:
SPMetal only generates the proxy classes code against lists that site columns are available in SP2010 foundation.  So you also don't get Manage Metadata or any custom site columns you create.  You also don't get most of the built in columns with the exception of ID & Version using SPMetal.  However, by changing the parameters.xml file you can include them i.e. Modified, CreatedBy.  You can also use the extended partial class method to display the hidden built in site columns - I would use the parametes file and not the extended partial class coding for hidden fields(Field = Site Column).

Tuesday 12 October 2010

CKSDev tool issue - The custom tool SPMetalGenerator failed

Problem: When adding a SPMetal Definition (CKSDev) item in visual studio, the SPMetal proxy generation fails with the error message: The custom tool 'SPMetalGenerator' failed.  Object reference not set to an instance of an object.

Hypothesis: CKSDev item template is failing, next I tried SPMetal from the command line and received the "Invalid File Name" error.  I had previously fixed this issue and here is the link.

I check the issue using the browser:

Resolution: Delete the list showing the Invalid file name error.  I could not do it from the UI so I used PowerShell.

More Info:
http://sp2010uk.blogspot.com/2010/08/issues-with-site-columns-content-types.html

Monday 11 October 2010

SPMetal naming conventions - good practice

Problem: The Linq to SharePoint proxy generates properties using the display name of the column, additionally spaces are removed.  If you use a friendly short name you get duplicates.  The 2nd instance is appended a integer.  If you add a column with the internal name "Advert Title" and it's display name is "Title", SPMetal generates 2 properties namely: Title (the default column in all lists) and Title0, not ideal to differeintiate in code.

Resolution:  Make the display name more specific to avoid the scenario.  I.e. "Publication Title" would become the property "PublicationTitle".

Tip: 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

Friday 8 October 2010

Extending SPMetal - Retrieving Attachments from SharePoint Lists

Problem: LINQ to SharePoint does not support attachments by default.
Hypothesis: Traditional Server Object Model approach to retrieving a specific list item’s attachments.  Approach is shown below:
Resolution: Use a partial class as described by Andrew Connell to use Linq to SharePoint to work with attachments. You could hydrate the attachment (SPFile objects) in the parital class but I choose not to, in case someone in the development team used the extended mapped property for retrieving a large list. This uses the Server object model and the hydration process is extremely heavy.  This method allows me to know if there are attachments, how many and where I can get them.  With this information I only get the specific SPFile objects if I need to work with the object.  Outlined are the steps to extend LINQ to SharePoint to retrieve attachment associated to items in a list.

1) Extend the SPMetal generated proxy class
2) Get a specific item using the LINQ to SharePoint model and using the String array returned by the extended class, get the file objects using SharePoint’s server side object model.
Tip: attachments is a string array, not an array of SPFile objects. The attachements.UrlPrefix property can be used to hydrate the SPFile objects for the list item.

Tip: I upload attachments using the Dialog framework point to attachfile.aspx as it does all the work for me.
_layouts/attachfile.aspx?ListId={5555D16F-5559-4CE4-555E-F5B0E9DC5555}&IsDlg=1&ItemId=



Monday 20 September 2010

CKSdev codeplex SPMetal SPI improvement

Matt Smith, David Mann, Todd Bleaker, Wes Hackett, Waldek Mastykarz & Wouter van Vugt have done a great job with CKSdev and they have upgraded the codeplex project recently that includes "Improved SPMetal SPI".  This is a visual studio template placed under the SharePoint 2010 grouping that allows you to add your SPMetal code to your project easily.  Previously you had to either create the LINQ to SharePoint class using the command prompt and provide switches such as the namespace.   Then I have to manually add/over right the class file generated into my source controlled code.  The other option was to add the SPMetal build to pre-build using the SPMetal cmds and each time and you need the SPMetal proxy file being overwritten to be out of source control i.e. this wouldn't work with your source control without a workaround.

Using the SPMetal Definitions included in the latest CKSDev project code is definitely a productivity improvement.

Monday 30 August 2010

Linq to SharePoint 2010 DSL extensions

I have been building a composite application for a client that has a heavy reliance on SPMetal.  There are several issues using SPMetal and I have spent time this weekend trying to figure out how SPMetal works so I could work around our issues.  I found this project from MS LINQ to SharePoint DSL extensions.  Pretty impressive tool.

A few years back I went to Microsoft to hear about Domain Specific Languages (DSL), at the time I thought it was pretty amazing but couldn't see any benefit for me and thought it was going to be pretty niche in fields like academic research.  I was wrong and this project is definitely worth a look it you are going to use SPMetal, you may not need or want the extra ability but pretty useful.  Olivier Carpentier has written a good user guide & made a pod cast to go along with the unsupported MS project.

Friday 20 August 2010

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.

Thursday 19 August 2010

SPMetal Invalid file name error

Problem: SPMetal fails with the following error:
Error: Invalid file name.
The file name you specified could not be used. It may be the name of an existing file or directory, or you may not have permission to access the file.

Initial Hypothesis: Permissions on the directory are wrong or the SPMetal proxy code file being created is locked.  Folder permissions haven't change and the file is removed from previous runs.  So my initial hypothesis is wrong.  Previously I was working on building lists created from custom content types and deployed via a feature.  There are 2 lists based on content types.  The list definition, custom content type & site columns have been removed.  The 2 lists still appear in my UI and when I click on the list I get an application NullReferenceException error.  Makes sense as SharePoint hasn't cleared up the list instances.
                            ============
Updated Problem: I have 2 lists on my SharePoint 2010 site that I can see in the web UI and through my Visual Studio Server Explorer however, I can't get into the UI as I am getting the NullReferenceException error.  I need to remove the lists to allow SPMetal to work.

Initial Hypothesis: Use another tool to delete the list, VS solution explorer won't let me update the list.  Use SharePoint Designer, on opening SPD it won't show me any lists or libraries.  So I'm left with using an stsadm cmd or Power Shell.  As I am loving PowerGui I went for the Power Shell Solution.
Resolution: Open the SharePoint Power Shell window, delete the lists using the following code:
$url = "http://demo1"
$site=new-object Microsoft.SharePoint.SPSite($url)
$web = $site.OpenWeb()
$list = $web.Lists["Customers"]
$list.Delete()
Now SPD works, SPMetal works - Problem resolved.
Summary: I created lists from content types, I removed the content types, list definitions however I left the list instance.  This cause SPD, SPMetal to err.  Cleaning the defunct lists using Power Shell corrects the issues.
More Info:
PowerGui sharePoint CheatSheet

Thursday 5 August 2010

Retrieving Publishing Columns using a CAML Query

Overview: LINQ is easy and strongly type however, occasionally we need to get data in an optimised fashion or LINQ won't do the job i.e. inefficient queries, Publishing fields and we need to revert to a CAML query.

Code Example:

Explanation:
  1. Perform a CAML query, U2U still works on SP2010 and I use it to work out my CAML query.
  2. Add code that performs the query, optimise it (you don't need every field returned by CAML, you can look at the query results without the "ViewFieldOnly" & "IncludeMadatoryFields" setting and you will see how many fields are returned which for big queries isn't good).
  3. Using the SPListItem retrieve the field data from the publishing fields.
Andrew Connell explains how to extend SPMetal
Tobias Zimmergrin's blog has good Linq to SharePoint 2010 info, his blog on showing the CAML generated by LINQ to SharePoint queries is invaluable to work out what SPMetal is generating.
Update: 09/10/2010 Extend SPMetal to retrieve list attachments

Update 27/10/2010 - A re-hash of Tobias Zimmergrin's blog describing retrieving the CAML generated by a LINQ to sharePoint Query.

Monday 26 July 2010

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
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

Friday 2 July 2010

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

Thursday 24 June 2010

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
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

Wednesday 9 June 2010

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.

Friday 4 June 2010

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