Linq query giving inappropriate output

Question

I have two transaction tables named as ParentTransaction and ChildTransaction in which TransactionId of ParentTransaction will act as foreign to ChildTransaction of TransactionId.

Now I want to get all those TransactionId of ParentTransaction whose payamount is not completed.

From below output I want record of transaction Id 3 because only 1000 has been paid for transactionid 3 instead of 5000.

I have one table like this:

Transactionid(p.k)    PayAmount
  1                   1000
  2                   3000
  3                   5000
  4                   6000

ChildTransaction

Id        TransactionId(F.k)   DepositAmount
1           1                  600
2           1                  400
3           2                  1000
4           2                  1000
5           2                  1000
6           3                  2000

This is my query:

var data = (from tmp in context.ParentTransaction
            join tmp1 in context.ChildTransaction on tmp.Transactionid equals
            tmp1.Transactionid where tmp.PayAmount !=tmp1.DepositAmount
                    select tmp);

But here I am getting Transaction Id 1 and 2 although their transaction has been completed in two parts that is 600 and 400 for transaction id 1.

Answer 1

The general idea of query languages is to express the desired result, not how to get it.

Applying it to your scenario leads to a simple query like this

var query = context.ParentTransaction
   .Where(t => t.PayAmount != context.ChildTransaction
      .Where(ct => ct.TransactionId == t.TransactionId)
      .Sum(ct => ct.DepositAmount));

If you are using EF and a proper model navigation properties, it would be even simple

var query = context.ParentTransaction
    .Where(t => t.PayAmount != t.ChildTransactions.Sum(ct => ct.DepositAmount));

One may say the above would be inefficient compared to let say the one from @Vadim Martynov answer. Well, may be yes, may be not. Vadim is trying to force a specific execution plan and I can understand that - we have to do such things when in reality encounter a query performance issues. But it's not natural and should be a last resort only if we have a performance problems. Query providers and SQL query optimizers will do (and are doing) that job for us in most of the cases, so we don't need to think of whether we need to use a join vs subquery etc.

Answer 2

I'm not sure that != is a best value. Here is a solution with > check and grouping:

var expectedValue =
            context.ChildTransaction
                .GroupBy(t => t.TransactionId, (key, group) => new { TransactionId = key, Deposit = group.Sum(e => e.Deposit) })
                .Join(context.ParentTransaction, grouped => grouped.TransactionId, transaction => transaction.TransactionId, (group, transaction) => new { Transaction = transaction, group.Deposit })
                .Where(result => result.Transaction.PayAmount > result.Deposit)
                .Select(result => result.Transaction);

This query can be read in a declare manner like next requirement:

1. Group collection of child transactions by TransactionId and for each group retrieve an anonymous type object with fields TransactionId = grouping key (== TransactionId) and Deposit which is sum of Deposits for rows with same TransactionId.
2. Join set from part 1 to the the table PaerntTransaction by TransactionId field. For each joined pair retrieve an anonymous type object with fields Transaction == transaction from ParentTransactions table and Deposit which is deposit from part 1 set which is sum of Deposits with the same TransactionId from the ChildTransactions table.
3. Filter from result set only objects where PayAmount greather than sum of deposits.
4. Return only ParentTransaction object for each filtered row.

This is SQL-optimized scenario because join, filter and grouping prevents nested queries which can be added to the actual execution plan in other cases and make worse performance.

UPDATE

To solve the problem with transaction that have no deposits you can use LEFT JOIN:

var expectedValue = from parent in context.ParentTransaction
            join child in context.ChildTransaction on parent.TransactionId equals child.TransactionId into gj
            from subset in gj.DefaultIfEmpty()
            let joined = new { Transaction = parent, Deposit = subset != null ? subset.Deposit : 0 }
            group joined by joined.Transaction
            into grouped
            let g = new { Transaction = grouped.Key, Deposit = grouped.Sum(e => e.Deposit) }
            where g.Transaction.PayAmount > g.Deposit
            select g.Transaction;

The same query with LINQ method chain:

var expectedValue =
            context.ParentTransaction
                .GroupJoin(context.ChildTransaction, parent => parent.TransactionId, child => child.TransactionId, (parent, gj) => new { parent, gj })
                .SelectMany(@t => @t.gj.DefaultIfEmpty(), (@t, subset) => new { @t, subset })
                .Select(@t => new { @t, joined = new { Transaction = @t.@t.parent, Deposit = @t.subset != null ? @t.subset.Deposit : 0 } })
                .GroupBy(@t => @t.joined.Transaction, @t => @t.joined)
                .Select(grouped => new { grouped, g = new { Transaction = grouped.Key, Deposit = grouped.Sum(e => e.Deposit) } })
                .Where(@t => @t.g.Transaction.PayAmount > @t.g.Deposit)
                .Select(@t => @t.g.Transaction);

Now you retrieve all parent transaction and join it with child transaction but if there is no children then use Deposit == 0 and group joined entities in a similar manner by ParentTransaction.

Answer 3

Problem

The issue lies on this statement:

where tmp.PayAmount != tmp1.DepositAmount //the culprit

And since the tmp1 is defined as a single child transaction, the statement would result in equating wrong values:

Visualizer:

1000 != 600 //(result: true -> selected) comparing parent 1 and child 1
1000 != 400 //(result: true -> selected) comparing parent 1 and child 2
3000 != 1000 //(result: true -> selected) comparing parent 2 and child 3
3000 != 1000 //(result: true -> selected) comparing parent 2 and child 4
3000 != 1000 //(result: true -> selected) comparing parent 2 and child 5
5000 != 2000 //(result: true -> selected) comparing parent 2 and child 5
//However, you do not want it to behave like this actually

But what you want to have is rather:

Visualizer:

1000 != (600 + 400) //(result: false -> not selected) comparing parent 1 and child 1 & 2, based on the TransactionId
3000 != (1000 + 1000 + 1000) //(result: false -> not selected) comparing parent 2 and child 3, 4, & 5, based on the TransactionId
5000 != (2000)  //(result: true -> selected) comparing parent 3 and child 6, based on the TransactionId 
6000 != nothing paid //(result: true -> selected) comparing parent 3 with the whole childTransaction and found there isn't any payment made

Thus, you should make tmp1 is as a collection of children rather than single child.

---

Solution

Unpaid Transaction

Change your code like this:

var data = (from tmp in context.ParentTransaction
            join tmp1 in context.ChildTransaction.GroupBy(x => x.TransactionId) //group this by transaction id
            on tmp.TransactionId equals tmp1.Key //use the key
            where tmp.PayAmount > tmp1.Sum(x => x.DepositAmount) //get the sum of the deposited amount
            select tmp)
           .Union( //added after edit
           (from tmp in context.ParentTransaction
            where !context.ChildTransaction.Select(x => x.TransactionId).Contains(tmp.TransactionId)
            select tmp)
           );                               

---

Explanations

This line:

join tmp1 in context.ChildTransaction.GroupBy(x => x.TransactionId) //group this by transaction id

Making use of GroupBy in Linq, this line makes tmp1 a group of children rather than a single child and, rightfully, based on its foreign key, which is the TransactionId.

Then this line:

on tmp.TransactionId equals tmp1.Key //use the key

We simply equates tmp.TransactionId with the children's group key tmp1.Key

Then the next line:

where tmp.PayAmount > tmp1.Sum(x => x.DepositAmount) //get the sum of the deposited amount

Get the sum value of the children's DepositAmount rather than single child's DepositAmount which is less than the PayAmount in the parent, and then

select tmp

Select all the parent transactions which satisfy all the criteria above. This way, we are half-done.

The next step is to consider transaction which occurs in the parent but not in the child(ren). This is considered as unpaid too.

We can combine the result of the first query with the second query using Union

.Union( //added after edit
(from tmp in context.ParentTransaction
 where !context.ChildTransaction.Select(x => x.TransactionId).Contains(tmp.TransactionId)
 select tmp)
);                              

This selects whatever exist in the parent transaction but doesn't exist at all in the child (and therefore considered unpaid).

And you would get the right data, consisting of your ParentTransaction rows which are not fully paid, both for the parent transaction whose TransactionId exists in the child or not.

---

Paid Transaction

As for the paid transaction, simply change the query from > to <=:

var datapaid = (from tmp in context.ParentTransaction
                join tmp1 in context.ChildTransaction.GroupBy(y => y.TransactionId)
                on tmp.TransactionId equals tmp1.Key
                where tmp.PayAmount <= tmp1.Sum(x => x.DepositAmount)
                select tmp);

---

Combined

We can further simplify the above query like this:

var grp = context.ChildTransaction.GroupBy(y => y.TransactionId);
var data = (from tmp in context.ParentTransaction
            join tmp1 in grp //group this by transaction id
            on tmp.TransactionId equals tmp1.Key //use the key
            where tmp.PayAmount > tmp1.Sum(x => x.DepositAmount)
            select tmp)
            .Union((
            from tmp in context.ParentTransaction
            where !context.ChildTransaction.Select(x => x.TransactionId).Contains(tmp.TransactionId)
            select tmp));

var datapaid = (from tmp in context.ParentTransaction
                join tmp1 in grp
                on tmp.TransactionId equals tmp1.Key
                where tmp.PayAmount <= tmp1.Sum(x => x.DepositAmount)
                select tmp);

Answer 4

List<int> obj = new List<int>();
using (DemoEntities context = new DemoEntities())
{
    obj = (from ct in context.CTransactions
    group ct by ct.Transactionid into grp
    join pt in context.PTransactions on grp.Key equals pt.Transactionid
    where grp.Sum(x => x.DepositAmount) < pt.PayAmount
    select grp.Key).ToList();
}

Answer 5

You control only one child transaction. You must use Sum() operation and need to use > instead of != Pls try this.

var data = (from tmp in context.ParentTransaction
            join tmp1 in context.ChildTransaction on tmp.Transactionid equals into tmp1List
            tmp1.Transactionid where tmp.PayAmount > tmp1List.Sum(l => l.DepositAmount)
            select tmp);