How to create extension methods in the Entity Framework using Linq?

  

Is it possible to create custom extension methods for Linq to entities?

Yes, but this is a bit complex. I'll explain the guidelines on how to do this.

Linq to Entities translates each extension method into a predicate according to provider , that is, if I make a Where() , the provider SQL Server will translate the extension to SQL with the best possible compatibility for SQL Server. The provider MySQL will do the same thing for MySQL, and so on.

So, the correct way to intercede in the SQL generation process is reimplementing ExpressionVisitor :

public class QueryTranslatorProvider<T> : ExpressionVisitor, IQueryProvider
{
    internal IQueryable source;

    public QueryTranslatorProvider(IQueryable source)
    {
        if (source == null) throw new ArgumentNullException("source");
        this.source = source;
    }

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        if (expression == null) throw new ArgumentNullException("expression");

        return new QueryTranslator<TElement>(source, expression) as IQueryable<TElement>;
    }

    public IQueryable CreateQuery(Expression expression)
    {
        if (expression == null) throw new ArgumentNullException("expression");
        Type elementType = expression.Type.GetGenericArguments().First();
        IQueryable result = (IQueryable)Activator.CreateInstance(typeof(QueryTranslator<>).MakeGenericType(elementType),
            new object[] { source, expression });
        return result;
    }

    public TResult Execute<TResult>(Expression expression)
    {
        if (expression == null) throw new ArgumentNullException("expression");
        object result = (this as IQueryProvider).Execute(expression);
        return (TResult)result;
    }

    public object Execute(Expression expression)
    {
        if (expression == null) throw new ArgumentNullException("expression");

        Expression translated = this.Visit(expression);
        return source.Provider.Execute(translated);
    }

    internal IEnumerable ExecuteEnumerable(Expression expression)
    {
        if (expression == null) throw new ArgumentNullException("expression");

        Expression translated = this.Visit(expression);
        return source.Provider.CreateQuery(translated);
    }

    #region Visitors
    protected override Expression VisitConstant(ConstantExpression c)
    {
        // fix up the Expression tree to work with EF again
        if (c.Type == typeof(QueryTranslator<T>))
        {
            return source.Expression;
        }
        else
        {
            return base.VisitConstant(c);
        }
    }
    #endregion
}

Then, implement your SameDate within IOrderedQueryable<T> :

public class QueryTranslator<T> : IOrderedQueryable<T>
{
    private Expression expression = null;
    private QueryTranslatorProvider<T> provider = null;

    public QueryTranslator(IQueryable source)
    {
        expression = Expression.Constant(this);
        provider = new QueryTranslatorProvider<T>(source);
    }

    public QueryTranslator(IQueryable source, Expression e)
    {
        if (e == null) throw new ArgumentNullException("e");
        expression = e;
        provider = new QueryTranslatorProvider<T>(source);
    }

    public IEnumerator<T> GetEnumerator()
    {
        return ((IEnumerable<T>)provider.ExecuteEnumerable(this.expression)).GetEnumerator();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return provider.ExecuteEnumerable(this.expression).GetEnumerator();
    }

    public QueryTranslator<T> SameDate(DateTimeOffset? source, DateTime? date) 
    {
        // Implemente aqui
    }

    public Type ElementType
    {
        get { return typeof(T); }
    }

    public Expression Expression
    {
        get { return expression; }
    }

    public IQueryProvider Provider
    {
        get { return provider; }
    }
}

Within it, you can create your own extension methods, correctly intercepting the calls to the provider database. The general idea is here ( question and answers) .

Yes, you can do this. The actual Where of IQueryable is actually an extension method, in fact, most of the filters that have IEnumerable and IQueryable are extension methods. That's why you need to add using System.Linq to your code so that extension methods are captured. In your case, just create a method as below and add the desired behavior:

public static IQueryable<TSource> SeuFiltroAqui<TSource>(
  this IQueryable<TSource> source,
  DateTime date
)

Important

1 - If you do not apply this above IQueryable and yes above IEnumerable , queries are going to be done in memory, then use IQueryable .

See the example below for how you can create an extension method as you want.

using System;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace stackoverflow
{
    public partial class BuscarProdutos : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (var ctx = new stackoverflowEntities())
            {
                var query = ctx.Produto

                    // métod de extesão para Where
                    .Where_Produto(DateTime.Today)

                    // métod de extesão para criação de uma view
                    .Vw_Produto(ctx)
                    .ToList();
            }
        }
    }

    public class cvw_Produto
    {
        public SubCategoria produto { get; internal set; }
        public string Descricao { get; internal set; }
        public int? Estoque { get; internal set; }
        public decimal? Preco { get; internal set; }
        public int ProdutoId { get; internal set; }
    }

    public static class ProdutoExt
    {
        public static IQueryable<Produto> Where_Produto(
                this IQueryable<Produto> qrIn, DateTime? teste)
        {
            if (teste == null)
                return qrIn;

            return qrIn
                .Where(b => b.DataInicio.Value.Day == teste.Value.Day &&
                b.DataInicio.Value.Month == teste.Value.Month && b.DataInicio.Value.Year == teste.Value.Year);
        }


        // aqui você ainda pode fazer views como a abaixo
        public static IQueryable<cvw_Produto> Vw_Produto(
                this IQueryable<Produto> qrIn, stackoverflowEntities ctx)
        {
            return qrIn
                .Select(p => new cvw_Produto 
                {
                    // você pode usar para fazer select em outras tabelas que tem relação com a tabela passada no parametro. IQueryable<Produto> qrIn
                    produto = ctx.SubCategoria.FirstOrDefault(sc => sc.CategoriaId == p.ProdutoId),
                    ProdutoId = p.ProdutoId,
                    Descricao = p.Descricao,
                    Preco = p.Preco,
                    Estoque = p.Estoque
                });
        }
    }
}