Linq – suming rows,create list of custom fields,create model class object list from data table

Querying data from a data table in .Net is easy with Linq. Linq is a query language which is applicable to all type of objects such as List,Tables etc in .Net . Infact you can simply convert the table data to object like List of generic type or cutom Model class object with Linq operation, in limited sense you can forget loops.

Sum table column

Let’ learn how to simply sum up a column in your data table using Linq.

var comms = (from p in public_members.payroll_posting.AsEnumerable() where p.Field<string>
("eid") == "1001" select p.Field<decimal>("amount")).Sum();

The above linq simply fetch amount from the data table for employee id “1001”. I also place an outer pareanthesis for summing up. The parenthesis come handy when you want to convert the values you selected to Arrays,List or perform aggragate functions like Sum,Count etct

Create List of rows with custom list

LINQ – CREATE LIST OF OBJECTS FROM A DATA TABLE

 var comm_list = (from p in posting.AsEnumerable() where  p.Field<string>("type") == 
"Commission" select new { amount = p.Field<decimal>("amount"), eno = p.Field<int>("pp_no"),
 edate = p.Field<DateTime>("post_date") }).ToList();

I have a table in which I store all employee payments under seperate types. I just want to create a list of commission paid to the employees as new list with custom column which is not exist in the data table .

The select new will create a model class like fields.

Create a list of objects from Linq query

Create list of Objects[Model class] with Linq

Following LinQ will help to build Model class object list from a data table, remember I used myown Model class which I not provided here. Use your own

 var payrolls = (from pp in payroll_entries.AsEnumerable()
                                  join emp in employees.AsEnumerable() on pp.Field<string>("eid") equals emp.Field<string>("eid")
                                  select new
                                  {
                                      p = new PayRollEntryModel()
                                      {
                                          VNO = pp.Field<int>("pe_no").ToString(),
                                          DATE = pp.Field<DateTime>("pe_date"),
                                          Employee = LedgeName(emp.Field<int>("lid")),
                                          EID = Convert.ToInt32(pp.Field<string>("eid")),
                                          CashAccount = LedgeName(pp.Field<int>("crledger")),
                                          Total = string.Format("{0:0.00}", pp.Field<decimal>("amount")),
                                          CrACID = Convert.ToInt32( pp.Field<int>("cash_ac")),
                                          Narration = pp.Field<string>("narration"),
                                          DrAcid = Convert.ToInt32( emp.Field<int>("lid")),                                         
                                          Allownaces=pp.Field<string>("allowances"),
                                          Commission=pp.Field<string>("comm"),
                                          Advance=pp.Field<string>("advance"),
                                          Basic=pp.Field<string>("bp"),
                                          Deductions=pp.Field<string>("deductions"),
                                          WDs= pp.Field<decimal>("wdays").ToString() ,
                                          WHs=  pp.Field<decimal>("whours").ToString(),                                       


                                      }
                                  }.p).ToList<PayRollEntryModel>();

At the 26th line of the above code I used .p because the linq only create a list of p which is a anonimous type. I want is a list of the Model class object which is PayRollEntryModel.

I think this will help somebody out there… leave comment and share your thoughts

All the examples I demonstrated here is part of my personal project and I should say these are working very well.

Advertisements