what wrong with this linq to sql

Question

i get this error

{"Method 'System.DateTime ConvertTimeFromUtc(System.DateTime, System.TimeZoneInfo)' has no supported translation to SQL."}

when i try to execute this linq to sql

var query = from p in db.Posts
            let categories = GetCategoriesByPostId(p.PostId)
            let comments = GetCommentsByPostId(p.PostId)
            select new Subnus.MVC.Data.Model.Post
            {
                Categories = new LazyList<Category>(categories),
                Comments = new LazyList<Comment>(comments),
                PostId = p.PostId,
                Slug = p.Slug,
                Title = p.Title,
                CreatedBy = p.CreatedBy,
                CreatedOn = TimeZoneInfo.ConvertTimeFromUtc(p.CreatedOn, TimeZoneInfo.FindSystemTimeZoneById("Romance Standard Time")),
                Body = p.Body
            };
return query;

is there another place i can convert the date to right format currently i have a macro i my _global.spark fil but that seems wrong

<macro name="DateAndTime" Date="DateTime">
# Date = TimeZoneInfo.ConvertTimeFromUtc(Date, TimeZoneInfo.FindSystemTimeZoneById("Romance Standard Time"));
${Date.ToString("MMMM d, yyyy")} at ${Date.ToString("hh:mm")}
</macro>
<macro name="Date" Date="DateTime">
# Date = TimeZoneInfo.ConvertTimeFromUtc(Date, TimeZoneInfo.FindSystemTimeZoneById("Romance Standard Time"));
${Date.ToString("MMMM d, yyyy")}
</macro>

Update: i now understand where the code does not work but when i remove it i get then same error for this code

 public IQueryable<Subnus.MVC.Data.Model.Comment> GetCommentsByPostId(int postId)
    {
        var query = from c in db.Comments
                    where c.PostId == postId
                    select new Subnus.MVC.Data.Model.Comment
                    {
                        Body = c.Body,
                        EMail = c.EMail,
                        Date = c.CreatedOn,
                        WebSite = c.Website,
                        Name = c.Name
                    };

        return query;
    }

Answer 1

LINQ-to-SQL only translates a subset of operations - and it trying (and failing) to write ConvertTimeFromUtc as TSQL. Some operations have TSQL counterparts (dateadd/datediff/etc) - but not all. You might choose to do your projection (select) using the raw value, and only do the ConvertTimeFromUtc once you have the object in memory (via LINQ-to-Objects).

For example - you could create the objects just using p.CreatedOn, then do the rest afterwards. Not ideal, but life. LINQ-to-Entities claims better translation options, but is significantly more complex. Depending on your scenario, LINQ-to-SQL also offers udf support, which sometimes lets you offload these things to the db - if there is a way of writing it as a udf - for example, you could write a method on the data-context and mark is as a composable function ([Function]), then something like:

 ...
    CreatedOn = ctx.MapDate(p.CreatedOn)
 ....

Which would then use the TSQL from [Function] - i.e. something like:

....
    dbo.MapDate(t2.CreatedOn)
....

Answer 2

@Marc Gravell - ran out of comment space

i do filter on the query later on so would like it to come out with the right data I have a blogservice that have

public List<Post> GetPublicPosts()
{
    var query = repository.GetPosts();
    var q = from p in query
            where p.IsPublic == true
            select p;
    return q.ToList();
}

and if i do some linq to object it will select the full table and don't apply my filter

repository.GetPosts() return the query I showed in my question

Answer 3

Linq-to-Sql can't translate arbitrary .net functions into SQL. Some DateTime functions can be translated however, and a full list is available here:-

http://msdn.microsoft.com/en-us/library/bb882657.aspx

In your example if you calculate the time offset outside of the projection you can add the offset to the retrieved "CreatedOn" DateTime using the AddMinutes method which has a supported translation into SQL.