Linq multiple join conditions using extra variables

Question

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.

Why can't you just use two constants, or just cast literals `(byte)0` and `(byte)1` ? — StuartLC, Jan 08 '15 at 12:21
Thanks Stuart, that worked for me! I now have {trans.id, FlowDirection = (byte)1}. I figured out the problem. When I subtracted 1 from the byte, it became an int as well. See here: http://stackoverflow.com/questions/941584/byte-byte-int-why — yesman, Jan 08 '15 at 12:25

score 4 · Accepted Answer · answered Jan 08 '15 at 12:38

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)

score 0 · Answer 2 · answered Jan 08 '15 at 12:17

0

How can I change the value of FlowDirection? I can't change the name of the variable or subtract 1 inside the anonymous object

To change your variable inside the anonymous object simply do:

new { fromTrans.tId, FlowDirection = fromTrans.FlowDirection - 1 }

for example.

answered Jan 08 '15 at 12:17

dav_i

27,509
17
104
136

Sadly not, this was what I tried as well in several forms. I get the same type error. FlowDirection -= 1 and FlowDirect = toTrans.FlowDirection - 1 don't work either. – yesman Jan 08 '15 at 12:19

score 0 · Answer 3 · answered Jan 08 '15 at 12:50

Another idea:

from trans in data.Transactions
join toTrans in data.TransactionDetails on trans.id equals new toTrans.tId
join fromTrans in data.TransactionDetails on trans.id equals fromTrans.tId
where toTrans.FlowDirection == 1 && fromTrans.FlowDirection == 1

I think this option should be easier to read.

Linq multiple join conditions using extra variables

3 Answers3

Linked