I have a T-SQL query which populates a DataSet from an MSSQL database.

string qry = "SELECT * FROM EvnLog AS E 
       WHERE TimeDate = 
       (SELECT MAX(TimeDate) 
            From EvnLog 
            WHERE Code = E.Code) 
        AND (Event = 8) 
        AND (TimeDate BETWEEN '" + Start + "' AND '" + Finish + "')"

The database is quite large and being the type of nested query it is, the Data Adapter can take a number of minutes to fill the DataSet. I have extended the DataAdapter's timeout value to 480 seconds to combat it, but the client still complains about slow performance and occassional timeouts.

To combat this, I was considering executing a simpler query (ie. just taking the date range) and then populating a Generic List which I could then execute a Linq query against.

The simple query executes very quickly which is great. However, I cannot seem to build a Linq query which generates the same results as the T-SQL query above.

Is this the best solution to this problem?

Can anyone provide tips on rewriting the above T-SQL into Linq?

I have also considered using a DataView, but cannot seem to get the results from that either.