Introduction:

Previously I written two articles on linq features LINQ to SQL and Linq to Objects, I specified in first article I will continue writing some more articles on LINQ features.

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

 What is LINQ to dataset?

LINQ to Dataset provides the easier and faster approach to query over data cached in a DataSet object.

LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources.

For example querying data on actual data and intermediate cached data source in financial web application, such scenarios required to create financial reports.

Following diagram explain relationship between LINQ to Dataset to ADO.NET 2.0 and Database.

Why LINQ to Dataset?

LINQ to Dataset allows developers to write queries from the programming language itself, instead of using separate query language.

Programming query languages who can now take advantage of the compile-time syntax checking, static typing, and IntelliSense support provided by the Visual Studio in their queries,this is especially useful for Visual Studio developers.

Advantages using Linq to Dataset:

1. Easy and readable.

2. No need to learn separate query languages like SQL (Structured query language), you can use visual studio programming language.
3.  You can use linq on two different datasets or you can retrieve some distinct values from dataset using Linq To Dataset.
4. You can use Sql queries to retrieve dataset but you can’t use SQL queries to retrieve value from dataset, in classic programming .net provides ado.net objects but with Linq to Dataset approach you can retrieve some distinct values from dataset.

 LINQ to Dataset Single table Queries:

The following obtains valid Products from the “C4Test” sample database using LINQ to dataset query.

  DataSet ProductsData = ProductDataAccess.GetProductDetails();  
  DataTable Products = ProductsData.Tables["Products"];

          var ValidProducts =
              from product in Products.AsEnumerable()
              where product.Field<string>("ProductName") != string.Empty
              && product.Field<int>("CategoryId") != 0
              select new
              {
                  ID =
                      product.Field<int>("ID"),
                  Name =
                      product.Field<int>("ProductName"),
                  Price =
                      product.Field<int>("Price")
              };

LINQ to Dataset Cross table query Example:

The following example performs a classic join (SQL join) of the Products and Categories tables from the “C4Test” sample database to obtain valid product details with categories.

LINQ framework provides two join operators, Join and GroupJoin. These operators perform equi-joins: that is, joins that match two data sources only when their keys are equal.

DataSet ProductsData = ProductDataAccess.GetProductDetails();
DataSet CategoriiesData = ProductDataAccess.GetProductCategories();
 
DataTable Products = ProductsData.Tables["Products"];
DataTable Categories = CategoriiesData.Tables["Categories"];

           var query =
               from product in Products.AsEnumerable()
               join category in Categories.AsEnumerable()
               on product.Field<int>("CategoryId") equals
                   category.Field<int>("ID")
               where product.Field<string>("ProductName") != string.Empty
               && product.Field<int>("CategoryId") != 0
               select new
               {
                   ID =
                       product.Field<int>("ID"),
                   Name =
                       product.Field<int>("ProductName"),
                   Category =
                       category.Field<string>("CategoryName"),
                   Price =
                       product.Field<int>("Price")
               };

Creating a DataTable from a Query (LINQ to Dataset):

The following example queries the Categories table for valid categories and uses the CopyToDataTable method to create a DataTable from that query.

 DataSet CategoriiesData = ProductDataAccess.GetProductCategories();
 DataTable Categories = CategoriiesData.Tables["Categories"];

  // Query the Categories table for only valid categories
  IEnumerable<DataRow> ValidCategories =
           from category in Categories.AsEnumerable()
           where category.Field<string>("CategoryName")!=string.Empty
           select category;
 
   // create a table from the query.
   DataTable NewValidDataTable = ValidCategories.CopyToDataTable<DataRow>();

Conclusion:

LINQ to Dataset allows developers to write queries from the programming language itself, instead of using separate query language.

 You can perform joins on different tables and you can retrieve only required data from different data sources.

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;

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;
        }