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

Question

You can create custom extension methods for Linq to entities?

In the project I work, for example, we have several columns of type DateTimeOffset?. In certain views I need to filter the results by date, so I do the following:

var teste = DateTime.Today;
var bars = this._barRepository.All();
bars = bars.Where(b => b.DataInicio.Value.Day == teste.Day && b.DataInicio.Value.Month == 
                 teste.DataInicio.Month && b.DataInicio.Value.Year == teste.Year).ToList();

It is possible to create an extension method so that I don’t need to compare day, month and year every time?

What I tried to create:

public static bool SameDate(this DateTimeOffset? source, DateTime? date)
{
    if(!source.HasValue || !date.HasValue)
        return false;

    return source.Value.Day == date.Value.Day && source.Value.Month == 
        date.Value.Month && source.Value.Year ==date.Value.Year;
}

But he throws me Exception:

LINQ to Entities does not recognize the method 'bool Samedate' method, and this method cannot be Translated into a store Expression.

I found some answers on Soen saying to use DbFunctions.TruncateTime(), but I wanted to create my own extensions - if that’s possible.

Answer 1

You can create custom extension methods for Linq to entities?

Yes, but that’s 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 the preview, that is, if I make a Where(), the preview SQL Server will translate the extension to SQL with the best possible compatibility for SQL Server. The preview Mysql will do the same thing for Mysql, and so on.

Therefore, the correct way to intercede in the SQL generation process is to reimplementate the 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
}

And then, implement your SameDate within a 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 by correctly intercepting calls to the preview of the database. The general idea is here (question and answers).

Answer 2

Yes, it has a way of doing this. Itself Where of IQueryable is actually a method of extension, in fact, most of the filters you have in IEnumerable and IQueryable are extension methods. That’s why you need to add using System.Linq in your code, so that the 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 the IQueryable and yes above the IEnumerable, queries will be made in memory, so use IQueryable.

Answer 3

See the example below of 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
                });
        }
    }
}