Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Linq to Objects

Introduction

Previously I written article on .net framework component LINQ to SQL, I specified in that article I will continue writing some more articles on LINQ features.

In this article I will describe LINQ to Objects in detail with some sample examples.

What is LINQ to Objects?

LINQ to objects provides the ability to query IEnumerable or IEnumerable<T> collection directly, without the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML.

You can use LINQ to query any enumerable collections such as List<T>, Array, or Dictionary<TKey, TValue>. The collection may be user-defined or may be returned by a .NET Framework API.

Why LINQ to Objects?

In classic old programming you had to write complex foreach loops that specified how to retrieve data from a collection.

Linq approach provides declarative code that describes what you want to retrieve.

Advantages using LINQ to Objects:

1. Easy and readable.
2. They provide powerful filtering, ordering, and grouping capabilities with a minimum of application code.
3. Removes unwanted complex code, reduce code redundancy.  
4.  Reusable code with some modifications or no modifications.

5. LINQ queries provides following advantages on old foreach loop:

 a. More concise, easy and readable, especially when filtering multiple conditions.
 b. Provide powerful filtering, ordering, and grouping features with reduced code.
 c. They can be ported to other data sources with little or no modification.

Generic list Linq to Objects Example:

In following example I used linq to objects query on categories generic list to retrieve only valid product categories.

  //Generic Categories list data
  List<ProductCategory>categories = ProductDataAccess.GetProductCategories();


 
  //Returns Records (products) that satisfy where condition Category name not empty
  var MobileCategory = from c in categories
                       where c.CategoryName != ""
                       select c;

 

IEnumerable collection linq to objects Example:

In following example I used linq to objects query on products IEnumerable collection to retrieve only valid products.
var ValidProductsByAscending = from p in products
                               where p.ProductName != string.Empty
                               orderby p.ProductName ascending
                               select p;

Query a String Array using LINQ to Objects:

In following example LINQ to Objects is used to query this string array to find and return a subset of the array in the form of all students with name contains with the word “kendre”.

string[] Students = { "Shouarya Kendre", "Anushka Kendre", "Aditya Kendre",
                   "Rudra Sarode", "Sanskruti Kendre", "Akshara Kendre","Sharad Sangle"}

 

var StudentsWithSurnameKendre = from s in Students
           where s.Contains("Kendre")
           select s;

 

Query an ArrayList using LINQ to objects:

The following example shows a simple query over an ArrayList. Linq to objects query retrieves those students who scored min 60 marks in all subjects.

 

ArrayList arrList = new ArrayList();
           arrList.Add(
               new Student
               {
                   FirstName = "Shouarya",
                   LastName = "Kendre",
                   Scores = new int[] { 98, 92, 81, 60 }
               });
           arrList.Add(
               new Student
               {
                   FirstName = "Rudra",
                   LastName = "Sarode",
                   Scores = new int[] { 75, 84, 91, 39 }
               });
           arrList.Add(
               new Student
               {
                   FirstName = "Sanskruti",
                   LastName = "Kendre",
                   Scores = new int[] { 88, 94, 65, 91 }
               });
           arrList.Add(
               new Student
               {
                   FirstName = "Aditya",
                   LastName = "Mundhe",
                   Scores = new int[] { 97, 89, 85, 82 }
               });

 

   var query = from Student student in arrList
              where  student.Scores[0] > 59 
                        && student.Scores[1] > 59 
                        &&student.Scores[2] > 59 
                        && student.Scores[3] > 59
              select student;

LINQ to SQL

Introduction:

From last Week, I have started to write a few articles to explain LINQ to SQL, LINQ to Entities, LINQ to XML, LINQ to objects, and LINQ to dataset.

In this article I will describe LINQ to SQL in detail with some sample examples.

Definition

LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.

In Detail LINQ TO SQL:

Some people says LINQ (Language Integrated Query) is to replaces the traditional sql query execution process.

But reality is that it doesn’t only manipulate database, but it can also be used to manipulate array/list collections, XML and objects.

In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.

Following diagram explains how LINQ interacts with sql server database


LINQ to SQL Supports:

1. Transactions
2. Views
3. Stored Procedures
4. User-defined functions

It also provides an easy way to integrate data validation and business logic rules into your data model, and supports single table inheritance in the object model.

LINQ to SQL programming:

How to use stored procedure using LINQ to SQL?

LINQ to SQL maps output parameters to reference parameters, and for value types declares the parameter as nullable.
The following example takes a single input parameter (the UserID) and returns an out parameter (the total Leaves for that Employee).

 

T-SQL Stored procedure:

Create PROCEDURE [dbo].[UserTotalLeaves_SP]
            -- Add the parameters for the stored procedure here
            @userId   int,
            @TotalLeaves  int output
 
AS
BEGIN
SELECT @TotalLeaves= sum(leaves) from LeaveEntitlement where UserId=@userId
END

 

Execute same stored procedure using LINQ TO SQL:

 [Function(Name = "dbo.UserTotalLeaves_SP")]
 [return: Parameter(DbType= "Int")]
 [public int UserLeaveTotal([Parameter(Name = "userId", DbType = "int")] int userId,    [Parameter(Name = "TotalLeaves", DbType = "int")] ref System.Nullable<int> TotalLeaves)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), userId, TotalLeaves);
            TotalLeaves =((System.Nullable<decimal>)(result.GetParameterValue(1)));
            return ((int)(result.ReturnValue));
        }

Querying database using LINQ TO SQL:

 I have created DataContext class in .net and named something like ‘ApplicationServices (These DataContext classes are now responsible for .NET To Database communications). In the linq query syntax, this object will be used to present the database.

 To query a database using LINQ to SQL, we first need to construct a DataContext object, like this

ApplicationServices BlogDbContext = new ApplicationServices();

Retrieve records from table:

Now we can use this LINQ query syntax to retrieve records from the database table ,Following example

Example:

        public List<Designation> GetEmployeeAllDesignations()
        {
            List<Designation> Designations = new List<Designation>();
            using (ApplicationServices DbContext = new ApplicationServices())
            {
               // var LeaveTypes = (from d in DbContext.Designation
                Designations = (from d in DbContext.Designation
                                             where d.DesId != 0
                                             orderby d.DesId
                                             select d).ToList();
            }
            return Designations;
        }

 

I will explain LINQ to SQL CRUD (insert, update, delete) operation example in next article.

LINQ TO SQL JOIN Example:

LINQ to SQL join example to retrieve Leave Types

Example:

Following example returns leave types by added user details using LINQ to SQL inner joins.

We can perform Inner join, outer joins and some custom joins with the help of LINQ to SQL.

public List<LeaveType> GetAllLeavTypes()
        {
 
            List<LeaveType> Types = new List<LeaveType>();
            using (ApplicationServices BlogDbContext = new ApplicationServices())
            {
                var LeaveTypes = (from l in BlogDbContext.LeaveType
                                  join e in BlogDbContext.User
                                  on new { PID = l.AddedBy } equals new { PID = e.UserId }
                                  select new
                                  {
                                      TypeId = l.TypeId,
                                      LeaveTitle = l.LeaveTitle,
                                      Description = l.Description,
                                      AddedBy = l.AddedBy,
                                      UpdateBy = l.UpdateBy,
                                      CreatedDate = l.CreatedDate,
                                      UpdateDate = l.UpdateDate,
                                      CreatedBy = e.UserName
                                  }
 
                     ).ToList();
                foreach (var l in LeaveTypes)
                {
                    LeaveType type = new LeaveType();
                    type.CreatedBy = new aspnet_Users();
                    type.TypeId = l.TypeId;
                    type.AddedBy = l.AddedBy;
                    type.LeaveTitle = l.LeaveTitle;
                    type.Description = l.Description;
                    type.CreatedDate = l.CreatedDate;
                    type.CreatedBy.UserName = l.CreatedBy;
                    type.UpdateBy = l.UpdateBy;
                    type.UpdateDate = l.UpdateDate;
                    Types.Add(type);
                }
 
            }
            return Types;
        }

 

Subqueries In LINQ

Subqueries using LINQ

Introduction:

Previously I have written article on sql sub quires and sql sub-query types, many users suggested me in email to write article on Linq sub-queries.

Considering users valuable suggestion, in this article I will describe in detail sub queries using linq.

Sql sub-queries:

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

Sub query types:

1. Self contained sub query.

 Sql self contained sub query categorized in three types
a. Self contained scalar sub query
b. Self contained multi valued query
c. Table-valued sub query

2. Co-related sub query.

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

Sql equivalent  linq sub queries :

Linq Self contained sub query

If we want to get the equivalent of SQL Self contained subquery, you need to construct an inner query first, and then use the Contains () method in outer query or main query.

Example:

Following example is multi valued subquery that returns single column but it produce multiple values for the column category id.
  //Generic product list data
   List<Product> ListProducts =ProductDataAccess.GetProducts();
  //Generic Categories list data
   List<ProductCategory> categories = ProductDataAccess.GetProductCategories();

 

  //Returns Records (products) that satisfy where condition Categoryname='Mobile'
   var innerQuery = from c in categories
                             where c.CategoryName == "Mobile" select c.Id;
    var OuterQuery = from p in ListProducts
                             where innerQuery.Contains(p.CategoryId)
                             select p;


Linq Co-related sub query

With linq query you can achieve correlated functionality using where condition with two linq queries. 

Example:

In below example Outer linq query CategoryId is compared with inner linq(2nd linq query )CategoryId.
Linq Inner qury return categoryid if categoryName matches where condition and linq inner query cetagoryid matches with outer query.
Finally linq nner query returned categoryid value is used in outer select statement.
//Returns only Records (products) that satisfy where condition Categoryname='Mobile' 
var CellPhones = (from item in ListProducts
                where item.ProductName != string.Empty
                where item.CategoryId == (from subitem in categories
                where subitem.CategoryName.Trim() == "Mobile"
                select subitem.Id).First() select item).ToList();

 

Conclusion:

LINQ are set features introduced by Microsoft that extends powerful query capabilities to the language syntax of C# and VB.

We can achieve all sql equivalent sub-queries in using Linq expression and linq features.