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
Recent Posts
Categories
- All
- Angularjs training in Chennai
- ASP.NET Core
- dot net training
- dot net training in chennai
- dotnet full stack developer
- Free dotnet training
- information on dotnet
- Learn Java in chennai
- Learn Python at Karaikudi
- learn python online
- learn python online from chennai
- Linq Queries in .net
- mutual funds
- MVC Training Tutorials
- PHP Training in Chennai
- pmp training online
- power apps online training
- Python Training Online
- share market
- Sharepoint framework online training
- SharePoint Freelancers in Chennai
- software testing
- spfx online training
- Stock market
- Uncategorized