I swear I've run into this problem before, and don't remember how I solved it.

I need to include Full Name in the select list of a dynamic list query as an Expression from Person.FirstName + " " + Person.LastName. I then need to group by this column as well.

The query emits:

([cat].[dbo].[Person].[FirstName] + @LLBLEP1) + [cat].[dbo].[Person].[LastName] AS [FullName], 

in the select list, and

([cat].[dbo].[Person].[FirstName] + @LLBLEP6) + [cat].[dbo].[Person].[LastName], 

in the group by clause. This fails because the parameters are different even though the value is the same.

This is how I create the field:

var firstNameExp = new Expression(PersonFields.FirstName, ExOp.Add, " ");
var fullNameExp = new Expression(firstNameExp, ExOp.Add, PersonFields.LastName);
new EntityField2(FULL_NAME_FIELD_NAME, fullNameExp)

I have also tried:

new EntityField2(FULL_NAME_FIELD_NAME, (PersonFields.FirstName + " " + PersonFields.LastName))

How can I get the literal (" ") to be reused as a single parameter in the query, or have the full expression be reused? Do I need to use a DBFunctionCall?

Thanks,

Phil

Using version 2.6.9.305 vs. SQL Server 2008.

How do you build the GroupByCollection? Do you use the same field in the selectList?

Walaa wrote:

How do you build the GroupByCollection? Do you use the same field in the selectList?

Yes, I use the same field variable for both the select list and the group by.

First of all could you please upgrade to the latest 2.6 release.

Walaa wrote:

First of all could you please upgrade to the latest 2.6 release.

Is there a bug in this area that was fixed with a newer release? If so, I may have to look at workarounds, as upgrading isn't an option in the short term.

I'm asking you to upgrade to a newer build of v.2.6. his should not bring breaking changes. Also I believe a lot of issues and tweaks have been solved and made since (2009 - the version you use)

Walaa wrote:

I'm asking you to upgrade to a newer build of v.2.6. his should not bring breaking changes. Also I believe a lot of issues and tweaks have been solved and made since (2009 - the version you use)

I understand, but our system has been tested against a specific build of the runtimes, and upgrading would require (by policy) approval and regression testing beyond the scope of the feature I am trying to add. We do plan on upgrading soon, but not before this feature is released.

That said, I will upgrade locally and see if it makes a difference.

I think this is by-design. LLBLGen emits a new parameter every time a constant is used. This is even in v3.x. So, to workaround this you can use DBFuncionCall specifying constants. Example:

// define a composite expression using a DBFunctionCall
var fullNameExp = new DbFunctionCall(@"{0} + ' ' + {1}", 
    new object[] {CustomerFields.CompanyName, CustomerFields.ContactName });

// build fields
var fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CompanyName, 0);
fields.DefineField(CustomerFields.CustomerId, 1, "NumberOfCustWithTheSameName", AggregateFunction.Count);
fields[0].ExpressionToApply = fullNameExp;

// define the groupBy
var grouper = new GroupByCollection();
grouper.Add(fields[0]);

// fetch
var results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, null, 0, null, true, grouper);
}

It might be that, for some cases, this won't work depending on what parameters you are passing to the 'object' collection in the DBFunctionCall. If that is the case, you can write a custom DBFunctionCall class. See this for details: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20592

Another way to solve this is to use a derived table. You get something like:

SELECT A.Field1, A.Field2, ... A.Fieldn FROM (SELECT X.FirstName + @p1 + X.LastName AS Field1, X.Field2 .... FROM Table X) A GROUP BY A.Field1

Derived tables are a bit of a pain to setup in v2.6 with the low-level API, but it's doable.

Thanks guys.

I think I solved it in the past by grouping by both first and last name, which should have no unintended side effects.

I'll check out the DBFunctionCall route, as the derived table sounds like it would be tricky.