If I have this SQL query:
"select distinct top 1 'PostId' = isnull(RootPost,Id), PostedDateTimeUtc from Post order by PostedDateTimeUtc desc"
And I need to enumerate the results with a DataContext. That is to say, how do I send this SQL to a DataContext and parse the result?
How do I do that? What would a method that returns the result anonymously look like?
For executing SQL Queries that will return results form an known entity, you could use the DataContext.ExecuteQuery method:
IEnumerable<Post> = dataContext.ExecuteQuery<Post>(sqlQuery);
For custom results sets, the Execute method cannot infer and create the anonymous type, but you still can create a class, that contains the fields which are selected in your custom SQL query.
class CustomPostResult // custom type for the results
{
public int? PostId { get; set; }
public DateTime PostedDateUtcTime { get; set; }
}
//...
string sqlQuery = @"SELECT DISTINCT TOP 1 'PostId' = ISNULL(RootPost,Id),
PostedDateTimeUtc FROM Post ORDER BY PostedDateTimeUtc DESC";
IEnumerable<CustomPostResult> = dataContext.
ExecuteQuery<CustomPostResult>(sqlQuery);
Check this article:
I usually dump the results into a List<> of the LINQ object you're using.
List<Post> posts = new List<Post>();
using(your datacontext)
{
var result = // Your query
posts = result.ToList():
}
return posts;
You could try a LINQ expression. Something like this should probably work.
var results = (from post in dc.Posts
orderby post.PostedDateUtcTime descending
select new Post
{
RootPost = (post.RootPost == null) ? post.Id : post.RootPost
}).Distinct<Post>().Take<Post>(1);
I haven't actually ran this, so if anyone finds a problem I'll fix this up.
