This Sum query results in an error (invalid column name avg_grade):

var y = new LinqMetaData().AssociateCompanyServiceMetric.Where(acsm => acsm.CompanyServiceId == 1).Sum(acsm => acsm.AvgGrade);

Results in this database query:

exec sp_executesql N'SELECT TOP(@p2) SUM([LPA_L1].[avg_grade]) AS [LPAV_] FROM (SELECT [LPLA_1].[avg_days_assign_to_schedule] AS [AvgDaysAssignToSchedule], [LPLA_1].[avg_days_assign_to_submit] AS [AvgDaysAssignToSubmit], [LPLA_1].[avg_grade] AS [AvgGrade], [LPLA_1].[company_service_id] AS [CompanyServiceId], [LPLA_1].[completed_files] AS [CompletedFiles], [LPLA_1].[login_id] AS [LoginId], [LPLA_1].[overdue_files] AS [OverdueFiles], [LPLA_1].[performance_indicator] AS [PerformanceIndicator], [LPLA_1].[rejected_count] AS [RejectedCount] FROM [PipeFire_Streetlinks].[dbo].[Associate_Company_Service_Metric]  [LPLA_1]  (nolock) WHERE ( ( [LPLA_1].[company_service_id] = @p3))) [LPA_L1]',N'@p2 bigint,@p3 int',@p2=1,@p3=1

This is our workaround, which works fine:

var x = new LinqMetaData().AssociateCompanyServiceMetric.Where(acsm => acsm.CompanyServiceId == 1).Select(acsm => acsm.AvgGrade).Sum();

Which results in this database query:

exec sp_executesql N'SELECT TOP(@p2) SUM([LPA_L1].[AvgGrade]) AS [LPAV_] FROM (SELECT [LPLA_1].[avg_grade] AS [AvgGrade] FROM [PipeFire_Streetlinks].[dbo].[Associate_Company_Service_Metric]  [LPLA_1]  (nolock) WHERE ( ( ( [LPLA_1].[company_service_id] = @p3)))) [LPA_L1]',N'@p2 bigint,@p3 int',@p2=1,@p3=1

The query output by the second is more efficient, but not as intuitive.

Brian

Odd. We'll look into it.

I can't reproduce it. It's in the name of the field in the projection of the inner select. Our query properly refers to the right field:

query: var s = metaData.SalesOrderDetail.Where(sod => sod.SalesOrderDetailId== 43659).Sum(od => od.OrderQuantity);

SELECT TOP 1 SUM([LPA_L1].[OrderQuantity]) AS [LPAV_] FROM (SELECT [LPLA_1].[CarrierTrackingNumber], [LPLA_1].[LineTotal], [LPLA_1].[ModifiedDate], [LPLA_1].[OrderQty] AS [OrderQuantity], [LPLA_1].[ProductID] AS [ProductId], [LPLA_1].[rowguid] AS [Rowguid], [LPLA_1].[SalesOrderDetailID] AS [SalesOrderDetailId], [LPLA_1].[SalesOrderID] AS [SalesOrderId], [LPLA_1].[SpecialOfferID] AS [SpecialOfferId], [LPLA_1].[UnitPrice], [LPLA_1].[UnitPriceDiscount] FROM [AdventureWorks].[Sales].[SalesOrderDetail]  [LPLA_1]   WHERE ( ( [LPLA_1].[SalesOrderDetailID] = @p1))) [LPA_L1]

Here you see OrderQuantity is properly refering to the re-aliased field in the derived table projection.

Please make sure you're using the latest 3.0 runtime build.

I will get the latest when I can and retest. Will post my findings.

Currently using 3.0.10.915 ORMSupportClasses and 3.0.10.809 LinqSupportClasses.

just installed and tried the latest build (11/5) and I still get the error. One thing I did not mention is that avg_grade is a nullable<decimal>. Would that make a difference?

I can't reproduce it either on Northwind using the following:

var x = metaData.Orders.Where(sod => sod.EmployeeId == 5).Sum(od => od.Freight);

Freight is a nullable Decimal field.

Looking closer at the sql I realized what the error is and why you probably cannot reproduce. The error is a sql error (kind of), not a LINQ error:

exec sp_executesql N'SELECT TOP(@p2) SUM([LPA_L1].[avg_grade]) AS [LPAV_] FROM (SELECT [LPLA_1].[avg_days_assign_to_schedule] AS [AvgDaysAssignToSchedule], [LPLA_1].[avg_days_assign_to_submit] AS [AvgDaysAssignToSubmit], [LPLA_1].[avg_grade] AS [AvgGrade], [LPLA_1].[company_service_id] AS [CompanyServiceId], [LPLA_1].[completed_files] AS [CompletedFiles], [LPLA_1].[login_id] AS [LoginId], [LPLA_1].[overdue_files] AS [OverdueFiles], [LPLA_1].[performance_indicator] AS [PerformanceIndicator], [LPLA_1].[rejected_count] AS [RejectedCount] FROM [PipeFire_Streetlinks].[dbo].[Associate_Company_Service_Metric] [LPLA_1] (nolock) WHERE ( ( [LPLA_1].[company_service_id] = @p3))) [LPA_L1]',N'@p2 bigint,@p3 int',@p2=1,@p3=1

If you look at the inner sub select it is selecting [LPLA_1].[avg_grade] AS [AvgGrade], the outer sum is looking for SUM([LPA_L1].[avg_grade]) (i.e. avg_grade is not in the resultset, but AvgGrade is). The sum should be SUM([LPA_L1].[AvgGrade]).

The second query generates the query correctly. The reason you may not be seeing it is because the column names in your DB match the C# names exactly.

Brian

That's indeed the issue, but I can't reproduce it with a renamed field, see my query above (copied below) SELECT TOP 1 SUM([LPA_L1].[OrderQuantity]) AS [LPAV_] FROM (SELECT [LPLA_1].[CarrierTrackingNumber], [LPLA_1].[LineTotal], [LPLA_1].[ModifiedDate], [LPLA_1].[OrderQty] AS [OrderQuantity], [LPLA_1].[ProductID] AS [ProductId], [LPLA_1].[rowguid] AS [Rowguid], [LPLA_1].[SalesOrderDetailID] AS [SalesOrderDetailId], [LPLA_1].[SalesOrderID] AS [SalesOrderId], [LPLA_1].[SpecialOfferID] AS [SpecialOfferId], [LPLA_1].[UnitPrice], [LPLA_1].[UnitPriceDiscount] FROM [AdventureWorks].[Sales].[SalesOrderDetail] [LPLA_1] WHERE ( ( [LPLA_1].[SalesOrderDetailID] = @p1))) [LPA_L1]

or am I overlooking something?

I used plain adventureworks, and renamed salesorderdetail.orderqty to orderquantity and used your query.

It seems to be the underscores. I tried against a table with camel cased columns and it is fine.

new LinqMetaData().CreditCardPayment.Where(ccp => ccp.CreditCardPaymentId < 10).Sum(ccp => ccp.SettleAmount);

brianchance wrote:

It seems to be the underscores. I tried against a table with camel cased columns and it is fine.

new LinqMetaData().CreditCardPayment.Where(ccp => ccp.CreditCardPaymentId < 10).Sum(ccp => ccp.SettleAmount);

Though the name is totally different (OrderQty in the table, OrderQuantity in the entity): So it's not a casing issue, or do you mean: I have done the same thing as you and renamed a field to something lengthier and it still works ?

In the new working example, the llblgen entity properties match the table field names exactly.

I was working on the assumption that since OrderQty to OrderQuantity tested a renamed column, the only difference was the underscores.

Do you have a table with underscored column names?

brianchance wrote:

In the new working example, the llblgen entity properties match the table field names exactly.

I was working on the assumption that since OrderQty to OrderQuantity tested a renamed column, the only difference was the underscores.

Looking at the error, it looks like a field is re-aliased in the projection of the actual source query (as the name in the entity is different from the target field and not only by casing) and the SUM() aggregate still uses the old table field name, not the alias of it. This works if the alias and the tablefield differ in casing and the db is case insensitive.

So a renamed field in the entity (avg_grade to AvgGrade or OrderQty to OrderQuantity) will cause this problem. I didn't see the problem with my field, so it must be something else. THe only difference is indeed that you have a underscore in the name, however I doubt that's the cause, but I'll try.

Do you have a table with underscored column names?

not in the regular tests, but that's quickly created. Will see what I can dig up

Nope, can't reproduce it, Brian...

var s = metaData.NameTestTab.Where(x => x.Id == 1).Sum(y => y.AvgGrade);

SQL:

Generated Sql query: 
    Query: SELECT TOP 1 SUM([LPA_L1].[AvgGrade]) AS [LPAV_] FROM (SELECT [LPLA_1].[avg_grade] AS [AvgGrade], [LPLA_1].[ID] AS [Id], [LPLA_1].[Name] FROM [AdventureWorks].[dbo].[NameTestTab]  [LPLA_1]   WHERE ( ( [LPLA_1].[ID] = @p1))) [LPA_L1]
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

which clearly shows the proper name is addressed in the SUM() part.

So to help track this down, I'll add the latest linq and ormsupportclasses dll I used for the test, perhaps you're using an oldie somewhere...

Still errors. The work around is fine for now if you want to drop it. Not sure how to narrow it down.

brianchance wrote:

Still errors. The work around is fine for now if you want to drop it. Not sure how to narrow it down.

Does it happen with other tables as well? I don't know how to track this down either, as we can't reproduce it, even with the same field name as you use. I'll try to use a nullable field btw, I used a non-nullable field in my last test, I overlooked that.

(edit) nullable field, as expected, doesn't make a difference. So I'm out of options...

I have tried it with other tables, it happens on all where the field names have underscores.

brianchance wrote:

I have tried it with other tables, it happens on all where the field names have underscores.

Ok, could you create a 1 table catalog and a project, simple console app with the query that fails? No data needed, the query runs or fails. If you can reproduce it with a simple repro, we could then check what we do differently and use it as a hunt to get to the bottom of this Thanks in advance.

Attaching test application.

The database folder has a CreateTable.sql file to create the table

SumTest console application, just change the app.config connection string.

You may need to change the SD.* reference paths, mine are not installed in the default location.

We'll look into it.

(edit) With the repro we can indeed reproduce it. It's very strange, as the query I used to reproduce it (which worked) is exactly the same... Looking into it.

Looks like it's selfservicing specific:

// selfservicing
LinqMetaData metaData = new LinqMetaData();
var s = metaData.OrderDetail.Where(od => od.OrderId == 10254).Sum(od => od.OrderId);

produces

Generated Sql query: 
    Query: SELECT TOP 1 SUM([LPA_L1].[OrderID]) AS [LPAV_] FROM (SELECT [LPLA_1].[Discount], [LPLA_1].[OrderID] AS [OrderId], [LPLA_1].[ProductID] AS [ProductId], [LPLA_1].[Quantity], [LPLA_1].[UnitPrice] FROM [Northwind].[dbo].[Order Details]  [LPLA_1]   WHERE ( ( [LPLA_1].[OrderID] = @p1))) [LPA_L1]
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10254.

which is wrong, it refers to OrderID, while the field is aliased to OrderId

same query in adapter:

// adapter
LinqMetaData metaData = new LinqMetaData(adapter);
var s = metaData.OrderDetail.Where(od => od.OrderId == 10254).Sum(od => od.OrderId);

produces

Generated Sql query: 
    Query: SELECT TOP 1 SUM([LPA_L1].[OrderId]) AS [LPAV_] FROM (SELECT [LPLA_1].[Discount], [LPLA_1].[OrderID] AS [OrderId], [LPLA_1].[ProductID] AS [ProductId], [LPLA_1].[Quantity], [LPLA_1].[UnitPrice] FROM [Northwind].[dbo].[Order Details]  [LPLA_1]   WHERE ( ( [LPLA_1].[OrderID] = @p1))) [LPA_L1]
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10254.

which correctly refers to ObjectId hence we couldn't reproduce it at first. It's odd though, as selfservicing and adapter both use the same code to find derived table referencing fields and correct them.

We'll see what we can dig up

Found it. It's a subtle bug, but has large consequences. Selfservicing has its persistenceinfo inside the field, adapter doesn't. This lead to the situation that for adapter, the field was correctly seen as a derived table targeting field, for selfservicing it wasn't, and it was emitted as if it was a from clause source targeting field. A bug in the visitor which finds derived table targeting fields didn't find it due to the aggregate, and it lead to this result.

Fixed in the dll attached.

You are awesome. Tested and works great.

Thanks for taking the time to dig in and help find+fix the issue. That is one of the best benefits of using your tools.

brianchance wrote:

You are awesome. Tested and works great.

Thanks for taking the time to dig in and help find+fix the issue. That is one of the best benefits of using your tools.