I want to create a field which is a calculated concatenation of a filtered column. Something like this in T-SQL:

DECLARE @productString NVARCHAR(max) = ''
SELECT @productString= @productString + [ProductName]+ ', '
  FROM [Northwind].[dbo].[Products]
  JOIN (SELECT DISTINCT ProductID FROM [Order Details]) o ON Products.ProductID = o.ProductID
SELECT @productString

What might be a good way of tackling this in Linq to LLBLGenPro?

Is this your real Sql query? I don't see the need of the JOIN like that (derived table). Anyway you can write derived tables in the LLBLGen API and LLBL2LINQ. Though I think the concatenation problem is better solvable client-side.

No. The real query isn't written yet and I want to avoid writing it by using a Linq statement.

Client-side is an Excel spreadsheet containing many hundreds of these concatenations for presentation.

The part of the query that I don't know how to do is

SELECT @productString= @productString + [ProductName]+ ', '

The rest of it is unimportant.

At the moment, it is achieved by the fairly cumbersome piece of code below where the field containing the concatenation is textEntities.OriginalRelatedDocuments:

...
            var textEntities = from text in metaData.QnText
                               join questions in metaData.QnBase on text.QnCode equals questions.QnCode
                               where
                                   questions.CountryCode == countryCode &&
                                   (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode) &&
                                   text.LanguageCode == languageCode
                               select
                                   new ScoreCardTextEntity
                                       {
                                           ScoreCardID = scoreCard.ScoreCardID,
                                           QnCode = text.QnCode,
                                           OriginalQn = text.Qn,
                                           OriginalQnGuideNote = text.QnGuideNote,
                                           LanguageCode = text.LanguageCode,
                                           OriginalRelatedDocuments = String.Empty
                                       };
...

foreach (var text in textEntities)
                {
                    text.OriginalRelatedDocuments = CreateItems(text.QnCode);
                    adapter.SaveEntity(text);
                }
...

        private string CreateItems(string qnCode, string languageCode)
        {
            var returnVal = String.Empty;
            var itemEntities = from items in metaData.QnItems
                               join text in metaData.ItemsText on items.ItemID equals text.ItemID
                               where items.QnCode == qnCode && text.LanguageCode == languageCode
                               select text.Item;
            foreach (var t in itemEntities)
            {
                returnVal += t + "-";
            }
            return returnVal;
        }

This code does the job, but in my pursuit for greater understanding of Linq to LLBLGenPro and Linq in general, I want to see if it can be done in a more elegant (and efficient) way.

I am wondering if the String.Empty could be replaced with something that would do the job and allow a SaveEntityCollection instead of muliple SaveEntity statements inside the loop.

(Note: The original design calls for this concatenation prior to delivery of the spreadsheet. I take your point and will approach the architect to propose a different model that conactenates in the Excel worksheet. I am still interested in better ways to code the above though.)

Your approach is fine in my opinion, it does the work. However you want to avoid to get back all items ids in the fetch. One options is to create an action procedure with a string output param and call it from your code.

Hi,

If the output isn't bigger then 8K Bytes (4k nvarchar) you can simply create an CLR function that does the concatination for you.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;
using DM.Build.Yukon.Attributes;
using System.Text;
using System.IO;


[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class fnList : IBinarySerialize
{
    private StringBuilder intermediateResult;

    public void Init()
    {
        intermediateResult = new StringBuilder();
    }

    public void Accumulate( [SqlParamFacet(MaxSize=128)] SqlString value, [SqlParamFacet(MaxSize=1)] SqlString Separator)
    {
        if (value.IsNull)
        {
            return;
        }
        intermediateResult.Append(value.Value).Append(Separator.IsNull ? ' ' : Separator.Value[0]);
    }

    public void Merge(fnList other)
    {
        intermediateResult.Append(other.intermediateResult);
    }

    [return: SqlFacet(MaxSize=-1)]
    public SqlString Terminate()
    {
        //delete the trailing comma, if any
        if ((this.intermediateResult != null) && (this.intermediateResult.Length > 0))
        {
            return new SqlString(intermediateResult.ToString(0, this.intermediateResult.Length - 1));
        }

        return SqlString.Null;
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

The code above is only for SQL Server 2008, for SQL Server 2005 you can't have the second parameter that specifies the concatination value and you must adapt the code for that.

For deployment of CLR I like following project: DeployPropAndAddIn_v2.6a.zip It creates for you a SQL script of the assembly and the functions.

then your query is simply:

select fnList(p.ProductName, ',')
   from dbo.Product p;

or for distinct values:

select fnList(distinct p.ProductName, ',')
   from dbo.Product p;

or in common

select dbo.fnList( [distinct] <Aggregate Column>
  from <table | view | derived table>
group by
  <Aggregate column>

So, next console application show how it will be done with LLBLGenPro:

using System;
using System.Linq;
using SD.LLBLGen.Pro.ORMSupportClasses;
using Test;
using Test.DatabaseSpecific;
using Test.Linq;

namespace ConsoleApplication2
{
    class Program
    {
        public class NorthwindFunctions
        {
            public static string fnCommaList(string fldName)
            {
                return string.Empty;
            }

            public static string fnDistinctCommaList(string fldName)
            {
                return string.Empty;
            }

        }

        public class NorthwindFunctionMappings : FunctionMappingStore
        {
            public NorthwindFunctionMappings()
                : base()
            {
                this.Add(new FunctionMapping(typeof(NorthwindFunctions), "fnCommaList", 1, "dbo.fnList({0}, ',')"));
                this.Add(new FunctionMapping(typeof(NorthwindFunctions), "fnDistinctCommaList", 1, "dbo.fnList(distinct {0}, ',')"));
            }
        }

        static void Main(string[] args)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                LinqMetaData metaData = new LinqMetaData(adapter, new NorthwindFunctionMappings());

                string q =
                    metaData.Products
                    .Select(o => NorthwindFunctions.fnCommaList(o.ProductName))
                    .First<string>();
                Console.WriteLine(q);
            }
        }
    }
}

Thanks very much for the detailed post. I'll study it as there are several things you do in it that I unfamiliar with.