How to fix this linq to sql query that has no support translation to sql?

Question

Hi folks.

i've got a LinqToSql query with a custom extension method at the end. this extension method is erroring when I try to linq2sql tries to generate the sql statement.

Error:

Method 'System.Collections.Generic.IList1[System.String] ToListIfNotNullOrEmpty[String](System.Collections.Generic.IEnumerable1[System.String])' has no supported translation to SQL.

Extension Method:

public static IList<T> ToListIfNotNullOrEmpty<T>(this IEnumerable<T> value)
{
    return value.IsNullOrEmpty()
               ? null
               : (value is IList<T> ? value as IList<T> : new List<T>(value));
}

Sample Linq to sql code:

public IQueryable<Models.Post> GetPosts()
{
    var dataLoadOptions = new DataLoadOptions();
    dataLoadOptions.LoadWith<Post>(x => x.PostTags);
    dataLoadOptions.LoadWith<PostTag>(x => x.Tag);
    _sqlDatabase.LoadOptions = dataLoadOptions;

    return from p in _sqlDatabase.Posts
        select new Models.Post
        {
            PostId = p.PostId,

            CommentList = (from c in p.Comments
                select new Models.Comment
                {
                    PostId = c.PostId,
                    < ... stuff snipped ... >
                }).ToListIfNotNullOrEmpty(),

            < ... more stuff snipped ... >

            TagList = (from t in p.PostTags
                select t.Tag.Description).ToListIfNotNullOrEmpty(),
        };
}

Normally, I would just have a ToList() method at the end, but i want to return a null object if the list is empty .. hence my extension method.

Can anyone suggest how I should fix this to make it work?

Answer 1

Have you tried this:

return _postRepository.Comments.ToList().ToNullIfEmpty();

With extension method:

public static IList<T> ToNullIfEmpty<T>(this List<T> value)
{
    return value.IsNullOrEmpty() ? null: value;
}

The problem is this bit:

TagList = (from t in p.PostTags
            select t.Tag.Description).ToListIfNotNullOrEmpty()

If you had this then it would work:

select new Models.Post
    {
        ....
    }.ToList().ToNullIfEmpty();

Or you could try changing the extension method to extend IQueryable as the other commenter suggested.

Answer 2

If you make an extension method taking IQueriable<T> instead of IEnumerable<T> you can control when the query is executed:

    public static IList<T> ToListIfNotNullOrEmpty<T>(this IQueryable<T> value)
    {
        var results = value.Provider.Execute<IEnumerable<T>>(value.Expression);
        return results == null || results .Count() == 0 
                   ? null
                   : results.ToList();
    }

Answer 3

I have had the same issue. The problem is that you can not use extension methods within a defered linq2sql expression. This is because the IQueryable provider tries to translate all the methods in the expression tree to sql when the qury is executed. It can not tell the diference between you method and the ones that are native to linq2sql. Therefore the problem.

The solution is to add the method to a partial class that will extend the native linq2sql classes instead of as an extension method.

EDIT: I am adding the suggested changes to the code

public IQueryable GetPosts()

{    
    var dataLoadOptions = new DataLoadOptions();
    dataLoadOptions.LoadWith<Post>(x => x.PostTags);
    dataLoadOptions.LoadWith<PostTag>(x => x.Tag);
    _sqlDatabase.LoadOptions = dataLoadOptions;
    return from p in _sqlDatabase.Posts
        select new Models.Post
        {
            PostId = p.PostId,
            CommentList = p.GetCommentList(),
            < ... more stuff snipped ... >
            TagList = (from t in p.PostTags
                select t.Tag.Description).ToListIfNotNullOrEmpty()
        };
}

I would then have the GetCommentList() method in a partial class

public partial class Post
{
     public List<Comment> GetCommentList()
     {
           List<Commment> resultList = from this.Comments etc...
           < ... put the select code here ... >
           if(resultList.Count > 0)
                return resultList;

           return null;
     }
}