CAML 中的 SQL IN 等效项 [英] SQL IN equivalent in CAML

问题描述

是否有一种不错"的方式来为 SharePoint 创建 CAML 查询来执行类似操作?

Is there a "nice" way to create a CAML query for SharePoint that does something like this?

SELECT *
FROM table
WHERE Id IN (3, 12, ...)

或者我是否陷入了嵌套 <Or> 节点的噩梦?

Or am I stuck with a nightmare of nested <Or> nodes?

这是我生成 <Or> 节点的解决方案.

This was my solution to generate the <Or> nodes.

/// Simulates a SQL 'Where In' clause in CAML
/// </summary>
/// <param name="columnType">Specifies the data type for the value contained by the field.</param>
/// <returns>Nested 'Or' elements portion of CAML query</returns>
public static string CamlIn<T>(string internalFieldName, string columnType, T[] values)
{
    XDocument doc = new XDocument();
    XElement prev = null;
    int index = 0;

    while (index < values.Length)
    {
        XElement element =
            new XElement("Or",
                new XElement("Eq",
                    new XElement("FieldRef",
                    new XAttribute("Name", internalFieldName)),
                new XElement("Value",
                    new XAttribute("Type", columnType),
                    values[index++].ToString())));

        if (index == values.Length - 1)
        {
            element.AddFirst(
                new XElement("Eq",
                    new XElement("FieldRef",
                    new XAttribute("Name", internalFieldName)),
                new XElement("Value",
                    new XAttribute("Type", columnType),
                    values[index++].ToString())));
        }

        if (prev != null)
            prev.AddFirst(element);
        else
            doc.Add(element);

        prev = element;
    }

    return doc.ToString(SaveOptions.DisableFormatting);
}

用法:

int[] ids = new int[] { 1, 2, 4, 5 };
string query = string.Format("<Where>{0}</Where>", CamlIn("SomeColumn", "Number", ids));

输出:

<Where>
    <Or>
        <Or>
            <Or>
                <Eq>
                    <FieldRef Name=\"SomeColumn\" />
                    <Value Type=\"Number\">5</Value>
                </Eq>
                <Eq>
                    <FieldRef Name=\"SomeColumn\" />
                    <Value Type=\"Number\">4</Value>
                </Eq>
            </Or>
            <Eq>
                <FieldRef Name=\"SomeColumn\" />
                <Value Type=\"Number\">2</Value>
            </Eq>
        </Or>
        <Eq>
            <FieldRef Name=\"SomeColumn\" />
            <Value Type=\"Number\">1</Value>
        </Eq>
    </Or>
</Where>

还使使用查找字段的重载变得更容易

Also made this overload for working with Lookup Fields a bit easier

/// <summary>
/// Simulates a SQL 'Where In' clause in CAML
/// </summary>
/// <param name="lookupId">Specify whether to use the Lookup column's Id or Value.</param>
/// <returns>Nested 'Or' elements portion of CAML query</returns>
public static string CamlIn<T>(string internalFieldName, bool lookupId, T[] values)
{
    XDocument doc = new XDocument();
    XElement prev = null;
    int index = 0;

    while (index < values.Length)
    {
        XElement element =
            new XElement("Or",
                new XElement("Eq",
                    new XElement("FieldRef",
                        new XAttribute("Name", internalFieldName),
                        lookupId ? new XAttribute("LookupId", "TRUE") : null),
                    new XElement("Value",
                        new XAttribute("Type", "Lookup"),
                        values[index++].ToString())));

        if (index == values.Length - 1)
        {
            element.AddFirst(
                new XElement("Eq",
                    new XElement("FieldRef",
                        new XAttribute("Name", internalFieldName),
                        lookupId ? new XAttribute("LookupId", "TRUE") : null),
                    new XElement("Value",
                        new XAttribute("Type", "Lookup"),
                        values[index++].ToString())));
        }

        if (prev != null)
            prev.AddFirst(element);
        else
            doc.Add(element);

        prev = element;
    }

    if (values.Length == 1)
    {
        XElement newRoot = doc.Descendants("Eq").Single();
        doc.RemoveNodes();
        doc.Add(newRoot);
    }

    return doc.ToString(SaveOptions.DisableFormatting);
}

推荐答案

NO，你需要处理嵌套的 OR 标签；这些是支持的 CAML 查询说明

NO, you'll need to deal with nested OR tags; these are supported query instructions on CAML

也许 CAML.NET 可以帮助您完成任务.

Maybe CAML.NET can help you in your quest.

这篇关于CAML 中的 SQL IN 等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！