Linq multiple join conditions using optional 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 

What I was trying to recreate using anonymous types as described 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 1 or 0, so I use the toFlow byte. This, however, gives an error:

The type of one of the expressions in the join clause is invalid.

According to this answer, the name and types must match. That 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 } 

What works! However, for the second connection, you must have a FlowDirection value of 0 instead of 1. How can I change the FlowDirection value? I cannot change the variable name or subtract 1 inside an anonymous object, otherwise it would be easy.

Answer 1

Just to expand the comment:

Of course, 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 } 

Can FlowDirect - 1 not work, because instead it turns FlowDirect into int? Does int subtract bytes from bytes into ints, maybe? Otherwise, I really don't know why your code works.

Yes, you will need to return the result back to byte (or literal 1 in byte so that the byte operator "-" is used)

Answer 2

How to change the value of FlowDirection? I cannot change the name of a variable or subtract 1 inside an anonymous object

To change a variable inside an anonymous object, simply do:

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

eg.

Answer 3

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 parameter should be easier to read.