LINQ basic query samples in csharp asp.net with join

Assuming “db” is considered as databasecontext object

/////// typical query with condition that returns annonymous object ///////////
var contacts =
from customer in db.Customers
where customer.Name.StartsWith(“A”) && customer.Orders.Count > 0
orderby customer.Name
select new { customer.Name, customer.Phone };

/////// get count with running query///////////
var query = (from o in Order
where CustomerID = 2
select o).Count();

/////////////// inner join (matching in both only) //////////////

var cr = from c in MyDatabase.GetReadOnlyContext().tblCategories
join p in MyDatabase.GetReadOnlyContext().tblProducts on c.CategoryId equals p.CategoryId
select new { c.CategoryName, p.ProductName };

//////////////// left outer joing

var crForLeftOuterJoin = from cat in dc.tblCategories
join pro in dc.tblProducts
on cat.CategoryId equals pro.CategoryId
into xname
from pro in xname.DefaultIfEmpty()
select new {
CAP_Cat = cat.CategoryName,
CAP_Product = pro == default(tblProduct) ? “(no product)” : pro.ProductName
};

//////// getting rows from table as LIST collection /////////////
List lstCat= dc.tblCategories.ToList();

//////// getting rows from table as Array /////////////
tblCategory[] arrCat= dc.tblCategories.ToArray();

///////////////seeking particular element/single element from query ///////////////////

var firstproduct = (from product in db.Products
where product.Category.CategoryName == “Beverages”
select product
).AsEnumerable().ElementAt(0);

Advertisements

One thought on “LINQ basic query samples in csharp asp.net with join

  1. Pingback: Find number of rows in table using cursor in SQL | borania siddharth - web developer,rajkot, gujarat, india

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s