Help please!!!

[ymorales]

I have this code:

Code: Select all

from th in db.transaction_header
join tl in db.transaction_line on th.transaction_id equals tl.transaction_id
join md1 in db.sv_merchandise_detail on new {th.transaction_id, tl.line_id} equals new {md1.transaction_id, md1.line_id} into md1
from md in md1.DefaultIfEmpty()
where th.transaction_id == transactionId && tl.line_void_flag == 0
group new {th, tl, md} by th.transaction_id
into g
select new ClassTransactionSummary
{
TransactionId = g.Key,
TotalGross = g.Where(w => w.tl.line_object_type.Equals(1)).Sum(s => (decimal?)s.tl.gross_line_amount) ?? 0,
TotalNet = g.Where(w => w.tl.line_object_type.Equals(2)).Sum(s => (decimal?)(s.tl.gross_line_amount - s.tl.pos_discount_amount) * s.tl.db_cr_none * -1 * s.tl.voiding_reversal_flag) ?? 0
}).FirstOrDefault();

Is just to sum all lines of transactions to get gross and net values.
In linq pad this work fine.
But in my project I got this error:

Exception:
Error on executing DbCommand.

Innermessage:
Invalid column name 'transaction_id1'.
Invalid column name 'line_id1'.
Invalid column name 'line_id'.
Invalid column name 'line_void_flag'.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Ambiguous column name 'db_cr_none'.
Ambiguous column name 'voiding_reversal_flag'.
Ambiguous column name 'db_cr_none'.
Ambiguous column name 'voiding_reversal_flag'.

Any help?
Thanks

[MariiaI]

Please specify the following details:
- the version of LinqConnect;
- the DBMS you are working with;
- the generated SQL query ( http://www.devart.com/linqconnect/docs/?Logging.html );
- the DDL/DML scripts for the necessary database tables;
- the definitions of the DataContext, entity classes, etc.;

If possible, send us the test project, so that we are able to investigate the issue in more details and find the solution for you in a shortest time.

[ymorales]

Sorry but no time for me. I installed EF6 and work perfect without error this linq query.
Anyways for your information, here is the problem to solve if you can.

Im trying this sql query:

Code: Select all

SELECT
  TH.transaction_id,
  SUM(CASE WHEN (TL.line_object_type=1) THEN TL.gross_line_amount ELSE 0 END) as gross,
  SUM(CASE WHEN (TL.line_object_type=1) THEN ((TL.gross_line_amount - TL.pos_discount_amount)* TL.db_cr_none * TL.voiding_reversal_flag)*-1 ELSE 0 END) as net
FROM 
  aw_asl_01.dbo.transaction_header TH
  JOIN aw_asl_01.dbo.transaction_line TL ON TH.transaction_id = TL.transaction_id
  LEFT JOIN aw_asl_01.dbo.sv_merchandise_detail MD ON TH.transaction_id = MD.transaction_id and TL.line_id = MD.line_id
WHERE 
 TH.transaction_id = @Transaction AND TL.line_void_flag = 0 
GROUP BY
 TH.transaction_id

- using latest version of linqconnect (standard)
- Sql dataserver
- the 3 tables has same column transaction_id, transaction_line & sv_merchandise_details has same columns: line_id, db_cr_none, voiding_reversal_flag
- Query:

Code: Select all

SELECT TOP(1) [t1].[transaction_id] AS [Key], COALESCE((
    SELECT SUM([t9].[gross_line_amount]) AS [C1], [t9].[line_object_type], [t9].[gross_line_amount], [t9].[pos_discount_amount], [t9].[db_cr_none], [t9].[voiding_reversal_flag], [t8].[transaction_id], [t10].[transaction_id] AS [transaction_id1], [t10].[line_id], [t9].[line_id] AS [line_id1], [t9].[line_void_flag]
    FROM [dbo].[transaction_header] [t8]
    INNER JOIN [dbo].[transaction_line] [t9] ON [t8].[transaction_id] = [t9].[transaction_id]
    LEFT OUTER JOIN [dbo].[sv_merchandise_detail] [t10] ON 1=1 
    WHERE [t9].[line_object_type] = @p2
    ),@p3) AS [C1], COALESCE((
    SELECT SUM(([gross_line_amount] - [pos_discount_amount]) * [db_cr_none] * @p5 * [voiding_reversal_flag]) AS [C1]
    FROM [dbo].[transaction_header] [t8]
    INNER JOIN [dbo].[transaction_line] [t9] ON [t8].[transaction_id] = [t9].[transaction_id]
    LEFT OUTER JOIN [dbo].[sv_merchandise_detail] [t10] ON 1=1 
    WHERE [line_object_type] = @p4
    ),@p6) AS [C2]
FROM (
    SELECT [t2].[transaction_id]
    FROM [dbo].[transaction_header] [t2]
    INNER JOIN [dbo].[transaction_line] [t3] ON [t2].[transaction_id] = [t3].[transaction_id]
    LEFT OUTER JOIN [dbo].[sv_merchandise_detail] [t4] ON ([t2].[transaction_id] = [t4].[transaction_id]) AND ([t3].[line_id] = [t4].[line_id])
    WHERE ([t2].[transaction_id] = @p0) AND ([t3].[line_void_flag] = @p1)
    GROUP BY [t2].[transaction_id]
    ) [t1]
WHERE ([transaction_id] = [transaction_id1]) AND ([line_id1] = [line_id]) AND ([transaction_id] = @p0) AND ([line_void_flag] = @p1) AND ([t1].[transaction_id] = [transaction_id])
-- @p0: Input Decimal (Size = 0; DbType = Decimal) [13090970]
-- @p1: Input Int (Size = 0; DbType = Int32) [0]
-- @p2: Input TinyInt (Size = 1; DbType = Byte) [1]
-- @p3: Input Decimal (Size = 0; DbType = Decimal) [0]
-- @p4: Input TinyInt (Size = 1; DbType = Byte) [1]
-- @p5: Input Decimal (Size = 0; DbType = Decimal) [-1]
-- @p6: Input Decimal (Size = 0; DbType = Decimal) [0]
-- Context: Devart.Data.SqlServer.Linq.Provider.SqlDataProvider Mapping: AttributeMappingSource Build: 4.4.563.0

A first chance exception of type 'Devart.Data.Linq.LinqCommandExecutionException' occurred in Devart.Data.Linq.dll

[MariiaI]

Thank you for the response.

Anyways for your information, here is the problem to solve if you can.

Unfortunately, the issue is not reproducible in our environment.
We would be grateful if you provide us with this information, too:
- the DDL/DML scripts for the necessary database tables;
- the definitions of the DataContext, entity classes, etc.;

Also, if possible, please send us a test model, with which you have encountered this issue.