DevStudent

LINQ to Entities

Construct ObjectQuery instance out of ObjectContext.
Create a query in C#.NET
Conversion of Linq operators and expressions into command trees.
Executing query with exceptions
Returning all the results to client directly.

ObjectContext used by Linq queries to connect to database.

Step by step example for linq to entities in Console applications:

In this example data from SQL Server database table is displayed, inserted, updated and deleted from console application

• Create database table using below query and insert some data
  create table tbl_student(sno int identity primary key, name varchar(20),course varchar(20))
  insert into tbl_student values('sudhir','abcd')
• Create a console application
  Right click on project name in solution explorer and click on add item
  Select ADO,NET ENTITY DATA MODEL.
  

• Click on Add
• Select generate from database and click on Next
• Click on Add new connection to create a new connection the database server
• Enter server name, select authentication, select database in which you created the database
• Click on test connection to check the connection. If it is passed then click on OK.
• Select the save entity connection settings check box and click on NEXT.

• Select the table and remember the Model namespace name which we use in the code
• Click on Finish.
  
  
  Example program in console application for CRUD operations with LINQ to Entities

LINQ to XML is used to query data from xml document using simple queries so that we can save data from xml in other formats.

Example program for LINQ to XML:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;

namespace LINQApp
{
    class Program
    {
        static void Main(string[] args)
        {
            //xml data in saved in string
            string myXML = @"<Students>
                       <StudentName>Amir</StudentName>
                       <StudentName>Salman</StudentName>
                       <StudentName>Sharukh</StudentName>
                       <StudentName>Hrithik</StudentName>
                       </Students>";

            XDocument xdoc = new XDocument();
            //Converting data in string to XDocument format.
            xdoc = XDocument.Parse(myXML);
            //Quering all elements under Students tag
            var result = xdoc.Element("Students").Descendants();
            //Printing Values inside all retreived nodes(Nodes inside Students node)
            foreach (XElement item in result)
            {
                Console.WriteLine("Student Name - " + item.Value);
            }

            Console.ReadKey();
        }
    }
}

LINQ to Objects queries will return data of IEnumerable type.

Example of LINQ to Object with array type:

using System;
using System.Linq;

namespace LINQApp
{
    class Program
    {
        static void Main(string[] args)
        {//Fruit names are created in an array object
            string[] fruits = { "Guava", "Orange", "Apple", "Mango", "Banana" };
            //All items from fruits array are queried using LINQ query
            var items = from f in fruits
                        select f;

            foreach (string s in items)
            {
                Console.Write(s + Environment.NewLine);
            }
            Console.ReadKey();
        }
    }
}

Output:

Guava
Orange
Apple
Mango
Banana


Example of LINQ to Objects using Custom type:

using System;
using System.Collections.Generic;
using System.Linq;

namespace LINQApp
{
    //Type with two properties Name and Password
    class StudentLogin
    {
        public string StudentName { get; set; }
        public string StudentPassword { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            //Student Object of List with StudentLogin type
            List<StudentLogin> Student = new List<StudentLogin>();
            //Adding 3 items to Student object
            Student.Add(new StudentLogin { StudentName="Sudhir",StudentPassword="1234"});
            Student.Add(new StudentLogin { StudentName = "Ram", StudentPassword = "Ramu" });
            Student.Add(new StudentLogin { StudentName = "Raj", StudentPassword = "Raju" });
            //Querying all items in Student Object
            var stud = from st in Student
                       select st;
            //Printing Name and passwords retreived using LINQ query
            foreach (var s in stud)
            {
                Console.WriteLine(s.StudentName +" , "+s.StudentPassword);
            }
            Console.ReadKey();
        }
    }

}

LINQ to SQL is also known as DLINQ. It is used to query data in SQL server database by using LINQ expressions.

Example:

Right click on data connections in server explorer.
Add connection -> enter servername, authentication, database name.
Test connection and click on ok.

Right click on your project in solution explorer
Click on Add item -> Add linq to Sql class(.dbml file) to your project

Open tables in new data connection added in server explorer and drag them to design view of linq to sql class.

Program in console application to display data from database:

using System;
using System.Linq;

namespace ConsoleApplication8
{
    class Program
    {
        static void Main(string[] args)
        {
            //You can see this context name in .dbml file in designer.cs class
            DataClasses1DataContext db = new DataClasses1DataContext("Data Source=.;Initial Catalog=stdntdb;Integrated Security=True");

            stdnttable newStdnt = new stdnttable();
            //Select command to retreive all rows from the table
            var q =
        from a in db.GetTable<stdnttable>()
        select a;
            //printing all rows of data
            foreach (var s in q)
            {
                Console.WriteLine("username: " + s.username + ", Password: " + s.pwrd);
            }

            //Select command with where condition to display names starting with sa
            Console.WriteLine("");
            var q1 =
       from a in db.GetTable<stdnttable>() where a.username.StartsWith("sa")
       select a;
            //printing all rows of data
            foreach (var s in q1)
            {
                Console.WriteLine("username: " + s.username + ", Password: " + s.pwrd);
            }

            Console.ReadKey();
        }
    }
}

LINQ stands for language integrated query.
It is a simple way to query SQL data, XML data or other C# objects.
It is written along with C# code hence it is known as language integrated query.
LINQ provides intellisense which helps in accurate and easy querying.
Debugging is available for writing complex logics easily.

System.Linq is the namespace used for LINQ.

Types of LINQ:

LINQ to Objects
LINQ to XML
LINQ to DataSeet
LINQ to SQL
LINQ to Entities

Example to query data from C#.NET array in console application:

using System;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //Created array with 5 numbers in it
            int[] array = new int[5] { 3, 7, 1, 7, 5 };
            //Var i contains data retreived using linq query
            //values greater than 5 are retreived
            var i = from s in array where s > 5 select s;
            //Retreived data is printed using for each loop
            foreach (int j in i)
            {
                Console.WriteLine(j);
            }
            Console.ReadKey();
        }
    }
}

Output:
7
7

//Model Table

 public partial class Tbl_ProductCategories
    {
        public Tbl_ProductCategories()
        {
             this.Tbl_products = new HashSet<Tbl_products>();
        }
   
        public long CId { get; set; }
        public string category { get; set; }
        public string stat { get; set; }
   
     }

//Data table object

 public class CategoriesDTO
    {
        public long CID { get; set; }
        public string Category { get; set; }
        public string image { get; set; }

    }


 //Get all categories which are active in state

 //Get all categories which are active in state
        public IQueryable<CategoriesDTO> GetAllCategories()
        {
            var categories = from b in db.Tbl_ProductCategories.Where(x => x.stat == "A")
                             select new CategoriesDTO()
                             {
                                 CID = b.CId,
                                 Category = b.category
                             };
            return categories;
        }


//Get sub categories based on category id and status is active

var subcategories = from b in db.Tbl_ProductSubCategories.Where(x => x.CId == cid && x.stat=="A")
                                select new SubCategoryDTO()
                                {

                                    SCID = b.SCId,
                                    SubCategory = b.SubCategory

                                };