Hi,

I'm Using SQL Server 2005, ASP.NET 3.0

LLBL Gen 2.5

I had a problem with DataType of the Newly created #TempTable of the First Column.

I'm using "+" operator when I connect 2 fields (nvarchar).

but It's converting as "BigInt" in #TempTable

CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL ,[Name][BigInt] NULL ,[AnnualLeave]Decimal NULL);

INSERT INTO #TempTable ([Name],[AnnualLeave])

SELECT DISTINCT TOP 15 [Territory].[TerritoryResourceDetail].[value] + ' (' + [Security].[User].[displayName] + ')' AS [Name] , SUM([Activity].[TerritoryActivity].[annualLeave]) AS [AnnualLeave]

Pls help me out, we are using this kind of thing for the past 18 months using LLBL.

now It's making problem

problem area is :

When I try to view the 1 page no problem coz NO TempTable try to access 2 page onwards, I have this issue.

Thanks in Advance

This sounds like a bug that was solved a year ago. Which LLBLGen runitme library version are you using?

LLBL Gen 2.5

Adapter

Please check the following thread to know how to get the LLBLGen runtime library version/build number: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

I don't think so problem with LLBL Gen

Upto the last week, we have not had this kind issue even we used the same code.

unexpectedly, we have this issue without any change with LLBL Gen for the past few months after updating the below said version.

RUN TIME version: v2.0.50727

RUN TIME version: v2.0.50727

That's not a correct version number. Please re-read the thread I mentioned carefully and come back with the correct runtime library version used.

Thanks.

LLBLGen Runtime Version : 2.5.7.1129

That's a very old build, would you please download and use the latest build of LLBLGen v.2.5

If you log in to our site with your customer id and password, then you can find the latest buld here: http://www.llblgen.com/pages/secure/ListDownloads.aspx?ProductVersion=5

This is indeed fixed some time ago. Please download the latest build for your version.

Ya, Thank you Mr.Otis.

I'll do it as you said.

but I have not faced the same problem earlier even I'm using the same type of Concatenation like s1 + ' ' +s2

I had used 3 days before, It was working with my version.

This may be resolved in the latest version as you said.

I want to clear something on this issue that why this issue raised today and why this is not making anything till the last one year. (we are using the same version for the past 1 yr).

any way, I'm happy with your support. and I'll update my dll as you guide.

Thanks Otis.

Otis wrote:

This is indeed fixed some time ago. Please download the latest build for your version.

Hi Otis,

I am from the same team where Mr.Prabhakar is working. We are using the build mentioned by him from December 2007 without having any hitch until now. From today only it got surfaced. Can you please let me know about this fix.

We have installed .Net framework 3.5 in the box where the service is hosted. Is this fix is related to this .net version.

Please provide with any links about this fix.

Thanks and Regards

Prabhu

I think it might be different than the issue mentioned earlier as fixed. Could you please post the C#/VB.NET code which produces this query (so your piece of code which results in this query), as I think I know what the problem is but unless I see your code I can't give you an advice how to fix it.

the general idea is that the field the expression is placed on is of type BigInt, but the expression itself is a string typed expression.

Otis wrote:

I think it might be different than the issue mentioned earlier as fixed. Could you please post the C#/VB.NET code which produces this query (so your piece of code which results in this query), as I think I know what the problem is but unless I see your code I can't give you an advice how to fix it.

the general idea is that the field the expression is placed on is of type BigInt, but the expression itself is a string typed expression.

Otis,

Please find the code below that is causing the problem

List<ActivityListInfo> activityListInfo = new List<ActivityListInfo>(); DataProjectorToCustomClass<ActivityListInfo> projector = new DataProjectorToCustomClass<ActivityListInfo>(activityListInfo);

            ResultsetFields fields = new ResultsetFields(13);
            fields.DefineField(ActivityHeaderFields.Id, 0, "ActivityId");
            fields.DefineField(ActivityHeaderFields.Id, 1, "StartDate");
            fields[1].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(ActivityHeaderFields.Id, 2, "EndDate");
            fields[2].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(ActivityHeaderFields.Id, 3, "ActivityStatus");
            fields[3].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN " + ActivityListInfo.ActivityStatusOptions.Event.GetHashCode() + " ELSE CASE WHEN {1} is null AND {2} is null THEN " + ActivityListInfo.ActivityStatusOptions.Planned.GetHashCode() + " ELSE " + ActivityListInfo.ActivityStatusOptions.Performed.GetHashCode() + " END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(ActivityHeaderFields.Id, 4, "ActivityDateStatus");
            fields[4].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN CASE WHEN {1} < getDate() AND {2} < getDate() THEN 1 WHEN {1} >= getDate() AND {2} <= getDate() THEN 2 WHEN {1} > getDate() AND {2} > getDate() THEN 3 ELSE 4 END ELSE CASE WHEN {3} is null AND {4} is null THEN CASE WHEN {1} < getDate() AND {2} < getDate() THEN 1 WHEN {1} >= getDate() AND {2} <= getDate() THEN 2 WHEN {1} > getDate() AND {2} > getDate() THEN 3 ELSE 4 END ELSE CASE WHEN {3} < getDate() AND {4} < getDate() THEN 1 WHEN {3} >= getDate() AND {4} <= getDate() THEN 2 WHEN {3} > getDate() AND {4} > getDate() THEN 3 ELSE 4 END END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.EndDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(TerritoryResourceDetailFields.Value, 5, "TerritoryName");
            fields.DefineField(UserFields.FirstName, 6, "UserName");
            fields[6].ExpressionToApply = new DbFunctionCall("{0} + ' ' + {1}", new object[] { UserFields.FirstName, UserFields.LastName });
            fields.DefineField(ActivityHeaderFields.StartDate, 7, "StartDateFormatted");
            fields[7].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN Convert(varchar(6), {1}, 113) + ' ' + Convert(varchar(5), {1}, 108) ELSE CASE WHEN {2} is null AND {3} is null THEN Convert(varchar(6), {1}, 113) + ' ' + Convert(varchar(5), {1}, 108) ELSE Convert(varchar(6), {2}, 113) + ' ' + Convert(varchar(5), {2}, 108) END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(ActivityHeaderFields.EndDate, 8, "EndDateFormatted");
            fields[8].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN Convert(varchar(6), {1}, 113) + ' ' + Convert(varchar(5), {1}, 108) ELSE CASE WHEN {2} is null AND {3} is null THEN Convert(varchar(6), {1}, 113) + ' ' + Convert(varchar(5), {1}, 108) ELSE Convert(varchar(6), {3}, 113) + ' ' + Convert(varchar(5), {3}, 108) END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.EndDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });
            fields.DefineField(ActivityResourceDetailFields.ShortValue, 9, "ActivityTypeName");
            fields.DefineField(PersonFields.FirstName, 10, "PersonName");
            fields[10].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'G' THEN 'Group Person' ELSE {1} + ' ' + {2} END", new object[] { ActivityGroupFields.Code, PersonFields.FirstName, PersonFields.LastName });
            fields.DefineField(LocationFields.Name, 11, "LocationName");
            fields[11].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'G' THEN 'Group Location' ELSE {1} END", new object[] { ActivityGroupFields.Code, LocationFields.Name });
            fields.DefineField(GeographyResourceDetailFields.ShortValue, 12, "SubbrickName");

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(TerritoryEntity.Relations.ActivityHeaderEntityUsingTerritoryId, JoinHint.Inner);
            bucket.Relations.Add(TerritoryResourceEntity.Relations.TerritoryEntityUsingDescriptionId, JoinHint.Inner);
            bucket.Relations.Add(TerritoryResourceDetailEntity.Relations.TerritoryResourceEntityUsingResourceId, JoinHint.Inner).CustomFilter = new PredicateExpression(TerritoryResourceDetailFields.LanguageId == objOptimaHeader.LanguageId);
            bucket.Relations.Add(UserEntity.Relations.ActivityHeaderEntityUsingUserId, JoinHint.Inner);
            bucket.Relations.Add(ActivityTypeEntity.Relations.ActivityHeaderEntityUsingActivityTypeId, JoinHint.Inner);
            bucket.Relations.Add(ActivityResourceEntity.Relations.ActivityTypeEntityUsingDescriptionId, JoinHint.Inner);
            bucket.Relations.Add(ActivityResourceDetailEntity.Relations.ActivityResourceEntityUsingResourceId, JoinHint.Inner).CustomFilter = new PredicateExpression(ActivityResourceDetailFields.LanguageId == objOptimaHeader.LanguageId);
            bucket.Relations.Add(ActivityPersonEntity.Relations.ActivityHeaderEntityUsingActivityId, JoinHint.Right);
            bucket.Relations.Add(PersonEntity.Relations.ActivityPersonEntityUsingPersonId, JoinHint.Right);
            bucket.Relations.Add(LocationEntity.Relations.ActivityPersonEntityUsingLocationId, JoinHint.Right);
            bucket.Relations.Add(SubBrickEntity.Relations.LocationEntityUsingSubBrickId, JoinHint.Right);
            bucket.Relations.Add(GeographyResourceEntity.Relations.SubBrickEntityUsingDescriptionId, JoinHint.Right);
            bucket.Relations.Add(GeographyResourceDetailEntity.Relations.GeographyResourceEntityUsingResourceId, JoinHint.Right).CustomFilter = new PredicateExpression(GeographyResourceDetailFields.LanguageId == objOptimaHeader.LanguageId);
            // Below tables are used for applying filter conditions
            bucket.Relations.Add(PeriodEntity.Relations.ActivityHeaderEntityUsingPeriodId, JoinHint.Inner);
            bucket.Relations.Add(BrickEntity.Relations.SubBrickEntityUsingBrickId, JoinHint.Right);
            bucket.Relations.Add(ActivityGroupEntity.Relations.ActivityTypeEntityUsingActivityGroupId, JoinHint.Inner);
            bucket.Relations.Add(ActivityCategoryEntity.Relations.ActivityGroupEntityUsingActivityCategoryId, JoinHint.Inner);

            FillActivityListSearchCriterias(bucket.PredicateExpression, inActivityParameter);

            List<IDataValueProjector> valueProjector = new List<IDataValueProjector>();
            valueProjector.Add(new DataValueProjector("ActivityId", 0, typeof(int)));
            valueProjector.Add(new DataValueProjector("StartDate", 1, typeof(DateTime)));
            valueProjector.Add(new DataValueProjector("EndDate", 2, typeof(DateTime)));
            valueProjector.Add(new DataValueProjector("ActivityStatus", 3, typeof(int)));
            valueProjector.Add(new DataValueProjector("ActivityDateStatus", 4, typeof(int)));
            valueProjector.Add(new DataValueProjector("TerritoryName", 5, typeof(string)));
            valueProjector.Add(new DataValueProjector("UserName", 6, typeof(string)));
            valueProjector.Add(new DataValueProjector("StartDateFormatted", 7, typeof(string)));
            valueProjector.Add(new DataValueProjector("EndDateFormatted", 8, typeof(string)));
            valueProjector.Add(new DataValueProjector("ActivityTypeName", 9, typeof(string)));
            valueProjector.Add(new DataValueProjector("PersonName", 10, typeof(string)));
            valueProjector.Add(new DataValueProjector("LocationName", 11, typeof(string)));
            valueProjector.Add(new DataValueProjector("SubbrickName", 12, typeof(string)));

            ISortExpression sort = null;
            if (inActivityParameter.Filter.ActivityStatus.HasValue && (int)inActivityParameter.Filter.ActivityStatus == ActivityListInfo.ActivityStatusOptions.Planned.GetHashCode())
                sort = new SortExpression(new SortClause(ActivityHeaderFields.StartDate, null, SortOperator.Descending));
            else if (inActivityParameter.Filter.ActivityStatus.HasValue && (int)inActivityParameter.Filter.ActivityStatus == ActivityListInfo.ActivityStatusOptions.Performed.GetHashCode())
                sort = new SortExpression(new SortClause(ActivityHeaderFields.PerformStartDate, null, SortOperator.Descending));
            else if (inActivityParameter.Filter.ActivityStatus.HasValue && (int)inActivityParameter.Filter.ActivityStatus == ActivityListInfo.ActivityStatusOptions.Event.GetHashCode())
                sort = new SortExpression(new SortClause(ActivityHeaderFields.StartDate, null, SortOperator.Descending));

            if (inActivityParameter.PageSize > 0)
                oData.FetchProjection(valueProjector, projector, fields, bucket, 0, sort, null, false, inActivityParameter.PageNumber, inActivityParameter.PageSize);
            else
                oData.FetchProjection(valueProjector, projector, fields, bucket, 0, sort, false);

            activityList = projector.Destination;

Regards

Prabhu

Otis wrote:

I think it might be different than the issue mentioned earlier as fixed. Could you please post the C#/VB.NET code which produces this query (so your piece of code which results in this query), as I think I know what the problem is but unless I see your code I can't give you an advice how to fix it.

the general idea is that the field the expression is placed on is of type BigInt, but the expression itself is a string typed expression.

Otis,

We have updated the runtime version from **2.5.7.1129 to 2.5.08.1012 **as mentioned by you and Walaa and also regenerated the BusinessEntities and DataAccess projects by updating the above reference . Still we are facing the above issue.

Our application is a data intensive applications that contains lot of Analysis reports. Using lot of query expressions in our business logic methods for pulling the data from db. In terms of UI we are using paging functionality in our grids. While the user comes to any of analysis reports page for first time we are not getting the above issue. While we naviagate to other pages of grid we are facing this issue.

Please find below the list of error messages thrown from Business logic methods while we try to access some of the analysis reports pages that utilized query expressions

Implicit conversion from data type datetime to int is not allowed. Error converting data type nvarchar to bigint. (This error message was reported by Mr.Prabhakar) Object of type 'System.Int64' cannot be converted to type 'System.Nullable`1[System.Decimal]'.

Please help us to fix the issue.

Regards

Prabhu

The query posted in the topicstart is not the query produced by the piece of code posted, as the piece of code has no reference to any of the fields mentioned.

that said, you should look for code like this:

fields.DefineField(ActivityHeaderFields.Id, 1, "StartDate");
fields[1].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });

You define a field 'StartDate' which is an ActivityHeaderFields.Id on which you define an Expression. The result of this is that the 'Id' field is replaced by the expression in the query, if 'fields' would be a normal select.

Because you're using paging, the query will create either a temptable (if sqlserver compatibility is left to the default, see 'ApplicationConfiguration through config files' in the manual) or a CTE. Your setup uses the temp-table, so it has to create a temptable with fields which have to have a proper type. And that's the problem here: the expression might have a result of a type that's unknown to the DQE when it produces the query. So it always looks at the EntityField's DataType property. In the code snippet above, you use the 'Id' field however you define a CASE statement on it which results in a DateTime value. This leads to a problem, because the temp table creation routine will create a field for 'StartDate' which has a type which is equivalent to the .NET type of 'Id', which is the field the expression is defined on.

So instead of the code above, use the following.

// create a helper method in some utility class which produces FieldInfo instances based on a .NET type:
public static IFieldInfo CreateFieldInfo(string name, Type dataType, int maxLength, byte precision, byte scale)
{
    return new FieldInfo(name, string.Empty, dataType, false, false, true, false, 0, maxLength, scale, precision);
}

// then, in your code, use:

fields.DefineField(new EntityField2(Utils.CreateFieldInfo("StartDate", typeof(DateTime), 0, 0, 0)), 1, "StartDate");
fields[1].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });

So I specify a new EntityField2 instance, and I specify the fieldinfo to use for that field, as the expression will replace the field in the query, but the .NET type of the field the expression is on is used for the temptable field (as the expression itself has no type). The workaround is a bit cumbersome, as there's no handy constructor on EntityField2 for this in v2.5. In v2.6, a better CTor is available for EntityField2, which accepts a name, expression and .NET type.

Another way to work around this is by using the Sqlserver 2005 compatibility mode in the config file, although it then sometimes still reverts to a temptable for correctness. It's therefore essential you change some field definitions in your dyn. lists with the example above.

(about the error during post. I think it was a timeout issue on the db. We'll change the timeout for the db).

Otis wrote:

The query posted in the topicstart is not the query produced by the piece of code posted, as the piece of code has no reference to any of the fields mentioned.

that said, you should look for code like this:

fields.DefineField(ActivityHeaderFields.Id, 1, "StartDate");
fields[1].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });

Otis,

Thanks for the reply. I have taken a different BL method which was not provided by Prabhakar. Sorry for the confusion.

Otis wrote:

Another way to work around this is by using the Sqlserver 2005 compatibility mode in the config file, although it then sometimes still reverts to a temptable for correctness. It's therefore essential you change some field definitions in your dyn. lists with the example above.

The issue is fixed now. It is due to removal of SqlServerDQECompatibilityLevel element in config file. We have changed the strategy to connect the database last week. Actually we are having multiple database assigned to a specific countries in a single db server. Now we have changed that in a such a way that there will be around 5 db servers that contains one or more databases.

While changing the dataaccess facade layer for implementing the above scenario we accidentally changed the DQE compatibility settings.

Otis wrote:

Because you're using paging, the query will create either a temptable (if sqlserver compatibility is left to the default, see 'ApplicationConfiguration through config files' in the manual) or a CTE.

Can you please explain why the query is creating a temptable for paging if it is SQLServer 7 and 2000 and CTE for 2005.

Otis wrote:

So instead of the code above, use the following.

// create a helper method in some utility class which produces FieldInfo instances based on a .NET type:
public static IFieldInfo CreateFieldInfo(string name, Type dataType, int maxLength, byte precision, byte scale)
{
    return new FieldInfo(name, string.Empty, dataType, false, false, true, false, 0, maxLength, scale, precision);
}

// then, in your code, use:

fields.DefineField(new EntityField2(Utils.CreateFieldInfo("StartDate", typeof(DateTime), 0, 0, 0)), 1, "StartDate");
fields[1].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'E' THEN {1} ELSE CASE WHEN {2} is null AND {3} is null THEN {1} ELSE {2} END END", new object[] { ActivityCategoryFields.Code, ActivityHeaderFields.StartDate, ActivityHeaderFields.PerformStartDate, ActivityHeaderFields.PerformEndDate });

What's your input for avoiding these kind of issues

By using the above approach or setting the DQE Comaptibility level in config files?

Thanks in advance for your clarification.

Regards

Prabhu

Can you please explain why the query is creating a temptable for paging if it is SQLServer 7 and 2000 and CTE for 2005.

The CTE approach is the fastest one, but it is not available in SQL Server 2000 or 7.

What's your input for avoiding these kind of issues By using the above approach or setting the DQE Comaptibility level in config files?

If you're on sqlserver 2005 or higher, use that compatibility level, so you use CTE's like Walaa explained.

If you're writing new code, and you don't want to rely on that compatibility level, be sure the field you define an expression on has as .NET type a type which is compatible with the expression's result. You can do that with the code I showed.

[quotenick="Otis"]

If you're on sqlserver 2005 or higher, use that compatibility level, so you use CTE's like Walaa explained.

If you're writing new code, and you don't want to rely on that compatibility level, be sure the field you define an expression on has as .NET type a type which is compatible with the expression's result. You can do that with the code I showed.

Thanks for your suggestion.