SharePoint Dragons

Nikander & Margriet on SharePoint

Convert SPMetal Linq query to CAML

As a follow up to article , based on a reader’s question. This is a slightly updated version of the code that writes the underlying CAML to the console output window, like so:

astro.Log = Console.Out;

By explicitly calling it like so you’re exporting the CAML to a text file:

myconsole.exe > caml.txt

The complete code looks like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
    class Program
        static void Main(string[] args)
            // Open entities context generated by spmetal
            // if a sharepoint context is available, use new AstroDataContext(SPContext.Current.Web.Url) instead.
            using (var astro = new AstroDataContext(“http://astro”))
                var timeItems = (from item in astro.MyMetalList
                                                                        where item.GetType() == typeof(Timecard)
                                                                        select (Timecard) item);

                // Call it like this: myconsole.exe > caml.txt
                astro.Log = Console.Out;               

                // Enter specific user name in lambda expression below, for example using: SPContext.Current.Web.CurrentUser.LoginName
                int occurrences = timeItems.Where(item => item.UserName.ToLower() == “loisandclark\\administrator”).Count();
                if (occurrences == 0)
                    Console.WriteLine(“it’s ok to add a request”);
                else if (occurrences == 1)
                    Console.WriteLine(“a request has already been submitted”);
                    Console.WriteLine(“There are duplicate requests. This is an error, contact the administrator”);

                foreach (var item in timeItems)


The resulting CAML looks like this:

<View><Query><Where><BeginsWith><FieldRef Name=”ContentTypeId” /><Value Type=”ContentTypeId”>0x01</Value></BeginsWith></Where></Query><ViewFields><FieldRef Name=”ID” /><FieldRef Name=”owshiddenversion” /><FieldRef Name=”FileDirRef” /><FieldRef Name=”Title” /><FieldRef Name=”ContentTypeId” /><FieldRef Name=”CustomerName_x003a__x0020_Compan” /><FieldRef Name=”CustomerName_x003a__x0020_FirstN” /><FieldRef Name=”CustomerName_x003a__x0020_LastNa” /><FieldRef Name=”MyCustWF” /><FieldRef Name=”URL” /><FieldRef Name=”Comments” /><FieldRef Name=”URLwMenu” /><FieldRef Name=”URLNoMenu” /><FieldRef Name=”Date” /><FieldRef Name=”DayOfWeek” /><FieldRef Name=”Start” /><FieldRef Name=”End” /><FieldRef Name=”In” /><FieldRef Name=”Out” /><FieldRef Name=”Break” /><FieldRef Name=”ScheduledWork” /><FieldRef Name=”Overtime” /><FieldRef Name=”NightWork” /><FieldRef Name=”HolidayNightWork” /><FieldRef Name=”Late” /><FieldRef Name=”LeaveEarly” /><FieldRef Name=”Oof” /><FieldRef Name=”ShortComment” /><FieldRef Name=”Vacation” /><FieldRef Name=”NumberOfVacation” /><FieldRef Name=”UserName” /><FieldRef Name=”PercentComplete” /><FieldRef Name=”Body” /><FieldRef Name=”StartDate” /><FieldRef Name=”TaskDueDate” /><FieldRef Name=”Priority” /><FieldRef Name=”TaskStatus” /><FieldRef Name=”AssignedTo” /><FieldRef Name=”DueDate” /><FieldRef Name=”Status” /><FieldRef Name=”Predecessors” LookupId=”TRUE” /><FieldRef Name=”FileLeafRef” /><FieldRef Name=”ItemChildCount” /><FieldRef Name=”FolderChildCount” /><FieldRef Name=”DocumentSetDescription” /><FieldRef Name=”Modified_x0020_By” /><FieldRef Name=”Created_x0020_By” /><FieldRef Name=”WikiField” /><FieldRef Name=”_vti_RoutingExistingProperties” /><FieldRef Name=”PreviewOnForm” /><FieldRef Name=”FileType” /><FieldRef Name=”ImageSize” /><FieldRef Name=”ImageWidth” /><FieldRef Name=”ImageHeight” /><FieldRef Name=”ImageCreateDate” /><FieldRef Name=”SelectedFlag” /><FieldRef Name=”NameOrTitle” /><FieldRef Name=”RequiredField” /><FieldRef Name=”Keywords” /><FieldRef Name=”Thumbnail” /><FieldRef Name=”Preview” /><FieldRef Name=”AlternateThumbnailUrl” /><FieldRef Name=”Description” /><FieldRef Name=”MyBcsName_x003a__x0020_FirstName” /><FieldRef Name=”MyySingleLineOfText” /><FieldRef Name=”MyMultiLines” /><FieldRef Name=”MyNumber” /><FieldRef Name=”MyCurrency” /><FieldRef Name=”MyDatTime” /><FieldRef Name=”MyLookup_x003a__x0020_LastName” /><FieldRef Name=”MyLookup_x003a__x0020_Company” /><FieldRef Name=”MyLookup_x003a__x0020_Phone” /><FieldRef Name=”MyYesNo” /><FieldRef Name=”MyLinkOrPic” /><FieldRef Name=”MyCalc” /><FieldRef Name=”MyExtDaa_x003a__x0020_LastName” /><FieldRef Name=”MyExtDaa_x003a__x0020_Phone” /><FieldRef Name=”MyChoice” /><FieldRef Name=”MyPersonOrGroup” /><FieldRef Name=”MySText” /><FieldRef Name=”MyMText” /><FieldRef Name=”ReviewStatus” /><FieldRef Name=”MyPeople” /><FieldRef Name=”MyYesNo0″ /></ViewFields><RowLimit Paged=”TRUE”>2147483647</RowLimit></View>

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: