Using LINQ to fetch result from nested SQL queries

Question

This is my first question and first day in Linq so bit difficult day for me to understand. I want to fetch some records from database

i.e.

select * from tblDepartment 
where department_id in 
(
   select department_id from tblMap 
   where Guest_Id = @GuestId
)

I have taken two DataTable. i.e. tblDepartment, tblMap

Now I want to fetch this result and want to store it in third DataTable.

How can I do this.

I have been able to construct this query up till now after googling.

var query = from myrow in _dtDepartment.AsEnumerable()
            where myrow.Field<int>("Department_Id") == _departmentId
            select myrow;

Please provide me some link for learning Linq mainly for DataTables and DataSets.

EDIT:

I have got a very similar example here but i m still not able to understand how it is working. Please put some torch on it.

I want to do this because I am already fetching data from database and dont want to send request again when I already have data. But I want to filter that data based on conditions.

Answer 1

What you are attempting is not actual LINQ-To-SQL. It is a common misunderstanding. In LINQ-To-SQL you map some tables to entities in a simple way, then at run-time your LINQ query is translated by SQL by the framework and returned as your pre-defined strongly typed entity objects. (I use "entity" as a descriptive term; nothing to do with Entity Framework.) LINQ-To-SQL is something you do instead of ADO.NET.

What you are doing is a flavor of LINQ-to-object often called "LINQ-To-Dataset", which I use often. The extension method .AsEnumerable() returns a DataTable's DataRows in an IEnumerable<DataRow> object, which is very handy. However, with LINQ-To-DataTable, you either (A) won't have strongly typed objects, or (B) you are taking responsibility for putting your data into strongly typed objects yourself. After you've used ADO.NET like you always have, LINQ-to-DataTable is a great way to get your data out of ADO.NET DataTables and into something else, whatever is consuming the data.

You LINQ query above will take your DataTable, which has already run against the DB, and return an IEnumerable<DataRow>, which is great. Do:

After your LINQ statement, do:

For Each DataRow in query
    //Do your stuff
Next

If you really need a DataTable object in particular at this point, then check this out. But do you really need a DataTable?

• Good intro to LINQ-To-DataSet (sometimes called LINQ-To-DataTable)
• Good explanation of LINQ-To-SQL

And I have to suggest a really great book about LINQ. LINQ rocks. In a .NET world that seems to have too much that we need to learn, LINQ is worth taking the time to really understand. Get: LINQ In Action.