Skip to content
/

With SharePoint it’s easy to configure multiple zones for your SharePoint Web Application. For example you have a Publishing Web Site with two zones.
After the content is published it’ll also be available on the anonymous site and most of the URLs will be automatically translated to corresponding zone URL.
There are however some places this is not the case.

With SharePoint it’s easy to configure multiple zones for your SharePoint Web Application. For example you have a Publishing Web Site with two zones.

  1. The authenticated CMS where editors can manage content: https://cms.int
  2. The anonymous website where everybody can view the content: http://www.ext

When the editors link to sites, pages, documents and images the URL will start with https://cms.int. After the content is published it’ll also be available on the anonymous site. Now most of the URLs will be automatically translated to corresponding zone URL and start with http://www.ext.

There are however some place this is not the case. You could try to use relative URLs but even that won’t fix every scenario.

Translate the URL using code

Facing this issue I had to translate the URLs myself. But I want to write minimal code. Lucky Microsoft has done most of the work for me.

On the you will find the . This “collection” is actually an instance of the and provides the .
And this is where the magic happens.

This method has an overload where you supply a Uri and a SPUrlZone. You can provide one of the values of the or you can provide the current zone.

To get your current zone you can use the static of the . This method requires a Uri so we provide the current one using the from the same class.

To wrap it all up I give you the code:

var originalUri = new Uri("https://cms.int/pages/default.aspx");

var zone = SPAlternateUrl.Lookup(SPAlternateUrl.ContextUri).UrlZone;

var translateUri = SPFarm.Local.AlternateUrlCollections
                                    .RebaseUriWithAlternateUri(originalUri, zone));

// When accessing from the authenticated zone
// translateUri == "https://cms.int/pages/default.aspx"

// When accessing from the anonymous zone
// translateUri == http://www.ext/pages/default.aspx

“Other” URLs

If you pass a URL which is not listed as an Alternate Access Mapping the method will return the original URL.

/

In a previous post I have written about Using the people picker over a one-way trust. In this post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in clear text for everybody to read, or to retrieve from the command line history.

SharePoint 2010 introduces several cmdlets to replace the “old” STSADM commands. Microsoft has posted an overview of the STSADM to Windows PowerShell mapping. However the commands for configuring the people picker are not available.

In a previous post I have written about . In that post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in plain text for everybody to read.

With SharePoint 2010 Microsoft introduces several cmdlets to replace the “old” STSADM commands. But looking at the you will see the commands for configuring the people picker are not present.

Creating my own script

PowerShell contains the which uses a dialog to request credentials from the user and stores the password in a . This triggered me to write a PowerShell script which will work the same as STSADM -o setproperty -pn peoplepicker-searchadforests, but instead of typing the credentials on the command line it will use the credential dialog for every trusted domain.

As written in my previous post the configuration is done in two steps.

SetAppPassword

First you need to create a secure store for the credentials. This is done by executing the SetAppPassword command on every server in your SharePoint Farm with the same password.

STSADM

stsadm -o setapppassword -password <password>

PowerShell

Set-AppPassword "<password>"
function Set-AppPassword([String]$password) {
  $type = [Microsoft.SharePoint.Utilities.SPPropertyBag].Assembly
                           .GetType("Microsoft.SharePoint.Utilities.SPSecureString")
  $method = $type.GetMethod("FromString", "Static, NonPublic", $null,
                                                                 @([String]), $null)
  $secureString = $method.Invoke($null, @($password))
  [Microsoft.SharePoint.SPSecurity]::SetApplicationCredentialKey($secureString)
}

PeoplePickerSearchADForests

The second step is to register the (trusted) domains to be visible in the people picker. Remember this setting is per web application and zone.

STSADM

stsadm -o setproperty -url <url> -pn "peoplepicker-searchadforests" -pv
  "forest:<source forest>;domain:<trusted domain>,<trusted domain>\<account>,<password>"

PowerShell

Set-PeoplePickerSearchADForests "<url>" "forest:<source forest>;domain:<trusted domain>"
function Set-PeoplePickerSearchADForests([String]$webApplicationUrl,
                                         [String]$value) {
  $webApplication = Get-SPWebApplication $webApplicationUrl

  $searchActiveDirectoryDomains = $webApplication.PeoplePickerSettings
                                                      .SearchActiveDirectoryDomains
  $searchActiveDirectoryDomains.Clear()

  $currentDomain = (Get-WmiObject -Class Win32_ComputerSystem).Domain

  if (![String]::IsNullOrEmpty($value)) {
    $value.Split(@(';'), "RemoveEmptyEntries") | ForEach {
        $strArray = $_.Split(@(';'))

        $item = New-Object Microsoft.SharePoint.Administration
                                         .SPPeoplePickerSearchActiveDirectoryDomain

        [String]$value = $strArray[0]

        $index = $value.IndexOf(':');
        if ($index -ge 0) {
            $item.DomainName = $value.Substring($index + 1);
        } else {
            $item.DomainName = $value;
        }

        if ([System.Globalization.CultureInfo]::InvariantCulture.CompareInfo
                                       .IsPrefix($value, "domain:","IgnoreCase")) {
            $item.IsForest = $false;
        } else {
            $item.IsForest = $true;
        }

        if ($item.DomainName -ne $currentDomain) {
            $credentials = $host.ui.PromptForCredential("Foreign domain trust"
              + " credentials", "Please enter the trust credentials to connect to"
              + " the " + $item.DomainName + " domain", "", "")

            $item.LoginName = $credentials.UserName;
            $item.SetPassword($credentials.Password);
        }

        $searchActiveDirectoryDomains.Add($item);
    }

    $webApplication.Update()
  }
}

Using the script

I have attached the script so you can use it in any way you want. You can put the commands in you own .ps1 file, or load the script in your current session using the following syntax:

. .\<path to file>PeoplePickerSearchADForests.ps1

(yes, that’s a dot, then a space, then the path to the script)

PeoplePickerSearchADForests.zip

/

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL is vanishing. Combining a in memory with a external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL Server is vanishing. Combining an “in memory” list with an external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.
In this post I want to filter my SQL data using a list of integers I have stored in memory.

Data model

I have created a small data model as illustration. This will be used for the examples. Follow the link on the image below for a larger version. The model is part of the sample code.

Visual representation of the datamodel as described above.
Data model

IQueryable.Join

The first option you can use is to use the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            join cid in countryIds on c.Id equals cid
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (I love IntelliTrace!) you can see a temporary table is created by using the .

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
    INNER JOIN (
        SELECT
            [UnionAll1].[C1] AS [C1]
        FROM (
            SELECT 1 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1]
            UNION ALL
            SELECT 2 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]
        ) AS [UnionAll1]
        UNION ALL
        SELECT 3 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable3]
    ) AS [UnionAll2] ON [Extent2].[Countries_Id] = [UnionAll2].[C1]

What happens is that 2 values are combined in a set, this set is extended with the next value, this set is extended with another value, and so on, and so on, until all values are present.

This works nicely, but as you can imagine, the SQL Query will grow rapidly when the IEnumerable list gets larger.

When you change the following:

-var countryIds = Enumerable.Range(1, 3);
+var countryIds = Enumerable.Range(1, 50);

you will run into a SqlException:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
SQL Server has a hard-coded limit of nesting queries, it doesn’t matter which version or edition of SQL Server you use.

IEnumerable Contains

Joining tables is one way of filtering data, an alternative is to use the . This can be done by using the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            where countryIds.Contains(c.Id)
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (Still loving IntelliTrace!) you can see now the is used.

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
WHERE
    [Extent2].[Countries_Id] IN (1,2,3)

This SQL Query is much smaller and filtering against a larger set will not give you the exception.

This is how the WHERE statement looks when filtering on 50 values:

WHERE [Extent2].[Countries_Id] IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
                                   16,17,18,19,20,21,22,23,24,25,26,27,
                                   28,29,30,31,32,33,34,35,36,37,38,39,
                                   40,41,42,43,44,45,46,47,48,49,50)

Conclusion

You can use both methods to filter SQL data with a list in memory. But when choosing for the join method you should be really sure your query will not reach the maximum level of nesting.

I prefer to use Contains method.

Although I used a many-to-many relation in this example, the same applies for a one-to-many scenario.

Sample code

I’ve added a small Visual Studio 2010 project so you can see the SQL statements for yourself.

You will need SQL Server Express 2008 R2 installed on your machine to run the code.

IQueryableIEnumerableDemo.zip

Filed under C#, SQL
Last update: