can LinQ perform Top/Group By/subqueries on local dataset?

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I am still using dataAdapters against sql server, Oracle, ... But I keep
reading the LinQ is overtaking these. One operation that I perform to
exclude duplicate rows from a dataset is to push the data from the dataset
back to the server to a #tmp table that I create from within the app. Then
I run a query from within the app against this #tmp table on the server using
a dataAdapter which uses the following statements:

SELECT * FROM #tmp t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM #tmp t2 WHERE t2.RecordId = t1.RecordId
Order By FirstDate) t3 WHERE t3.ID = t1.ID)

Can LinQ perform an operation like this locally to the app so that I don't
have to push the data back to the server? If yes, what would the LinQ code
look like?

Thanks,
Rich
 
I am still using dataAdapters against sql server, Oracle, ...  But I keep
reading the LinQ is overtaking these.  One operation that I perform to
exclude duplicate rows from a dataset is to push the data from the dataset
back to the server to a #tmp table that I create from within the app.  Then
I run a query from within the app against this #tmp table on the server using
a dataAdapter which uses the following statements:

SELECT * FROM #tmp t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM #tmp t2 WHERE t2.RecordId = t1.RecordId
Order By FirstDate) t3 WHERE t3.ID = t1.ID)

Can LinQ perform an operation like this locally to the app so that I don't
have to push the data back to the server?  If yes, what would the LinQ code
look like?
Click to expand...

If I parse your SQL query correctly, you are trying to get only one
record for each unique RecordId, and that one record should always be
the one with the earliest date (unless you've missed DESCENDING in the
ORDER BY clause). In this case, the functionally equivalent LINQ query
would be:

DataTable tmp;
...
var q =
from r in tmp
group r by r["RecordId"] into g
select g.OrderBy(r => r["FirstDate"]).First();

By the way, LINQ isn't "overtaking" MSSQL, Oracle, or any other RDBMS
database out there. LINQ to Objects ("client-side") is certainly not a
replacement for them as it's not a database - it works on in-memory
collections of objects, and it doesn't do any query optimizations.
LINQ to SQL and LINQ to Entities are ways to access existing
relational databases using LINQ, which may be more convenient to work
with, but it still involves roundtrips to the server, and is
transformed to SQL in the end anyway.
 
Thank you for your reply. And on overtaking I meant overtaking Ado.Net
DataAdapters (not the databases). Question:

DataTable tmp;
...
var q =
from r in tmp
group r by r["RecordId"] into g
select g.OrderBy(r => r["FirstDate"]).First();

I have seen this notation:
r => r["FirstDate"]

is ' => ' specific to LinQ or is it a C# notation?

Anyway, it looks like the answer to my question is "Yes" LinQ can perform my
operation within the application - very cool (and yes I am only retrieve the
first unique RecordID based on the earliest Date in Asc order).

Pavel Minaev said:
I am still using dataAdapters against sql server, Oracle, ... But I keep
reading the LinQ is overtaking these. One operation that I perform to
exclude duplicate rows from a dataset is to push the data from the dataset
back to the server to a #tmp table that I create from within the app. Then
I run a query from within the app against this #tmp table on the server using
a dataAdapter which uses the following statements:

SELECT * FROM #tmp t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM #tmp t2 WHERE t2.RecordId = t1.RecordId
Order By FirstDate) t3 WHERE t3.ID = t1.ID)

Can LinQ perform an operation like this locally to the app so that I don't
have to push the data back to the server? If yes, what would the LinQ code
look like?
Click to expand...

If I parse your SQL query correctly, you are trying to get only one
record for each unique RecordId, and that one record should always be
the one with the earliest date (unless you've missed DESCENDING in the
ORDER BY clause). In this case, the functionally equivalent LINQ query
would be:

DataTable tmp;
...
var q =
from r in tmp
group r by r["RecordId"] into g
select g.OrderBy(r => r["FirstDate"]).First();

By the way, LINQ isn't "overtaking" MSSQL, Oracle, or any other RDBMS
database out there. LINQ to Objects ("client-side") is certainly not a
replacement for them as it's not a database - it works on in-memory
collections of objects, and it doesn't do any query optimizations.
LINQ to SQL and LINQ to Entities are ways to access existing
relational databases using LINQ, which may be more convenient to work
with, but it still involves roundtrips to the server, and is
transformed to SQL in the end anyway.
Click to expand...
 
I have seen this notation:
r => r["FirstDate"]

is  ' => '  specific to LinQ or is it a C# notation?
Click to expand...

For one thing, LINQ in C# is also "C# notation" (LINQ in VB.NET has
somewhat different syntax, for example). However, "=>" is not specific
to LINQ - it's a general-purpose C# lambda declarator. Lambdas are
essentially shorthand notation for anonymous delegates, introduced in
C# 3.0. In general, a lambda expression "arg => expr" is equivalent to
"delegate(T arg) { return expr; }", with the type of argument T
automatically inferred from the context (though you can specify it
explicitly if you want). Enumerable.OrderBy extension method takes a
single delegate that should, given an element of the sequence, return
the value of the key on which the elements should be ordered. In this
case, I used explicit OrderBy invocation and lambda because it was
shorter; however, the same could be done using LINQ syntactic sugar as
well:

(from r in g orderby r["FirstDate"] select r).First();

There's actually more to lambdas, such as expression trees that enable
LINQ to SQL and LINQ to Entities. I refer you to MSDN documentation on
them for more info:
http://msdn.microsoft.com/en-us/library/bb397687.aspx
 
Rich said:
I am still using dataAdapters against sql server, Oracle, ... But I keep
reading the LinQ is overtaking these. One operation that I perform to
exclude duplicate rows from a dataset is to push the data from the dataset
back to the server to a #tmp table that I create from within the app.
Then
I run a query from within the app against this #tmp table on the server
using
a dataAdapter which uses the following statements:

SELECT * FROM #tmp t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM #tmp t2 WHERE t2.RecordId =
t1.RecordId
Order By FirstDate) t3 WHERE t3.ID = t1.ID)

Can LinQ perform an operation like this locally to the app so that I don't
have to push the data back to the server? If yes, what would the LinQ
code
look like?
Click to expand...

Someone has answered the linq side of this but I would like to add that you
didn't need to wait for linq to do this on the client side. Sending data to
a server just to run sql on it and return the results is very poor practice
and you should have just written some good old fashioned code to solve the
problem before linq existed.
 
Back
Top