Tag Archives: Linq

Nullable date with Linq and Model class in C#


In C# Model class you may have experienced a nullable Error while fetching the rows with nullable column from database to your Model objects.

To treat the nullable in proper way , you have to tell your Model class and Linq this is nullable field.

Model class

*The nullable is applied to Value Type .This is not required for string

In the Model class I have a field edate which is in database is a nullable field. I want to make this field nullable in my model

 class StocKBatchModel
    {
        public int B_Id { get; set; }        
        public string Name { get; set; }
        public DateTime? Edate { get; set; }
}

By placing ? after the type of my class property , it becomes nullable. Say the DateTime becomes nullable , I can use the same in Linq in my LInq query as follows

 
var blist = (from m in batch_table.AsEnumerable()                                 
                                 select new
                                 {
                                     obj = new Models.StocKBatchModel()
                                     {
                                         B_Id = m.Field<int>("b_id"),
                                         Name = m.Field<string>("name"),
                                         Edate = m.Field<DateTime?>("exp_date"),                                       
                                 }.obj).ToList();

Hope this will helpful for someone.

How to find sum from obervablecollection list in C#


This example shows how you can simply find aggregate functions like sum, average etc from a Obervablecollection list.

ObservableCollection and Model-View-ViewModel is a ideal for a typical WPF application, these are programmers two of the best tools.

In this example I am using a model class , and want to find sum of inventory qty from batch list.

double qty=_batches.Sum((b) => b.qty));

Here _batches is representing the ObservableCollection of Model batch.

C#Tips/Tricks – Linq and Functions


Do you know Linq in c# has special capabilities to handle data smoothly. As we learned you can operate on user defined types and list using Linq.

Even you can use your own functions from within the Linq

C# Linq

The following example illustrate how to use functions within Linq

 var salesSummary = (from s in salesTable.AsEnumerable()
                           join c in Customer.AsEnumerable() on s.Field<int>("cid").ToString() equals
    c.Field<int>("id").ToString()  
                           select new
                           {
                               sv= new SalesView()
                               {
                                   Date = s.Field<DateTime>("date"),                                   
                                   invice = s.Field<int>("vno"),                                   
                                   Customer = c.Field<string>("name"),
                                   Amount = string.Format("{0:0.00}", s.Field<decimal>("amount")),                 
 InvBalance = string.Format("{0:0.00}", GetActBalance(s.Field<DateTime>("date"), s.Field<int>("cid"), s.Field<string>("vno"))),                  
}}.sv).ToList<SalesView>();

In the above example I used a function GetActBalance to display, the invoice balance at the point of sale , assume that they may paid some amount at the time of sale.

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

C#Tips/Tricks – Linq to array


You can easily convert a data table rows into object array using Linq query in C#.Net

var names_array = (from cols in tblcols.AsEnumerable() select cols.Field<string>("student_name")).ToArray();

Array can be used to supply items to list box and combo box controls as follows

 listbox1.Items.AddRange(names_array );

DataGridView meets Linq


DataGridView is a tool for displaying, modifying, entering values in Visual Studio.Net. Usually we are using loops or readers to traverse through rows or columns.

Linq query make it possible to do these take with few lines, instead of complicated loops.

We have a data entry GridView which accepts purchase details such as name,qty,price etc. Lets get the sum of quantity supplied in the grid using Linq query

We do the following

  1. Create List of values
  2. Call the Sum() for total function for the column we specified

The Linq Querry

var qt = from DataGridViewRow row in dataGridView1.Rows where row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim().Length!=0 && row.Cells[COL_QTY.Index].FormattedValue != null select ( row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim());

We have to make sure the cell is not null or it has any values at all, we place where section, otherwise the System will fire you for an unhandled exception, lol.

Summing up values

txt_tqty.Text = qt.Sum(c => Convert.ToDouble(c)).ToString();

That’s all