Yes, we can write Linq queries in .net like database are called Linq Queries.
These are tables available in database.
Now using ORM We can able to make them as database objects to
application objects.
Using Linq to Sql classes (.dbml) we are making them as
object in Application objects. In application we can able to working with
object easily with writing quires.
Linq to Sql classes---name as àwcfdbDatacontext.dbml
so in code behind
Class Declaration
wcfdbDataClassesDataContext dbobj = new wcfdbDataClassesDataContext();
IQueryable<emp> empdata = from
n in dbobj.emps
select n;
GridView1.DataSource = empdata;
GridView1.DataBind();
Writing Queries IN LINQ
We can write queries in 2 ways
1.
Query operators
2.
Clauses.
Query operators:
IQueryable<emp>
data = from n in dbobj.emps select n;
Var data
=from n in dbobj.emps select new { n.ename, n.eloc, n.deptr, n.dept };
//Anonymous method representation.so right side Var
only suitable datatype.
Operator based query is implictly changed to Clause
based like below.
Table(emp).Select(n => new
<>f__AnonymousType0`4(ename = n.ename, eloc = n.eloc, deptr = n.deptr,
dept = n.dept))
SELECT [t0].[ename], [t0].[eloc], [t0].[deptr], [t2].[test], [t2].[did], [t2].[dname], [t2].[dloc], [t2].[uploadstatus]
FROM [dbo].[emp] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS
[test], [t1].[did], [t1].[dname], [t1].[dloc], [t1].[uploadstatus]
FROM [dbo].[dept]
AS [t1]
) AS [t2] ON [t2].[did] = [t0].[deptr].
Note: Every
query ends with select or group by operators.
How to use where clasue:
Operator base:
var data = from n in dbobj.emps where n.eloc == "bapunagar"
select n;
changed
as=èTable(emp).Where(n
=> (n.eloc = "bapunagar"))
clause base:
IQueryable<emp> data = from
n in dbobj.emps.Where(n => (n.eloc == "bapunagar")) select
n;
Incase:
var data = from n in
dbobj.emps.Where(n => (n.eloc == "bapunagar"))
select new {
n.eloc };
Table(emp).Where(n
=> (n.eloc = "bapunagar")).Select(n => new
<>f__AnonymousType0`1(eloc = n.eloc))
SELECT
[t0].[eloc] FROM [dbo].[emp] AS [t0] WHERE [t0].[eloc] = 'bapunagar'
How to use Group By operators:
Operater base
var
grouped = from product in
dbobj.Products group product by product.YearOfProduction;
group
by returns the data of each group based on key.Generally we uses group by for
grouping the data based on particular column(that
is Key).
Ex:
In LINQ TO OBJECTS How to use Groupby
public class
Product
{
public string Name { get; set; }
public int YearOfProduction { get;
set; }
}
class GroupbyClause
{
static void Main()
{
var
products = new[]
{
new Product {Name = "Flashlight",
YearOfProduction = 2009},
new Product {Name = "Nanobot",
YearOfProduction = 2012},
new Product {Name = "Disintegrator",
YearOfProduction = 2012}
};
//
IEnumerable<IGrouping<int,Product>> grouped = from product
in products group product by product.YearOfProduction; ---operator based
IEnumerable<IGrouping<int,Product>> grouped = from
product in products.GroupBy(n =>
n.YearOfProduction) select product;---clasue
based
foreach
(IGrouping<int,Product> group in
grouped)
{
Console.WriteLine("Total of {0} products produced in year {1}:",
group.Count(), group.Key);
foreach
(Product item in
group)
{
Console.WriteLine(item.Name);
}
Console.WriteLine();
}
Console.Read();
}
}
How to use LINQ
to Sql Group by:
Operator:
IQueryable<IGrouping<int?,
Product>> grouped = from product in dbobj.Products group product by
product.YearOfProduction;
or
clause:
IQueryable<IGrouping<int?,
Product>> grouped = from product in
dbobj.Products.GroupBy(n => n.YearOfProduction) select
product;
foreach (IGrouping<int?,
Product> group in
grouped)
{
Label1.Text += " "
+ group.Count(); //1, 1, 2 as well group.key; // 2009 2010,2012
foreach
(Product item in
group)
{
Label2.Text +=" \n "+ item.ProductName;
}
}
Binding
data to Gridview:
var grouped = from product in
dbobj.Products group product by new {
product.ProductName,product.proid } into g select new {
g.Key.ProductName,g.Key.proid };
GridView1.DataSource = grouped;
GridView1.DataBind();
Order by :
Orderby operator
is used sort the data in particular column.default is ascending(1---10);
//IOrderedQueryable<Product>
grouped = from n in dbobj.Products orderby
n.YearOfProduction select n;
or
var grouped = from n in
dbobj.Products select n;
GridView1.DataSource
= grouped;
GridView1.DataBind();
Default is asc(ascdeing) so no need order by for ascending
order.
IF we desending then use orderby clause.
IOrderedQueryable<Product> grouped = from
n in dbobj.Products orderby
n.YearOfProduction descending select n;
And
var grouped = from
n in dbobj.Products orderby
n.YearOfProduction descending select new
{ n.ProductName };
Note:
where clause return type is IEnumerable<Type> why because we can return multiple rows/ objects so
return type is IEnumerable<type>.
CRUD
Operations in Linq to sql:
Insert an Object :
Student t1
= new Student
{ sname = "sai" };
// Student
sobj = new Student
{ sname = TxtStuname.Text };
context.GetTable<Student>().InsertOnSubmit(t1);
context.SubmitChanges();
Insert collection of Objects: Using InsertAllOnSubmit<mytable>(myt);
Ex:
IEnumerable<Student> students = GetStudents();
context.GetTable<Student>().InsertAllOnSubmit<Student>(students);
context.SubmitChanges();
public IEnumerable<Student> GetStudents()
{
Student
t1 = new Student
{ sname = "first" };
Student
t2 = new Student
{ sname = "second" };
List<Student> ts = new
List<Student>();
ts.Add(t1);
ts.Add(t2);
return
ts.AsEnumerable();
}
Delete
an Object:
using (NORTHWNDDataContext cont=new
NORTHWNDDataContext())
{
Student s = cont.Students.Single(s1 => s1.sname == TxtStuname.Text);
cont.Students.DeleteOnSubmit(s);
cont.SubmitChanges();
}
Note : cont.Students -> total students.-> then single student in lamda query.
Student s = cont.Students.Single(s1 => s1.sname == TxtStuname.Text);
Or
Table<student> s = context.students; [//.Single(t => t.sname == "sai");]
IQueryable<student> stu =s.Where(t => t.sname ==
"sai");
Delete Collection of Objects
using (NORTHWNDDataContext cont=new
NORTHWNDDataContext())
{
IEnumerable<Student>
s =
cont.Students.Where(s1 => s1.sname == txtSname.Text);
IEnumerator<Student> sir=s.GetEnumerator();
while (sir.MoveNext())
{
Student
stu = sir.Current;
cont.Students.DeleteOnSubmit(stu);
cont.SubmitChanges();
}
}
Updateing an Object:
Student stu =
context.Students.Single(p => p.sname == "efgh");
stu.sname = TextBox1.Text;
context.SubmitChanges();
Note:
few clauses like where clause returns multiple
rows : so return type must be IEnumerable<Student> ;
few methods are not available in case of query-expression: like skip(),Take()...ect.
few methods are not available in case of query-expression: like skip(),Take()...ect.
No comments:
Post a Comment