Click here to Skip to main content
15,068,645 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can a list be filtered,
If suppose i got a model <list> like

lstEmpDetails:
EmpName  FineAmt  PaidAmt
  A       10        -
  A       -         10
  B       50        -
  A       -         25
  C       -         100

I need the output like:
EmpName  FineAmt  PaidAmt
  B       50        -
  A       -         25
  C       -         100

How can I do this using Linq by using the EmpName,FineAmt and PaidAmt
Posted
Updated 21-Jan-15 1:00am
v4
Comments
Staffan Bruun 21-Jan-15 4:47am
   
Do you only need to list those entries where the EmpName occurs exactly once in the set, or is the output based on some arithmetic calculation?
fiashieen 21-Jan-15 4:52am
   
no no,i just need like, if the EmpName occur's twice and the same amount exist in two different column like FineAmt and PaidAmt then discard from the list else copy.
Thanks7872 21-Jan-15 5:22am
   
In this case,why A with FineAmt or PaidAmt 10 is removed? What is the exact criterion?
fiashieen 21-Jan-15 5:48am
   
Actually the Employee 'A' was having a Fine Amount of Rs. 10 afterwards that amount had got paid by him.So that two rows want to be discarded from the list.

The expression below does the following:

First, it groups the records by name and amount (fine or paid).

Then, for each group:

It selects all the FineAmt rows but skips as many as there are PaidAmt rows also in the same group, so that if there are unpaired FineAmt rows, these will be the only elements in the list. If there are more PaidAmt rows than FineAmt rows, this results in an empty list.

It then does the same thing in reverse for PaidAmt, and concatenates this with the previous list (only one of these lists will have any rows.)

The resulting list, for each group, consists of any unpaired rows in the input.

NOTE: The query assumes that at least one of r.FineAmt or r.PaidAmt in each row has a value other than null.

C#
var filteredRows = lstEmpDetails
                   .GroupBy(r=>new {r.EmpName, Amount=r.FineAmt ?? r.PaidAmt})
                   .SelectMany(g=>
			          g.Where(r=>r.FineAmt.HasValue)
			           .Skip(g.Count(r=>!r.FineAmt.HasValue))
				   .Concat(g.Where(r=>!r.FineAmt.HasValue)
                                            .Skip(g.Count(r=>r.FineAmt.HasValue))));


Update:
To instead get the remaining sums of all the EmpName rows:

This statement groups the rows by name only, then computes the balance for each group.

Finally, it selects only those groups with non-zero balance, then generates the FineAmt, PaidAmt columns accordingly.

NOTE: This assumes you are using int as the data type; if not, change the last Select statement accordingly.

C#
var filteredRows = lstEmpDetails
                   .GroupBy(r=>r.EmpName)
                   .Select(g=>new{
                       EmpName=g.Key, 
                       Balance=g.Sum(r=>(r.FineAmt ?? 0) - (r.PaidAmt ?? 0))
                   })
                   .Where(r=>r.Balance != 0)
                   .Select(r=>new{
                       r.EmpName,
                       FineAmt = r.Balance > 0 ? (int?)r.Balance : null,
                       PaidAmt = r.Balance < 0 ? (int?)-r.Balance : null
                   });
   
v2
Comments
fiashieen 21-Jan-15 6:46am
   
is there any way to get the field(FineAmt and PaidAmt)difference and display the result in the corresponding side
like
suppose we considering 'A', then the X=sum(FineAmt)=10 and Y=sum(FineAmt)=35 and the diff(X,Y)=25 and since the Y is greater the O/P for 'A' will be like

EmpName FineAmt PaidAmt
A - 25


is there any way to do that???
Staffan Bruun 21-Jan-15 7:11am
   
See updated solution.
fiashieen 22-Jan-15 3:35am
   
yup,it works.....thank you so much....:)
fiashieen 4-Mar-15 7:32am
   
if suppose i am having the list like:

EmpCata EmpName FineAmt PaidAmt
Cata1 A 10 -
Cata1 A - 10
Cata2 B 50 -
Cata1 A - 25
Cata2 C - 100


how can i group the EmpName,FineAmnt,PaidAmt under the EmpCata .
Is that possible by "SubGroups"??
Staffan Bruun 4-Mar-15 9:40am
   
Depends. If you just want one table ordered so that all rows with the same EmpCata are together, you can use OrderBy(r=>r.EmpCata) after all the other stuff.

If you want separate tables for each EmpCata group, you need to use GroupBy(r=>r.EmpCata) at the end, then you need to iterate over each group to get the rows:

<pre lang="C#">
// processedData is assumed to be the data as processed previously
var groups = processedData.GroupBy(r=>r.EmpCata) // group data by EmpCata

foreach(var g in groups)
{
Console.Out.WriteLine("EmpCata: {0}", g.Key); // Key is whatever you put inside GroupBy()
Console.Out.WriteLine("EmpName\tFineAmt\tPaidAmt"); // optional, prints table headers
foreach(var row in g) // g itself is iterable and contains all rows in the group
{
Console.Out.WriteLine("{0}\t{1}\t{2}", row.EmpName, row.FineAmt, row.PaidAmt);
}
}
</pre>
Try this:
C#
var Result = lstEmpDetails.SkipWhile(x => lstEmpDetails.Any(y => x.FineAmt == y.PaidAmt && x.PaidAmt == y.FineAmt && x.EmpName== y.EmpName)).ToList();


Regards..
   
v2
Comments
Staffan Bruun 21-Jan-15 9:42am
   
The problem with that approach is that it will allow one row with the same FineAmt to cancel out several rows with the same PaidAmt, and vice versa.

I understood that the idea was to make sure that each FineAmt row only cancels out one PaidAmt row, which is why my initial solution focuses on counting the number of identical rows of each type.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900