Get ODD Number using LINQ in C#.Net

static bool GetOdd(int number)
        {
            if (number % 2 != 0)
                return true;
            else
                return false;
        }

var result = from r in
                         Enumerable.Range(1, 50).Where(GetOdd)
                         select r;
            foreach (var r in result)
                Console.WriteLine(r);
            Console.ReadKey(true);

Insert ,Update ,Delete and Display in Datagridview using LINQ To SQL in C#.Net

Insert ,Update ,Delete and Display in Datagridview using LINQ To SQL in C#.Net

Create tables in SQL server

tbl_department_master
depId  int -- Primary Key -- Set Identify is true
DepName -- varchar(Max)

tbl_Position_Master
PatId int -- Primary key
Patname-- varchar(Max)

Add LINQ To SQL in the your project
Go To Server Explore
Add Data Connection
Drag Tables into LIQToSQL Class
Create user Control

Add User Control in your Project.
Add Datagridview and Two buttons on the User Control
Declare Variables ,Data Context  and Events

 DataClasses1DataContext objDC = null;
 tbl_Department_Master objDM = null;
 tbl_Position_Master objPM = null;
public event EventHandler SaveClicked;
public event EventHandler CloseClicked;

/// To Display Data

 public void Displaydata(string mode)
        {
            switch (mode)
            {
                case "Department":
                    DisplayData("Department");
                    break;
                case "Position":
                    DisplayData("Position");
                    break;
            }
        }
 private void DisplayData(string mode)
        {
            objDC = new DataClasses1DataContext();
            switch (mode)
            {
                case "Department":
                    var query = from q in objDC.tbl_Department_Masters
                                select q;
                    dataGridView1.DataSource = query;
                    break;
                case "Position":
                    var Posquery = from Pq in objDC.tbl_Position_Masters
                                   select Pq;
                    dataGridView1.DataSource = Posquery;
                    break;
            }        
        }

/// Delete Data

 private void DeleteData(string mode)
        {
            objDC = new DataClasses1DataContext();
            switch (mode)
            {
                case "Department":
                    objDM = new tbl_Department_Master();
                    if (objDM != null)
                    {
                        var Delet = from d in objDC.tbl_Department_Masters
                                    select d;
                        foreach (var details in Delet)
                        {
                            objDC.tbl_Department_Masters.DeleteOnSubmit(details);
                        }
                        objDC.SubmitChanges();
                    }
                    break;
                case "Position":
                    objPM = new tbl_Position_Master();
                    if (objPM != null)
                    {
                        var Delet = from d in objDC.tbl_Position_Masters
                                    select d;
                        foreach (var details in Delet)
                        {
                            objDC.tbl_Position_Masters .DeleteOnSubmit(details);
                        }
                        objDC.SubmitChanges();
                    }
                    break;
            }
        }

/// Save Data

public void SaveData(string mode)
        {
            switch (mode)
            {
                case "Department":
                    DeleteData("Department");
                    objDC = new DataClasses1DataContext();
                    for (int index = 0; index < dataGridView1.Rows.Count - 1; index++)
                    {
                        objDM = new tbl_Department_Master();
                        objDM.depId = Convert.ToInt32(dataGridView1.Rows[index].Cells[0].Value);
                        objDM.DepName = Convert.ToString(dataGridView1.Rows[index].Cells[1].Value);
                        objDC.tbl_Department_Masters.InsertOnSubmit(objDM);
                        objDC.SubmitChanges();
                    }
                    DisplayData("Department");
                    break;
                case "Position":

                    DeleteData("Position");
                    objDC = new DataClasses1DataContext();
                    for (int index = 0; index < dataGridView1.Rows.Count - 1; index++)
                    {
                        DataGridViewRow objRowIndex = new DataGridViewRow();
                        objPM = new tbl_Position_Master();
                        objPM.PatId = Convert.ToInt32(dataGridView1.Rows[index].Cells[0].Value);
                        objPM.Patname = Convert.ToString(dataGridView1.Rows[index].Cells[1].Value);
                        objDC.tbl_Position_Masters.InsertOnSubmit(objPM);
                        objDC.SubmitChanges();
                    }
                    DisplayData("Position");
                    break;
            }
        }

Call this User Control in your form:

Add the code in your code Page

private void DisplayData(string mode)
        {
          switch (mode)
            {
                case "Department":
                    ucDepartment1.Displaydata("Department");
                    break;
                case "Position":
                    ucDepartment1.Displaydata("Position");
                    break;
            }

        }

        private void FrmDepartment_Load(object sender, EventArgs e)
        {
            DisplayData("Position");
        }

        private void ucDepartment1_Load(object sender, EventArgs e)
        {

        }

        private void ucDepartment1_CloseClicked(object sender, EventArgs e)
        {
            ((Form)this.TopLevelControl).Close();
        }

        private void ucDepartment1_SaveClicked(object sender, EventArgs e)
        {
            ucDepartment1.SaveData("Position");

        }

Define Lambda Expression With LINQ

LINQ queries can be written in two syntaxes:

•  General Query Syntax
•  Lambda Expression Syntax

Syn for Lambda Expression:

DataSource.Clause(DataAliasName => Expression)

Define Mandatory Clause in LINQ

1) from clause 2)in clause3) select clause

Define Clause in LINQ

1) from clause 2)in clause 3)let clause 4)where clause 5) orderby clause 6)select clause 7)group by clause

Use of System.XML.XLINQ

It contains classes to provide functionality to LINQToXML

Use of System.Data.DLINQ

Provide functionality to Work with LINQToSQL.

Qualifiers in LINQ

They are LINQ Extension Methods return Boolean Value.

1. All
2. Any
3. Contains
4. SquenceEqual

Main Components in LINQ

There are three Main Components in LINQ

1.  Standard Query Operator
2.  Language Extension
3.  LINQ Provider

Data Table Function for LINQToSQL

public static DataTable AsDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
{
// Use reflection to get property names, to create table, Only first time, others will follow if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}

LINQ -- I

1.      Difference between First vs FirstOrDefault in LINQ

•    First: Gives first record from the collection, if collection doesn't have any object then throws error.
• FirstOrDefault: FirstOrDefault extension method is used to retrieve the first element from the collection, if not then gives null.

2.      Difference between Last vs LastOrDefault in LINQ?

• Last:Last extension method gives last element from the collection. If there is no element in the collection, it throws error.
• LastOrDefault:LastOrDefault extension method gives the last element form the collection, if not gives null.

3.     When will we use ThenBy in LINQ?

               ThenBy is used with OrderBy or OrderByDescending. It is used to apply sorting on already sorted collection using OrderBy or OrderByDescending.

4.     Where to Use SequenceEqual ?

                   SequenceEqual extension method is used to determine if both collections sequence are equal.

5.     what is mean by SkipWhile?

            SkipWhile extension method is used to skip the elements that satisfies the given filter criteria till it doens't get any element that doesn't satisfy.

6.     How to use Take Method in LINQ?

          Take extension method is used to take specified number of elements from the collection.

7.     What is mean by Lambda expression?

• A lambda expression is an function that contain many expressions and statements.
• Lambda expression used to create delegates or expression tree types.
• All lambda expressions use the lambda operator =>, which is read as "goes to".
• The left side of the lambda operator specifies the input parameters.
• The right side holds the expression or statement block.

8.     What is ORM?

         ORM stands for Object-Relational Mapping. Sometimes it is called O/RM, or O/R mapping. It is a programming technique that contains a set of classes that map relational database entities to objects in a specific programming language.

9.     What is ODBC?

        ODBC (Open Database Connectivity) was developed to unify all of the communication protocols for various RDBMSs. ODBC was designed to be independent of programming languages, database systems, and Operating Systems. So with ODBC, an application could communicate with different RDBMSs by using the same code, simply by replacing the underlying ODBC drivers.

10. What is LINQ To SQL?

•  LINQ to SQL is a component of the .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.
• LINQ to SQL fully supports transactions, views, Stored Procedures, and 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, ADO.NET SqlClient adapters are used to communicate with real SQL Server databases.

11. Difference between LINQ To SQL and LINQ To Object?

·        LINQ to SQL needs a Data Context object. The Data Context object is the bridge between LINQ and the database. LINQ to Objects doesn’t need any intermediate LINQ provider or API.

·        LINQ to SQL returns data of type IQueryable<T> while LINQ to Objects returns data of type IEnumerable<T>.

·        LINQ to SQL is translated to SQL by way of Expression Trees, which allow them to be evaluated as a single unit and translated to the appropriate and optimal SQL statements. LINQ to Objects does not need to be translated.

·        LINQ to SQL is translated to SQL calls and executed on the specified database while LINQ to Objects is executed in the local machine memory.

12. What is mean by Entity Framework?

·        ADO.NET Entity Framework (EF) was first released with Visual Studio 2008 and .NET Framework 3.5 Service Pack 1. So far, many people view EF as just another ORM product from Microsoft, though by design it is supposed to be much more powerful than just an ORM tool.

·        The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL), the actual storage model is expressed in the Storage Schema Definition Language (SSDL), and the mapping in between is expressed in the Mapping Schema Language (MSL).

13. Difference between LINQ To SQL and LINQ To Entities?

·        LINQ to Entities applications work against a conceptual data model (EDM). All mappings between the languages and the databases go through the new EntityClient mapping provider. The application no longer connects directly to a database or sees any database-specific construct; the entire application operates in terms of the higher-level EDM model.

14. What are all the Operator Type  in LINQ To Object?

·        Aggregation Aggregate, Average, Count, LongCount, Max, Min, Sum

·        Conversion Cast, OfType, ToArray, ToDictionary, ToList, ToLookup, ToSequence

·        Element DefaultIfEmpty, ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault

·        Equality EqualAll

·        Generation Empty, Range, Repeat

·        Grouping GroupBy

·        Joining GroupJoin, Join

·        Ordering OrderBy, ThenBy, OrderByDescending, ThenByDescending, Reverse

·        Partitioning Skip, SkipWhile, Take, TakeWhile

·        Quantifiers All, Any, Contains

·        Restriction Where

·        Selection Select, SelectMany

·        Set Concat, Distinct, Except, Intersect, Union

LINQ Hint -2

Difference between First vs FirstOrDefault in LINQ?
First: Gives first record from the collection, if collection doesn't have any object then throws error.
FirstOrDefault: FirstOrDefault extension method is used to retrieve the first element from the collection, if not then gives null.

Difference between Last vs LastOrDefault in LINQ?
Last:Last extension method gives last element from the collection. If there is no element in the collection, it throws error.
LastOrDefault:LastOrDefault extension method gives the last element form the collection, if not gives null.

When will we use ThenBy in LINQ?
ThenBy is used with OrderBy or OrderByDescending. It is used to apply sorting on already sorted collection using OrderBy or OrderByDescending.

Where to Use SequenceEqual ?
SequenceEqual extension method is used to determine if both collections sequence are equal.

what is mean by SkipWhile?
SkipWhile extension method is used to skip the elements that satisfies the given filter criteria till it doens't get any element that doesn't satisfy.

How to use Take Method in LINQ?
Take extension method is used to take specified number of elements from the collection.

What is mean by Lambda expression?
A lambda expression is an function that contain many expressions and statements.
Lambda expression used to create delegates or expression tree types.
All lambda expressions use the lambda operator =>, which is read as "goes to".
The left side of the lambda operator specifies the input parameters.
The right side holds the expression or statement block.

What is ORM?
ORM stands for Object-Relational Mapping. Sometimes it is called O/RM, or O/R mapping. It is a programming technique that contains a set of classes that map relational database entities to objects in a specific programming language.
What is ODBC?
ODBC (Open Database Connectivity) was developed to unify all of the communication protocols for various RDBMSs. ODBC was designed to be independent of programming languages, database systems, and Operating Systems. So with ODBC, an application could communicate with different RDBMSs by using the same code, simply by replacing the underlying ODBC drivers.
What is LINQ To SQL?
LINQ to SQL is a component of the .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.
LINQ to SQL fully supports transactions, views, Stored Procedures, and 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, ADO.NET SqlClient adapters are used to communicate with real SQL Server databases.



Difference between LINQ To SQL and LINQ To Object?

    LINQ to SQL needs a Data Context object. The Data Context object is the bridge between LINQ and the database. LINQ to Objects doesn’t need any intermediate LINQ provider or API.
    LINQ to SQL returns data of type IQueryable<T> while LINQ to Objects returns data of type IEnumerable<T>.
    LINQ to SQL is translated to SQL by way of Expression Trees, which allow them to be evaluated as a single unit and translated to the appropriate and optimal SQL statements. LINQ to Objects does not need to be translated.
    LINQ to SQL is translated to SQL calls and executed on the specified database while LINQ to Objects is executed in the local machine memory.




What is mean by Entity Framework?
ADO.NET Entity Framework (EF) was first released with Visual Studio 2008 and .NET Framework 3.5 Service Pack 1. So far, many people view EF as just another ORM product from Microsoft, though by design it is supposed to be much more powerful than just an ORM tool.
The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL), the actual storage model is expressed in the Storage Schema Definition Language (SSDL), and the mapping in between is expressed in the Mapping Schema Language (MSL).


Difference between LINQ To SQL and LINQ To Entities?
 LINQ to Entities applications work against a conceptual data model (EDM). All mappings between the languages and the databases go through the new EntityClient mapping provider. The application no longer connects directly to a database or sees any database-specific construct; the entire application operates in terms of the higher-level EDM model.


Difference between SQL Query and LINQ ?
http://www.codeproject.com/Articles/136028/Learn-SQL-to-LINQ-Visual-Representation


What are all the Operator Type  in LINQ To Object?

Operator Type    Operator Name
Aggregation    Aggregate, Average, Count, LongCount, Max, Min, Sum
Conversion    Cast, OfType, ToArray, ToDictionary, ToList, ToLookup, ToSequence
Element    DefaultIfEmpty, ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault
Equality    EqualAll
Generation    Empty, Range, Repeat
Grouping    GroupBy
Joining    GroupJoin, Join
Ordering    OrderBy, ThenBy, OrderByDescending, ThenByDescending, Reverse
Partitioning    Skip, SkipWhile, Take, TakeWhile
Quantifiers    All, Any, Contains
Restriction    Where
Selection    Select, SelectMany
Set    Concat, Distinct, Except, Intersect, Union

LINQ To Object --Example -2

Today, we will see how to use Order class in LINQ.

Create a One Class Called : ClsEmployee

Three Properties in the Class.

1)EmpId2)Name3)Salary

public string Name { get; set; } public string EmpID { get; set; } public int Salary { get; set; }

Create a One form

Drag and Drop one Button.

Button name is btn_Result.

Create a Method:

Method name is "InsertintoTable()

List objEmployee = new List { new ClsEmployees { EmpID = "1001" ,Name ="GANTEC", Salary =10000 }, new ClsEmployees { EmpID = "1002" ,Name ="IBM", Salary =15000 }, new ClsEmployees { EmpID = "1003" ,Name ="CTS", Salary =20000 }, new ClsEmployees { EmpID = "1004" ,Name ="WIPRO", Salary =30000 }, new ClsEmployees { EmpID = "1005" ,Name ="SATHYAM", Salary =40000 }, new ClsEmployees { EmpID = "1006" ,Name ="BUTTERFLY", Salary =50000 }, new ClsEmployees { EmpID = "1007" ,Name ="CON", Salary =10000 }, new ClsEmployees { EmpID = "1008" ,Name ="TVS", Salary =75000 }, new ClsEmployees { EmpID = "1010" ,Name ="TATA", Salary =90000 }, new ClsEmployees { EmpID = "1009 " ,Name ="GANTEC COR", Salary =100000 }, };

Call this method in button Click Event and Add below codes,

var OrderBy = (from emp in objEmployee select emp).OrderBy(x => x.Name);

Happy Coding!!!

LINQ To Object -- Example 1

 Count of Rows in the Table using LINQ
1) Create a One Class Called : ClsEmployee
Three Properties in the Class.
1)EmpId2)Name3)Salary
Code :
   public string Name { get; set; }
   public string EmpID { get; set; }
   public int Salary { get; set; }
2)  Create a One form
Drag and Drop one Button.
Button name is btn_Result.
Create a Method:

Method name is "InsertintoTable()

   List<ClsEmployees> objEmployee = new List<ClsEmployees>
            {
                new ClsEmployees { EmpID = "1001" ,Name ="GANTEC", Salary =10000 },
                new ClsEmployees { EmpID = "1002" ,Name ="IBM", Salary =15000 },
                new ClsEmployees { EmpID = "1003" ,Name ="CTS", Salary =20000 },
                new ClsEmployees { EmpID = "1004" ,Name ="WIPRO", Salary =30000 },
                new ClsEmployees { EmpID = "1005" ,Name ="SATHYAM", Salary =40000 },
                new ClsEmployees { EmpID = "1006" ,Name ="BUTTERFLY", Salary =50000 },
                new ClsEmployees { EmpID = "1007" ,Name ="CON", Salary =10000 },
                new ClsEmployees { EmpID = "1008" ,Name ="TVS", Salary =75000 },
                 new ClsEmployees { EmpID = "1010" ,Name ="TATA", Salary =90000 },
                 new ClsEmployees { EmpID = "1009" ,Name ="GANTEC COR", Salary =100000 },
             };
Call this method in button Click Event and Add below codes,
 var CountofRows = (from emp in objEmployee select emp.Salary).Count();
 MessageBox.Show("Total Records in Employee Table :" + CountofRows );
Result: "Total Records in Employee Table: 10" in the Message Box

LINQ Hints

LINQ :
  LINQ -- Language INtegrate Query.
  It supporst all .Net Language.
LINQ -- Data Provider:

1.    LINQ To Object
2.   LINQ To ADO.NET
3.   LINQ To XML

     LINQ To ADO.NET:

•              LINQ To SQL.
•              LINQ To Entity Framework.
•              LINQ To Dataset.

Query Basics:
         Query Expression consists of set of Clauses written in a declarative Syntax similar to SQL /XQuery.
  Query must:

•      Begin with from Clause.
•      End with select/Group clause.
•      Between one or more of following clauses using
•              Where,Join,from orderby,let,into.

Aggragate Clause:
    Select ,Distinct,Order bymTake,Takewhile,skip,skipwhile,wheremjoinmgroupbymlet,into

Two ways to write LINQ Query:

1.    Query syntax -- Use Query
2.    Method Syntax -- Uses Lambda Expression.

Lambda Expression:
    => Goes into Separate Parameters from method body.

LINQ To Object:

• Object supports either IEnumerable or Ienumerable<t>
• Array,ArrayList,Collection,Generic Collection.
• Using System.LINQ namespace.

LINQ To SQL:

•     Using System.Data.LINQ namespace.
•     Requires Entity  classes which you can build using the ORD.
•     ORD-- Object Relational Designer.
•     E-R data models will use for Other Database.

LINQ To Dataset:

•       Collections of data from ADO.NET and Put into Dataset or DataTable.