How can I extract DbFunctions/SqlFunctions call into a resuable extension/method with EntityFramework 6.2?

asp.net-mvc c# entity-framework entity-framework-6 linq

Question

I have an application written using C# on the top of the ASP.NET MVC 5 framework. In addition, I am using EntityFramework 6.2 as an ORM to interact with my data.

I wrote the following join statement using Fluent LINQ

List<string> names = new List<string>{"", ....};
query = TopLevelQuery.Where(x => x.EndedAt.HasValue && x.StartedAt >= startedAt && x.EndedAt.Value <= endedAt)
                     .Join(UserService.QueryUniqueRecords(),
                     entry => entry.UserId,
                     rec => rec.UserId,
                     (entry, rec) => new { entry, rec })
                     .Where(result => result.entry.IsEqualDateOf(result.rec.DayOf) 
                         && names.Contains(result.rec.Name))
                     .Select(x => x.entry);

However, I get the following error during runtime

LINQ to Entities does not recognize the method 'IsEqualDateOf', and this method cannot be translated into a store expression.

Here is my IsEqualDateOf extension method

public static class MyModelExtensions
{
    public static DateTime GetLocalDate(this MyModel entry)
    {
        var local = DbFunctions.TruncateTime(SqlFunctions.DateAdd("ss", entry.UtcOffset, entry.StartedAt));

        return local.Value;
    }

    public static bool IsEqualDateOf(this MyModel entry, DateTime dateOf)
    {
        bool isEqual = entry.GetLocalDate().Equals(dateOf.Date);

        return isEqual;
    }
}

However, if I convert my LINQ expression to the following pseudo, it works as expected

query = TopLevelQuery.Where(x => x.EndedAt.HasValue && x.StartedAt >= startedAt && x.EndedAt.Value <= endedAt)
                     .Join(UserService.QueryUniqueRecords(),
                     entry => entry.UserId,
                     rec => rec.UserId,
                     (entry, rec) => new { entry, rec })
                     .Where(result => DbFunctions.TruncateTime(SqlFunctions.DateAdd("ss", entry.UtcOffset, entry.StartedAt)) == result.rec.DayOf 
                         && names.Contains(result.rec.Name))
                     .Select(x => x.entry);

But, I want to be able to reuse the same logic in multiple places within my project, which is why I want to extract it into some kind of extension or method.

How, can I extract the DbFunctions and SqlFunctions call into a reusable method in which can be used in LINQ before AsEnumerable() is called?

UPDATED

I also tried to extract the logic into lambda expression by adding the following code to MyModel class

public class MyModel
{
    public DateTime StartedAt { get; set; }
    public int UtcOffset { get; set; }
    // ...

    public Expression<Func<MyModel, bool>> IsDateOf(DateTime dayOf)
    {
        return p => p.StartedAt.AddSeconds(p.UtcOffset) == dayOf.Date;
    }
}

Then I tried to consume it like so

query = TopLevelQuery.Where(x => x.EndedAt.HasValue && x.StartedAt >= startedAt && x.EndedAt.Value <= endedAt)
                     .Join(UserService.QueryUniqueRecords(),
                     entry => entry.UserId,
                     rec => rec.UserId,
                     (entry, rec) => new { entry, rec })
                     .Where(result => result.entry.IsDateOf(result.rec.DayOf) 
                         && names.Contains(result.rec.Name))
                     .Select(x => x.entry);

But that throws the following syntax error when trying to consume it

Operator '&&' cannot be applied to operands of type 'Expression<Func<MyModel,bool>>' and bool.

Moreover, I tried making the IsDateOf expression static and called it like so

query = TopLevelQuery.Where(x => x.EndedAt.HasValue && x.StartedAt >= startedAt && x.EndedAt.Value <= endedAt)
                     .Join(UserService.QueryUniqueRecords(),
                     entry => entry.UserId,
                     rec => rec.UserId,
                     (entry, rec) => new { entry, rec })
                     .Where(result => result.entry.Where(MyModel.IsDateOf(result.rec.DayOf))
                         && names.Contains(result.rec.Name))
                     .Select(x => x.entry);

But that gives me the following syntax error

'MyMode' does not contain a definition for Where and the best extension method overload Queryable.Where<MyModel>IQueryable<MyModel>, Expression<Func<MyModel, bool>>) required a reciever of type IQueryable

1
2
12/27/2018 4:19:44 PM

Popular Answer

Remove the parameter from your method and make the expression accept two parameters:

public static Expression<Func<MyModel, DateTime, bool>> IsDateOf 
    = (MyModel p, DateTime d) => p.StartedAt.AddSeconds(p.UtcOffset) == d.Date;

Note I have made it a static field, not a method. Then in your LINQ expression, you need to invoke it:

MyModel.IsDateOf(result.entry, result.rec.DayOf)

If you don't make it a field (or property), you have to first invoke the method to get the expression; then you need to invoke the expression:

MyModel.IsDateOf()(result.entry, result.rec.DayOf)

Which, in my opinion, looks weird.

2
12/26/2018 9:16:24 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow