Hi, I wondering if someone could help me out on the syntax of using a nested query to query datatables.
Here is an example provided by LINQ documentation:
DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable orderLines = ds.Tables["SalesOrderDetail"];
var query = from o in ordersQuery
join ol in orderLinesQuery
on o.Field<int>("SalesOrderID") equals ol.Field<int>("SalesOrderID")
where o.Field<bool>("OnlineOrderFlag") == true &&
o.Field<DateTime>("OrderDate").Month == 8
select new { SalesOrderID = o.Field<int>("SalesOrderID"),
SalesOrderDetailID = ol.Field<int>("SalesOrderDetailID"),
OrderDate = o.Field<DateTime>("OrderDate"),
ProductID = ol.Field<int>("ProductID") };
I want to create a nested query inside the where clause, how do you do this
For example, Select * From Orders, OrderLines Where ordersID in ( Select OrdersID in OrderLines Where OrderLines ID > 100)
Can you create nested queries using LINQ I need an example in order to figure out the syntax. Is there any further documentation on querying dataset/datatables
Querying Dataset, nested query
enric vives
I don't know what your datatables look like and I'm not an expert on this, but based on some experimentation I did I think you should try something like this:
var query = from o in ds.Orders
where o.OrderID == 45
from c in ds.Customers
from co in c.Orders // assuming each Customer has a collection of Orders
where co.ItemName == o.ItemName // I just made this up
select c.Name;
Hope that helps,
Genevieve Orchard
andrealisp
I like to indent the nested from to make it clearer, e.g.:
var orders = from c in customerswhere c.Region == "WA"
from o in c.Orders
where o.OrderDate >= cutoffDate
select new {c.CustomerID, o.OrderID};
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I'm having trouble implementing this into querying some datatables...
This is what is intuitive to me coming from sql:
var query = from c in ds.Customers where c.CustOrder in ( from e in ds.Orders where e.OrderID = 45 ) select new { //SomeSelection}
I'm not sure if there is a difference when querying datatables versus other objects such as those generated by sqlmetal.
I know the sql language fairly well so I was hoping that the syntax was for linq was a little more similar.
chadmv
I was just looking through the LINQ Project Overview document and came across another way of performing a nested query. From page 17:
SelectMany is ideal for combining two information sources:
string[] names = { "Burke", "Connor", "Frank", "Everett",
"Albert", "George", "Harris", "David" };
var query = names.SelectMany(n =>
people.Where(p => n.Equals(p.Name))
);
In the lambda expression passed to SelectMany, the nested query applies to a different source, but has in scope the n parameter passed in from the outer source. Thus people.Where is called once for each n, with the resulting sequences flattened by SelectMany for the final output. The result is a sequence of all the people whose name appears in the names array.
Thought I would pass along this information as well.
-Genevieve Orchard
TemporaryNick
Unfortunately, I have found that the TSQL generated from LINQ with joins typically is converted into sub-selects. In part, this is necessary when considering LINQ's deferred execution scheme. However, it can provide performance issues as the application becomes more chatty over the network and query plan optimizations may not perform as well depending on the indexing schemes in place.
One other aspect you may want to consider when doing nested queries is the "into" keyword (see page 26 of the C# 3.0 overview document.)
Jim Wooley
http://devauthority.com/blogs/jwooley
DDressel
Have you looked at the "multiple from" example at http://msdn.microsoft.com/vcsharp/future/linqsamples/projection/default.aspx#multipleFrom That might help you out.
I like to indent the nested from to make it clearer, e.g.:
var orders = from c in customers
where c.Region == "WA"
from o in c.Orders
where o.OrderDate >= cutoffDate
select new {c.CustomerID, o.OrderID};
Genevieve Orchard - C# Documentation Team
jjb3rd
Thanks, this is useful.
If anyone could offer anymore documention on querying datasets that would be much appreciated. I have taken a look at the documentation on the linq webpage but it isn't quite enough.
aruyp
I have a post regarding the VB implementation of Linq to Datasets at http://devauthority.com/blogs/jwooley/archive/2006/06/10/1276.aspx. It is rather elementary, but might offer some help.
Jim Wooley
http://devauthority.com/blogs/jwooley