Applying join on two datatable in asp.net (c#) using LINQ | using join, where clause in LINQ

Applying join on two datatable in asp.net (c#) using LINQ.

Below is the Query :

1.LINQ Join Query:

DataTable result = (from t1 in SomeDatatable1.AsEnumerable()
                    join t2 in SomeDatatable2.AsEnumerable() on t1.Field<string>("EmpId") equals                                                                                       t2.Field<string>("EmpId")
                    select t1).CopyToDataTable();


2.LINQ Join with Where clause Query:

DataTable result = (from t1 in SomeDatatable1.AsEnumerable()
                    join t2 in SomeDatatable2.AsEnumerable() on t1.Field<string>("EmpId") equals                                                                                       t2.Field<string>("EmpId")
                    where t2.Field<string>("EmpId") == "100"
                    select t1).CopyToDataTable();

using where condition like sql in .net | Linq query to fetch rows based on where condition

HI lets see how to fetch records in LINQ based on the where condition specified.

Example:

var query = from r in dtSampleDataTable.AsEnumerable()

                        where r.Field<string>("Salary") == "1000"

                        select r;

            if (query.Count() > 0)

            {

                DataTable dt1 = query.CopyToDataTable(); //fetching filtered rows to a new datatable.

                object sumSalary = dt1.Compute("Sum(Salary)", "");

            }

string s = sumSalary.ToString();

LINQ startswith,EndsWith example | Asp.net LINQ Query to get or fetch rows from a datatable for a coloumn starting with any letter,word,Number

Hi in this post i will show using LINQ query how to fetch rows from a dataTable for a column starting or ending with any letter,word,Number etc.

Example:

1. here i will fetch rows from a datatable for Names Ending with 'ar'

var query = dtSampleDatatable.AsEnumerable()
                 .Where(row => row.Field<string>("Name").EndsWith("ar"));

            DataTable dtSampleDatatableNew = new DataTable();
            dtSampleDatatableNew = query.CopyToDataTable(); // Adding filtered rows to a new datatable.

2. To fetch Names starting with 'ar'

var query = dtSampleDatatable.AsEnumerable()
                 .Where(row => row.Field<string>("Name").StartsWith("ar"));

            DataTable dtSampleDatatableNew = new DataTable();
            dtSampleDatatableNew = query.CopyToDataTable(); // Adding filtered rows to a new datatable.

Using ADO.net Entity framework in asp.net example | Bind grid using ado.net Entity Framework | LINQ to ado.net entity data model

Hi in this post i will show how to use ADO.NET entity data model in asp.net.

1. Go to your web application and Add New Item from the solution explorer called ADO.Net Entity Data Model. Giving the file name as myFirstModel.edmx

2.Then it will enter into Entity Data Model Wizard from there select "Generate from database" option. click on next and enter the database connection login details.

3. Then you will see a entity connection string getting generated. Save the entity connection string name as Test_DBEntities and click on next.

4. From the next screen choose the data objects you want to use and finish to exit the data model wizard.

5. After u click on finish, u may get a security warning just click on ok.

6. Now using ado.net entity framework we will bind a gridview

7. Below is the code :

protected void BindGrid()
        {
            Test_DBEntities db = new Test_DBEntities();

            var GridData= from a in db.carWorlds select a;

            GridView1.DataSource = GridData.ToList();
            GridView1.DataBind();
        }

find duplicate records in a datatable using LINQ | Example to find duplicate values from datatable or dataset using Linq Query

hi in this post i will show to find duplicate records in a datatable or dataset using LINQ.

Example:

Below is the sample employee table which i will take it into a datatable and then will search for duplicate employee names present inside the table using a LINQ query.

1. EMP Table

Code :

 public void checkDuplicate()
    {
        string conStr = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString.ToString();
        StringBuilder s = new StringBuilder();
        SqlConnection s1 = new SqlConnection(conStr);
        s1.Open();

        string queryString = "SELECT * FROM employee";
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, s1);

        DataSet employee = new DataSet();
        adapter.Fill(employee, "employee");

        var duplicateRecords = employee.Tables[0].AsEnumerable()
                 .GroupBy(r => r["EmpName"]) //coloumn name which has the duplicate values
                 .Where(gr => gr.Count() > 1)
                 .Select(g => g.Key);


        foreach (var d in duplicateRecords)
        {
           s.Append("," + d.ToString());
        }

         Response.Write("<script language='javascript'>alert('Duplicates Names: "+ s.ToString() +"');</script>");
         s1.Close();
        
    }

Output :

Output