LINQ – Language integrated query – Integrated feature of the native language

Standard

                .Net feature allows to write query in .Net languages such as C# or Vb and then allows to debug and find the issues in compile time. This feature allows to manipulate the XML or relational data using native language. Syntax look like usual SQL syntax but starts with ‘From’, ‘join …. on … equal’, ‘Where’, ‘Orderby … descending/ascending’, ‘group … by’ and then finally ‘select …(new)’. Still I am digging this feature to familiarise to get the hands on it.

                This LINQ introduction example, I have created two different classes for employee’s educational background [LinqEducationalBackground] and employee’s skill set [LinqEmployeeSkill] and have used wrapper class methods such as [LinqDataWrapper].LinqEducationalBackgroundData(), [LinqDataWrapper].LinqEmployeeSkillData() to create the array values of those classes. And I have created some methods in the ‘Program’ class to generate the data using LINQ. Those are just for the introduction how to write simple Language integrated queries. Still I had not used this feature in any live system when I was writing this blog. But Thoroughly used this in year 2012 one of my projects.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace ProjectLINQ

{

class Program

{

static void Main(string[] args)

{

getNumberOfPersonsBySkillUsingLINQ(“.Net”);

getEmployeeSkillsWithMultipleOrConditionUsingLINQ();

getListOfEmployeesEducationalBackground();

getListEmployeesWithSpecificSkill(“java”);

getListOfEmployeesSingleMatchWithPageNumber(3);

Console.ReadLine();

}

//LINQ using ‘group … by’

static void getNumberOfPersonsBySkillUsingLINQ(string @skill)

{

var LinQqueryResult = from x in LinqDataWrapper.LinqEmployeeSkillData()

group x by LinqDataWrapper.processArray(x.skills).Contains(@skill) into Skills

select new

{

skills = @skill,

iCount = Skills.Count()

};

foreach (var s in LinQqueryResult)

{

Console.WriteLine(string.Format(“{0} \t {1} \r\n”, s.skills, s.iCount.ToString()));

}

Console.WriteLine(LinqDataWrapper.GetDataUsingGenericList());

}

//LINQ with more than one condition to filter the values

static void getEmployeeSkillsWithMultipleOrConditionUsingLINQ()

{

var LinQqueryResult = (from x in LinqDataWrapper.LinqEmployeeSkillData()

where x.fullName == “Kumaran” ||

x.fullName.StartsWith(“S”) ||

x.fullName.Length < 10

orderby x.fullName descending

select x).Skip<LinqEmployeeSkill>(3).Take<LinqEmployeeSkill>(2);

foreach (var s in LinQqueryResult)

{

Console.WriteLine(string.Format(“{0:A}, \t {1:A}, \t {2:A} \r\n”, s.fullName, s.employeeNo, LinqDataWrapper.processArray(s.skills)));

}

}

//List of employees with their educational background using LINQ – Join query

static void getListOfEmployeesEducationalBackground()

{

var query = from x in LinqDataWrapper.LinqEmployeeSkillData()

join x1 in LinqDataWrapper.LinqEducationalBackgroundData()

on x.employeeNo equals x1.employeeNo

select new { Fullname = x.fullName, Study = x1.Study };

foreach (var s in query)

{

Console.WriteLine(string.Format(“{0, -65} \t {1} \r\n”, s.Fullname, s.Study));

}

}

//List of employees with specific skill using LINQ – Select new

static void getListEmployeesWithSpecificSkill(string skill)

{

var query = from x in LinqDataWrapper.LinqEmployeeSkillData()

join x2 in LinqDataWrapper.LinqEducationalBackgroundData() on x.employeeNo equals x2.employeeNo

from x1 in x.skills

where x1.ToUpper() == skill.ToUpper()

select new { Fullname = x.fullName, Skills = LinqDataWrapper.processArray(x.skills), Study = x2.Study };

foreach (var s in query)

{

Console.WriteLine(string.Format(“{0, -65}, \t {1, -2 }, \t {2, 22} \r\n”, s.Fullname, s.Skills, s.Study));

}

}

//Using LINQ to get the data – paging implementation

static void getListOfEmployeesSingleMatchWithPageNumber(int ipageSize)

{

int pageSize = ipageSize;

int iRecordFetchedCurrentLoop = 0;

int iCurrentPage = 0;

do

{

var query = (from x inLinqDataWrapper.LinqEmployeeSkillData()

join x2 in LinqDataWrapper.LinqEducationalBackgroundData() on x.employeeNo equals x2.employeeNo

from x1 in x.skills

select new LinqEmployeeDetails { fullName = x.fullName, skills = x1, Study = x2.Study }

).Skip(iCurrentPage * pageSize).Take(pageSize);

iRecordFetchedCurrentLoop = (int)query.Count();

if (iRecordFetchedCurrentLoop != 0)

Console.WriteLine(string.Format(“Page number: {0} \r\n”, iCurrentPage));

foreach (var s in query)

{

Console.WriteLine(string.Format(” {0, -65}, \t {1, -2 }, \t {2, 22} \r\n”, s.fullName, s.skills, s.Study));

}

iCurrentPage++;

} while (iRecordFetchedCurrentLoop != 0);

}

}

public class LinqEmployeeSkill

{

public string fullName { get; set; }

public string employeeNo { get; set; }

public string[] skills { get; set; }

public LinqEmployeeSkill(String FullName, String EmployeeNo, String[] Skills)

{

this.fullName = FullName;

this.employeeNo = EmployeeNo;

this.skills = Skills;

}

}

public class LinqEducationalBackground

{

public string employeeNo { get; set; }

public string Study { get; set; }

public LinqEducationalBackground(String EmployeeNo, String study)

{

this.employeeNo = EmployeeNo;

this.Study = study;

}

}

public static class LinqDataWrapper

{

// Create array values of Employee skill data using LinqEmployeeSkill Class

public static LinqEmployeeSkill[] LinqEmployeeSkillData()

{

LinqEmployeeSkill[] lqEmpSkill = {

new LinqEmployeeSkill(“Thirumavalavan”, “Thir298989989”, “.NET, C#, SQL”.Split(‘,’)),

new LinqEmployeeSkill(“Kumaran”, “Kuma983298989”, “.NET, C#, SQL”.Split(‘,’)),

new LinqEmployeeSkill(“Syaan”, “Syaa272209098”, “.NET, VB.NET, Oracle”.Split(‘,’)),

new LinqEmployeeSkill(“Linqman”, “Linq0982722090”, “Java, Unix, Oracle”.Split(‘,’)),

new LinqEmployeeSkill(“RianRoa”, “Rian272209054”, “.NET, C#, SQL”.Split(‘,’)),

new LinqEmployeeSkill(“Dikenman”, “Dik0982722090”, “.Net, Shell, Oracle”.Split(‘,’)),

new LinqEmployeeSkill(“SmartGuys”, “Sma272209054”, “Network, Hardware, Open Source”.Split(‘,’))

};

return lqEmpSkill;

}

// Create array values of Employee Educational Background data using LinqEducationalBackground class

public static LinqEducationalBackground[] LinqEducationalBackgroundData()

{

LinqEducationalBackground[] lq = {

new LinqEducationalBackground(“Thir298989989”, “3+2 Years University Degree”),

new LinqEducationalBackground(“Kuma983298989”, “Diploma in Information Technology”),

new LinqEducationalBackground(“Syaa272209098”, “Graduate Degree in Head Hunt”),

new LinqEducationalBackground(“Linq0982722090”, “Business Administration in Neuron technology”),

new LinqEducationalBackground(“Rian272209054”, “Certification of Agile”),

new LinqEducationalBackground(“Dik0982722090”, “Diploma in Software Engineering.”),

new LinqEducationalBackground(“Sma272209054”, “Certification of SDLC”) ,

new LinqEducationalBackground(“Sma272209054”, “Diploma in Ethical Hacking”),

new LinqEducationalBackground(“Sma272209054”, “Post graduate degree to Business Management”)

new LinqEducationalBackground(“Sma272209054”, “Certification of  Project Management.”)

};

return lq;

}

//Method to check the data using generic list

public static string GetDataUsingGenericList()

{

string LD = string.Empty;

List<LinqEmployeeSkill> ListData = new List<LinqEmployeeSkill>(LinqEmployeeSkillData());

foreach (LinqEmployeeSkill lq in ListData)

{

LD += string.Format(“{0},{1},{2}”, lq.fullName, lq.employeeNo, processArray(lq.skills)) + “\r\n”;

}

return LD;

}

//Helping Method

public static string processArray(String[] str)

{

string result = string.Empty;

foreach (string s in str)

{

result += s + “,”;

}

return result;

}

}

}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s