I have this query:
SELECT *
FROM transaction t
JOIN transactionDetail toTrans ON t.id = toTrans.tId and toTrans.FlowDirection= 1
JOIN transactionDetail fromTrans ON t.id = fromTrans.tId and fromTrans.FlowDirection= 0
Which I tried to recreate using anonymous types, as explained here.
byte toFlow = 1;
byte fromFlow = 1;
from trans in data.Transactions
join toTrans in data.TransactionDetails on new {trans.id, toFlow} equals new {toTrans.tId, toTrans.FlowDirection}
join fromTrans in data.TransactionDetails on new { trans.id, fromFlow } equals new { fromTrans.tId, fromTrans.FlowDirection }
Flowdirection is always either 1 or 0, so I'm using the toFlow byte. This however, gives the error:
The type of one of the expressions in the join clause is incorrect.
According to this answer, both name and types need to match. Which would mean:
byte FlowDirection= 1;
from trans in data.Transactions
join toTrans in data.TransactionDetails on new {trans.id, FlowDirection} equals new {toTrans.tId, toTrans.FlowDirection}
join fromTrans in data.TransactionDetails on new { trans.id, FlowDirection} equals new { fromTrans.tId, fromTrans.FlowDirection }
Which works! However, the second join needs to have a FlowDirection of value 0 instead of 1. How can I change the value of FlowDirection? I can't change the name of the variable or subtract 1 inside the anonymous object, or else this would have been easy.
Just to expand on the commentary:
Surely you can just use two constants (or literals)?, i.e.
from trans in data.Transactions
join toTrans in data.TransactionDetails
on new {ID = trans.id, Flow = (byte)1}
equals new {Id = toTrans.tId, Flow = toTrans.FlowDirection}
join fromTrans in data.TransactionDetails
on new { Id = trans.id, Flow = (byte)0}
equals new { Id = fromTrans.tId, Flow = fromTrans.FlowDirection }
Could FlowDirect - 1 not work because it turns FlowDirect into an int instead? Does subtracting an int from a byte turn the byte into an int maybe? Otherwise, I really don't know why your code works.
Yes, you would need to cast the result back to byte (or the literal 1
to byte
so that byte operator "-" is used)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With