The following code give a general error about 0: dtFilter = New PredicateExpression dtFilter.Add(TranFields.Tdat < comp.Fiscalbegin) Dim expField As IEntityField = TranFields.Amt expField.ExpressionToApply = New DbFunctionCall("CASE {0} WHEN '1' THEN {1} ELSE {2} END", New Object() {TranFields.Debit, TranFields.Amt, TranFields.Amt * -1}) Dim trns As New TranCollection RevPre = trns.GetScalar(TranFieldIndex.Amt, expField.ExpressionToApply, SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Sum, dtFilter)

Any ideas? I've never used a dbfunctioncall before, just trying to follow your code example from iif and getscalar problem, my linq solution was too slow since there are too many records.

Thanks,

Hi Terry. I don't see any obvious missing thing Could you please give me more info about the error (compile time, runtime, stacktrace). Also post the generated sql

Runtime error: An exception was caught during the execution of a retrieval query: Incorrect syntax near '0'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Generated Sql: SELECT TOP 1 SUM(CASE {0} WHEN '1' THEN {1} ELSE {2} END([gtf].[dbo].[tran].[debit], [gtf].[dbo].[tran].[amt], [gtf].[dbo].[tran].[amt] * -1)) AS [Amt] FROM [gtf].[dbo].[tran] WHERE ( [gtf].[dbo].[tran].[tdat] < '1/1/2008')

Related working sql: SELECT TOP 1 SUM(CASE [tran].debit WHEN '1' THEN [tran].amt ELSE -1*[tran].amt END) AS [Amt] FROM [gtf].[dbo].[tran]
WHERE ( [gtf].[dbo].[tran].[tdat] < '1/1/2008')

I tested this:

Dim expField As IEntityField = ProductsFields.UnitPrice
expField.ExpressionToApply = New DbFunctionCall("CASE {0} WHEN '1' THEN {1} ELSE {2} END", _
     New Object() { _
          ProductsFields.Discontinued, _
          ProductsFields.UnitPrice, _
          ProductsFields.UnitPrice * -1})

Dim filter As New PredicateExpression()
filter.Add(ProductsFields.ProductId > 0)

Dim prods As New ProductsCollection()
Dim tmp As Object = prods.GetScalar(ProductsFieldIndex.UnitPrice, expField.ExpressionToApply, AggregateFunction.Sum, filter)
        ' result = (tmp == null) ? 0 : (decimal)tmp;

that return this:

Query: SELECT TOP 1 SUM(CASE [Northwind].[dbo].[Products].[Discontinued] WHEN '1' THEN [Northwind].[dbo].[Products].[UnitPrice] ELSE [Northwind].[dbo].[Products].[UnitPrice] * @LO30e51581 END) AS [UnitPrice] FROM [Northwind].[dbo].[Products]  WHERE ( ( [Northwind].[dbo].[Products].[ProductID] > @ProductIdd2))
    Parameter: @LO30e51581 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -2.
    Parameter: @ProductIdd2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.

So I think I forgot to ask you an important thing: What LLBLGen version are you using? (seeing your generated SQL it must be you are using v2.0).

Yes, I'm using version 2.0, what version should I be using to get your results?

Can I use 2.6 with 2.0 apps? Big asp.net apps don't convert to 3.5 well until SP1 is out.

Yes, I'm using version 2.0, what version should I be using to get your results?

CASE support in DBFunctionCalls exists since v2.5.

Can I use 2.6 with 2.0 apps? Big asp.net apps don't convert to 3.5 well until SP1 is out.

Yes you can still use LLBLGenPro on 1.1 and 2.0 apps.

Okay that's great that I can upgrade now, but do I just download the demo?

Please help me with this issue.

I read into those linq articles you mentioned, but I'm not really getting to my question.

I'm already using Linq to objects with llblgenpro collections to reduce properties and resort, refilter for reuse.

How is 2.6 going to help when it already seems to work?

Also, if I get a filtered collection of 40,000 transactions that have a lot of fields, then use Linq to objects to get a 2 fields version of that collection before I've done anything else with the original collection, is memory usage helped by such a maneuver?

Thanks,

tvoss wrote:

Okay that's great that I can upgrade now, but do I just download the demo? Please help me with this issue.

Just go to the Customer Area, and download the version you want to upgrade to, e.g. 2.6 at the left of the customer area menu.

I read into those linq articles you mentioned, but I'm not really getting to my question.

I'm already using Linq to objects with llblgenpro collections to reduce properties and resort, refilter for reuse.

How is 2.6 going to help when it already seems to work?

The thing is that v2.0's DbFunctionCall could just handle a function name and that's it. V2.5 introduced formatted SQL text, so you can use the DbFunctionCall to produce custom SQL snippets, like a CASE statement. So to use this feature, you need v2.5 or higher, otherwise you can't write code which produces the SQL snippet you want (the CASE statement).

Also, if I get a filtered collection of 40,000 transactions that have a lot of fields, then use Linq to objects to get a 2 fields version of that collection before I've done anything else with the original collection, is memory usage helped by such a maneuver? Thanks,

Linq to Objects makes copies of objects, so the data is copied.

In v2.6, you can use Linq queries instead of predicates and the like to filter databases, and we have build in support for IIF (the VB statement) in Linq queries in v2.6

Please read the 'migrating your code' section in the documentation to learn more about what will break, what to look out for etc.

Linq to Objects copies data... Yes I understand, but what I am asking is:

Since I never used the original all fields version of the collection except to create a 2 fields version of that collection, isn't the bigger collection going away with gargabe collection as I am using the Linq to Objects 2 field version of the collection?

Am I really gaining something there. Too many procedures require complex code in a loop on a collection that will cause out of memory situations if I'm not using an efficient fieldswise collection.

iows lots of records, but few fields.

I have this problem everytime I try to upgrade and this is why I don't upgrade until required by something. I go login with the user and pw that I have saved in my sql database, wondering why the login is not automatic anymore. I enter u/pw and get bad login, so I click on I forgot my password and no email ever arrives and there is no error message telling what's wrong so I can't get to the customer area.

Makes you feel really dumb.

tvoss wrote:

Linq to Objects copies data... Yes I understand, but what I am asking is:

Since I never used the original all fields version of the collection except to create a 2 fields version of that collection, isn't the bigger collection going away with gargabe collection as I am using the Linq to Objects 2 field version of the collection?

If the collection isn't referenced anywhere, then yes, the collection is garbage collected, but that can take a while (the GC runs every 15 minutes or so).

In 2.5 we introduced projections, which allows you to formulate a query with a couple of fields and project it onto entities for example (so you just fetched the fields you needed). In v2.6, you can use Linq to query the db directly using LLBLGen pro code, so you could rewrite the Linq to Objects query to fetch the data directly from the DB, so you don't have to fetch it into entities first. Also, in v2.5 we introduced excluding/including fields, which allows you to specify which fields should be excluded from a fetch, so in your case yuo could exclude all fields but the 2 fields you need in the linq to objects query.

You can also use a dynamic list and then simply traverse that in a linq to objects query, if you want to / have to stay on v2.0

I have this problem everytime I try to upgrade and this is why I don't upgrade until required by something. I go login with the user and pw that I have saved in my sql database, wondering why the login is not automatic anymore. I enter u/pw and get bad login, so I click on I forgot my password and no email ever arrives and there is no error message telling what's wrong so I can't get to the customer area.

Makes you feel really dumb.

Since for now I can't get to the customer area, is there any problem with using the demo version? Can't chance code stopping working on production.

I tried the demo version 2.6 and get relationcollection and predicateexpression are ambiguous in ormsupport namespace. Any ideas?

tvoss wrote:

I have this problem everytime I try to upgrade and this is why I don't upgrade until required by something. I go login with the user and pw that I have saved in my sql database, wondering why the login is not automatic anymore. I enter u/pw and get bad login, so I click on I forgot my password and no email ever arrives and there is no error message telling what's wrong so I can't get to the customer area.

Makes you feel really dumb.

Since for now I can't get to the customer area, is there any problem with using the demo version? Can't chance code stopping working on production.

the email is the same as the one in your profile in this forum?

I tried the demo version 2.6 and get relationcollection and predicateexpression are ambiguous in ormsupport namespace. Any ideas?

Be sure you have referenced the 2.6 runtimes in all projects in the solution.

(edit). I found 2 v2 accounts for you. I'll reset both and will mail you the customerids and new password to the email address in your profile.

The email to reset a password could have been bounced or caught by spamfilters. Sometimes this happens.

(edit) mailed.

tvoss wrote:

I tried the demo version 2.6 and get relationcollection and predicateexpression are ambiguous in ormsupport namespace. Any ideas?

Did you regenerate code or just re-assigned the ORMSupportClasses assembly?