Tip Introduction to LINQ Queries

basma

Member
Joined
Feb 27, 2016
Messages
5
Programming Experience
3-5
LINQ (Language Integrated Query) is a feature introduced in Visual Studio 2008 used for querying and updating data. It can be used with databases (LINQ to SqL, LINQ to Dataset, LINQ to Entities), XML (LINQ to Xml), and with Objects (LINQ to Objects).

LINQ architecture in .NET:

LINQ has a 3 layered architecture, the upper layer consists of the languages extensions and the bottom layer consists of datasources that are IEnumerable or IQueryable. This architecture is figured in the image below:



Example of LINQ Query in C#:

There are three operations in a LINQ query:
1. Obtain the datasource.
2. Create the query.
3. Execute the query.
This is an example explaining these three operations:

class Test
{        
    static void Main()
    {
        //  1. Obtain data source.
        string[] towns = new string[4] { "Algeria", "Tunisia", "Egypt", "Morocco"};

        // 2. Query creation.
        var townQuery =
            from x in towns
            where x.Length < 7
            select x;

        // 3. Query execution.
        foreach (string s in townQuery)
        {
            Console.WriteLine(s);
            Console.ReadKey();
        }
    }
}

The result of this query is Egypt.

How to write LINQ Query in C#:

There are three ways in which we can write LINQ query in C#:

1. Using Query Syntax :

This is the recommended way of writing the query syntax to create a query expression like the above example.

2. Using Method Syntax:

It consists of expressing the query as a method call. These methods must be called last in any query because they return a single value. The following example shows a method call in query expression:
List<int> numbers1 = new List<int>() { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
List<int> numbers2 = new List<int>() { 15, 14, 11, 13, 19, 18, 16, 17, 12, 10 };
double average = numbers1.Average();

IEnumerable<int> concatenationQuery = numbers1.Concat(numbers2);
 

3. Mixed Query and Method Synatx:

The following example explains this way

int numCount1 =
    (from num in numbers1
     where num < 3 || num > 7
     select num).Count();

IEnumerable<int> numbersQuery =
    from num in numbers1
    where num < 3 || num > 7
    select num;

int numCount2 = numbersQuery.Count();


Reference:
http://www.freelearningdz.info/2016/03/introduction-to-linq-queries-c.html
 
Last edited:
TIP SELECT, INSERT, UPDATE, DELETE using LINQ to SQL in C#

This article offers a simple example of the use of LINQ TO SQL within Windows Forms in C# and how to perform Select, Insert, Update, and Delete operations on a Datatable.
This example is developed in Visual Studio 2010. First we add a DatagridView and we declare the ConnectionString



SqlConnection con = new SqlConnection(WindowsFormsApplication12.Properties.Settings.Default.Database1ConnectionString);

Then we add a LINQ to SQL classe to our project:



And drag and drop the DataTable (Employee) from Server Explorer to the LINQ to SQL classe.



Now we perform LINQ to SQL statements:
[h=4]1. SELECT statement:[/h] In Form Load event we add this code to display DataTable data:

private void Form1_Load(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}

[h=4]2. INSERT statement:[/h] In Insert button click event we add this code:

private void InsertButton_Click(object sender, EventArgs e)
{
    //create the new employee
    DataClasses1DataContext dc = new DataClasses1DataContext(con);
    Employee NewEmp = new Employee();
    NewEmp.Code = "2";
    NewEmp.Name = "bbb";
    NewEmp.Address = "bbb";

    //add the new employee to database
    dc.Employees.InsertOnSubmit(NewEmp);

    //save changes to database
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}
[h=4]3. UPDATE statement:[/h] Put this code in UpdateButton click event to perform updating database record:

private void UpdateButton_Click(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    //get employee to update
    Employee employee = dc.Employees.FirstOrDefault(emp => emp.Code.Equals("1"));

    //update employee
    employee.Name = "cccc";
    employee.Address = "cccc";

    //Save changes to Database.
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}
[h=4]4. DELETE statement:[/h] Add this code to DeleteButton click event:

private void DeleteButton_Click(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    //get the employee to delete
    Employee DeleteEmployee = dc.Employees.FirstOrDefault(emp => emp.Code.Equals("1"));

    //delete the employee
    dc.Employees.DeleteOnSubmit(DeleteEmployee);

    //save changes to database
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}


Reference:
SELECT, INSERT, UPDATE, DELETE using LINQ to SQL in C# - FreeLearningDz
 
Back
Top Bottom