Problem in filtering the data from the same table

Question

I have a class like below :

 public class MasterTransaction 
    {
        public int Id { get; set; }
        public int EmployeeId { get; set; }
        public int? SubTransactionId { get; set; }

        [ForeignKey("SubTransactionId")]
        public virtual MasterTransaction SubTransaction { get; set; }
        public virtual ICollection<CommissionForManager> CommissionForManager { get; set; }
    }
    
    public class CommissionForManager
    {
        public int Id { get; set; }
        public int ManagerId { get; set; }
        public decimal CommissionMoney { get; set; }
        
         public int MasterTransactionId { get; set; }

        [ForeignKey("MasterTransactionId")]
        public virtual MasterTransaction MasterTransaction { get; set; }
    }

MasterTransaction will store all the master transactions. CommissionForManager table stores data related to all the transactions from where the Manager is getting a commission.

Sample Data :

MasterTransaction:

Id   EmployeeId     SubTransactionId
50   100            null  //100 is normal employee            
51   101            50    //101 is a Manager
52   102            null


CommissionForManager:

Id  ManagerId   CommissionMoney MasterTransactionId
1   101         5000            50  
2   101         6000            52  

Now Manager creates transaction from a transaction of Employee and in that case "SubTransactionId" column will hold the TransactionId of that transaction.

For example: Manager 101 created a transaction from a transaction 50 hence SubTransactionId holds value 50.

So I want to get list of transactions where Manager is getting commission but those particular transactions shouldnt be referenced in "SubTransactionId" column.

For ex: Transaction Id = 50 and 51 because 51 is created from 50 so I want to ignore both. I only want Transaction 52 because it is not being referenced in "SubTransactionId" column in another Transaction.

Query :

string searchString;
int managerId;
var query = context.CommissionForManager.Where(c => c.CommissionMoney > 0)
                                        .Where(c=> c.MasterTransaction.Employee.EmployeeName.Contains(searchString));
                                        
if (managerId > 0)
    query = query.Where(c=>c.ManagerId == managerId);
    
return query.ToList();
    

But here, I am not getting how do I make another search of Transaction Id in the SubTransactionId of the same MasterTransaction table?

Can someone please help me or guide me through :)

Answer 1

• but those particular transactions shouldn't be referenced in "SubTransactionId" column

and

• For ex: Transaction Id = 50 and 51 because 51 is created from 50 so I want to ignore both

The second doesn't really match the first requirement. Also SubTransactionId is poorly chosen name since it gives an impression of a pointer (reference) to some child, while in fact it seems to have parent reference semantics.

So if the second statement is correct, then the requirement can be formulated as follows: "include only transactions which have (1) no parent and (2) no child transactions".

The first condition is easy checked through navigation property, and the other needs subquery on the corresponding DbSet (remember it is queryable, so resolves to table access inside the LINQ to Entities query).

e.g. (skipping the initial part which applies other filters)

query = query.Where(c =>
    c.MasterTransaction.SubTransctionId == null // (1)
    &&
    !context.Set<MasterTransaction>().Any(t => t.SubTransactionId == c.MasterTransactionId) // (2)
);

The dilemma/issue for the question seems to be because of the lack of the inverse navigation property in the model. If you have it (as you should, since it's helping understanding the relationship better and property querying it from both sides), i.e. something like

public class MasterTransaction 
{
    // ...
    public virtual ICollection<MasterTransaction> ChildTransactions { get; set; }
}

then the second condition would be simply

!c.MasterTransaction.ChildTransactions.Any() // (2)