"No supported translation in SQL" Linq workaround?

Question

Currently im trying to make my query short with reusable peice of code like this to check for post if it's eligible to display.

    // Logic to check if post is eligible for display
    public bool isEligibleForDisplay(Post n)
    {
        var pubDate = n.PUBLISH_DATE ?? DateTime.MinValue;
        var endDate = n.END_DATE ?? DateTime.MaxValue;
        var correctState = (n.STATE == PostState.Publish || n.STATE == PostState.Furture);
        var dateInRange = (DateTime.Now >= pubDate && DateTime.Now <= endDate);
        return correctState && dateInRange;
    }

my linq look like this:

var q = from n in _db.Posts
                    where isEligibleForDisplay(n)
                    group n by n.POST_ID into g
                    select g.OrderByDescending(t => t.CREATE_DATE).First();
            return q.Take(quantity);

I ran into "No supported translation in SQL" problem for the first time of using linq to sql, I am just wondering if there are anyway that can use as a work around for this case, which could be troublesome if I include whole lot of those checking logic into my query everytime.

I'm so looking forward for a reply. Thanks!

Answer 1

You can create a function on your SQL server called isEligibleForDisplay that does the SQL equivalence of these checkes and add that to your dbml file.

I haven't tested this, but I'm thinking the easiest would be if you create a function where you pass the values you want, rather than the whole record, and I think something like this might work:

CREATE FUNCTION isEligibleForDisplay(
    @publishDate DATETIME,
    @endDate DATETIME,
    @state TINYINT -- correct me if i'm wrong...
) RETURNS bit

AS

BEGIN

    DECLARE @return bit
    DECLARE @dateStart DATETIME, @dateEnd DATETIME

    SET @return = 0

    SET @dateStar t= COALESCE(@publishDate, CONVERT(DATETIME, '1900-01-01'))
    SET @dateEnd = COALESCE(@endDate, CONVERT(DATETIME, '9999-12-31'))

    IF getdate() BETWEEN @dateStart AND @dateEnd
    BEGIN

        IF @state IN(1,3) -- or whatever the int representations of your enum are
            SET @return = 1

    END


    RETURN @return

END

Answer 2

How maqny records are in _db.Posts? If not much, you can do .ToList() first, and than linq will be able to use isEligibleForDisplay function.

Answer 3

I used the extension of linq to include a method, which can actually work very fine using IQueryable.

public static IQueryable<T> getPostActive<T>(this IQueryable<T> items) where T : P015.Models.SQLModel.Post
{
    // Logic to check if post is eligible for display
    var now = DateTime.Now;
    return items.Where(n =>
                           (n.STATE.Trim() == PostState.Publish || n.STATE.Trim() == PostState.Furture || n.STATE.Trim() == PostState.Draft) &&
                           (
                                ((n.END_DATE ?? SqlDateTime.MaxValue.Value) >= now) && 
                                ((n.PUBLISH_DATE ?? SqlDateTime.MinValue.Value) <= now)
                           )
                      );
}