Compilemode: LINQ Online Tutorials IoT, Microsoft Azure, AWS, ASP.NET Core, C#,Amazon Web Service,SQL,CosmosDB, React, Angular

Convert DataTable To Generic List Using LINQ C#

Filed Under: ADO.NET, c-sharp, csharp, LINQ on Jun 16, 2021

In this article, we will learn how to convert Data Table to Generic list using the LINQ C#. Let's create the property class which stores the Data Table into the generic list.

EmpModel.cs

public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }

        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }

Create the GetAllEmployees() method to convert the Data Table into the generic list and return the records in the list.

public List<EmpModel> GetAllEmployees()
        {
            connection();
            List<EmpModel> EmpList =new List<EmpModel>();

            SqlCommand com = new SqlCommand("GetEmployees", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();
            //Bind EmpModel generic list using dataRow 
            foreach (DataRow dr in dt.Rows)
            {

                EmpList.Add(

                    new EmpModel {

                        Empid = Convert.ToInt32(dr["Id"]),
                        Name =Convert.ToString( dr["Name"]),
                        City = Convert.ToString( dr["City"]),
                        Address = Convert.ToString(dr["Address"])

                    }
                    
                    
                    );
            }

            return EmpList;

        }

I hope from the preceding example you have learned how to convert the Data Table to a generic list using the LINQ.

Convert DataTable To Generic List Using AsEnumerable In ASP.NET MVC

Filed Under: ADO.NET, ASP.NET, c-sharp, LINQ, MVC on Sep 19, 2015

Sometimes we need to work with a generic list, which will be generated from a model class. Suppose we are getting data from a database in a data table and thinking of binding a view from a model class generic list, then first we need to convert the data table into a generic list .

So let us see how we can convert a datatable to a generic list using AsEnumerable.

Suppose the model class is EmpModel.cs as below.

public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }

        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }

The following is the conversion logic

public List<EmpModel> GetAllEmployees()
        {
            connection();
            List<EmpModel> EmpList =new List<EmpModel>();
            SqlCommand com = new SqlCommand("GetEmployees", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();
            //Bind EmpModel generic list using AsEnumerable 
           List<DataRow> list = dt.AsEnumerable().ToList();
            foreach (var item in list)
            {
                EmpList.Add(

                   new EmpModel
                   {
                       Empid = Convert.ToInt32(item["Id"]),
                       Name = Convert.ToString(item["Name"]),
                       City = Convert.ToString(item["City"]),
                       Address = Convert.ToString(item["Address"])

                   });
            }

            return EmpList;

        }

Summary

I hope this article is useful for all readers. If you have any suggestions, then please contact me.

Convert DataTable To Generic List Using LINQ In ASP.NET MVC

Filed Under: ADO.NET, ASP.NET, LINQ, MVC

Many times in MVC we need to work with generic list which will be generated from model class , Suppose we are getting data from DataBase in DataTable and thinking to bind view from model class generic list then first we need to convert DataTable into generic list .
There are many ways to achieve it .refer previous article using is we loop through all data row

Convert DataTable To Generic List In ASP.NET MVC

Using above method its require huge amount of code and time to loop on each record so to overcome this problem we can use LINQ to convert DataTable to Generic list . So let us see how we can achieve this

Suppose the model class is EmpModel.cs as below

public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }

        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }

Now Class will be look like as follows

public List<EmpModel> GetAllEmployees()
        {
            connection();
            List<EmpModel> EmpList =new List<EmpModel>();

            SqlCommand com = new SqlCommand("GetEmployees", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();
            //Bind EmpModel generic list using LINQ
           EmpList = (from DataRow dr in dt.Rows

                    select new EmpModel()
                    {
                        Empid = Convert.ToInt32(dr["Id"]),
                        Name = Convert.ToString(dr["Name"]),
                        City = Convert.ToString(dr["City"]),
                        Address = Convert.ToString(dr["Address"])
                    }).ToList();

            return EmpList;

        }

Please refer below similar article as above

Summary
I hope this article is useful for all readers. If you have any suggestion then please contact me.

How To Read XML File Using LINQ

Filed Under: LINQ, XML on Sep 12, 2015

In this code snippet we will learn how to read xml file using Linq ,the following is the simple code which reads the xml file

var xmldoc = XDocument.Load(@"E:/VS 2010 Projects/ReadXMLFileUsingLINQ/XMLFile.xml");  
  
      var results = from Emp in xmldoc.Descendants("empdetails")  
                     
                    select Emp;

Summary
I hope this code snippet is useful ,if you have any suggestion regarding this article then please contact me.

Join Two DataTables Using LINQ In ASP.Net

Filed Under: ADO.NET, ASP.NET, C#, LINQ on May 26, 2015

I have read many forum posts regarding how to join two DataTables, so by considering those requirements I have decided to write this article. So let us learn step-by-step how to Join two DataTables using LINQ

LINQ

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. Using LINQ you can manipulate the data similar to as is done using SQL queries . Let us learn practically how to convert a LINQ query result into a DataTable by creating a sample application.

Now create the project as:

"Start" - "All Programs" - "Microsoft Visual Studio 2010".
"File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
Provide the Project name such as "JoinDataTableUsingLINQ" or another as you wish and specify the location.
Then right-click on Solution Explorer and select "Add New Item" then select Default.aspx page.
Drag and drop three Grid view to bind the records after Joining the two data table .

Now the Default.aspx source code will be as follows:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title></title>  
</head>  
<body style="background-color: Blue">  
    <h4 style="color: White">  
        Article by Vithal Wadje</h4>  
    <form id="form1" runat="server">  
    <div>  
        <h4 style="color: White">  
            Product Table Records Before joining  
        </h4>  
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
            <AlternatingRowStyle BackColor="White" />  
            <EditRowStyle BackColor="#7C6F57" />  
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
            <RowStyle BackColor="#E3EAEB" />  
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
            <SortedAscendingCellStyle BackColor="#F8FAFA" />  
            <SortedAscendingHeaderStyle BackColor="#246B61" />  
            <SortedDescendingCellStyle BackColor="#D4DFE1" />  
            <SortedDescendingHeaderStyle BackColor="#15524A" />  
        </asp:GridView>  
        <br />  
        <h4 style="color: White">  
            Tax Master Table Records Before joining  
        </h4>  
        <asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
            <AlternatingRowStyle BackColor="White" />  
            <EditRowStyle BackColor="#7C6F57" />  
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
            <RowStyle BackColor="#E3EAEB" />  
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
            <SortedAscendingCellStyle BackColor="#F8FAFA" />  
            <SortedAscendingHeaderStyle BackColor="#246B61" />  
            <SortedDescendingCellStyle BackColor="#D4DFE1" />  
            <SortedDescendingHeaderStyle BackColor="#15524A" />  
        </asp:GridView>  
        <br />  
    </div>  
    <h4 style="color: White">  
        Tax and Product Table Records after joining  
    </h4>  
    <asp:GridView ID="GridView3" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">  
        <AlternatingRowStyle BackColor="White" />  
        <EditRowStyle BackColor="#7C6F57" />  
        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
        <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />  
        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />  
        <RowStyle BackColor="#E3EAEB" />  
        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />  
        <SortedAscendingCellStyle BackColor="#F8FAFA" />  
        <SortedAscendingHeaderStyle BackColor="#246B61" />  
        <SortedDescendingCellStyle BackColor="#D4DFE1" />  
        <SortedDescendingHeaderStyle BackColor="#15524A" />  
    </asp:GridView>  
    <br />  
    </form>  
</body>  
</html>

Now let's use the scenario that we have one Product table and one Tax Master Table and we want to join these tables depending on Tax Id, so let us create the tables instead of going to the DataBase.

Open the Default.aspx.cs Page and provide the following code to create the Product table as in the following:

//creating Product DataTable  
           DataTable dt = new DataTable();  
           DataRow dr = null;  
           dt.TableName = "Product";  
           dt.Columns.Add("Id",typeof(int));  
           dt.Columns[0].AutoIncrementSeed=1;  
           dt.Columns[0].AutoIncrement = true;  
           dt.Columns.Add("Product Name");  
           dt.Columns.Add("Brand Name");  
           dt.Columns.Add("Tax Id",typeof(int));  
           dr = dt.NewRow();  
           dr["Product Name"] = "Laptop";  
           dr["Brand Name"] = "Samsung";  
           dr["Tax Id"] = 1;  
           dt.Rows.Add(dr);  
           DataRow dr1 = null;  
           dr1 = dt.NewRow();  
           dr1["Product Name"] = "Mouse";  
           dr1["Brand Name"] = "Dell";  
           dr1["Tax Id"] = 1;  
           dt.Rows.Add(dr1);  
      
      
           DataRow dr2 = null;  
           dr2 = dt.NewRow();  
           dr2["Product Name"] = "Mobile";  
           dr2["Brand Name"] = "Apple";  
           dr2["Tax Id"] = 1;  
           dt.Rows.Add(dr2);  
      
           DataRow dr3 = null;  
           dr3 = dt.NewRow();  
           dr3["Product Name"] = "Book";  
           dr3["Brand Name"] = "C# Corner Press";  
           dr3["Tax Id"] = 2;  
           dt.Rows.Add(dr3);

The Product table records will be such as follows:

Now let us create the Tax Master table using the following code:

//creating Tax DataTable  
           DataTable dtTax = new DataTable();  
             
           dtTax.TableName = "taxmaster";  
           dtTax.Columns.Add("Tax Id", typeof(int));  
           dtTax.Columns[0].AutoIncrementSeed = 1;  
           dtTax.Columns[0].AutoIncrement = true;  
           dtTax.Columns.Add("Product Category");  
           dtTax.Columns.Add("Charge", typeof(int));  
           DataRow drtax = null;  
           drtax = dtTax.NewRow();  
           drtax["Product Category"] = "Electronics";  
           drtax["Charge"] = 10;  
           dtTax.Rows.Add(drtax);  
      
           DataRow drtax1 = null;  
           drtax1 = dtTax.NewRow();  
           drtax1["Product Category"] = "Educational";  
           drtax1["Charge"] = 8;  
           dtTax.Rows.Add(drtax1);

Now the Tax Master table records will be such as follows:

Now let us see the preceding two tables, one is Product and the other is Tax Master. From them we want to display the tax charge of each product and tax charges are in the Tax Master table, so to get it we need to join the preceding two tables using LINQ .

So let us write the code to join the Product and Tax Master tables as in the following:

//joining Product and Tax DataTable   
  
        var JoinResult = (from p in dt.AsEnumerable()  
                          join t in dtTax.AsEnumerable()  
                          on p.Field<int>("Tax Id") equals t.Field<int>("Tax Id")  
                          select new  
                          {  
                              ProductName = p.Field<string>("Product Name"),  
                              BrandName = p.Field<string>("Brand Name"),  
                              ProductCategory = t.Field<string>("Product Category"),  
                              TaxCharge = t.Field<int>("Charge")  
  
  
                          }).ToList();

Now create a function to name the LINQResultToDataTable that converts the LINQ result to a table as in the following

public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)    
        {    
            DataTable dt = new DataTable();    
    
               
            PropertyInfo[] columns = null;    
    
            if (Linqlist == null) return dt;    
    
            foreach (T Record in Linqlist)    
            {    
                    
                if (columns == null)    
                {    
                    columns = ((Type)Record.GetType()).GetProperties();    
                    foreach (PropertyInfo GetProperty in columns)    
                    {    
                        Type IcolType = GetProperty.PropertyType;    
    
                        if ((IcolType.IsGenericType) && (IcolType.GetGenericTypeDefinition()    
                        == typeof(Nullable<>)))    
                        {    
                            IcolType = IcolType.GetGenericArguments()[0];    
                        }    
    
                        dt.Columns.Add(new DataColumn(GetProperty.Name, IcolType));    
                    }    
                }    
    
                DataRow dr = dt.NewRow();    
    
                foreach (PropertyInfo p in columns)    
                {    
                    dr[p.Name] = p.GetValue(Record, null) == null ? DBNull.Value : p.GetValue    
                    (Record, null);    
                }    
    
                dt.Rows.Add(dr);    
            }    
            return dt;    
        }

The preceding function takes the LINQ query result and converts it into the Data Table. If you want to learn more about IEnumerable and the preceding function, refer to the following article of mine:

IEnumerable Interface in C#

Now we have a common DataTable after joining the Product and Tax Master tables, so let us bind three grid views from the three Data Tables so we can understand the difference. The entire code of Defualt.aspx.cs will be such as follows.

Now run the application, then the GirdView records will be such as follows before joining:

Now see the third GiridView records will be such as follows after joining:

From the preceding example it's clear that we can join two DataTables using LINQ.

Notes

You can also bind the DataTables from the database.

Summary
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.

Compilemode

Featured Posts

Learn IoT : What is Internet of Things (IoT)?

Getting Started with Azure IoT Hub

Getting Started with Azure IoT Hub Device Provisioning Service

Convert DataTable To Generic List Using LINQ C#

Related Articles

Convert DataTable To Generic List Using AsEnumerable In ASP.NET MVC

Convert DataTable To Generic List Using LINQ In ASP.NET MVC

How To Read XML File Using LINQ

Join Two DataTables Using LINQ In ASP.Net

Trending up

Categories

www.CodeNirvana.in