+91-90427 10472
         
Dot net training in Chennai

Linq query with different data sources List, Array and SQL

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

Create StudentDetails Table in TestDB using the below snippet,

CREATE TABLE [dbo].[StudentDetails] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[StudentName] VARCHAR (50) NULL,
[Age] INT NULL,
[inchargeStudentId] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

– Create a Console Application
Use SQL to Linq Converter,
http://www.sqltolinq.com/
Click on Generate Linq to SQL Model Files,

Browse a Folder and Click on OK.
Click on Generate Linq to SQL Files,


Convert SQL query to Linq as below,

Age> 24 and StudentName contains ‘Sco’

Screen clipping taken: 10-09-2016 19:21
Self Join in Linq


Right click on the table and select Regenerate Model Files.
Insert the connection below in App.config,

<connectionStrings>
<add name=”TestConnection” connectionString=”Data Source=(LocalDb)\v11.0;Initial Catalog=TestDB;Integrated Security=True;Pooling=False” providerName=”System.Data.SqlClient” />
</connectionStrings>

Add StudentDetails.cs and data context file as below,

public class StudentDetails
{
public int Id { get; set; }
public string StudentName { get; set; }
public int? Age { get; set; }
public int? InchargeStudentId { get; set; }
}
public class UsersContext : DbContext
{
public UsersContext(): base(“TestConnection”)
{
}
public DbSet<StudentDetails> studentDbset { get; set; }
}

Insert the below Code snippet in Console,

class Program
{
private static UsersContext db = new UsersContext();
static void Main(string[] args)
{
/* Getting the Student Db Collection from DB */
List<StudentDetails> obj= db.studentDbset.ToList();
foreach (var item in obj)
{
Console.WriteLine(“Student Name” + “\t” + item.StudentName + “\t” + “Age:” + item.Age);
}
/* Getting the Student Db Collection for Age>24 from DB */
Console.WriteLine(“———————————————————-“);
Console.WriteLine(“Printing Age > 24”);
var query =
from StudentDetails1 in db.studentDbset
where
StudentDetails1.Age > 24
select new
{
Id = StudentDetails1.Id,
StudentName = StudentDetails1.StudentName,
Age = StudentDetails1.Age
};
foreach (var r in query)
Console.WriteLine(“Student Name” + “\t” + r.StudentName + “\t” + “Age:” + r.Age);
/* Getting the Student Db Collection for Age>24 from DB */
Console.WriteLine(“———————————————————-“);
Console.WriteLine(“Printing Age > 24 && Student Name contains Sco”);
var query1 =
from StudentDetails1 in db.studentDbset
where
StudentDetails1.Age > 24 &&
StudentDetails1.StudentName.Contains(“Sco”)
select new
{
Id = StudentDetails1.Id,
StudentName = StudentDetails1.StudentName,
Age = StudentDetails1.Age
};
foreach (var r in query1)
Console.WriteLine(“Student Name” + “\t” + r.StudentName + “\t” + “Age:” + r.Age);
/* Getting the Student Db Collection for getting Incharge on Self join from DB */
Console.WriteLine(“———————————————————-“);
Console.WriteLine(“Printing self join for Students Lead”);
var query2 =
from s in db.studentDbset
join s1 in db.studentDbset on new { InchargeStudentId = s.InchargeStudentId.Value } equals new { InchargeStudentId = s1.Id }
select new
{
s.Id,
s.StudentName,
s.Age,
incharge = s1.StudentName
};
foreach (var r in query2)
Console.WriteLine(“Student Name” + “\t” + r.StudentName + “\t” + “Age:” + r.Age + “\t” + “Incharge:” + r.incharge);
/* Getting the Collection from DB */
Console.WriteLine(“———————————————————-“);
Console.WriteLine(“Printing from Array”);
string[] ary = new string[] { “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec” };
var res = from months in ary
where months.StartsWith(“J”)
select new { MonthStartWith_J = months };
foreach (var r in res)
Console.WriteLine(“Month:” + “\t” + r.MonthStartWith_J);
/* Getting the Collection from DB */
Console.WriteLine(“———————————————————-“);
Console.WriteLine(“Printing from Collection”);
List<string> objstring = new List<string>();
objstring.Add(“Yahooo”);
objstring.Add(“Google”);
objstring.Add(“HCL”);
var obj1 = from company in objstring where company.StartsWith(“Goo”)
select new { companyStartWith_Goo = company };
foreach (var r in obj1)
Console.WriteLine(“Month:” + “\t” + r.companyStartWith_Goo);
Console.ReadLine();
}
}

The Output as below,

Screen clipping taken: 10-09-2016 21:34
Click below for download,
https://1drv.ms/u/s!ArddhCoxftkQg6hZlD6medX1rPn9pw
Reference
http://www.dotnetlearners.com/linq/linq-to-string-array-with-example.aspx