Posts Tagged ‘LINQ’

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

One of he culprits of working with the LINQ to SharePoint contexts that are being auto-generated for us is that it actually doesn’t necessarily render all the fields or properties you require. For example the built-in fields "Created By" or the "Attachments" property of a list item. Oftentimes we need these fields in our queries, whether they be CAML queries or LINQ queries. With LINQ to SharePoint you have the ability to extend an Entity that you’ve generated, and allow additional code to be executed for those entities. In this article I’ll talk briefly about how you can create and manage an extension to your LINQ context to allow additional fields in your queries.

One of the powerful advantages of doing this is that you post-deployment can extend your queries. Lets for example say that the list in your deployment gets some additional fields in the future, then perhaps you’ll need to make sure that your LINQ context can cope with querying those fields as well – this can easily be done by extending an entity context as I’ll talk about below.

In this article I’ll give you a sample of how to extend the LINQ entity to fit our custom needs, including mapping the "Created By" and "Modified By" fields and some additional logical customizations.

Generate your context

First and foremost we’ll need to generate our context. This can be done using SPMetal (Which you can read more about in this article: Getting Started with LINQ to SharePoint), or for example the extensions in CKS:Dev. I’m going to generate a quick and easy sample for this by utilizing the CKS:Dev tools and then build my samples on top of that.

If you haven’t installed the CKS:Dev tools yet, go right ahead and do that – then get back to this step!

1) Generate a LINQ context by using the CKS:Dev plugin, which will give you this additional context menu in the SharePoint server browser:

image

This should auto-generate a class file for you, containing all the entities for the selected site:

image

The file contains all the LINQ to SharePoint data context information that a normal SPMetal.exe command would generate, since this tool in essense is using SPMetal.exe:

image

As you can see, we’ve easily created the LINQ data context that we need, but unfortunately it’s missing some vital parts for our application – namely some of the built-in fields that you would normally want to work with. In my case, I was lacking the "Created By" and "Modified By" fields and I’d have to find a way to extend the LINQ context to cope with this so my queries can be easily constructed even for those fields.

Extending an entity in your generated LINQ context

In order for us to be able to extend the LINQ context, Microsoft provides us with an interface called ICustomMapping.

We’ll start by inheriting a new class from this interface, and name the class along the lines of the entity we want to extend.

// ----------------------------------------------------------------------- 
// <copyright file="AnnouncementExtension.cs" company="TOZIT AB"> 
// Awesomeness. 
// </copyright> 
// -----------------------------------------------------------------------

namespace TOZIT.Samples.ExtendingLinq 
{ 
    using Microsoft.SharePoint.Linq;

    /// <summary> 
    /// A LINQ extension to the Announcement-entity 
    /// </summary> 
    public partial class Announcement : ICustomMapping 
    { 
        public void MapFrom(object listItem) 
        { 
            throw new System.NotImplementedException(); 
        }

        public void MapTo(object listItem) 
        { 
            throw new System.NotImplementedException(); 
        }

        public void Resolve(RefreshMode mode, object originalListItem, object databaseListItem) 
        { 
            throw new System.NotImplementedException(); 
        } 
    } 
} 

Make sure you follow these rules:

  • Class name should be the same name as the entity you’re extending (partial class)

Let’s go ahead and add the necessary code to extend our entity. I’ll throw in some simple sample code here on how to map the fields we want, and to add additional logic to our new extension.

// -----------------------------------------------------------------------
// <copyright file="AnnouncementExtension.cs" company="TOZIT AB">
// Awesomeness.
// </copyright>
// -----------------------------------------------------------------------

namespace TOZIT.Samples.ExtendingLinq
{
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.Linq;

    /// <summary>
    /// A LINQ extension to the Announcement-entity
    /// </summary>
    public partial class Announcement : ICustomMapping
    {
        #region Properties

        /// <summary>
        /// Gets or sets the Created Byproperty
        /// </summary>
        public string CreatedBy { get; set; }

        /// <summary>
        /// Gets or sets the Created By Login Name property
        /// Returns a reader-friendly version of the user's loginname
        /// </summary>
        public string CreatedByLoginName { get; internal set; }
        /// <summary>
        /// Gets or sets the Modified By property
        /// </summary>
        public string ModifiedBy { get; set; }

        /// <summary>
        /// Gets or sets the Modified By Login Name property
        /// Returns a reader-friendly version of the user's login name
        /// </summary>
        public string ModifiedByLoginName { get; internal set; }

        #endregion

        #region Methods

        /// <summary>
        /// Assigns a field (column) to a property so that LINQ to SharePoint can read data 
        /// from the field in the content database to the property that represents it.
        /// </summary>
        /// <param name="listItem"></param>
        [CustomMapping(Columns = new[] { "Editor", "Author" })] // Needs to be the InternalName of fields..
        public void MapFrom(object listItem)
        {
            var lItem = listItem as SPListItem;

            if (lItem != null)
            {
                // === MAP THE AUTHOR-FIELD ===

                // Map the Created By field to the Author (Created By) field
                CreatedBy = lItem["Author"].ToString();

                // Map the CreatedByLoginName field to the Author's actual LoginName
                SPField authorField = lItem.Fields.GetFieldByInternalName("Author");
                var authorFieldValue = authorField.GetFieldValue(lItem["Author"].ToString()) as SPFieldUserValue;
                if (authorFieldValue != null)
                {
                    CreatedByLoginName = authorFieldValue.User.LoginName;
                }

                // === MAP THE EDITOR-FIELD ===
                // Map the Modified By field to the Editor (Modified By) field
                ModifiedBy = lItem["Editor"].ToString();

                // Map the ModifiedByLoginName field to the Editor's actual LoginName
                SPField editorField = lItem.Fields.GetFieldByInternalName("Editor");
                var editorFieldValue = editorField.GetFieldValue(lItem["Editor"].ToString()) as SPFieldUserValue;
                if (editorFieldValue != null)
                {
                    ModifiedByLoginName = editorFieldValue.User.LoginName;
                }
            }
        }

        /// <summary>
        /// Assigns a property to a field (column) so that LINQ to SharePoint can save 
        /// the value of the property to the field in the content database.
        /// </summary>
        /// <param name="listItem">List Item</param>
        public void MapTo(object listItem)
        {
            var lItem = listItem as SPListItem;
            if (lItem != null)
            {
                lItem["Author"] = CreatedBy;
                lItem["Editor"] = ModifiedBy;
            }
        }

        /// <summary>
        /// Resolves discrepancies in the values of one or more fields in a list item with respect to its current client value, 
        /// its current value in the database, 
        /// and its value when originally retrieved from the database
        /// 
        /// Read more: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.linq.icustommapping.resolve.aspx
        /// </summary>
        /// <param name="mode">Refresh Mode</param>
        /// <param name="originalListItem">Original list item</param>
        /// <param name="databaseObject">Database object</param>
        public void Resolve(RefreshMode mode, object originalListItem, object databaseObject)
        {
            var origListItem = (SPListItem)originalListItem;
            var dbListItem = (SPListItem)databaseObject;

            var originalCreatedByValue =    (string)origListItem["Author"];
            var dbCreatedByValue =          (string)dbListItem["Author"];

            var originalModifiedByValue =   (string)origListItem["Editor"];
            var dbModifiedByValue =         (string)dbListItem["Editor"];

            if (mode == RefreshMode.KeepCurrentValues)
            {
                // Save the Current values
                dbListItem["Author"] = CreatedBy;
                dbListItem["Editor"] = ModifiedBy;
            }
            else if (mode == RefreshMode.KeepChanges)
            {
                // Keep the changes being made
                if (CreatedBy != originalCreatedByValue)
                    dbListItem["Author"] = CreatedBy;
                else if (CreatedBy == originalCreatedByValue && CreatedBy != dbCreatedByValue)
                    CreatedBy = dbCreatedByValue;

                if (ModifiedBy != originalModifiedByValue)
                    dbListItem["Editor"] = ModifiedBy;
                else if (ModifiedBy == originalModifiedByValue && ModifiedBy != dbModifiedByValue)
                    ModifiedBy = dbModifiedByValue;
            }
            else if (mode == RefreshMode.OverwriteCurrentValues)
            {
                // Save the Database values
                CreatedBy = dbCreatedByValue;
                ModifiedBy = dbModifiedByValue;
            }
            
        }

        #endregion
    }
}

As you can see in my sample above, I not only mapped the two fields I need but I also slightly extended the entity with additional properties for retrieving a clean LoginName from the user-objects. Obviously this can be done using a normal .NET 3.5 extension method on the SPUser object, but here I implemented it in the context to show how it can be easily extended to fit our needs.

Writing a query with our extended data context

So if we’ve followed the very simple steps of creating a new extension for one of our entities, we can now easily access these details from the query we’re writing:

image

using System.ComponentModel; 
using System.Web.UI.WebControls.WebParts; 
using Microsoft.SharePoint;

namespace TOZIT.Samples.ExtendingLinq.LinqWebPart 
{ 
    using System.Linq; 
    using System.Web.UI.WebControls;

    [ToolboxItemAttribute(false)] 
    public class LinqWebPart : WebPart 
    { 
        protected override void CreateChildControls() 
        { 
            using (var ctx = new SocialflowDataContext(SPContext.Current.Web.Url)) 
            { 
                // Fetches the items where the current user is the creator 
                var myItems = from item in ctx.Announcements 
                                  where item.CreatedByLoginName == SPContext.Current.Web.CurrentUser.LoginName 
                                  select item;

                foreach (var item in myItems) 
                { 
                    Controls.Add(new Literal { Text = item.Title + " : " + item.CreatedByLoginName + " (you) created this item<br/>" }); 
                } 
            } 
        } 
    } 
} 

The list contains a few items (all created by the current user, which is the system account – please use your imagination… :-)  )

image

And the results:

image

Summary

All in all, this gives us the flexibility to customize the way we do our queries in SharePoint using LINQ. I’ve gotten the question about extending LINQ to SharePoint quite a few times over the past years, so I thought it’d be time to reflect those thoughts in this post. I hope you enjoy it and can start utilizing the extension of your LINQ queries!

Enjoy!

SP 2010: LINQ to SharePoint – What CAML lies behind my query?

February 19th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

The two following questions are quite popular in my SharePoint 2010 developer training classes, so I ought to answer them right here.

  1. "What does LINQ to SharePoint really do?"
  2. "Can I see the CAML query generated by the LINQ to SharePoint query?"

The answer is simple: Yes, you can see the results of your generated LINQ query by using the Log property of your DataContext object.

What CAML lied behind my LINQ to SharePoint query?

In order to fetch the CAML query that lies behind your LINQ query, all you need is to work with the .Log object of your DataContext.

See this simple example, which simply outputs the CAML query to an XML-file for easy reading:

image

This will essentially generate the following content in the file C:MyEntitiesDataContextQuery.xml:

image

As you can see, the LINQ to SharePoint query is automatically turned into a CAML Query.

Summary

Yep, all you need is the .Log property to fetch the CAML query from your LINQ statement. In my sample I’m outputting it to a file called C:MyEntitiesDataContextQuery.xml.

You could of course output it in any other way you want – not just a physical file on the file system. The Log property is of the type TextWriter.

Enjoy!

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

In SharePoint 2010 you now have the ability to use LINQ syntax to fetch items from your lists instead of using the "traditional" approach of CAML queries. (Including SPSiteDataQuery and SPQuery objects)

In this article I will give you a brief introduction to how you can get started using LINQ queries in SharePoint, also known as LINQ to SharePoint.

Basics of LINQ?

As a prerequisite to this article, I’m going to imply that you know what LINQ is and how to write basic LINQ queries in any .NET application already. I’m not going to dive into the details about LINQ or the syntax itself here – please see MSDN for that!

LINQ to SharePoint!

In order to work with LINQ in SharePoint 2010, we need use a tool called SPMetal.exe which resides in the 14bin folder. This tool is used to generate some entity classes which Visual Studio 2010 can use to get IntelliSense, and allows for LINQ-based queries to be performed on your lists.

Noteworthy:

  • LINQ to SharePoint queries are translated to proper CAML queries
  • CAML queries are in turn later translated to SQL queries

SPMetal.exe

Using the tool called SPMetal, we generate our entity-classes that are needed to perform these object oriented queries toward our SharePoint server.

These are the required steps to get hooked up:

  1. Launch a cmd-window and navigate to C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14bin
    image
  2. Run the following command to utilize the SPMetal.exe tool with the following syntax:
    1. SPMetal.exe /web:http://yoursite /code:C:YourEntityFile.cs
    2. Example:
      image
  3. Now navigate to C: (or wherever you chose to output your file) and make sure the file has been generated:
    image
  4. Open up the file and take a look at the content that SPMetal now have provided us with:
    image
    Note that the class name is now MyEntitiesDataContext. It’s based on the name you specify as your code file in the SPMetal.exe command line tool.

    If you were to use /code:C:Awesome.cs instead, it would generate a class called AwesomeDataContext.

With that done – all we need to do is import it to one of our projects and use it!

Visual Studio 2010 – Let’s create a sample Web Part that utilizes LINQ to SharePoint

In this sample I will create a simple Web Part that will use LINQ to SharePoint syntax to fetch some information from the Announcements list. A basic sample I use in my training classes as well, and should be fairly easy to grasp!

  1. Create a new project (I’m going to create a new Visual Web Part project)
  2. Import your DataContext-file by choosing your Project -> Add -> Existing Item:
  3. Specify your file (mine is called MyEntities.cs):
  4. Make sure it’s properly placed in your project structure – then we’re good to go:
    image

Alright – that’s easy enough. Thus far we have created an entity file using SPMetal.exe and now we have successfully imported it into our project.

Add proper references

Now in order to use LINQ to SharePoint, you also need to reference the Microsoft.SharePoint.Linq assembly. Point to references, right-click and choose "Add Reference" and select the Microsoft.SharePoint.Linq.dll file:
image

In your code, reference the assemblies:
image

Ready to code?

What you should’ve done up until now is this:

  1. Generate your entities using the SPMetal.exe tool
  2. Reference the newly created file from your SharePoint project
  3. Make sure you’re using the proper references for System.Linq and Microsoft.SharePoint.Linq
  4. Be ready to code :-)

Code!

In my example I will have a Visual Web Part that will use LINQ to SharePoint to fetch all Announcements from my Announcement-list and work with those results. If you want to see the entire project, look at the bottom of this article where you can download it.

image

IntelliSense!

While you code your queries using LINQ to SharePoint, you will now have access to IntelliSense, which you did not have with CAML queries:
image

I’m going to leave it at that – very (very) easy to get started with LINQ to SharePoint, and all you really need to know is to start using the SPMetal tool to generate your entity classes and hook’em up with Visual Studio to start coding.

Summary & Download

As you can see, there’s not a lot of things you need to do in order to work with LINQ in your SharePoint applications with your SharePoint-data.

I’ve been pinged plenty of times on how you can get started with this, and there you have it. More in-depth articles to come later – this is just to get your wagon rolling!

To download the sample project, click here: [Download]

Enjoy!

Author: Tobias Zimmergren
Blog: http://www.zimmergren.net

Introduction

In my previous article title "How to: Get up and running with .NET 3.5 in your SharePoint environment" I talked about how you can manually set up your SharePoint environment for use with Microsoft .NET Framework 3.5.

In this article I will talk about how you can incorporade some of the technologies used in .NET 3.5 to query a SharePoint list. More precisely, I will talk about how you easily can use LINQ from the .NET 3.5 framework to get started with .NET 3.5 in SharePoint.

A more in-depth article might be posted later, but this one is simply providing simple code instructions to what a Web Part that utilizes LINQ can look like!

Prerequisites

In order to follow along with this walkthrough, you should have the following bulletpoints checked:

Creating a custom Web Part which utilizes .NET 3.5 in SharePoint

The code below will give you the heads up on how to fetch the SPListItem objects from an SPList object and sort them alphabetically. You can of course use the ‘where’-clause with LINQ aswell to filter out which objects (SPListItem objects) to fetch from the SPList.

Example task list to fetch the data from:
image

Using the following code, I’ve used LINQ to retreive all the list items and sort them ascending by title!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace Zimmergren.net35.LINQWebPartSample
{
    public class SPLINQ : System.Web.UI.WebControls.WebParts.WebPart
    {
        protected override void Render(System.Web.UI.HtmlTextWriter writer)
        {
            base.Render(writer);
            SPList taskList = SPContext.Current.Web.Lists["Tasks"]; 

            // Get items, order by title alphabetically and assign to taskListItems
            var taskListItems = from SPListItem tItem in taskList.Items
                                orderby tItem.Title
                                ascending select tItem;
 

            foreach (SPListItem taskItem in taskListItems)
                writer.WriteLine(taskItem.Title + "<br/>n");
        }
    }
}

The bold text in the above code block is the LINQ statement to fetch all items in the SPList and order them ascending by Title. You can of course make much more complex queries using LINQ in order to fetch other objects based on different criteria.

This will produce a simple output like this, sorting the items alphabetically:
image

Summary and Download

This was a very (very) basic and simple example of how you can use any .NET 3.5 technology to get started with some new cool stuff.

I utilized a basic LINQ expression in this article which of course can be heavily modified if you want to retreive other things from your list(s). Perhaps a future article will take on some more advanced LINQ expressions in conjunction with SharePoint?! :-)

Anyway, you can download the sample project from here: Zimmergren.net35.LINQWebPart.zip

Thanks for tuning in and please leave a comment

As always, there’s plenty of readers but few people showing their appreciation through the comments – please leave a comment :)

Hope it helps :-)

LINQ to SharePoint

March 28th, 2008 by Tobias Zimmergren

For those of you who’s new to LINQ (Language Integrated Query), here’s a brief description:
 
"General-purpose query facilities added to the .NET Framework apply to all sources of information, not just relational or XML data. This facility is called .NET Language-Integrated Query (LINQ). "
 
I was just about to start up a new project where I wanted to make LINQ-like queries to SharePoint, but when I did some research I found out that there’s a CodePlex project called LINQ to SharePoint that does about the same as I was about to start doing.
 
If you’re interested in LINQ and SharePoint, and perhaps an integration between the both – check it out: http://www.codeplex.com/LINQtoSharePoint
 
Hopefully there’ll be some further development on the project even though it’s been quiet for a while…
 
Good weekend!