"No supported translation in SQL" Linq workaround?

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;
 开发者_JAVA技巧       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!

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

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

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.