C#: LINQ to SQL: Executing literal query

Question

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?

Answer 1

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:

• Executing arbitrary queries in LINQ to SQL

Answer 2

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;

Answer 3

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.