CODEWITHSUNDEEP
.netlinq-to-sql
Pure.Krome
Pure.Krome

Reputation: 86957

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

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?

Upvotes: 1

Views: 1871

Answers (3)

Sruly
Sruly

Reputation: 10540

I have had the same issue. The problem is that you can not use extension methods within a deferred linq2sql expression. This is because the IQueryable provider tries to translate all the methods in the expression tree to SQL when the query is executed. It can not tell the difference 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<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 = 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;
     }
}

Upvotes: 0

Jonathan Parker
Jonathan Parker

Reputation: 6795

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.

Upvotes: 1

Pop Catalin
Pop Catalin

Reputation: 62940

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();
    }

Upvotes: 2

Related Questions