LINQ to SQL - Help with restructuring a query

[Paul Prewett]

Hi -

I'm trying to get my head around LINQ to SQL and the new syntax. I have a
query that's working, but I have a feeling that it's not as clean as it could
be. I would like some guidance on how to improve it.

In short, there's a table that can have multiple entries for a single
respondent and I want the record that they entered last (i.e.
MAX(ResponseDate))

In T-SQL, it would look like this:

SELECT [xml] FROM tblRawResults r
WHERE r.RespondentId = 111111
AND r.ResponseDate =
(
SELECT MAX(ResponseDate) FROM tblRawResults r
WHERE r.RespondentId = 111111
)

I have translanted the query to LINQ as follows:

output = db.RawResults.Single
(
r1 => r1.RespondentId == p_RespondentId &&
r1.ResponseDate ==
(
(from r in db.RawResults
where r.RespondentId == p_RespondentId
select r.ResponseDate).Max()
)
).Xml;


I have suspect that there's a way to do this w/o the use of that subquery
that T-SQL requires. Somehow integrating that MAX into the main query. Am I
correct, or is this the proper way to translate it?

Thanks for your input.

 

[Marc Gravell]

Have you tried hooking the data-context's Log into Console.Out? What
does the TSQL look like?

Marc

 

[Andy]

Hi -

I'm trying to get my head around LINQ to SQL and the new syntax. I have a
query that's working, but I have a feeling that it's not as clean as it could
be. I would like some guidance on how to improve it.

In short, there's a table that can have multiple entries for a single
respondent and I want the record that they entered last (i.e.
MAX(ResponseDate))

In T-SQL, it would look like this:

SELECT [xml] FROM tblRawResults r
WHERE r.RespondentId = 111111
AND r.ResponseDate =
(
SELECT MAX(ResponseDate) FROM tblRawResults r
WHERE r.RespondentId = 111111
)

I have translanted the query to LINQ as follows:

output = db.RawResults.Single
(
r1 => r1.RespondentId == p_RespondentId &&
r1.ResponseDate ==
(
(from r in db.RawResults
where r.RespondentId == p_RespondentId
select r.ResponseDate).Max()
)
).Xml;

I have suspect that there's a way to do this w/o the use of that subquery
that T-SQL requires. Somehow integrating that MAX into the main query. Am I
correct, or is this the proper way to translate it?

Yikes. I think a better bet would be something like this:

output =
(
from results in db.RawResults
order by results.ResponseDate desc
select results
).Take( 1 ).Xml;